T-SQL Tuesday #89 Invitation – The times they are a-changing

TSQL2SDAY-150x150This month’s topic by Koen Verbeeck (B|T) is based around times that are a changing. To break it down somewhat it was inspired by a blog post that Kendra Little (B|T) put together around Will the Cloud Eat My DBA Job. Koen is wanting to know with the ever-changing world of technology what kind of impact has it had on you and how do you plan to deal with the future of data management/analysis.

To The Cloud

I think one of the topics that I’ve seen a gradual change in is the topic that revolves around the cloud. Being in the financial district cloud talk is not always welcome. It is a myth to some; I am glad that a while back I headed some of Grant Fritchey’s (B|T) advice in that he said you better start learning cloud techniques sooner than later. The cloud discussion is not always a welcomed one, but is one that needs to be had to keep up with innovative technologies.

Finding what is the best solution for you and your respective area the cloud does allow for flexibility and control. One of the main issues I see most shops running into are security based around a cloud model along with costs in data size etc.

With the proper planning and oversight the cloud is a viable option that should not scare away data professionals

Third Party Utilities

I think over time some of the third-party tools have become game changers in a lot of shops. I see vendors such as SQL Sentry and Red Gate that have evolved over time to help streamline and provided better efficiencies around data management and monitoring. Cutting edge keeps the users of these third-party tools on edge and wanting more. Tying all these into automation of daily tasks and not just on premise monitoring but cloud monitoring has been a huge plus for the community.

The Way Business Interprets Data

Data is what drives us; it is what a lot of decisions are derived from and direction of companies and shops. The data is ever-changing and how we look at it. Take for example Power BI. The methods we used years ago have morphed into a greater approach of delivery to businesses. I never thought I would be watching a professional sports game and see them pull out their tablets and review live data in between innings or set of downs.

PowerShell

No, this isn’t a tribute to Mike Fal (B|T) or Drew Furgiuele (B|T), but I do appreciate their nudge in getting me to utilize PowerShell for some of my every day usage. This could fall under the third-party utilities section up above but I thought it beneficial to state that in some cases it has been a game changer. Stumbling upon the DBATools website has been a blessing in disguise; I love getting to work with technology that I may have not utilized as much in the past.

Do You Feel Endangered?

No, and neither should you. I should paraphrase that with don’t be afraid of change for it allows us to learn new technologies and grow on our journey. There will be opportunities to always learn; each day you should strive to learn something new that you didn’t know before. A wise SQL community member once told me, “The data will always be here; will you?”

T-SQL Tuesday

For those that are not aware T-SQL Tuesday is a block party started by Adam Machanic (B|T). Each month community members who blog pick a topic then gather all the blogs who participated in the event and provides a recap. It’s a great method to share knowledge and an avenue to give back to the community. If you are an avid blogger and would like to be a host then do reach out to Adam via the methods provided.

T-SQL Tuesday #83: We’re still dealing with the same problems

tsql2sday-300x300It’s about time I got back into participating in the T-SQL Tuesday block parties that are hosted by community members each month. First, let’s take care of some house keeping rules.

Who Is Hosting?

This month Andy Mallon (b|t) is hosting and has chosen a topic of We’re still dealing with the same problems. This topic can have very many avenues to go down; with that said Andy….great topic sir. I suggest you stop by Andy’s blog when you get a chance; he has some great posts along with Shortcut’s Cheat Sheet that I ran across the other day.

So what are some of the same problems?

Again, we can take this path down several different roads. We can get extremely technical or we can get extremely general. For the sake of time and having you in mind as I type I may sprinkle a little of both into this post.

Professional Level

From all different levels of a database professional’s life one can see repetitive things being done:

  • Resume fluff – by this I mean interviewees having a little to much fluff on the resume that has to be weeded out.
  • Dev/DBA/Infrastructure – do I need to expound? The age old silos that are often built with blame moving to and from each unit.
  • Overworked personnel – being in management my team is key. It’s time to look at the people as that, people, and not just a number.
  • Routines – we all get stuck in a routine at times; how do we get ourselves out of so called “ruts” and light the fire that we once had?
  • Meetings – this topic can have its own blog and how to handle them. These are important in some cases and in some cases they aren’t, but the fact remains I still see a lot of shops with an endless supply of meetings.

SQL Level

Up next are the SQL scenarios I still see as ongoing battles:

  • Max Memory Setting – when installing SQL please check this setting.
  • SA – every vendor wants SA for their app; it’s how the world goes round.
  • Backups – yes I take backups….that is great news; have you ever restored any? Nope…..you see where I’m going with this.
  • Trace Flags – I see a lot of people not taking advantages of these across all SQL platforms.
  • SQL Versions – a lot of shops are not keeping up to date with their SQL versioning as often as they should.

