Moving a legacy application is rarely a simple database backup and restore. The database may be straightforward while the application depends on an old driver, a case-sensitive behavior, a local file share, a SQL Agent job, a linked server, or a login name embedded in configuration. Successful migrations make those hidden contracts visible before the production cutover.
Begin with workload and dependency discovery
Inventory every database, instance-level object, integration, and operational job associated with the application. Include logins and role mappings, SQL Agent schedules, Database Mail, linked servers, credentials, certificates, file paths, command-line utilities, reporting connections, ETL packages, and backup processes. Review actual connection and execution evidence because documentation often omits an infrequent month-end process.
Record the current SQL Server edition, build, collation, database compatibility level, recovery model, high-availability features, file layout, encryption, and peak workload. Capture a performance baseline for important user actions and batch jobs. Without a baseline, a target that is technically online can still be a business regression.
Choose the target from requirements
A modern target might be a supported SQL Server instance on new infrastructure, SQL Server in a virtual machine, Azure SQL Managed Instance, or Azure SQL Database. The right option follows from required features, administrative responsibility, availability, latency, security boundaries, cost behavior, and the application's ability to change.
Do not select a platform from the database size alone. A small database can depend heavily on instance-level features. A large database with a clean, self-contained application pattern may fit a managed service well. Document which party will own backups, patching, monitoring, identity, network controls, and incident response after migration.
Separate compatibility from correctness
Assessment tools can identify unsupported syntax and features, but passing an assessment does not prove application behavior. Test authentication, transaction isolation assumptions, date and string handling, collation-sensitive comparisons, error processing, timeouts, and client-driver support. Exercise both ordinary workflows and unusual cases such as duplicate submissions, canceled batches, and a failed downstream service.
Changing the database compatibility level can alter optimizer behavior independently of the engine upgrade. Preserve Query Store history or establish a new baseline, then test important queries at the intended level. Plan correction should be based on evidence rather than lowering compatibility indefinitely as a substitute for tuning.
Rehearse the data movement
Choose a migration method that fits database size, change rate, allowed outage, encryption, and target platform. Backup and restore may be ideal for one environment; log shipping, distributed availability technology, replication, or a platform migration service may be appropriate elsewhere. Each option has prerequisites and a different failure surface.
- Run a full rehearsal with production-like volume.
- Measure backup, transfer, restore, upgrade, recovery, and validation time separately.
- Script instance-level dependencies and verify ownership and permissions.
- Run integrity checks and reconcile critical row counts and business totals.
- Test the application through the same network and identity path planned for production.
Rehearsal results should drive the cutover estimate. A spreadsheet guess cannot reveal a slow transfer link, a certificate problem, or the time needed to rebuild an index after a version change.
Design cutover and rollback together
A cutover runbook should identify owners, UTC timestamps, entry criteria, stop conditions, communication points, exact commands, validation, and the moment after which rollback becomes more complex. Freeze or control writes before the final synchronization. Do not allow the old and new systems to accept independent production changes unless a tested reconciliation design exists.
| Decision | Required evidence |
|---|---|
| Proceed | Successful rehearsal, current backup, target health, and available owners |
| Accept target | Application smoke tests, data reconciliation, jobs, monitoring, and performance |
| Roll back | Defined trigger, preserved source, connection reversal, and data-change plan |
Rollback is not merely reconnecting to the old server. If users have written data on the target, the business must decide whether and how those changes return. Set that decision boundary before the outage begins.
Modernize in controlled stages
A migration already changes engine, infrastructure, network, and operations. Rewriting application data access at the same time can make faults difficult to isolate. Unless an unsupported dependency forces the issue, first establish equivalent behavior on the target. Then modernize schemas, queries, deployment practices, and integrations through separate measured changes.
Some technical debt should be removed before moving: unsupported drivers, hard-coded administrator credentials, unmaintainable backup processes, and integrity problems can make migration unsafe. The distinction is between prerequisites needed for a controlled move and optional improvements that can follow it.
Operate the new platform from day one
Before acceptance, prove backups and recovery at the target, configure alert routing, establish performance baselines, document maintenance responsibilities, and test access for support staff. Validate high availability through an intentional exercise rather than relying on a status screen. Review cost and resource consumption after the workload settles.
A migration is complete when the business can operate and recover the application on the new platform, not when the database first comes online.
Keep the source protected for the approved retention period, but prevent accidental reconnection and document its disposition. Close temporary firewall rules and elevated accounts. Finally, update the system inventory and recovery runbook so the new environment does not become the next undocumented legacy platform.