dexter/DBAdmin
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
DBAdmin/projects/PARHEON/migration.md
T
Paweł Domański 5529f48942 Aug 10, 2026, 3:24 PM
2026-08-10 13:24:35 +00:00

8.3 KiB
Raw Permalink Blame History

SQL Server Standalone Migration Runbook

SQL Server 2016/2019 → SQL Server 2022

Document Version: 1.0
Migration Type: Side-by-Side Migration
Source Server: _________________
Target Server: _________________
Migration Date: _________________
DBA: _________________

1. Discovery / Assessment

1.1 Source Server Inventory

Operating System

  • Server name documented
  • Windows version documented
  • Windows patch level documented
  • CPU configuration documented
  • Memory configuration documented
  • Disk layout documented
  • Antivirus exclusions documented
  • Firewall rules documented
  • ODBC DSNs documented
  • Windows Scheduled Tasks documented

SQL Server Instance

SELECT @@VERSION;
  • SQL Version
  • SQL Edition
  • SQL Build Number
  • SQL CU/SP Level
  • Collation
  • Authentication Mode
  • Max Server Memory
  • MaxDOP
  • Cost Threshold for Parallelism
  • Trace Flags
  • Startup Parameters
  • TempDB Configuration
  • Backup Compression Settings
  • Instant File Initialization Status

2. Database Inventory

For each database collect:

  • Database Name
  • Size (Data / Log)
  • Recovery Model
  • Compatibility Level
  • Database Owner
  • AutoGrowth Settings
  • TDE Enabled
  • CDC Enabled
  • Replication Enabled
  • Service Broker
  • FullText Search
  • FileTables / FILESTREAM
  • CLR Assemblies
  • Query Store
  • Database Scoped Credentials

Database Report

SELECT
    name,
    compatibility_level,
    recovery_model_desc,
    state_desc
FROM sys.databases;

3. Instance Objects Inventory

Security

  • SQL Logins
  • Windows Logins
  • Server Roles
  • Credentials
  • Endpoints
  • Certificates
  • Server Permissions

SQL Agent

  • Jobs
  • Job Steps
  • Schedules
  • Operators
  • Alerts
  • Proxies

Integrations

  • Linked Servers
  • Linked Server Login Mappings
  • Database Mail
  • Maintenance Plans
  • SSIS Packages
  • PowerShell Scripts
  • Batch Files
  • External Interfaces

4. Target Server Preparation

Windows Configuration

  • Server built
  • Joined to domain
  • Latest approved patches installed
  • Antivirus configured
  • Firewall configured
  • Monitoring agent installed
  • Backup agent installed

Storage Layout

Example:

C: OS
D: SQL Binaries
E: Data
F: Logs
G: TempDB
H: Backups

Validation

  • Disk letters match design
  • NTFS Allocation Unit Size = 64K
  • Capacity validated
  • Permissions configured

5. SQL Server 2022 Installation

Installation

  • SQL Engine installed
  • SQL Agent installed
  • Full Text Search installed (if required)
  • Latest approved CU installed

Initial Configuration

  • TCP/IP enabled
  • Static TCP Port configured
  • Max Server Memory configured
  • MaxDOP configured
  • Cost Threshold configured
  • IFI enabled
  • TempDB configured
  • Error Log retention configured

Validation

SELECT @@VERSION;

6. Migrate Instance Objects

Logins

  • Script logins with SID
  • Migrate server roles
  • Migrate permissions
  • Validate orphaned users

Linked Servers

  • Linked Servers migrated
  • Login mappings configured
  • Passwords restored
  • Connectivity tested

Database Mail

  • Accounts created
  • Profiles created
  • Test email sent

SQL Agent

  • Jobs imported
  • Schedules imported
  • Operators imported
  • Alerts imported
  • Proxies imported

Keep all jobs disabled until cutover.

7. Migration Testing

Restore Test

Backup

BACKUP DATABASE <DatabaseName>
TO DISK = '<Path>'
WITH CHECKSUM, COMPRESSION;

Verify

RESTORE VERIFYONLY
FROM DISK = '<Path>';

Restore

RESTORE DATABASE <DatabaseName>
FROM DISK = '<Path>';

