A SQL Server migration checklist covers every component that must be transferred, validated, or reconfigured when moving databases between SQL Server instances. Done properly, a migration preserves data integrity, maintains security, and keeps business applications running without interruption. Done poorly, it causes outages, broken application connections, missing jobs, and security gaps that can take days to untangle.

SQL Server migrations are one of the highest-risk activities in database administration. The actual data movement is often the straightforward part. It's everything surrounding the databases - the logins, the agent jobs, the linked servers, the SSIS packages - that catches teams off guard. This article walks through a comprehensive SQL Server migration checklist so nothing gets left behind.

Why Do SQL Server Migrations Fail?

Most SQL Server migration failures aren't caused by technical incompetence. They're caused by incomplete planning. Teams focus on the databases themselves and overlook the supporting infrastructure that makes those databases usable. An application might connect successfully after a migration, only to fail silently because a linked server is missing, a login doesn't exist on the new instance, or a scheduled job that was running nightly maintenance simply isn't there anymore.

The other common failure mode is version compatibility. Migrating from SQL Server 2014 to SQL Server 2022 isn't just a copy-and-paste exercise. Features get deprecated, syntax changes, and vendor support agreements often specify which database versions are supported. Skipping the compatibility assessment step can mean voiding your software support contracts or discovering post-migration that a critical application no longer behaves correctly.

The Complete SQL Server Migration Checklist

Work through each category below methodically. Don't assume anything has been handled until it's been verified on the destination instance.

1. Data Files

This is the obvious starting point, but it still requires careful attention. You need to account for every database on the source instance, including system databases if any custom configurations have been applied to them. Identify the location of all data files (.mdf, .ndf) and log files (.ldf) on the source, and plan the appropriate file layout on the destination. Don't just replicate the source folder structure blindly - a migration is a good opportunity to align with best practices like separating data and log files onto different drives.

Verify database sizes, estimate transfer times, and plan your migration window accordingly. A 500 GB database migrated over a congested network will take far longer than expected.

2. Database Objects

Beyond the raw data, confirm that all database objects are accounted for: tables, views, stored procedures, functions, triggers, indexes, and constraints. If you're using backup and restore as your migration method, these come across automatically. If you're scripting objects or using other transfer methods, each object type needs explicit attention.

Check for any objects that reference external resources - servers, file paths, or environment-specific configurations - that will need updating on the destination.

3. Logins and Security Settings

Logins live at the instance level, not the database level. This is one of the most commonly missed items on a SQL Server migration checklist. After restoring databases to a new instance, users inside those databases become orphaned - they exist in the database but have no matching login on the new server.

Transfer all SQL Server logins, including their password hashes and SIDs, to preserve the mapping between database users and instance-level logins. Windows logins and Active Directory group memberships also need to be validated on the destination, particularly if the new server is in a different domain or OU.

Don't forget server-level roles and permissions. A login that had sysadmin rights on the source needs those rights explicitly granted on the destination.

4. Linked Servers

Linked servers are easy to forget and painful to rediscover after the fact. Document every linked server on the source instance - the remote server name, the provider, the security context used to connect, and which applications or stored procedures depend on it. Recreate each linked server on the destination and test connectivity before cutover.

Pay attention to the credentials configured for linked server connections. These often use stored passwords or specific service accounts that may need updating in the new environment.

5. System DSNs and ODBC Drivers

System Data Source Names (DSNs) configured on the server itself, rather than on client machines, need to be recreated on the destination. Check both 32-bit and 64-bit ODBC data sources, as applications sometimes use one or the other depending on how they were built.

Verify that the required ODBC drivers and OLE DB providers are installed on the destination server. A newer version of Windows Server may not include older drivers by default, and some third-party providers need to be installed separately.

6. Extended Stored Procedures

If your environment uses extended stored procedures, particularly custom ones, these need to be explicitly migrated. Standard system extended stored procedures will be present on the destination, but any custom xp_ procedures registered from external DLLs require the DLL to be present on the new server and the procedure to be re-registered.

7. SQL Server Configuration Settings

The destination instance will have default configuration settings that may differ significantly from a tuned source instance. Review and replicate relevant settings using sp_configure, including max server memory, max degree of parallelism, cost threshold for parallelism, and any other instance-level settings that have been adjusted for performance.

Don't overlook the SQL Server service account, startup parameters, trace flags, and any configuration applied through SQL Server Configuration Manager.

8. SQL Server Agent Jobs

Every SQL Server Agent job needs to be scripted out from the source and recreated on the destination. This includes the job steps, schedules, alerts, operators, and notification settings. Jobs that reference specific file paths, proxy accounts, or linked servers will need those references updated for the new environment.

Test each job after migration. A job that appears to have migrated successfully may fail at runtime because a proxy account credential is missing or a file path no longer exists.

9. SQL Server Integration Services (SSIS) Packages

SSIS packages stored in the MSDB database or in the file system need to be explicitly migrated. If the destination uses the SSIS Catalog (SSISDB), packages stored in the legacy package store will need to be converted and redeployed. Connection managers within packages will need updating to reflect new server names, credentials, or file paths.

This is often more work than it appears. Budget time to test each package end-to-end after migration.

10. Maintenance Plans

Maintenance plans are essentially SSIS packages under the hood, but they deserve their own checklist item because they're often managed separately. Recreate or migrate all maintenance plans covering backups, index maintenance, statistics updates, and integrity checks. Verify that the backup destinations referenced in those plans exist and are accessible from the new server.

11. User-Defined Error Messages

Custom error messages added via sp_addmessage are stored in the master database and don't travel with user databases. If your applications rely on specific error message numbers, these need to be scripted and recreated on the destination instance.

What About Version Upgrades?

If the migration involves moving to a newer version of SQL Server - say from SQL Server 2016 to SQL Server 2022 - the checklist above still applies, but you need an additional compatibility assessment step first.

Microsoft provides the Database Experimentation Assistant (DEA) and the SQL Server Migration Assistant (SSMA) for this purpose. Run these tools against your source instance before migration to identify deprecated features, syntax changes, and compatibility issues. The database compatibility level setting also needs consideration - you can restore a database to a newer SQL Server instance while keeping the compatibility level at the source version, which gives you time to test application behaviour before switching to the new level.

Critically, check with every application vendor whether their software is supported on the target SQL Server version. Running an unsupported database version can void your support agreement, which is a real commercial risk, not just a theoretical one.

Key Takeaways

  • A SQL Server migration checklist must cover far more than databases alone. Logins, Agent jobs, linked servers, SSIS packages, and configuration settings are all equally critical.
  • Orphaned database users are one of the most common post-migration issues. Always migrate SQL Server logins with their original SIDs and password hashes.
  • Version upgrade migrations require a compatibility assessment before cutover. Use Microsoft's DEA or SSMA tools to identify issues in advance.
  • Verify vendor support for your target SQL Server version before migrating. Running an unsupported version can void application support agreements.
  • Test everything after migration, including every Agent job, SSIS package, linked server connection, and maintenance plan, before declaring the migration complete.

Need Help With Your SQL Server Migration?

A missed item on a SQL Server migration checklist can mean hours of unplanned downtime and frantic troubleshooting at the worst possible time. DBA Services has managed SQL Server migrations for Australian organisations across a wide range of industries and SQL Server versions. Our pre-migration health checks identify risks before they become problems, and our managed migration service covers every item on this checklist so your team doesn't have to.

Get in touch with DBA Services to discuss your migration requirements.