You’re a DBA, you should know these

  • How many databases are you responsible for?
  • How many different version do you support?
  • How much data have we handled this month?
  • How much capacity will we need next year?
  • What new IT techniques will help you get there?

These are some of the questions people from business will tend to ask a DBA, and as soon as such questions are thrown at you, all that comes to ones mind is metrics. These metrics help you answer a lot of questions for the business people and help you get ready for any actions to be taken. For example, when you know about the different versions that you support, you can easily identify when you have to upgrade a server. Another example would be if you know how much data you handled this month, or How much capacity will you need next year, then you can easily ask for a specific amount in the budget that you might need to handle next year’s capacity. Remember that most business folks know what you do, but they don’t necessarily know how you do it. All they understand is numbers that relate to them and you need to be ready with a set of answers for questions as above when thrown at you. You don’t want to be searching for a way to get those answers when asked, but be ready with the answers.

Is every database being backed up?

The answer to this question must be a definitive yes, and nothing but yes. But saying yes isn’t enough, and it’s best to prove it with information coming from MSDB.DBO.BACKUPSET. From here, you can get all the information that you require to answer the above question. Even so, that is not all. For example, Do you know, if you are backing up to a folder on disk, are your backups making it to the tape? It’s not enough to know that you’re backups are going fine. You need to know that their backups are going fine as well. And by saying their , I mean the sysadmins. Are they completing the tape backups properly or not. There is a neat and easy way in windows to do this and almost all tape backup software supports this feature. All you have to do is switch to the details view in the file explorer and select the attributes flag for the files to be seen. You might see the files having the flags ‘A’. This means that these files are ready to be archived and sent to tape. Now whenever a tape backup software checks through these files and moves it to the tapes, most of them have a way to clear this flag and unset them. So, a file that has been moved to the tape, will not have this flag setup as ‘A’. If you are not seeing the flags unset, then its best to talk to the sysadmins and configure the tape backup software to unset the flags once the files are moved to the tape.

Will SQL Email us when something goes horribly wrong?

Again, there should be no other answer to this question than a definite and a confident YES. To do this all one needs to do is to setup the following:

  • SQL Server Agent
  • Database Mail
  • Operator
  • Failsafe operator
  • Alerts

If you have all these configured, then at times when something goes wrong with the SQL Server, then you will get an email. But what if, the SQL Server Agent goes down, as SQL Server Agent is the one to check for anything going wrong and send an email. For this, you can use any 3rd party freeware tool available, to healthcheck on the SQL Server agent. Or you can use the healthcheck tool that you currently use to check in on the status of SQL Server agent. Again, when configuring the above, its best to use distribution lists and not individuals, so that in the absence of one person, someone else can respond to the issue at hand. Also, know that these SQL Server agent alerts are not your best option, but they are free so always include them as a part of your strategy.

Find out if any of your databases have a different collation before it’s too late

Collation can be a headache, if they are unknown to you, or if you know about it too late. This is because, in SQL Server, collation can be server level, database level, schema level and even objects level. So if there are multiple collation in a single server. It would make your work a lot easier if you knew about all those different collations, before doing any work in production machines. Imagine a scenario where your database is in collation A and your server is in collation B. Now you go ahead and create a temporary table and insert data from your database. Which collation do you think the data in the temporary table will be?

The answer is, it will inherit whatever collation the server is in, unless the temporary table that was created has been explicitly assigned a collation in its DDL. And this messes up a lot of things especially sorting and storage in SQL Server.

Are you sure all of our servers are set up right?

There is no second answer to this question. It’s always the same, and must remain that way. And that answer is a big overwhelming YES. This is something that you as a DBA must answer with a yes and to prove it, you must be ready to provide supporting answers in a jiffy. Now you must be thinking, I have tons of servers that I handle, how can I query all of them at once and provide answers to the stakeholders. Well, there are a lot of ways to do that, but the most easiest and convenient way to do it would be through server registration and central management server in the SSMS. You can group all your servers into categories like production, DR, development, etc and even into versions, as there are usually differences in the DMV query output in the version when you do a SELECT * in any of them. Having said so, once you have this setup, you can easily fire a multi-server query and get your results, that can help you know if your servers are all setup right or not.

Finally, can you get answers to all these questions really fast?

When business people come to you with such questions, they want answers, fast. They don’t have the time for you to query the results and make them wait. When they say now, they mean it. So how do you go about getting all these answers in a moment notice, so that you are ready to splash out the metrics to the business people and support your requirements or requests for budget or hardware or anything that you need to support proper working of the system.

Here is something I personally use and have been using for a long time and find it ever so more an arsenal in my code repository to get my metrics right.

Brent Ozar’s sp_Blitz

This was a life saver for me when I began my career as a DBA, and gave me information about my servers that I wasn’t even aware of. This way I avoided a lot of scenarios that could have gotten my servers in a whole lot of trouble and now it’s currently helping me in providing the business people the metrics they ask for and it’s also helping me to fine tune my servers for better operation.

What other things do you think a DBA should know, other than the ones that I listed? Let me know your thoughts on this one in your comments below. Tune in until next time.

This entry was posted in Best Practices, ms sql, Uncategorized and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s