Validation

  • Restore successful
  • CHECKDB successful
  • Application connectivity successful

8. T-7 Days Activities

Change Freeze

  • No schema changes
  • No new jobs
  • No new linked servers
  • No major application releases

User Acceptance Testing

  • Login test
  • Read test
  • Write test
  • Reporting test
  • ETL test

9. Cutover Activities

Start Change

  • Open bridge call
  • Confirm stakeholders available
  • Confirm rollback owner

Disable Source Activity

SQL Agent

  • Disable jobs

Applications

  • Stop application services
  • Stop ETL processes
  • Stop integrations

Final Backup

Full Backup

BACKUP DATABASE <DatabaseName>
TO DISK='<FullBackup>';

Transaction Log Backup

BACKUP LOG <DatabaseName>
TO DISK='<LogBackup>';

Validation

  • Backup completed
  • VERIFYONLY successful

Restore to SQL 2022

Restore Full

RESTORE DATABASE <DatabaseName>
FROM DISK='<FullBackup>'
WITH NORECOVERY;

Restore Logs

RESTORE LOG <DatabaseName>
FROM DISK='<LogBackup>'
WITH NORECOVERY;

Recover Database

RESTORE DATABASE <DatabaseName>
WITH RECOVERY;

10. Post-Restore Configuration

Security

  • Logins validated
  • Credentials migrated
  • Linked servers validated
  • Database Mail validated

SQL Agent

  • Jobs enabled
  • Schedules enabled
  • Operators enabled
  • Alerts enabled

11. Smoke Testing

SQL Validation

  • Databases ONLINE
  • SQL Agent running
  • Logins functional
  • Backup path accessible
  • Database Mail functional

Validation Query

SELECT
    name,
    state_desc
FROM sys.databases;

Application Validation

  • Login works
  • Read operations work
  • Write operations work
  • Reports work

ETL Validation

  • ETL jobs complete successfully
  • SSIS packages execute successfully
  • Data import/export successful

12. Performance Validation

Compare against source server baseline.

Metrics

  • CPU
  • Memory
  • Disk Latency
  • Blocking
  • Wait Statistics
  • TempDB Usage
  • Backup Throughput

Wait Stats

SELECT TOP (20)
       wait_type,
       wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

13. Rollback Plan

Rollback Criteria

Rollback if:

  • Application unavailable
  • Data inconsistency
  • Critical login failures
  • ETL failures
  • Severe performance degradation

Rollback Steps

Stop Target Environment

  • Disable target jobs
  • Stop application services

Redirect Applications

  • Revert connection strings
  • Revert DNS aliases

Enable Source

  • Enable source jobs
  • Start source applications

Validate

  • Login successful
  • Read successful
  • Write successful

14. 24-Hour Validation

  • Review SQL Error Logs
  • Review Windows Event Logs
  • Validate Backup Success
  • Validate Job Success
  • Validate Monitoring Alerts
  • Validate ETL Execution

15. 30-Day Stabilization

Daily Review

  • Backup Status
  • Failed Jobs
  • Database Growth
  • Disk Consumption
  • Blocking Events
  • Long Running Queries

Post-Stabilization Tasks

  • Raise compatibility level (if planned)
  • Enable Query Store tuning (if planned)
  • Remove old server references
  • Update CMDB
  • Update documentation
  • Schedule source server decommission

DBA Killer Checklist

Most frequently forgotten migration items:

  • SQL Agent Schedules
  • SQL Agent Proxies
  • Linked Server Passwords
  • ODBC DSNs
  • Scheduled Tasks
  • Database Mail
  • TDE Certificates
  • Backup Jobs
  • Maintenance Plans
  • SSIS Packages
  • CLR Assemblies
  • Service Broker
  • Monitoring Agents
  • Antivirus Exclusions
  • Shared Folders
  • Connection Strings
  • TempDB Configuration
  • Trace Flags
  • Startup Parameters
  • Local Service Accounts

Migration Success Criteria

Migration is successful when:

  • All databases are ONLINE
  • Applications connected successfully
  • ETL processes completed successfully
  • SQL Agent jobs functioning
  • Backups functioning
  • Monitoring functioning
  • Performance acceptable
  • Business owner sign-off received
Reference in New Issue View Git Blame Copy Permalink