Well, here it is the second Tuesday of the month – which means that it’s time for yet another T-SQL Tuesday… The time when the T-SQL blogosphere goes wild and all of the bloggers write a blog post about a topic near and dear to the heart of the host for the month. This monthly blogging party was started by Adam Machanic (b / t) nearly 6 years ago, and it has turned out to be wildly popular (well, depending on the topic selected by the host). And speaking of hosts, this month, the host is Jen McCown (b / t), half of the MidnightDBA team, and the topic that she wants us to write about is: “Strategies for managing an enterprise”. Specifically, she wants to know “How do you manage an enterprise? Grand strategies? Tips and tricks? Techno hacks? Do tell.”
My main Grand Strategy / tip is: Consistency. This comes across in several different ways:
- Every instance needs to be built the same way (when possible / feasible – there are always exceptions). The OS and SQL installs can be installed from configuration files – use them. Alternatively, if you are using virtual machines, you can have preconfigured templates with everything already installed in the template, ready to go. And let’s not forget using SysPrep. By using configuration files, all you need to do is change the server name. This involves OS level setup also… drives and drive letters, installation directories, security permissions, etc.
- Continuing with the setup, you probably have specific configuration actions that your company likes to have done on all servers. These should be scripted out so that the actions are, well, consistent. Not to mention that it’s faster to run this script than it is to manually perform these steps every time.
- Maintenance routines – you need to have processes for running your maintenance routines. Again, these should be part of your install script. The routines that you use should, at a minimum, perform database backups, integrity checks, and index / statistics maintenance. I’ll talk further about these later on.
There are tools, such as SPADE(Scripted PowerShell Automated Deployment Engine), that allow you to automate all of this (not just the SQL installation, but the subsequent scripts that you run also). I have also been seeing a lot of SQL Saturday sessions lately where folks are automating SQL installations with PowerShell – just scan the SQL Saturday sessions for what the speakers are doing.
Why is consistency so important? So that you know what is going on. Do you really want to waste the time to examine a server every time something is going on to find out how it is configured? Sure, you will have exceptions, but not only should they be rare, they should be documented.
The next main tip I have is to use the Enterprise (I’m talking about your overall environment, not the edition of SQL) features that are included in SSMS / SQL Server. Some of the features that are available will help you to:
- Easily work with a large number of SQL Server instances. In one enterprise environment that I worked in, there were thousands of SQL Server instances. Connecting to these one by one to do the same thing is, well, ridiculous. Not to mention very time consuming. You can use Registered Servers to have groups of servers. Alternatively, you can use Central Management Server (CMS) to have these groups (the main differences are that CMS uses windows logins only while registered servers can use windows or SQL logins, and to use CMS you need to set up a SQL Server instance as a Central Management Server to store all of the servers). Once set up, you only need to right-click the group, and select “New Query”. SSMS will then open up a query window that has a connection to every one of the servers within that group (including nested subgroups). When you run the query, it is run against all of the connected servers. Definitely easier than connecting to each server individually.
- Ensure compliance with company policies by using the aptly named Policy Based Management (PBM). This feature can be used to just report on the instances where there are policies that aren’t being followed, or it can be set up so that policies can be enforced – it will prevent you from making a change that is against that policy. If you are in an Enterprise environment, enforcing compliance via PBM will help you meet many of the auditing requirements that you may be required to adhere to.
- Ensure that all of the servers are using the same version of your jobs by using Multi Server Administration: A server can be configured to be a master job server (MSX), where jobs on this server are the master. Changes to these jobs can be made on this one server, and they be automatically pushed out to one or more target servers (TSX).
I mentioned previously about performing maintenance routines – specifically database backups, integrity checks and index / statistics maintenance. While there are plenty of other tasks that should be done, these are the absolute minimum. The first two keep your databases safe, so that they can be recovered in the event of something going wrong. The latter keeps the database humming along. Fortunately, there are several free tools available that can help you do these.
The most popular is Ola Hallegren’s Maintenance Solution – a T-SQL script that installs a few stored procedures which handles all three of these maintenance tasks. This solution is so popular, and endorsed by so many, that this has become the de facto standard for maintenance scripts. However, I have run into several issues with this solution, and I no longer recommend this solution for my clients, especially those in an Enterprise environment. Some of the specific issues that I have encountered are:
- Databases skipped.
- Indexes skipped.
- Logging skipped.
- Performance – it can be a lot slower than alternative methods.
- Does not perform checkdb against tempdb. Yes, this is needed.
- It can create a DNS denial-of-service attack. In that large environment that I mentioned earlier, we had to start all maintenance at the same time. This solution works by going to a command shell and then launching SQLCMD to connect to the server. When it does this, it needs to pass the computer name to the command, and the command needs to resolve the computer name. When running this for these thousands of servers, this solution would fail on many of the servers because the DNS server was flooded with requests. Here we have a self-induced DNS denial-of-service attack.
That being said, I would rather clients be using this maintenance solution than nothing, or even using Maintenance Plans for this maintenance.
Free and (in my opinion) better alternatives:
- Michelle Ufford wrote a utility for handling indexes. This solution is pretty robust, however it hasn’t been updated for index changes in recent versions (but do you really need to defragment a ColumnStore index when it makes the table read-only (non-clustered, SQL Server 2012-2014)) or you can’t have other indexes on the table (clustered, SQL Server 2014)?), and there are some minor issues with it. It was recently put into the public domain, and I hope to see folks pick this up and enhance it.
- And finally (in my short recommended list), there is Minion, a new product line by MinionWare that consists of two modules: Minion Backup and Minion Reindex (Minion CheckDB is in development). In my opinion, Minion is way better than anything that is currently available – it is the up and coming “Diamond Standard”, and this is what I now recommend to clients. It overcomes all of the issues that I listed above, and it is Enterprise friendly – all of the Minion products are designed around being administration friendly across an Enterprise environment.
- Write your own. This will be customized to do exactly what you need (not what someone else thinks that you need) – and in doing so, you will also be learning more about how SQL Server works.
In summary:
Have consistent installations (use tools such as SysPrep / SPADE to assist).
Use the available Enterprise features of SSMS.
Ensure that server maintenance (integrity checks / backups / index defragmentation) is being performed (use a tool such as Minion to assist).
