Migrating SQL Server 2008 R2 Databases to SQL Server 2016

2019-06-26 · mssql, migration, upgrade

Extended support for SQL Server 2008 and 2008 R2 ends this July, and I still have a few line-of-business instances I'm nagging people about. My preferred way off is a side-by-side migration to SQL Server 2016 on a new server: the old instance stays untouched until everyone is happy, and rollback is just pointing the application back. The backup and restore is the easy part. Logins, Agent jobs, the cardinality estimator change and compatibility levels are what catch people.

This is the checklist I follow. Before starting I want a 2016 instance (SP2, latest CU) of the same or newer edition with the same server collation, Data Migration Assistant (DMA) on a workstation that can reach both servers, and a full backup of every source database at cutover. And one dress rehearsal against a copy. Skipping that is how you find the missing linked server at 6 a.m.

Step 1: Assess with Data Migration Assistant

Create a new Assessment project in DMA, source type SQL Server, target SQL Server, and connect to the 2008 R2 instance. Select the databases and both check types: Compatibility issues and New features' recommendation. The report lists breaking changes (removed COMPUTE BY, FASTFIRSTROW hints, deprecated SET ROWCOUNT for DML, old-style *= outer joins) and behavior changes per compatibility level. Fix the breaking changes before migrating; note the behavior changes for testing.

Step 2: Script the logins with matching SIDs

Restoring a database brings its users but not the logins, and logins created by hand get new SIDs, which leaves the users orphaned. I use Microsoft's sp_help_revlogin procedure on the source (KB 918992 has the script), which generates CREATE LOGIN statements with the original SID and hashed password:

EXEC sp_help_revlogin;

Sample output:

CREATE LOGIN [app_sales] WITH PASSWORD = 0x0200A1B2... HASHED, SID = 0x4E3C..., DEFAULT_DATABASE = [Sales], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

Run the output on the 2016 instance. Also script server-level permissions and role memberships; sp_help_revlogin does not include them:

SELECT 'ALTER SERVER ROLE ' + QUOTENAME(r.name) + ' ADD MEMBER ' + QUOTENAME(m.name) + ';'
FROM sys.server_role_members rm
JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals m ON m.principal_id = rm.member_principal_id
WHERE m.name NOT LIKE 'NT %' AND m.name <> 'sa';

Step 3: Back up on the source and restore on the target

-- on 2008 R2
BACKUP DATABASE Sales TO DISK = N'\\fileserver\migration\Sales.bak' WITH COPY_ONLY, COMPRESSION, CHECKSUM, INIT;

-- on 2016
RESTORE DATABASE Sales FROM DISK = N'\\fileserver\migration\Sales.bak'
WITH MOVE N'Sales' TO N'E:\SQLData\Sales.mdf',
     MOVE N'Sales_log' TO N'F:\SQLLog\Sales_log.ldf',
     CHECKSUM, STATS = 5;

The restore automatically upgrades the internal database version from 661 to 852; you will see Converting database 'Sales' from version 661 to the current version 852 in the messages. There is no way back, so keep the .bak. A database restored from 2008 R2 also cannot be restored to anything older than 2016 afterwards.

If your migration window is short, use log shipping or a full plus differential backup to shorten the final copy.

Step 4: Post-restore chores

I run these on every migrated database, in this order:

USE Sales;
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 100;       -- keep 2008 behavior for now
DBCC CHECKDB (Sales) WITH DATA_PURITY, NO_INFOMSGS;
DBCC UPDATEUSAGE (Sales);
EXEC sp_updatestats;
ALTER DATABASE Sales SET PAGE_VERIFY CHECKSUM;

DATA_PURITY only needs to run once on databases created before SQL Server 2005; run it anyway, it is cheap. sp_updatestats matters because statistics from an older version are rebuilt in the new format on first use, and you want that done before users show up.

Check for orphaned users despite the SID-preserving login script:

EXEC sp_change_users_login 'Report';
-- fix a straggler:
ALTER USER app_sales WITH LOGIN = app_sales;

Step 5: Move Agent jobs, linked servers and other server objects

In SSMS on the source, Object Explorer → SQL Server Agent → Jobs, select all, right-click Script Job as → CREATE To → New Query Editor Window, and run the script on the target after fixing any paths. Do the same for operators and alerts. Linked servers are scripted the same way, but their passwords are not included; re-enter them. Also copy Database Mail profiles, any custom model settings, and server configuration values (max server memory, cost threshold for parallelism, max degree of parallelism) worth carrying forward.

Step 6: Raise the compatibility level

SQL Server 2014 introduced a new cardinality estimator, active at compatibility level 120 and above. Most workloads get faster; a few queries regress badly. Turn on Query Store first so you can catch regressions, then flip the level:

ALTER DATABASE Sales SET QUERY_STORE = ON;
ALTER DATABASE Sales SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 1024);
-- after a week of baseline at 100:
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 130;

If a query regresses, you can force its old plan with Query Store, or keep the legacy estimator on level 130 with ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON.

Final check

SELECT name, compatibility_level, recovery_model_desc, page_verify_option_desc, is_query_store_on
FROM sys.databases WHERE database_id > 4;

SELECT dp.name FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type IN ('S','U') AND dp.principal_id > 4 AND sp.sid IS NULL;   -- should return nothing

Then have the application team run their tests against the new server. I keep the old instance running for a week with the databases set offline, so nobody writes to it by accident.

Problems I've run into

Msg 3169: The database was backed up on a server running version 10.50.6000. That version is incompatible with this server, which is running version 13.00.5026. This message appears when going backwards, for example restoring a 2016 backup to 2008 R2. Forward restores work; there is no downgrade path.
The database owner SID recorded in the master database differs from the database owner SID recorded in database. Run ALTER AUTHORIZATION ON DATABASE::Sales TO sa; (or the intended owner) after the restore.
Applications fail with Login failed for user after cutover. Check the login list from Step 2 was actually applied and that the server is in the same authentication mode as the old one.
A stored procedure that ran instantly now takes minutes. Cardinality estimator regression. Compare plans in Query Store; force the old plan or use the legacy estimator database scoped configuration while the query is rewritten.

Order matters most: logins with SIDs before the restore, compatibility level last, with Query Store watching. Do it that way and the migration is boring, which is what you want.