SQL Server MVP Deep Dives 2, Chapter 7
When first reading this chapter all of the advice seemed like common sense, but much like common sense, good testing is not so common. Allan outlines what the ideal situation should be for testing environments along with what things should be tested and how to manage change. He lists the four typical environments, Development, Testing, Staging, and Production along with good descriptions of each. My favorite point in this section was when he described a separate environment just for the administrators to do their own testing. This way when a new patch needs to get added or a new application on the same server it can be tested without interfering with the developers. This is probably the easiest environment to create because the data does not have to stay as tightly in sync. I have worked at places where we created and deleted virtual servers when we wanted to test these types of changes, but we didn’t have a dedicated environment.
As I read the chapter it was hard not to compare Allan Hirt’s outline for SQL Server testing environments to the set up at the different places I have worked. I don’t think any place has gotten it quite right, but each company had its own challenges. When I worked at a small company we could not afford four different environments to migrate changes through. Eventually we did manage a testing environment so we were not making changes to our production databases, but only after years of rolling the dice. The testing environment we maintained required a lot of effort because of the restrictions of the third-party application we ran on top of SQL server, but it saved us a lot of pain by finding errors that both we and the vendor would have moved into production with high levels of confidence. I have also worked at larger companies that have all four environments, but they struggle with how they should be used.
After going over these environments and listing out what things need to be tested (restore your backups, test disaster recovery plans, relevent processes, application functionality after updates, and specific hardware configurations before installing SQL) he bravely takes on the topic of change control. As with testing set ups I have worked with different levels of change control. Some work well, but other systems just inhibit getting things done (says the developer in me). Mr. Hirt recognizes that change control needs to be done correctly.
Heed this warning: change control should be a transparent process that’s simple and easy to follow. For all of its benefits, change control can also hamper a company and have the opposite effects of the benefits it should provide. – Chapter 7, SQL Server MVP Deep Dives 2
In addition to change control he states that all of the systems should have baselines and standards should be applied to all of the processes around these items (testing, change control, etc.) Here he mentions looking at the Microsoft Operations Framework (MOF), which may be overkill for some smaller shops, but is a great starting place to develop standards and processes. The MOF is something I had not seen prior to reading about it in Deep Dives.
This chapter makes for a great set of goals for a new DBA at a small company and much like many of the items in Deep Dives I wish I had read it ten years ago. The concepts he talks about here have not changed much in that time. Virtualization makes implementing some of these ideas easier, but they are solid methodologies that will most likely always be true.
Chapter Seven SQL Server MVP:
Allan Hirt (B|T) is a consultant, published author, speaker, and trainer who has been using Microsoft SQL Server in various guises since 1992. He’s based in the Boston area and for the past 10 years has traveled all over the world to work with and train clients. His most recent book is Pro SQL Server 2008 Failover Clustering (Apress 2009), and he is currently working on a mission-critical book for SQL Server 2012.