Friday, June 8, 2012

SQL 2012 Upgrade Part 3: Test Prep

Playback trace capture is a simple process but it is imperative that we capture enough data to get an accurate test. This requires something I haven’t mentioned much yet but really should have: communication. If you don’t want to freak out your developers, especially ones that still have scars from their upgrade bites, you want to get them on board early. Not only will the developers like you better, they will be more apt to help you out. You’re probably going to need their help to communicate to your/their customers and to help you get the right test data together.

Data Engine Playback Trace Requirements

In our BI Environment, more than 95% of our relational database traffic comes from Analysis Services database processing. Since we already had it in the plans to process all our databases I didn’t want to capture that traffic. Our SQL Server-based Enterprise Data Warehouse is still in its infancy so we don’t really have any traffic hitting the Data Engine instances except from Reporting Services.

The SharePoint and SQL Server DBAs were able to give me a list of all the Reporting Services machines so I was able to limit the traces to pick up queries from the Reporting Services boxes only. If I had to include queries from end-users as well I could have just excluded the SSAS and SSIS servers and captured all other traffic; however, I prefer inclusive filters whenever possible as it results in fewer surprises.

Analysis Services Playback Trace Requirements

SSAS handles the bulk of our BI queries so we have a lot of data to capture here. The tough part with this is to make sure you’re not capturing processing commands. I’m honestly not sure if the playback will issue the processing requests but might take an unnecessarily long time to run the playbacks if they do get executed.

Since we have a server dedicated to processing analysis databases we can eliminate most of the processing commands just by excluding the queries from the processing server. I haven’t done this myself, but I suspect that we would have had to filter on the <BATCH> command to eliminate the processing commands.

Collecting Playback Traces

Using SQL Server Profiler to perform a playback trace is just like any other trace activity. Microsoft even provides a template that includes all the data required for playback. The Data Engine template is called “TSQL_Replay” and the SSAS template is called “Replay”. I collected the data in files but you can collect with a table if you prefer. Since I didn’t want to tax the production server too much, I did not enable the Server processes trace data option.

Once you have the traces configured it’s time to get the developers involved actively. If possible, asking them to prepare in advance is helpful but your mileage will surely vary depending on their workloads and attitudes toward the upgrade. Getting developers to run reports and common queries will help ensure that you get really good test data. You might even encourage them to work with their clients and get them to perform some of their common activities to give you even better data.
Data Engine Replay Trace Capture Dialog
After performing a bit of trace playback I figured out that the SSAS "Replay" template collected quite a bit more data than was necessary. The only event you really need to capture is the "Query Begin" event, as shown in the Event Selection screen shot below.

Analysis Services Replay Trace Capture Dialog - General Options
Analysis Services Replay Trace Capture Dialog - Event Selection

SSIS Preparation

Preparing for the SSIS upgrade mainly requires that you know which third-party SSIS components are in use on your system. You probably know what is installed on your servers but it can be very difficult to keep track of which components are used in each package.

This is another area where developers will come in handy. I just sent out an email asking for some packages that were using some of the non-Microsoft components. From their responses I compiled a list of packages that needed to be included in the test runs.

SQL Server 2012 Upgrade Advisor

Microsoft provides a great tool that will help you determine how much work is going to be involved with your upgrade to SQL Server 2012, the aptly named SQL Server 2012 Upgrade Advisor. I ran into a little bit of trouble installing it and wrote up some instructions to help you avoid the same problems. Running the Upgrade Advisor is as simple as can be and we only had two issues reported; both were related to Integration Services.

Third-party SSIS components not compatible with SQL 2012

We had expected this. The details of this warning indicate that the SSIS components that we use need to be compiled against SQL Server 2012. Upgrading the components was part of our upgrade plan to begin with.

Provider names in connection strings need to be updated

SQL Server 2012 comes with a new version of the SQL Server Native Client and the associated data provider has a new name. The name change will result in SSIS connection strings becoming invalid but there is a simple solution. The SSIS Package Upgrade Wizard will fix the packages for you. You just need to run it on the appropriate packages after the upgrade is complete. I believer you have to update configuration files manually but I haven't confirmed that yet.


Preparation is a key part of the testing plan. Collecting the right trace data and getting a lot of it requires an early start. Communicating with the people that can help you get the necessary data is also important and will reduce the number of assumptions you need to make while deciding what to collect.


No comments:

Post a Comment