Upgrade Assistant Tool for SQL Server 2012 (Ch.12 of 14): Fixing Differences
This article is part of the serie on the SQL Server Upgrade Assistant:
[[Upgrade Assistant Tool for SQL Server 2012]] 1.0 Introduction
[[SQL Server Upgrade Assistant-Upgrade Assistant Process|1.1 Upgrade Assistant Process]]
[[SQL Server Upgrade Assistant-Upgrade Assistant Results|1.2 Upgrade Assistant Results]]
[[SQL Server Upgrade Assistant-Required Knowledge and Skills|1.3 Required Knowledge and Skills]]
[[SQL Server Upgrade Assistant-System Requirements|2.0 System Requirements]]
[[SQL Server Upgrade Assistant-Create a Test Environment|3.0 Create a Test Environment]]
[[SQL Server Upgrade Assistant-Create a Single Computer Test Environment|3.1 Single Computer Test Environment]]
[[SQL Server Upgrade Assistant-Create a Multiple Computer Test Environment|3.2 Multiple Computer Test Environment]]
[[SQL Server Upgrade Assistant-Installing SQL Server Upgrade Assistant|3.3 Install SQL Server Upgrade Assistant]]
[[SQL Server Upgrade Assistant-Capturing a Test Workload|4.0 Capture a Test Workload]]
[[SQL Server Upgrade Assistant-SQL Server 2005/2008 Baseline System|5.0 SQL Server 2005/2008 Baseline System]]
[[SQL Server Upgrade Assistant-Run Upgrade Advisor|6.0 Run Upgrade Advisor]]
[[SQL Server Upgrade Assistant-Replay Baseline Trace on SQL Server 2005/2008|7.0 Replay Baseline Trace on SQL Server 2005/2008]]
[[SQL Server Upgrade Assistant-Set Up Playback Test System|8.0 Set Up Playback Test System]]
[[SQL Server Upgrade Assistant-Replay Trace on SQL Server Code Name Denali|9.0 Replay Trace on SQL Server 2012]]
[[SQL Server Upgrade Assistant-Compare Trace Files|10.0 Compare Trace Files]]
[[SQL Server Upgrade Assistant-Advanced Topics|11.0 Advanced Topics]]
[[SQL Server Upgrade Assistant-Fixing Differences|12.0 Fixing Differences]]
[[SQL Server Upgrade Assistant-Troubleshooting|13.0 Troubleshooting]]
[[SQL Server Upgrade Assistant-Support|14.0 Support]]
This chapter contains:
12.0 Fixing Differences
This section is not meant to be an exhaustive listing of every possible difference but a discussion of the more common issues and how to address them.
The most common differences encountered are replay provider errors. A replay provider error indicates that when the provider replayed a SQL statement SQL Server returned an error. In some cases this will indicate a difference that must be fixed. For example, if the syntax of a command has changed in a new release, some statements may fail. It is unusual to see these errors if you ran SQL Server Upgrade Advisor because it generally finds syntax changes. In most cases fixing this type of error is a simple matter of updating your source code to comply with the new syntax,but in some cases the command may have been removed altogether or it may reference system objects that have been changed or removed. In these situations you may have to come up with an alternate way to accomplish what the old command did or remove it from your source code altogether. Changing the compatibility level of the database to the previous version will often fix these differences but this should be considered a short term solution.
Another type of replay provider error is caused by environmental differences between the two replay instances. For example, the command may fail in the SQL Server 2012 environment because the user the command ran as on the replay capture machine does not exist on the SQL Server 2012 server. This kind of error shows up in the difference viewer as a provider error so you must look at the error message to tell the difference between environmental errors and errors that result from version differences. In most cases you just need to fix the login to make these work. When the replay client runs, it does an Execute As to run the command as the user who ran the command during the playback capture. In some cases, the user running the playback does not have permissions to impersonate the user or Windows does not allow impersonation.
Another difference occurs when a command fails on both the capture and the playback but the errors generated are different. This happens commonly because the SQL Server team continually reviews error messages to ensure they are as clear and descriptive as possible. If the same error number is returned in both replays but the message is different, it is usually safe to ignore the difference. Very few developers write logic that depends on the text of an error message and if they do, it should be changed to compare error numbers. A difference that needs to be fixed is one where the error number changes between versions. This is a rare occurrence but the SQL Server team may have decided to split a single error into multiple errors to make it more obvious what caused the error. In this case, the error number returned will be different and the code will have to be changed to accommodate the new error number. If your application will run only on SQL Server 2012 after the upgrade then you can change the application to look for the new error number. If you need to support both the previous level and Denali you will need to modify the code to accept either error number and handle it correctly.
Compatibility bugs
Occasionally, an upgrade difference can be caused by a bug in SQL Server. Examples of this might be a different error being returned or incorrect results being returned. The first thing to do if you suspect a difference is caused by a bug in SQL Server 2012 is to examine the results carefully to determine that the difference is truly caused by a bug in SQL Server 2012 and not by a bug in your current version that was fixed by SQL Server 2012. If you think you have discovered a bug or at least a difference that shouldn't be there then you should report it to Connect. The steps to do this are:
- Go to http://connect.microsoft.com/sql
- In the upper right corner of the page there is a "sign in" link. Click this to sign in with your Windows Live ID
- In the Left Menu, click "Feedback"
- On the feedback page use the search box to search for your problem. If it has already been reported, you can vote for the problem to increase its priority.
- If your problem hasn't been reported click on "Submit Feedback" in the search results page to report it
- Select either the "SQL Server Bug form" or the "SQL Server suggestion form" to submit your bug or suggestion
- Once you have completed the feedback, send an email with the bug number to sqldevx@microsoft.com for expedited processing
If your problem is with the tool instead of with SQL Server 2012 you should contact Scalability Experts for support.
Support for running Upgrade Assistant is available here: [[SQL Server Upgrade Assistant-Support|Support]]