# 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

```sql
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

```sql
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:

```text
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

```sql
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

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

### Verify

```sql
RESTORE VERIFYONLY
FROM DISK = '<Path>';
```

### Restore

```sql
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

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

### Transaction Log Backup

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

### Validation

- [ ] Backup completed
- [ ] VERIFYONLY successful

---

## Restore to SQL 2022

### Restore Full

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

### Restore Logs

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

### Recover Database

```sql
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

```sql
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

```sql
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