Upgrade Types:
- In-Place (single server)
- Side-by-Side / Backup and Restore (swing migration)
- Transaction Log Shipping
- HA Rolling Upgrade / Availability Groups
Audit Checklist:
Audit:
- Internal SQL version numbers of both source and target servers (if swing)
- Database compatibility levels of each database
- SQL Release - SQL Version - Database Compatibility Level - Notes
- Deprecated and discontinued features
OS and SQL Compatibility Matrix:
| SQL Server Version | Minimum OS | Maximum OS (supported) | .Net Minimum |
| 2025 | Server 2019 / Windows 10 | <current - 2025> | .Net Framework 4.7.2 |
| 2022 | Server 2019 / Server Core 2016 / Linux (RHEL 8.0+) / Windows 11 | <current - 2025> | .Net Framework 4.7.2 |
| 2019 | Windows Server 2016 / Server Core 2016 / Windows 10 TH1 1507+ / Linux (RHEL 7.7+) | <current - 2025> | OS minimum .Net sufficient |
| 2017 | Windows Server 2012 / Windows 10 SP3 / Linux (RHEL 7.3+) | Server 2022 Standard - Datacenter | .Net Framework 4.6 |
| 2016 | Windows Server 2012 / Windows 10 |  | |
| 2014 | Windows Server 2008 SP2 / Windows 8 SP3 | | |
| | | |
Audit Common Deprecated SQL (Feature) Calls:
BACKUP WITH PASSWORD |
--> |
Backup encryption (2014+) |
Database Mirroring |
--> |
Always On Availability Groups (2016+) |
DBCC SHOWCONTIG |
--> |
sys.dm_db_index_physical_stats (2008 R2) |
Non-ANSI *= outer join syntax |
--> |
LEFT OUTER JOIN (2008 R2) |
Polybase with Hadoop |
--> |
Polybase with ODBC generic (2022+) |
sp_db_vardecimal_storage_format |
--> |
Row compression (always on 2016+) |
Stretch Database |
--> |
Azure Synapse / Tiered Storage (2019+) |
WITH FASTFIRSTROW hint |
--> |
OPTION(FAST n) (2014+) |
Audit SQL/OS Versions (T-SQL):
SELECT
@@SERVERNAME AS ServerName,
@@VERSION AS FullVersion,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ServicePack,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition; -- 3=Enterprise, 2=Standard
Audit OS Edition (Standard vs Datacenter, for example, T-SQL):
Note:
- returns rows for enterprise features found
SELECT
feature_name,
feature_enabled AS IsEnabled
FROM sys.dm_db_persisted_sku_features;
Audit Database Compatibility Levels (T-SQL):
SELECT
name AS DatabaseName,
compatibility_level,
state_desc AS State,
recovery_model_desc AS RecoveryModel,
log_reuse_wait_desc AS LogReuseWait,
CAST(ROUND(size * 8.0 / 1024 / 1024, 2) AS DECIMAL(10,2)) AS DataFileSizeGB
FROM sys.databases
WHERE database_id > 4 -- exclude system databases
ORDER BY size DESC;
Audit Deprecated Features:
SELECT
object_name,
counter_name,
instance_name,
cntr_value AS UsageCount
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Deprecated%'
AND cntr_value > 0
ORDER BY cntr_value DESC;
Audit Deprecated Syntax in Query Plans:
SELECT TOP 20
qs.execution_count,
qs.total_worker_time / qs.execution_count AS avg_cpu_us,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) AS StatementText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%FASTFIRSTROW%' -- removed in 2016
OR st.text LIKE '%HOLDLOCK%table%' -- syntax change
OR st.text LIKE '%sp_db_vardecimal%' -- removed in 2016
ORDER BY qs.execution_count DESC;
Audit/Inventory any Linked Servers:
SELECT
name AS LinkedServerName,
product AS Product,
provider AS Provider,
data_source AS DataSource,
is_linked AS IsLinked,
is_remote_login_enabled
FROM sys.servers
WHERE is_linked = 1;
Audit References Current (Old) Server Name:
SELECT
j.name AS JobName,
js.step_name,
js.command
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id
WHERE js.command LIKE '%' + @@SERVERNAME + '%'
OR js.command LIKE '%linked_server_name%'
ORDER BY j.name;
Audit Scheduled/Agent Jobs in MSDB:
(that don't backup and restore)
SELECT
'Jobs' AS ObjectType, COUNT(*) AS Count FROM msdb.dbo.sysjobs UNION ALL
SELECT 'Schedules', COUNT(*) FROM msdb.dbo.sysschedules UNION ALL
SELECT 'Operators', COUNT(*) FROM msdb.dbo.sysoperators UNION ALL
SELECT 'Alerts', COUNT(*) FROM msdb.dbo.sysalerts UNION ALL
SELECT 'Maintenance Plans', COUNT(*) FROM msdb.dbo.sysmaintplan_plans;
Audit SQL Users with Password Hash and SID:
SELECT
'CREATE LOGIN [' + name + '] '
+ 'WITH PASSWORD = ' + CONVERT(NVARCHAR(MAX), password_hash, 1)
+ ' HASHED, '
+ 'SID = ' + CONVERT(NVARCHAR(MAX), sid, 1) + ', '
+ 'DEFAULT_DATABASE = [' + default_database_name + '], '
+ 'CHECK_POLICY = ' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + ', '
+ 'CHECK_EXPIRATION = ' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
+ ';' AS CreateLoginStatement
FROM sys.sql_logins
WHERE name NOT IN ('sa', '##MS_PolicyTsqlExecutionLogin##', '##MS_PolicyEventProcessingLogin##')
AND is_disabled = 0
ORDER BY name;
Audit Mail Accounts:
(that don't backup and restore)
SELECT
a.name AS AccountName,
a.description,
a.email_address,
a.display_name,
s.servername AS SMTPServer,
s.port AS SMTPPort,
s.enable_ssl
FROM msdb.dbo.sysmail_account a
JOIN msdb.dbo.sysmail_server s ON s.account_id = a.account_id;
Audit Mail Profile Configuration:
SELECT
p.name AS ProfileName,
a.name AS AccountName,
pa.sequence_number
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_profileaccount pa ON pa.profile_id = p.profile_id
JOIN msdb.dbo.sysmail_account a ON a.account_id = pa.account_id
ORDER BY p.name, pa.sequence_number;
Audit Full-Text Indexes Created:
(that don't backup and restore)
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS FullTextIndexName,
c.name AS CatalogName,
c.path AS CatalogPath,
i.change_tracking_state_desc
FROM sys.fulltext_indexes i
JOIN sys.fulltext_catalogs c ON c.fulltext_catalog_id = i.fulltext_catalog_id;
Audit Replication Check
(must be removed if performing side-by-side migration, and added back afterwards)
SELECT
name AS DatabaseName,
is_published,
is_subscribed,
is_merge_published,
is_distributor
FROM sys.databases
WHERE is_published = 1
OR is_subscribed = 1
OR is_distributor = 1;
Audit Database Size and Get Estimated Backup and Restore Duration:
(assumption: backup at ~500 MB/s, and ~400 MS/s)
SELECT
DB_NAME(database_id) AS DatabaseName,
SUM(size) * 8.0 / 1024 / 1024 AS TotalSizeGB,
CAST(SUM(size) * 8.0 / 1024 / 1024 / 500 * 60 AS INT) AS EstBackupMin,
CAST(SUM(size) * 8.0 / 1024 / 1024 / 400 * 60 AS INT) AS EstRestoreMin
FROM sys.master_files
WHERE database_id > 4
GROUP BY database_id
ORDER BY TotalSizeGB DESC;
Validate Backup:
- Perform Full backup + Tail-Log backup
- Perform VERIFYONLY
SELECT
d.name AS DatabaseName,
MAX(b.backup_finish_date) AS LastFullBackup,
DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) AS HoursSinceLastBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b
ON b.database_name = d.name AND b.type = 'D'
WHERE d.database_id > 4
GROUP BY d.name
ORDER BY LastFullBackup ASC;
previous page
|