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.Conclusion
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