SQL Server Migration Planning Guide

Pre-Migration Assessment

• All databases on the source instance are inventoried — names, sizes, recovery models, owners
• All applications and connection strings pointing to the source instance are documented
• Linked server dependencies are documented — what connects to this instance from elsewhere
• SQL Server Agent jobs are inventoried — schedules, owners, proxy accounts, notification operators
• SQL Server logins and users are inventoried — Windows logins, SQL logins, SIDs
• SQL Server Agent proxies and credentials are documented
• SSIS packages deployed to the SQL Server catalog (SSISDB) are inventoried
• SSRS reports deployed to the report server are inventoried
• Database Mail profiles and accounts are documented
• Server-level objects are documented: triggers, linked servers, server roles, extended events, Resource Governor settings
• Third-party tools or monitoring agents installed on the source are inventoried
• Current disk I/O, CPU, and memory baselines are captured from the source

Compatibility and Risk Assessment

• Database Compatibility Level is reviewed — all databases documented at current compat level
• Microsoft Database Experimentation Assistant (DEA) or Query Store has been used to compare query plans across versions
• Deprecated features in use on the source have been identified (sys.dm_os_performance_counters, DBCC SHOWCONTIG, etc.)
• Breaking changes in the target SQL Server version have been reviewed against current usage
• Application vendors have confirmed compatibility with the target SQL Server version
• CLR assemblies, OPENROWSET, ad hoc distributed queries, and xp_cmdshell usage are documented and verified to work on target
• TLS version compatibility between SQL Server and connecting applications has been reviewed
• Collation differences between source and target have been assessed
• For cloud migrations: DTU/vCore sizing or VM sizing is based on measured workload, not assumption

Target Environment Setup

• SQL Server is installed on target with the same edition as source (or upgrade is intentional and licensed)
• Instance configuration matches source or is intentionally changed and documented (max memory, MAXDOP, cost threshold)
• TempDB is configured correctly — multiple files, dedicated drive
• Logins have been migrated from source — SIDs match for SQL logins (sp_help_revlogin or equivalent)
• Database Mail is configured and tested on target
• SQL Agent operators and alerts are configured on target
• SQL Agent proxy accounts and credentials are recreated on target
• Linked servers are recreated or decommissioned as appropriate
• Antivirus exclusions are configured for SQL Server data, log, and backup directories

Migration Method Selection

• Migration method is documented and agreed upon — backup/restore, detach/attach, log shipping, AG with planned failover, or replication
• For backup/restore: restore has been tested successfully on the target before the cutover window
• For log shipping as a pre-stage: log shipping lag is within acceptable bounds before cutover
• For AG-based migration: AG is set up between source and target, all databases are synchronized, planned failover has been tested
• Estimated data migration duration is calculated and fits within the agreed maintenance window
• Maintenance window is communicated to all affected stakeholders

Cutover Plan

• Cutover steps are written out in order with estimated time for each step
• A designated cutover lead is assigned — one person making calls, not a committee
• Applications are taken offline or put into maintenance mode before database migration begins
• A final full backup of all source databases is taken immediately before cutover
• Connection strings are updated to point to the new instance or listener
• DNS records or aliases (CNAME) are updated if the server name is changing
• Application services are restarted after connection strings are updated
• Initial smoke testing is performed before declaring cutover successful
• A go/no-go decision point is defined — maximum elapsed time before rollback is initiated

Rollback Plan

• Rollback path is documented and has been tested
• The source instance is left intact until the migration is declared successful (not decommissioned during cutover)
• Connection strings can be reverted to the source instance without application redeployment
• If log shipping was used, the source can be brought back online with minimal data loss
• Rollback decision criteria are defined in advance — who decides and when
• Rollback time estimate is calculated and fits within the maintenance window

Post-Migration Validation

• All databases show ONLINE state on the new instance
• DBCC CHECKDB runs without errors on all migrated databases
• SQL Agent jobs are enabled and run successfully on first post-migration execution
• Backup jobs run successfully and files are written to expected locations
• Database Mail sends successfully from the new instance
• Application-level smoke testing covers critical workflows — not just connectivity
• Performance baselines on the new instance are compared to source baselines
• Database Compatibility Level is updated if intentionally changing (not automatically on restore)
• Query Store is enabled if moving to SQL Server 2016 or later
• Old server or instance is decommissioned only after a defined stability period (minimum 2–4 weeks)
• Migration is documented — what was moved, when, what issues were encountered and resolved