Automation

You may ask why I put this topic in a section all by itself? I learned earlier on from John Sansom (b|t) that automation is key to becoming a successful DBA. I don’t see enough of it really? There are a lot of new tools and methods that are available to data professionals that, if taken the time, can be set up to automate a lot of the mundane tasks I see small to medium shops experiencing. Heck, even some bigger shops still struggle with the art of automation.

Professional Development

Anyone who knows me knows I like to challenge myself. Each and every person has their own set of goals and desires as they move through life and no one else can define that. I see over and over again where we tell people that they need to do this and need to do that. No, I’m not talking about mentoring or leadership. Those are separate entities; this section is meant to encourage the readers of this post that you control where you want to go in your career and not the other way around. Some knew technology comes out and you want to learn it then go for it.

What Is T-SQL Tuesday Anyway?

Well, I’m glad you asked. Adam Machanic (b|t) started this party about (I’m approximating here) 7-8 yrs ago. Each month a new blogger is chosen to host the party and they in turn get to pick the topic. If you blog about SQL and have been keeping up to date with it for a while now then drop Adam a line and let him know you are interested.

Conclusion

As you can see there are still items that occur all around us on a daily basis in the same manner. I challenge you to see, if what, can be changed. Where can you make a difference? Time to get after it ~ Let’s Roll.

Thanks Andy for taking the time out of your day to host this month.

T-SQL Tuesday #080–Round Up

TSQL2SDAY-150x150The roundup is finally here, and cheers to all of you who participated. We had a great turnout this month with many returning participants along with some newcomers.

We had a wide range of topics with many great insights from everyone, but don’t take my word for it. Check out the links below and see what your colleagues from around the SQL Community had to say:

 

Jason Brimhall – SQL Server Desired Enhancements

Riley Major – The Gift that Keeps on Giving

Jon Morsi – SQL Server 2014 Service Pack 2

Melissa Connors – DBCC CloneDatabase

Aaron Bertrand – Create Or Replace

Lori Edwards – SQL Server R Services

Robert Davis – SQL Birthday Present

Allen Kinsel – SQL Server’s Birthdays

Matt Gordon – Change Always On Endpoint Ports

Bjorn Peters – My Open Points for SQL Knowledge

Chris Bell – The Gift of the Basics

Andy Yun – Give Yourself a SQL Gift

Kenneth Fisher – Default Database File Sizes

Dave Mason – SQL Server Shutdown Event

Shane Eillis – Can Powershell Get What T-SQL Cannot

Raul Gonzalez – Using the Query Store For Read Only Database

Rob Farley – Finally SSMS Will Talk To Azure SQL DW

Kennie Nybo Pontoppidan – I want DBCC ConeDatabase Available on all (supported) Versions of SQL Server

Ginger Grant – SSIS Projects, Packages, and Deployments

Mike Walsh – Happy Birthday Chris, Have Some Changes

Wayne Sheffield – SQL Gifts

As you can see we had a great turnout and everyone continues to share their knowledge daily, weekly, and monthly basis. Thank you again for all who participated, and a big thank you to Adam Machanic (B|T) for allowing me to host another block party. Hope you all have a good one and look forward to next month.

images

T-SQL Tuesday #080

TSQL2SDAY-150x150T-SQL Tuesday time is here again! And I’m honored to be hosting this months block party. Why you ask? T-SQL Tuesday #80 happens to fall on my birthday believe it or not!

With that said let’s get this party started and have some fun with it this month shall we. Treat yourself to a birthday gift and come up with a present for yourself SQL related – no limitations:

  • Is there something you’ve been wanting to do for a long time in SQL, but haven’t done it yet?
  • Perhaps there is a feature you would like to see added into SQL that just isn’t there yet – what is it?
  • Maybe you are a consultant and know something many of us don’t as you parachute in and out of shops that a reader can benefit from?
  • Is there a co-worker struggling with something SQL related; here is your chance to share the present (knowledge).

Gift wrap your post for yourself and be innovative with what you write about.

images

Some Ground Rules Apply

The post must go live on your blog between 00:00 GMT Tuesday, July  12, 2016 and 00:00 GMT Wednesday, July 13, 2016.

Link your post back to this post and make sure you include the T-SQL Tuesday logo in your post.

Leave a comment here in case that ping back doesn’t quite work out I’ll still be able to find you. Once all the posts are submitted I’ll circle back around and publish a roundup. Enjoy this one and have some fun with it. Be creative; after all….it’s your gift.

