SQL Migration Assistance

  • Article

Often I get asked what the impact would be to upgrade to a new version of SQL. Microsoft does an amazing job, but short of scouring the release notes or deploying to the new version and running a full test, it was very difficult to come up with a good impact assessment.

The SQL team just released a new Database Experimentation Assistant.

It will assist in evaluating a targeted version of SQL for a given workload. Customers who are upgrading from previous SQL server versions (starting 2005 and above) to any new version of the SQL server will be able to use these analysis metrics provided by tool, such as, queries that have compatibility errors, degraded queries and query plans, and other workload comparison data to help them build higher confidence, making it a successful upgrade experience.

 

While this tool still requires you to setup a new instance of the target SQL server, and copy your database....this does remove the application from the equation. It allows you to isolate the database from the application code. I think that is a major step forward. You can capture a trace from an existing SQL workload, and then replay the trace on the target server.

ViewexistingReport-1024x502

Pros

  • Removes application from the testing scenario, allowing you to focus only on the database.
  • Replay trace on target SQL instance
  • Significantly reduced test effort for migration testing
  • Detailed performance impact assessments and comparisons between the SQL server versions
  • Early warning reports that can help you remediate issues faster to complete the migration

Cons

  • Requires you to setup the target SQL instance, and copy over your database
  • Focuses only on the SQL side, and does not provide any guidance for the application\code

 

While this is not a perfect tool, I believe this will help organizations significantly reduce time and effort on their application upgrade projects.You can download this tool from the Microsoft Download Center.

For best practices, assessing your current SQL instance or migrating to a new SQL version, here are some great tips and techniques to get you going.