SQL Server 2012 to 2022:
A Complete Migration Guide

Why Migrate Now?

SQL Server 2012 is over a decade old and no longer receives security updates from Microsoft. Beyond the security risk, you're missing out on significant performance improvements introduced in SQL Server 2016, 2017, 2019, and 2022 — including Intelligent Query Processing, Accelerated Database Recovery (ADR), and improved In-Memory OLTP.

Phase 1 — Assessment

Before touching anything in production, run a thorough assessment to identify compatibility issues and deprecated features.

Use the Database Experimentation Assistant (DEA)

Microsoft's free DEA tool captures a workload trace from your SQL Server 2012 instance and replays it against a SQL Server 2022 test instance, comparing query performance and flagging regressions.

Run the MAP Toolkit

The Microsoft Assessment and Planning Toolkit inventories your SQL Server environment — versions, editions, feature usage, and database sizes — across your entire estate.

SELECT
    feature_name,
    usage_count
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Deprecated%'
    AND cntr_value > 0
ORDER BY usage_count DESC;

Phase 2 — Choose Your Migration Strategy

There are three main approaches, each with different risk and downtime profiles:

Option A — Backup and Restore (Simplest, some downtime)

1. Take a full backup of each database on SQL Server 2012
2. Restore to the new SQL Server 2022 instance
3. Run DBCC CHECKDB to verify integrity
4. Update connection strings and cut over

Downtime: Equal to backup + restore time + testing. Acceptable for smaller databases or during maintenance windows.

Option B — Log Shipping (Minimal downtime)

Log shipping continuously ships transaction log backups from the source to the target, keeping it in near-sync. The final cutover only requires the last log backup to be applied.

-- Step 1: Set recovery model to FULL
ALTER DATABASE [YourDB] SET RECOVERY FULL;

-- Step 2: Take initial full backup
BACKUP DATABASE [YourDB]
TO DISK = '\\backup-share\YourDB_full.bak'
WITH COMPRESSION, STATS = 10;

-- Step 3: Configure log shipping via SSMS wizard
-- or sp_add_log_shipping_primary_database

Option C — Always On Availability Groups (Zero downtime)

If you're on SQL Server 2012 Enterprise with AG already configured, you can add a SQL Server 2022 replica, synchronise it, and failover with near-zero downtime. This is the gold standard for production migrations.

Phase 3 — Pre-Migration Checklist

• Document all SQL Server Agent jobs and replicate them on the new instance
• Export all logins using a script (passwords are hashed — don't recreate manually)
• Document linked servers, SSIS packages, and Integration Services catalogue
• Check for CLR assemblies and their permission levels
• Verify .NET Framework version requirements on the new server
• Review and migrate SQL Server certificates and keys
• Test application connectivity with updated connection strings

SELECT
    'CREATE LOGIN [' + name + ']' +
    CASE type
        WHEN 'S' THEN ' WITH PASSWORD = ' +
            CONVERT(varchar(max), password_hash, 1) +
            ' HASHED, SID = ' + CONVERT(varchar(max), sid, 1)
        WHEN 'U' THEN ' FROM WINDOWS'
    END AS CreateLoginScript,
    name,
    type_desc
FROM sys.server_principals
WHERE type IN ('S', 'U')
    AND name NOT LIKE '##%'
    AND name NOT IN ('sa', 'guest')
ORDER BY name;

Phase 4 — Post-Migration Steps

Update Compatibility Level Gradually

Don't jump straight to compatibility level 160. Start at 110 (SQL Server 2012), test your workload, then increment to 130, 140, 150, and finally 160. Each level introduces new query optimiser behaviour that could affect existing queries.

-- Check current level
SELECT name, compatibility_level
FROM sys.databases;

-- Upgrade to SQL Server 2022 level
ALTER DATABASE [YourDB]
SET COMPATIBILITY_LEVEL = 160;

Update Statistics and Rebuild Indexes

After migration, update all statistics and consider rebuilding fragmented indexes. The query optimiser will perform better with fresh statistics on the new engine.

EXEC sp_updatestats;

-- Or for a specific database
USE [YourDB];
EXEC sp_MSforeachtable 'UPDATE STATISTICS ?';

Enable New SQL Server 2022 Features

Once stable, consider enabling:

• Accelerated Database Recovery (ADR) — dramatically reduces recovery time and version store bloat
• Intelligent Query Processing — adaptive joins, memory grant feedback, and batch mode on rowstore
• Query Store — capture query performance history and force good execution plans
• Ledger tables — cryptographic tamper-evidence for audit trails

Summary

Migrating from SQL Server 2012 to 2022 is a worthwhile investment that pays dividends in security, performance, and supportability. The key is a structured approach: assess first, choose the right migration strategy for your downtime tolerance, and upgrade compatibility levels gradually after the migration.

If you need help planning or executing your SQL Server migration, get in touch with our team — we've done this dozens of times and can help you avoid the common pitfalls.