I’d like to say a few words today about consistency. Whether you are responsible for maintaining two SQL Servers or dozens, your job will be a lot easier if you can make things as consistent as possible across all your servers. It can be difficult, especially when your servers are running different versions of SQL Server. But if you take the time to standardize your scripts and processes across your servers, you’ll save loads of time when you have to troubleshoot something.

Standardization is the foundation of many large companies’ successes. McDonald’s food tastes like crap (in my opinion), but they are consistent. You can go into any McDonald’s in the world, order a Big Mac, and know exactly what you are going to get: two all beef patties, special sauce, lettuce, cheese, pickles, onions on a sesame seed bun. There is some comfort in that.

Similarly, consistency in your SQL Servers provides a certain level of comfort for a DBA. My maintenance plans are the same across all my servers. Sure, maybe some of the servers have databases that aren’t as critical as others. Maybe we can accept losing 6 hours worth of data in some places, but only 1 hours worth in others.  No matter. I’m taking transaction log backups hourly. It is so much simpler, when someone asks you “How often are backups being made?”, to say “Every hour” rather than “It depends. What database are you talking about?” Should you need to restore a database, it’s also easier knowing how many transaction log back ups you need to look for and restore.

I have a couple homemade scripts that run on each server. One of these scripts checks the fragmentation level of all the indexes in each database and, if it is above a certain threshold, rebuilds the index. (This is a modified version of the script created by Kimberly L. Tripp.)  The script recently failed on one of my servers. It turns out, the script, as written by Kimberly, did not handle tables owned by users other than dbo (or schemas other than dbo, in more specific terms). So I tweaked the script slightly to handle this. Even though this script was failing on only one of my servers, I deployed the updated script to all of my servers. Why? Because my changes amounted to two lines of code in a script of over 100 lines. If, 6 months from now, the script started failing on one server, but not on others, I would not want to have to compare all those lines of code looking for the differences between the two while troubleshooting. By keeping everything the same across servers, I eliminate one unknown from the list of potential problem sources. I have also just provided myself with a bit of insurance. Should some non-dbo schemas start being used on other servers, I won’t even notice. The job will smoothly handle them and I won’t get a job failed alert at two in the morning.

In some instances, scripts will need to use different commands, depending on the version of SQL Server they are running under. In that case, I still like to write one script, but I check the status of the @@version variable and include branching logic to run the appropriate command based on the server version. That way, I can still have one script for all my servers.

Another place consistency helps is the location of your reports. I have my scheduled jobs set up to write log files, which helps in providing details when jobs fail. If you specify a common place where they are stored, it makes finding them a lot easier. I don’t necessarily mean one directory that all servers write to, but one consistent place across servers. For example, I put my logs in a folder called LOGS in the backup directory. This is a different physical location for each server, but I know exactly where to go to find them, no matter which server I am on. I also like to put a shortcut to that folder on the desktop of all my servers for quick access. Again, consistency saves time.

Server configuration is another place where consistency can help. All my servers are (now) set to use mixed mode authentication. I did however, run into a server in the recent past that wasn’t. I wasted three hours tracking down the problem. I also like to configure all my servers to use trace flag 3226, which stops backup success messages from bloating your server’s event log. This isn’t a critical thing, but again, it’s nice to be consistent.

As your company grows and the number of servers you are responsible for increases, consistency will play a major role in reducing and simplifying your workload.