UPDATED 10.11.2020
Do you have a SQL Server migration project ahead? Here's our detailed checklist for successful migration planning, step by step.
Comprehensive guide on SQL Server Migration Planning
Here's all parts to this blog series:
Part 1: Detailed SQL Server migration specification and compatibility check
Detailed process diagram for the migration project and advice on a light-weight or full compatibility check.
Part 2: SQL Server High Availability planning
Let's explore different solutions and combinations of the SQL Server High Availability techniques.
Part 3: SQL Server Migration project phasing (Checklist)
A helpful step by step checklist for project phasing to make sure that all the important parts of migration will be covered in the right order.
This series continues our previous blog series about mastering Microsoft SQL Server consolidation planning with detailed information on the migration process. If you'd like to learn all there is to know about SQL Server consolidation planning, click the link below.
SQL Governor automates SQL Server migration planning
There's a lot to consider when planning a SQL Server migration. Our job is to make it easier for you.
SQL Governor is a unique, easy-to-use, domain-independent DBA tool for efficient database migration capacity planning in a Microsoft SQL Server DBMS environment. With SQL Governor and our patented method it is possible to:
- Calculate an optimal server configuration for your target architecture based on efficient time series analysis and machine learning
- Save up to 30-50% in license and server investment costs compared to your existing environment
If you want to know more, book a free demo to see SQL Governor in action!
Part 1: Detailed SQL Server migration specification
Below is the detailed process diagram for planning your migration. The purpose of detailed planning is to assure that everything is taken into consideration during planning so that no important aspects are accidentally left out.
First Step: Compatibility Check
If a SQL Server version upgrade is involved in the migration process (as often is the case), a compatibility check must be performed. With every new version, some features are deprecated, some are new and some are changed. To ensure that our application and all components are still compatible with the new version and performance does not degrade, we have to plan a compatibility check.
The overall scope and depth of this check depends on two things:
- The complexity of the system: how many databases, applications, or components are involved?
- How old is the version to be upgraded, i.e. how many newer versions are we skipping?
Based on these, we can decide to do a light-weight or full compatibility check.
Light compatibility check
The basic idea here is to run Data Migration Assistant and review the results for compatibility issues and/or new feature recommendations.
If you want to check code (TSQL) compatibility, SQL Server Profiler can be used as described in next section.
Full compatibility check
After Data Migration Assistant, we can continue to check TSQL-code. There are three approaches:
- Using SQL Server Profiler with the following procedure:
- Backup the current (production/test) database
- Run Profiler with TSQL_Replay-template
- Get a representative trace (all kinds of activities) of all TSQL traffic to server
- Restore the database to a new server version, update compatibility level and update statistics
- Replay the trace and review results for compatibility issues
- The same can be used with the Distributed Replay tool
- This requires quite a lot of setup effort (servers and time)
- But we can also test performance of mission-critical workload in addition to functional compatibility
- Build a complete test environment with the new version. This involves:
- Installing new server and restoring database(s) and other depended components (Logins, Jobs, SSIS packages etc.)
- Testing applications against this server
The last option to build a complete test environment uses a side-by-side upgrade method, which we will cover in detail in future blog posts.
Access the next parts here:
- Part 2: SQL Server High Availability planning
Let's explore different solutions and combinations of the SQL Server High Availability techniques. - Part 3: SQL Server Migration project phasing (Checklist)
A helpful step by step checklist for project phasing to make sure that all the important parts of migration will be covered in the right order.
Pekka Korhonen
Lead Architect, MCM
DB Pro Oy
