Friday, June 1, 2012

SQL 2012 Upgrade Part 1: The Test Environment

This post begins a series I will be doing on how we upgraded all the servers used by our BI Team to SQL Server 2012.

The biggest part of our SQL Server 2012 Upgrade Plan is testing. Although Microsoft hasn’t really changed a whole lot in what we do, there is still the off-chance that some minor change will blow up one of our solutions. I am pretty explosion-averse and was about to start pounding my head against a wall trying to come up with a way to test without impacting developers or users. Then I got lucky; one day I was chatting with a co-workers and he had a really great idea for how to create a test environment that would fit our needs.


Here are the requirements we laid out for our test environment to make sure it was as accurate a depiction of our live, production environment as possible.
  1. All Software Needs to be at the same service pack and patch level as the live environment.
  2. All Databases (Relational and Analysis Services) databases need be recent copies of live, production databases. I support multiple agile development teams so keeping the clones in sync with the production databases wasn’t very practical. Keeping the data in sync was definitely not a consideration.
  3. Each type of server (we have separate servers for each SQL Server BI product: Data Engine, SSAS, and SSIS) needs to be represented by a separate server in the test environment.
  4. Reporting Services is not managed by the BI Team (our SharePoint folks do a great job of handling SSRS for us) and is, therefore, out of scope of the 2012 upgrade.

Our solution was able to meet these requirements with the help of our crackerjack Windows Server Team. Our entire BI environment currently runs on VMWare with a fancy SAN backing up all the servers. Using their bionic technology, they were able to clone one server of each type (Data Engine, SSAS, and SSIS). This required a brief outage during a maintenance window. I don’t know the exact details but, somehow, they made copies of our servers with different names and we had an excellent starting point that satisfied requirements 1 and 3 and part of number 2. Getting the rest of the way was pretty easy.

The first, and probably the most tedious, step was to edit all the configuration files to make sure all the connections were pointed to the appropriate environment. Most of our “master” data sources were left unchanged but any connection that pointed to a BI server was changed to connect to the appropriate cloned server.

Copying the relational databases from the production servers that weren’t cloned was performed via the standard backup/restore process you would expect from SQL Server. If you have regular nightly backups you can even restore from those rather than making a whole bunch of “copy only” backups. If you are stuck with making your own backups for whatever reason, make sure you are using the COPY_ONLY option for any databases that you maintain transaction log backups on; you don’t want to break the backup chain!

Copying the Analysis Services databases was even easier. The Synchronize Database feature worked like a charm for one-step copies. Following the ASDB sync, we changed all the connections to point to the relational databases on our cloned server.

One thing that I might have done was copy all the logins from the relational database instances to the cloned instance. I thought about it but decided that our tests did not require them to be in place. I’ll go into more detail on the test plan later on in this series.

There are a few considerations you need to make when cloning your servers.
  • SQL Agent jobs are still there on the cloned servers. You should review each job and disable any that you don’t want running in the cloned environment, preferably before the cloning process. We found that one out the hard way and had some production jobs kicking off from two servers where we only expected it to be running from one.
  • Make sure you have enough disk space. Unless you’re not going to test all your databases, your cloned environment will need as much space as all your live servers combined. Thankfully, our server team understood that our needs were temporary and didn’t give me any trouble when I requested about 2 TB of space.
  • After the cloning process is complete, you’ll need to update any data engine instances to reflect the new server name. This is a simple process that is documented in the Microsoft Knowledge Base.

While our exact solution might not fit your environment, especially if you’re not virtualized or on a SAN, you might find some similarities in how you would build yours. What are some differences in your environment and how are you thinking about handling them? Please post your suggestions in the comments!

No comments:

Post a Comment