Tuesday, June 5, 2012

SQL 2012 Upgrade Part 2: Test Plan


While I probably should have worked out the test details earlier in the SQL Server 2012 upgrade planning, some of the details didn’t come out until later in the planning process (after the schedule was pretty much set). Luckily, we didn’t wait too long and it didn’t impact our upgrade schedule.

Test Preparation

One of the key tests is to make sure that user queries still work as expected after the upgrade. Capturing these queries is very easy with the help of SQL Server Profiler, both for the Data Engine and Analysis Services.

While the Data Engine and SSAS didn’t change much in SQL Server 2012, it’s a game changer when it comes to Integration Services. SSIS Packages built for 2012 must use components compiled for SSIS 2012. The original plan was to upgrade the components before the upgrade and upgrade the packages as part of the upgrade. This would probably have worked but we found a simpler way to handle our SSIS upgrade; see Part 4 for more details on what we ended up doing with SSIS.

Details of the test preparation will be covered in Part 3 of this series.

Pre-Upgrade Tests

I wanted to be certain that the test environment was valid so I performed some simple tests to ensure that my “test subjects” behave the same on both the live and test systems.

We use the SharePoint List Adapter so that we can use SharePoint lists in Reporting Services reports. I tested the adapter prior to the upgrade to make sure the cloning process left everything in the same state they were in before the clone.

I also ran some of the SSIS components to make sure nothing was broken as a result of the cloning process. Following the “no-change” tests I upgraded the third-party SSIS components that we have in use to the latest version and ran the packages again. The goal here was to see if we could go ahead and upgrade the components in advance of the SQL Server upgrade.

Perform Upgrade

The plan for the upgrade was pretty straight forward although it evolved a little bit during the testing process. The basic plan was to upgrade the Integration Services server first, followed by the Data Engine servers, and finally, the Analysis Services boxes. The basic plan held up but some of the details changed as a result of the tests. See Part 7 of this series which will cover the upgrade plan.

Post-Upgrade Tests

At the time of this post, the post-upgrade tests were yet to be performed.

The post-upgrade tests will pretty much be a repeat of the pre-upgrade tests with the addition of the trace playbacks and SSAS database processing. SSIS components and packages were tested just as they were in the pre-upgrade tests along with the SharePoint List Adapter.

Trace playbacks were performed on both the Data Engine and the Analysis Services databases. Since the AS databases were also processed we didn’t feel the need to collect traces on the Data Engine databases for queries initiated from the AS processing. More details on this will be described in the next part of this series which covers the test preparation.

Coming Up Next

The SQL Server 2012 Upgrade series will continue in my next post which will, as I mentioned earlier, will put the details into the test preparation step.

<< Part 1: The Test Environment -- Part 3: Test Prep >>

Have you started upgrading to SQL Server 2012 or maybe even completed an upgrade? Tell us something about your test plan in the comments!

No comments:

Post a Comment