House of Brick Principal Architect

How much time are you wasting every month with your SQL Servers because of nonstandard server configurations? Sit back and add it up.

I’ll pause while you pick your jaw up off the floor.

Every server is different. They have different purposes. They could be from different vendors, from different purchase cycles in different years, and have different operating systems at different patch levels. Even identical hardware can have different BIOS and driver revisions. Windows and SQL Server can be wildly different with patches and Service Packs. How can you keep everything up to date when every configuration is unique?

Do you even try?

How standard are your SQL Server configurations? From an ongoing operations standpoint, as your servers converge towards a common configuration, you save time and reduce risk over the lifecycle of that server.

Want an example? Just look at Southwest Airlines as a prime example of standardization. The airline standardized on Boeing 737 aircraft for their entire fleet. By standardizing on one aircraft, they have one aircraft type to maintain and operate at all of their airports. Other airlines have many more types in operation. The maintenance crews can master just one aircraft, simplifying operations. Spare part stores can be kept at a minimum. You get the picture.

Why do I bring up this topic? Consider asking your server admins and DBAs to determine the number of distinct server configurations in your environment. Now, filter out those systems that require a special configuration (vendor requirements, SQL Server collation settings, security, etc.).

I will guess that this number is a bit shocking.

Now consider the time spent handling patches, firmware and driver updates, and routine maintenance that is tailored to each system. Every different configuration requires special considerations and care to manage properly, and each unique configuration requires its own amount of update rollback planning prior to each maintenance cycle. You DO have rollback plans, don’t you?

These numbers are astounding. This amount of time that you just calculated could be most of someone’s full-time job duties. Or more frequently, it could be the amount of time that pushes an employee into dangerous levels of overtime.

Where does virtualization fit in?

By now, you have read some of my posts and know that I am a huge proponent of virtualization. Virtualization can help you standardize your infrastructure. You can create a master SQL Server virtual machine template on any modern hypervisor, and use it to deploy your entire server infrastructure with the same standardized and pre-approved configuration. Of course, server customizations are normal, but the differences between your templates and your production deployments are few. The benefits of this practice can be tremendous.

Some benefits are obvious.

• Pre-approved templates mean guaranteed consistency with your server builds. The server was built to your standards going in, and you can guarantee that those standards are maintained with each server deployment.
• You also dramatically reduce your new server deployment time. Server deployments can now take mere minutes, rather than the weeks or months of a traditional physical server procurement and deployment cycle.
• You also eliminate the need to constantly monitor and upgrade things such as BIOS and hardware driver revisions. The only component that needs updating is the VMware Tools, and as of vSphere 5.1, those updates do not require a reboot.

Now, some of the benefits of template-based virtualization are much less obvious.

• Your maintenance and upgrade operation rollback plans drop from terrible, time-consuming processes each and every time you have to update or patch — down to just a few clicks, thanks to VM-level snapshots.
• The time and resources required to perform the inevitable hardware refresh goes to zero once the VM migration has been completed. Simply hot-migrate (via vMotion or Live Migrate) the VM to the new hypervisor hosts and you are done! This act alone will pay great dividends across the organization if you factor in staff time into the ROI calculations.
• You can now hot-clone a production server to an isolated network and test various configuration changes (and witness the effects) on a common server configuration, thanks to VM-level cloning operations. This task can reduce the risk of unexpected impacts of configuration changes on those servers with common templates.

Now, how do you handle standardization when your business treats server configurations like it is the Wild West?

• The business must sign off that these templates will be used across the board, and that one-off configuration changes be documented once they are approved.
• The business should also standardize on common configurations, policies, pre-installed tools, etc. Some businesses have different organizational units that each have their own server build standards. Physical and virtual server sprawl can turn into template sprawl if not kept in check, and the goal of VM standardization goes right out the window.
• Audit server configuration changes periodically, and check your audit history for compliance. Anyone sneaking configuration changes onto a server without going through a change control process should be discovered.
• Patch as needed, but try to keep the deployed servers as close to the same patch level as possible. This keeps the server configuration close, and makes testing patches and updates that much easier.

Keep your server builds as close as possible, and watch the time spent on rote operations drop! Your administrators will thank you for it.