Get after it; Let’s Roll

Yates

T-SQL Tuesday #75 Invitation: Jump into Power BI!

SqlTuesday_thumb.pngThis month the talented Jorge Segarra (B|T) hosts the T-SQL Tuesday block party, and he has chosen the topic of Power BI.

So, what exactly is this thing called Power BI? I admit, I have several friends and colleagues who have dove into this type of technology, but I have not done so in great depth yet.

I think Jorge pretty much summed me up in his first paragraph when he says, “If you’re reading this and thinking ‘crap, I’m not a BI person!’, don’t you fret. My intention is to make folks who normally don’t use BI on a day to day basis try their hand at creating cool new visualizations and reports with Power BI and seeing how the other side lives.”

So what did I do? I think the below tweet sums it all up:

image

I downloaded the Power BI Desktop from Jorge’s link and was immediately amazed by the simplicity of ease in working with the utility. I had some dummy data laying around for drive space so decided to hook up to the data source and start messing around with it.

The data set populated through some clicks and a query provided by me led to the data set being available. Great; next what caught my eye was the visualization section; which it would, right? Everyone likes a good picture with analytical data attached.

image

Now, in my data set I am capturing dummy data, because we would never use prod data for a post, right? I have 5 drive letters (C,E,F,G, and H); I immediately went for the filters for the visual effects:

image

Once my filters were in place for the given time period I could quickly tell what I was dealing with from a visualization form of view:

image

In my short amount of time using this product I can definitely see a lot of usefulness that I could get from it. I am beginning to look at ways to incorporate this into some current presentations both in the shop and out of the shop that will better define my data sets to different groups (know your audience).

So Jorge, thank you for motivating me to dive into this a little bit. While I have only just scratched the surface with it, I see glimmers of how powerful this could be in my data professional journey.

What is T-SQL Tuesday?

T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was founded by Adam Machanic (B|T). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic.

T-SQL Tuesday #72 Invitation – Data Modeling Gone Wrong

SqlTuesdayT-SQL Tuesday is here again. I’ve had good intentions the past few times this event has come around and even have drafts still waiting to be queued up which I will eventually turn into regular blog posts, but I decided to just make time this month and jump back into the monthly party.

This month Mickey Stuewe (b|t) is hosting and has asked for some data modeling practices that should be avoided, and how to fix them if they occur.

What is Data Modeling?

Data Modeling itself is referred to as the first step of database design as you move from conceptual, to logical, to actual physical schema.

While that definition sounds simplistic, we can expound upon it to arrive to the conclusion that data modeling is a very important aspect from database design on all levels.

What to Avoid?

As a data professional and in senior management I’ve seen pit falls wide-spread in various business units when it comes to design architecture. The listing you are about to read are some of the methods and items I’ve discovered on my journey and conducting gap analysis type of events that carry a chain reaction. They consist of doomed failure from the get go.

  1. Audience – the audience and/or participants should be defined up front. I differ with many and that’s okay. To me the ability to identify business stakeholders, subject matter experts, technical groups, BA’s is an integral piece to the puzzle. Too many times I have seen the engine pull out of the gates with a design to only find out that the design and documentation to not even meet the criteria and standards of the shop.
  2. Detailed Project – how many times have you received documentation only to find out there was not enough meat to get the project off the ground? As a data professional we do think out of the box; however it is imperative to be clear and concise up front. When my team is given projects to complete that involve Database Design and creation, I implore business units to provide as much detail up front that is agreed upon. This helps streamline and makes for better efficiency.
  3. Understandability – With details comes the ability to articulate understandably. All to often items get lost in translation which causes additional work on the back-end of the database. This could mean unfortunate schema changes, large amounts of affected data, and so on.
  4. Business Continuity – ask yourself a question in design phase. Is what you are building that will be presented to the business efficient? Will business be able to decipher what is being presented back to them; if not why?
  5. Downstream Analytics – How does the business want to see this data in the form of analytics or reporting?  Most modern systems are going to either be queried by, or push data to, ETL processes that populate warehouses or other semantic structures.  Avoid complex table relationships that can only be interpreted by the code that stores the data.  Make sure you define all your data domains so that the BI professionals are not scratching their heads trying to interpret what a status of ‘8’ means. (In speaking with a colleague, Tom Taylor, at my shop – he brought up this valid point).

Items To Look For

