In this blog post, I would like to discuss the most important SQL Server best practices. I thought I might start off with 10 best practices I reckon would be any DBA’s most important maintenance best practices. And these are only the basics. I’m not an SQL expert like many of the MPVs and other professionals who have devoted their entire career to SQL Servers; however, I just wanted to share with you some common best practices.
I’ll discuss some security basics, data and log file management, tempDB management, the importance of SQL Server documentation, changing autogrowth default settings, why regular backups mean the world to your company’s data and why patching your SQL Servers is crucial.
Security
What you must have is a trusted person whom you can give permissions to manage SQL Servers. This is important because you don’t want to have everyone snooping around and accessing corporate data. I would also suggest you encrypt your backups and allow only certain users to access it. You don’t want anything happening to the backed-up data.
Ransomware is really popular these days so be sure to have backup on a hidden and secure location where not everybody is able to access it. For example, keep it on a shared drive instead of a mounted drive and allow access only to your DBAs.
Moving on to authentication: When connecting to SQL Servers, I advise you to use Windows Authentication instead of your system admin account (SA). Additionally, try not to use the SA account to connect to web applications on SQL Servers.
Do not share the SA user account information with everyone. SA access should be reserved for your fellow DBAs.
If someone insists on having DBA permissions, please inform them that they can take over server administration and be in charge of backing up corporate data. See how they like that idea ;) I bet no developer would accept such a proposal.
Be sure to change passwords every once and a while and audit file logins regularly to spot any irregularities. Changing passwords too often is a whole lot of work—you need to do it from time to time, but don’t overdo it and change it every few hours.
Data and log management
Make sure you have a dedicated drive for SQL server installation—one for MDF and another for the LDF log files. Don’t just store them on different partitions—each one needs a dedicated physical drive. That’s the most important advice I can give you.
You see, when you have them on the same physical disk, it tends to run slower. That’s because the writes on the disk will occur at the same time; in fact, they go from writing log files to writing data files, and the process goes randomly back and forth.
If you’re using an SSD drive, it’s not a big issue; however, the majority of people still have physical disks that run on plates. This is because SQL databases can be quite large and SSD drives can be expensive; actually, very expensive when you need tens of terabytes.
If the databases are not very active, they can be on the same drive as well. In fact, it all depends on the number of databases and their activity.
Shrinking database files
The usual best practice is that you should avoid shrinking database files; however you should differentiate bwtween when shrinking is good and when it’s simply not recommended. I would recommend not shrinking data files on active databases because once the shrinking begins, all other transactions in the database are stopped. Be really careful about what you want to shrink and WHY!
If you have the urge to shrink the files just to save some space—don’t. Restrain yourself. It’s a bad idea. Instead, make a plan for database growth. Shrinking can heavily fragment the index and reverse the order of the clustered index by taking it from perfectly fragmented to defragmented, hurting the database’s performance. Shrink files if you really must—not the database itself!
You can shrink databases that are not so important, such as the non-production database, development or UAT perhaps. Maybe even shrink them if you need to free up disk space. For example, if you’re dealing with a database that’s not that important and you need to have a simple transaction log instead of a full one, it’s perfectly okay to shrink it.
TempDB management
The default tempDB database size is set to 8 MB, with a 1 MB transaction log file. This is quickly used up so you have to deal with overgrowth.
Set it to grow in larger increments. Of course, test that as well to see how much your tempDB is growing and then set the size accordingly. Know that tembDB sizing operations, especially if small, will generate a lot of the IO on the storage, so be careful with what you’re doing.
Always place the tempDB on the fastest storage possible because that will be the busiest database on your server. Also, remember to have one tempDB per CPU core. In small environments, have it on a SSD and if you have a storage guy for multiple LUNs, use a different one for each tembDB.
Make these changes only for the production server; for the test server and UAT or QA, the default settings are just fine.
SQL Server documentation
You might say that gathering and creating comprehensive SQL Server documentation is tough, boring, and what-not, but I tell you, I doesn’t have to be.
The important thing every experienced, hardcore DBA will tell you is that SQL Server documentation is a must-have. Now let me tell you why this is such an important thing. If nothing else, it’s always good to have an internal IT infrastructure.
What must a proper documentation contain?
- details about all your SQL servers
- list of databases, their sizes, information about various database files
- general SQL configuration settings
- important data about server administrators, user privileges, and DB owners
- comparison of your settings with industry best practices
Check out an awesome tool that can help you in this regard.
I was heavily involved in its planning and development :) SQLDockit is the name. Send me your feedback on it—we have tried to cover everything I am discussing here.
Change default autogrowth settings
Default autogrow is 1MB, and 10 percent for log. If the truncation you run over the database requires more space than you have free, the transaction will take as much as the grow + the transaction, so the truncation can time out if the autogrow is too large. I would recommend setting increments in the MB, not the percentages. Of course, there is no silver bullet in terms of what numbers you can enter here because it heavily depends on the size of the databases.
Monitor the growth of the databases over time and plan accordingly for setting correct MB value for the autogrow. (the tool I mentioned up there, SQLDocKit, is also handy). This will help you track your database size over time and forecast database size based on snapshots from its history).
Why running antivirus and antispyware is a bad idea
You may know that you need to pick a different antivirus solution if you’re running it in an environment with SQL Servers. As you know, running antivirus on production SQL Servers isn’t the brightest idea—at least exclude the data and logs files or/and folders.
For many reasons, it tends to cause problems in production and can even make certain files unavailable when you need them. It’s known to overload the SQL Server CPU. Another reason you shouldn’t insist on installing antivirus software on SQL Servers is that they don’t seem to protect you from actual viruses and potential threats.
If installing antivirus software is necessary, you should try to manage it and your servers in such a way that they don’t interfere with one another.
Backup, backup, backup so there will be no tears later on
Before you start, you must have a strategically planned option even for the worst-case scenarios, at least for the most important databases.
You can always schedule automatic backups, however, important corporate data should be handled and backed up manually as well.
Data loss is, of course, unimaginable, and for this reason you should backup your data regularly.
Take special care of backing up transaction logs, because if you do not backup it, transaction log in full mode will just grow depending on the database activity so you can restore to any point in time from the transaction log only. BTW, the tool I mentioned will show you the last date of the DB backup as well.
I would suggest that you don’t store backups on the same physical disk as your database files because if something goes wrong, you will lose the disk and the backup along with it. Cloud storage is pretty cheap these days so store the backup cheaply in the cloud if you do not have a lot of space on prem.
And another wise thing to do is to TEST YOUR BACKUPS. How many stories of non-working backups have you heard? :)
Regularly patch your SQL Servers
Your most important concern should be to regularly install service packs, security patches, and cumulative updates. Before you can do that, you must keep up with the latest patches out there and the support cycle for your SQL Servers. Then you can go over the process of patching an SQL Server. The tool I told you about will help you check server patches and it will tell you if your server is not up to date and, what CUs or SPs you need to apply.
I think I covered pretty much the most important best practices, but in case I left something out, leave a comment and I’ll be happy to discuss it with you.
