Two tools to resolve SQL Server migration issues

SQL Server 2012 has been released and we need to do some preparation before upgrading. You must first verify the software and hardware requirements, and prepare sufficient resources for testing and implementing the new system. However, you may have overlooked some important aspects-the client application that handles SQL Server data.

This is the role of SQL Server 2012 's upgrade Assistant (upgrade assistant). Upgrade Assistant provides an automated processing mechanism for testing application compatibility from SQL Server 2005, 2008, or 2008r2 porting to SQL Server 2012. Using upgrade Assistant, you can detect possible functional and performance issues during the upgrade process. Upgrade Assistant can validate the application's SQL Server Transact-SQL query, which can be used to detect and correct application source code compatibility issues.

Upgrade Assistant is developed in collaboration with Microsoft and Scalability Experts Inc. Upgrade Assistant uses workload tests to compare older versions of SQL Server with SQL Server 2012 application performance. If you follow the steps described in the product documentation (while considering the structure of the user interface), you can identify issues that may affect your application, such as deprecated attributes or Transact-SQL syntax changes.

Upgrade Assistant Testing Process

Upgrade Assistant can guide you through verifying your application's compatibility with SQL Server 2012. Before you begin, create a test environment to run the sample workload. In theory, tests can be performed on a production database, but there are some risks to this approach. This not only negatively affects the performance of the running applications, the background database, and the network itself, but also risks damaging application and data integrity if a naming conflict or transaction interruption occurs.

At the very least, your test environment should contain a database server, and then install the current version of SQL Server (2005, 2008, or 2008 R2) and upgrade Assistant on it. The server must also support SQL Server 2012 upgrades. In addition, you must connect to the database server from the application you are testing. The application should be a test system to perform an automated process that fully complements the entire application's characteristics. If you are using a production application to perform tests, you will most likely only be able to check for a smaller percentage of functionality.

Once you have established a test environment, you can use SQL Server Upgrade Assistant to perform the following steps:

Back up the relevant system and user databases to get the sample workload. Upgrade Assistant captures the workload as a trace file, which records a representative application database query record.

Establish a baseline environment based on the backup and trace files created in the first step. In this process, run SQL Server Upgrade Advisor to determine the upgrade issues in the database trace files and backup files. At this point, you should resolve all the problems that occur before continuing upgrade the assistant steps.

Use the workload tracking example created in the first step to re-establish a baseline trace. In the final step, this baseline trace is compared to SQL SERVER 2012 tracking.

Upgrade the SQL Server instance to SQL Server 2012.

Use the workload tracking example created in the first step to re-establish SQL Server 2012 upgrade tracking.

Compare baseline tracking with upgrade tracking to determine potential problems that might arise from running the sample workload.

There is no doubt that these steps are just an overview of the entire testing process, but can help you approximate how to use upgrade Assistant to check data-driven applications before you deploy SQL Server 2012.

SQL Server Upgrade Technology

You may notice that the second step is to run SQL Server Upgrade Advisor. Although it is not part of the upgrade assistant, it is an important part of the whole process.

Just upgrade Advisor. Very similar to upgrade assistant on names, you can analyze existing SQL Server components that are ready to be upgraded to SQL Server 2012. Upgrade Advisor generates a report that identifies the SQL Server instance issues that should be fixed before the update. This analysis includes a number of database objects, such as triggers, stored procedures, scripts, and trace files. If you are using both Upgrade Advisor and upgrade Assistant, add the workload tracking example that you created in the first step.

Another important SQL Server 2012 technology during the upgrade Assistant test is distributed replay (distributed replay), which can be used to assess the impact of hardware and operating system upgrades. Similar to SQL Server Profiler, distributed replay can replay captured traces in an upgrade test environment. The Upgrade assistant adds distributed replay functionality to achieve more realistic performance testing than the older SQL Server.

Practice has proven that the Upgrade Assistant tool is suitable for all organizations that provide data-driven projects. The final step is to upgrade to SQL Server 2012 and then break all applications. Then, be sure to remember that SQL Server 2012 runs the legacy database in compatibility mode, which is equivalent to the version of SQL Server that was used when the database was created. In this way, you can deploy SQL Server 2012, but you still have enough time to optimize your application's performance. Your goal should be to adjust your application to run on SQL Server 2012, but before that, you have another option. If you are ready to upgrade your application, you will find that upgrade Assistant is very useful.