To add some much-needed humor at the moment I thought I would do a post about ten comments I think every SQL Server DBA wants to say. In addition, a brief explanation about them.
To make things easier I have also added links to them below. So, if you want to you can go straight to a specific one instead.
- I’m glad I automated this.
- So glad we test our backups.
- Thanks for paying for my training boss.
- Glad we have reasonable maintenance windows.
- Good job you ran this query in a transaction.
- I’m glad our vendor allows us to update easily.
- We ensure everybody works by least privilege.
- Really happy that our maintenance jobs don’t overlap.
- We proactively monitor this system so I will see what is happening.
- Luckily we have the Database Scoped default settings set.
In reality, a lot of DBA’s out there already know the feeling of joy when you finally automated a task that is fairly complex. If you’re not one of them and you are still running a process manually that you have to do more than once I highly recommend you look into doing it.
Of course, I’m aware there’s always of the argument that it can take a long time to automate something. However, you will reap benefits from doing it.
In addition, a lot more companies are looking for SQL Server professionals with automation experience these days. So, it is definitely a skill worth having.
Now this is something that every DBA should do, or at least persuade whoever is responsible for backups to do it.
Otherwise you may find yourself in a situation where a database is corrupt and a restore is not possible. Which means that you have to try and recover the database using other methods like the one here.
I can tell you from experience that this is definitely not the best situation to be in unless you enjoy working for over twenty-four hours straight. So, if your backups are not being tested at the moment then I highly recommend you change that.
Developing your employees is essential for any company that wants to survive. In fact, investing in your employees is essential.
If an employee is paying for their own training, I can assure you it’s only a matter of time before they go to work for a company that invests in them.
In order to do essential database maintenance, you must have reasonable maintenance windows.
For example, if you have large databases and only a couple of hours a week to run DBCC CheckDB and perform other database maintenance tasks it’s going to be a struggle.
Of course, if you are in this situation there are some things that you can do to help. For instance, you could look to separate the tasks to run at different times during the week.
Now I’m sure most SQL Server professional have encountered an issue at some stage due to some SQL that was not run in an explicit transaction.
For example, running a delete statement in Management Studio with the where clause not being selected and having to get the data back from elsewhere.
In reality, this can cause a lot of issues so try to encourage explicit transactions. You can read more about explicit transactions in detail here.
Now this is definitely one that causes a lot of SQL Server professionals’ headaches.
Imagine the scenario where the latest version of SQL Server has been available for a while. You’ve tested it and you’re very keen to upgrade your servers.
Only to find that a vendor does not support that version of SQL Server yet. So, you’re stuck with an older version of SQL Server and unable to take advantage of the new version until they support it.
Luckily, Microsoft are aware of this and are encouraging vendors to support the database compatibility level and not the version of SQL Server.
You can read more about Microsoft’s stance on this in detail here.
On a side note, there is another issue relating to this. Which is that some vendors will only support their application if it is running on the same server as the SQL Server instance. A lot of these recommendations are due to legacy reasons.
So, if you work for a vendor and you read this maybe you should check if you really still need this. Before your customers decide to look at other products which offer more flexibility.
Now this is definitely one ideal situation that every SQL Server professional wants. In reality, nobody wants to walk into a new place to find that everybody is sysadmin on SQL Server instances.
Especially if the databases in question need to be audited for one reason or another. For example, databases that need to be audited for PCI-DSS.
Of course, there needs to be a healthy balance with this. Developers still need enough rights in order to deliver new items. Which is why it needs to be by least privilege and not a total lockdown on all instances.
Even some vendors realize that their service accounts no longer have to be sysadmin anymore to work with their databases. Hopefully others will realize this over time.
Now I’ve seen this happen on servers in the past. For instance, you get complaints that maintenance jobs take too long and when you look you see multiple jobs are scheduled to run at the same time.
For example, DBCC CheckDB, backups and reindexing all trying to be done at the same time. So even doing something simple like having them run one after the other can make a big difference.
Now this can be an interesting situation. If you don’t monitor your databases you have no way of knowing what is going on.
So, even if you start monitoring the database after an issue has being reported investigating the problem is hard. You should always aim to monitor as many of your servers as possible, especially the customer facing ones.
In fact, a big part of using deployment pipelines to deliver database updates is fast feedback. With this in mind, monitoring your databases will allow you to get the fast feedback that is required. Instead of a phone call from a customer.
On a side note, I got asked recently what free monitoring tool I recommended. My answer was sqlwatch, which you can read about in detail here.
In SQL Server 2019 there is a new database Scoped configuration setting for online index rebuilds. Which means that if somebody runs and ONLINE INDEX REBUILD statement it will try to do an online index rebuild by default.
Now, I have heard some horror stories in the past where people have started index rebuilds and have forgotten to include the online option. Which has caused performance issues due to blocking. So, if you use SQL Server 2019 this setting can help reduce these issues.
I discussed some Database Scoped configuration setting for indexes in an old post. Which you can read in detail here.
I hope you enjoyed reading ten comments I think every SQL Server DBA wants to say.
Of course, there’s plenty of other statements I’m sure some of you would prefer to say. With this in mind, feel free to comment what they are below.