Some key and general practices to look at and decide on are:

  1. Primary Keys – yes they are your friend – add them.
  2. Look at all audit data and what needs to be audited
  3. Clustered/Non Clustered indexes – have you read through your execution plan?
  4. Has the scope of the data model been met?
  5. Are tables normalized properly?
  6. One Data Modeling Tool – it’s easier if the team is looking at one utility together; if you have many varieties spread across many team members it could leave views skewed.

Conclusion

Data modeling, in and of itself, is a key component for any business. What often falls by the wayside is the poor leg work done up front. You have to lay a proper foundation in order to be successful with any design; taking into consideration all personnel in order to make the best strategic decisions to move forward.

Hopefully the next time you go down this path you have some questions to ask yourself along with some solutions to those problems.

What is T-SQL Tuesday.

Adam Machanic’s (b|t) started the T-SQL Tuesday blog party in December of 2009. Each month an invitation is sent out on the first Tuesday of the month, inviting bloggers to participate in a common topic. On the second Tuesday of the month all the bloggers post their contribution to the event for everyone to read. The host sums up all the participant’s entries at the end of the week. If you are interested in hosting and are an active blogger than reach out to Adam and let him know of your interest.

TSQL Tuesday #63 – Security

SQL TuesdayThis months T-SQL Tuesday is hosted by none other than Kenneth Fisher (B|T). His topic for this month revolves around security and how you manage security. There probably couldn’t be a more fitting topic; especially with the many breaches we have had lately both ones that are known and ones that are not known.

With that said I want to take this time to expound on a wider variety of topics instead of diving into specific targeted areas within SQL.

When I first heard this topic I immediately drifted to thoughts such as:

  • Afterthought
  • Responsibility
  • Lax
  • Validation
  • Vendor Apps
  • Breaches within
  • Password Strength

Afterthought

Countless times over the years I have seen, reviewed, fixed, and contemplated over security within SQL that simply was an afterthought. Security whether role based, AD Groups, etc. should be worked into any project plan. If you have ever inherited a system only to review that 600 users have sysadmin access you know how detrimental that could be to the data contained within.

Responsibility

Being a DBA means you have great responsibility. Every single database is under your care, own it. Each day someone will be trying to access that database; at least that should be your mentality especially with any production environment.

Lax

A lot of us are creature are habits. It is very easy for a data professional to fall into the trap of becoming accustomed to daily routines. Security should not fall into this category; I repeat security should not fall into this category. Do you know who has access to your databases and why? Do you know what user accounts are tied to specific groups? If you can’t answer this then you may find yourself in this category

Validation

I like this one, how many of us validate our security measures? Do we take any proactive approaches to see just how safe our data is? Maybe you rely on an outside 3rd party to see if they can hack in; whatever the case maybe it would behoove us as a group of data professionals to be actively testing our systems looking for points of entry. I will be completely honest; if you aren’t you can guarantee someone else is.

Vendor Apps

Yes, I am a vendor installing an app your company purchased and we will need sysadmin rights on the box or cluster. Um. yeah you go right ahead – NOT. I hope by now, as a DBA, you have strategies in place where you will work with the vendor directly or have some form of processes that allow for tracking of such activity. Remember, these databases are yours if you maintain them; you be the gate keeper not the other way around. Don’t let anyone on  your system without our knowledge and you better know what kind of data is on your system and who is accessing it.

Breaches Within

If you aren’t careful all your eggs will be in the “protecting from the outside syndrome”. Yes potential threats are rampant from people both stateside and abroad; with that said however have you ever thought about what maybe at risk within your own walls? Do you have safeguards in place for co-workers and fellow employees? Security cannot be just thought of with outside threats. No you need to prepare for both outside and inside threats. To make it even better if you are on a DBA team is your team being audited to keep everyone honest? The data should be your top priority

Password Strength

These little rinky dinky passwords aren’t cutting it guys. Ensure you are following best practices and standards when setting up password strength. The easier you make it the easier it is for threats and breaches to occur. Are the passwords on  your systems set to be changed every so often? But that would require a lot of work – yes and when you sign up to be a DBA or Data Professional you retain great responsibility.

Conclusion

Security is one place where you cannot be lackadaisical about. It is a crucial role within SQL or any platform for that matter that usually becomes an afterthought. If you are in a shop you should review your security guidelines and if you don’t have any I suggest that you take initiative and create some. Without proper security you ones business could be jeopardized and once  issues arise what would become of the companies reputation; or your reputation. Be proactive, make it yours, own it, and get it done.

TSQL Tuesday

This is a block party started by the renowned Adam Machanic (B|T); if you are interested in becoming a host one month and an avid blogger you can reach out to him via the methods above.