SQL Server DBA Tips that Can Save Your Ass

On November 9th I presented a session in the PASS Database Administration Virtual Chapter. The title of the session was “SQL Server DBA Tips that Can Save Your Ass”. Sharon Dooley, the virtual chapter leader, kindly modified the last word to “A$$”, but the important thing is what’s in it for the DBA – 10 useful tips that can help you do your DBA work better.

The session recording is available here, and you can download all the session materials (slides and scripts) from the Meeting Archive page.

There were 298 attendees, and I was told that this may be a record for a session in the PASS DBA Virtual Chapter. I didn’t expect so many attendees, and it was exciting for me to see the number of attendees going up, hitting 100, then 200, and then almost reaching 300. And there were also many great questions from the audience. Unfortunately, because there were so many great questions, I didn’t have enough time to go over all the materials, and also to answer all the questions. This session could easily span two hours, as many people wrote in the feedback. I spoke with Sharon about it, and hopefully I’ll be back for part 2 of this session sometime soon.

In the meanwhile, I would like to answer the questions that were asked during the session. I will order the questions according to the tips presented in the session…

 

Tip #1 – SQL Server Setup Best Practices

Question:

When you say “Install SQL Server on a Dedicated Machine” – do you mean dedicated physical server or dedicated virtual machine?

Answer:

In a production environment, it is recommended to install SQL Server on a dedicated machine, which is intended only to run the SQL Server instance. Don’t install other application, such as SharePoint or TFS on the same server. This will allow SQL Server to max resource utilization and perform at its best. Of course, it’s important to estimate the correct amount of resources (CPU, memory and IO), and install SQL Server on an appropriate machine. If you have the appropriate amount of resources, then it doesn’t matter if it’s a physical machine or a virtual machine. If you use a virtual machine, then you do need to know how to configure the hypervisor in such a way that your virtual machine does not share resources with other virtual machines.

Question:

Do you recommend to use domain accounts or managed service accounts for SQL Server services?

Answer:

Well, I got this one all wrong. My slide says you should use domain accounts. This is an old slide, and I haven’t updated the recommendation for quite a while. Sorry about that. But it’s never too late to fix it…

So before SQL Server 2012 and before Windows 2008 R2, the recommended approach was to use domain accounts. This allows for a central management of all SQL Server services across the domain, and it was more secured, because we had the ability to create a dedicated account for each service with only the required permissions. The alternative was to use local accounts (no central management) or built-in accounts (shared by many services and thus require too many privileges).

Windows 2008 R2 introduced Managed Service Accounts, and the first version of SQL Server to support it is SQL Server 2012. With Managed Service Accounts (MSA), you get automatic password management, which means strong passwords are set and replaced automatically, and you don’t need to deal with it. The other advantage of MSA is that it can automatically register an SPN for Kerberos authentication.

A single MSA can only be used on a single host, so it is not suitable for clustered environments, such as Failover Cluster Instances or Availability Groups. But if you’re running SQL Server 2016 on Windows 2012 and above, then you can use Group Managed Service Accounts (gMSAs), which can be used across multiple hosts.

So here is a summary of the service accounts best practice:

  1. Before SQL Server 2012 or before Windows 2008 R2 – Use Domain Accounts

  2. Standalone SQL Server 2012 and above on Windows 2008 R2 and above – Use MSAs

  3. Clustered SQL Server (before 2016) – Use Domain Accounts

Clustered SQL Server 2016 on Windows 2012 and above – Use gMSAs

Question:

Is Instant File Initialization (IFI) necessary with flash storage?

Answer:

Yes. It’s true that the impact of IFI is much lower with flash storage than with magnetic storage, but there is still an impact. And if you need to restore a very large database to a flash storage, then IFI can still save you a few precious minutes of downtime.

Here is a nice blog post by Erin Stellato showing some numbers.

Question:

How important is it to set the min server memory as well as the max server memory?

Answer: