SQL Serverupgrade planning guide

SELECT
    SERVERPROPERTY('MachineName') AS machine_name,
    SERVERPROPERTY('ServerName') AS server_name,
    SERVERPROPERTY('InstanceName') AS instance_name,
    SERVERPROPERTY('Edition') AS edition,
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductLevel') AS product_level,
    SERVERPROPERTY('ProductUpdateLevel') AS product_update_level,
    SERVERPROPERTY('ProductBuild') AS product_build,
    SERVERPROPERTY('EngineEdition') AS engine_edition,
    @@VERSION AS version_string;

SELECT
    name,
    value,
    value_in_use,
    description
FROM sys.configurations
ORDER BY name;

SELECT
    d.name AS database_name,
    d.compatibility_level,
    d.recovery_model_desc,
    d.state_desc,
    d.is_read_only,
    d.create_date
FROM sys.databases AS d
WHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')
ORDER BY d.compatibility_level, d.name;

SELECT
    DB_NAME() AS database_name,
    configuration_id,
    name,
    value,
    value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN (
    N'LEGACY_CARDINALITY_ESTIMATION',
    N'QUERY_OPTIMIZER_HOTFIXES',
    N'PARAMETER_SNIFFING',
    N'MAXDOP'
)
ORDER BY name;

-- Template only. Change compatibility level after testing the workload.
-- Do not combine this with the engine upgrade unless it is part of the tested plan.

ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 160;

WITH RecentJobHistory AS (
    SELECT
        h.job_id,
        h.step_id,
        h.step_name,
        h.run_status,
        h.run_date,
        h.run_time,
        h.run_duration,
        h.message
    FROM msdb.dbo.sysjobhistory AS h
    WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -14, SYSDATETIME()), 112))
)
SELECT TOP (100)
    j.name AS job_name,
    j.enabled,
    h.step_id,
    h.step_name,
    h.run_status,
    DATETIMEFROMPARTS(
        h.run_date / 10000,
        (h.run_date % 10000) / 100,
        h.run_date % 100,
        h.run_time / 10000,
        (h.run_time % 10000) / 100,
        h.run_time % 100,
        0
    ) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS run_duration_seconds,
    h.message
FROM msdb.dbo.sysjobs AS j
LEFT JOIN RecentJobHistory AS h
    ON h.job_id = j.job_id
ORDER BY
    CASE WHEN h.run_date IS NULL THEN 1 ELSE 0 END,
    run_start_time DESC,
    j.name,
    h.step_id;

SELECT
    N'Linked servers' AS dependency_type,
    (SELECT COUNT(*) FROM sys.servers AS s WHERE s.is_linked = 1) AS item_count,
    STUFF((
        SELECT TOP (10) N'; ' + QUOTENAME(s.name)
        FROM sys.servers AS s
        WHERE s.is_linked = 1
        ORDER BY s.name
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, N'') AS sample_items

UNION ALL

SELECT
    N'Credentials' AS dependency_type,
    (SELECT COUNT(*) FROM sys.credentials AS c) AS item_count,
    STUFF((
        SELECT TOP (10) N'; ' + QUOTENAME(c.name)
        FROM sys.credentials AS c
        ORDER BY c.name
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, N'') AS sample_items

UNION ALL

SELECT
    N'Enabled SQL Agent jobs' AS dependency_type,
    (SELECT COUNT(*) FROM msdb.dbo.sysjobs AS j WHERE j.enabled = 1) AS item_count,
    STUFF((
        SELECT TOP (10) N'; ' + QUOTENAME(j.name)
        FROM msdb.dbo.sysjobs AS j
        WHERE j.enabled = 1
        ORDER BY j.name
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, N'') AS sample_items

UNION ALL

SELECT
    N'Server triggers' AS dependency_type,
    (SELECT COUNT(*) FROM sys.server_triggers AS tr WHERE tr.is_disabled = 0) AS item_count,
    STUFF((
        SELECT TOP (10) N'; ' + QUOTENAME(tr.name)
        FROM sys.server_triggers AS tr
        WHERE tr.is_disabled = 0
        ORDER BY tr.name
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, N'') AS sample_items;

WITH LastBackups AS (
    SELECT
        bs.database_name,
        bs.type,
        MAX(bs.backup_finish_date) AS last_backup_finish_date
    FROM msdb.dbo.backupset AS bs
    WHERE bs.type IN ('D', 'I', 'L')
    GROUP BY bs.database_name, bs.type
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    d.state_desc,
    MAX(CASE WHEN lb.type = 'D' THEN lb.last_backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN lb.type = 'I' THEN lb.last_backup_finish_date END) AS last_differential_backup,
    MAX(CASE WHEN lb.type = 'L' THEN lb.last_backup_finish_date END) AS last_log_backup
FROM sys.databases AS d
LEFT JOIN LastBackups AS lb
    ON lb.database_name = d.name
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc, d.state_desc
ORDER BY d.name;

-- Template only. Schedule this where the runtime and I/O load are acceptable.
-- Run before and after the upgrade test run, and record the output.

DBCC CHECKDB (N'YourDatabase') WITH NO_INFOMSGS;

SELECT
    DB_NAME() AS database_name,
    actual_state_desc,
    desired_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    query_capture_mode_desc,
    size_based_cleanup_mode_desc,
    stale_query_threshold_days
FROM sys.database_query_store_options;

SELECT TOP (50)
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    rs.count_executions,
    CAST(rs.avg_duration / 1000.0 AS decimal(18,2)) AS avg_duration_ms,
    CAST(rs.avg_cpu_time / 1000.0 AS decimal(18,2)) AS avg_cpu_ms,
    CAST(rs.avg_logical_io_reads AS decimal(18,2)) AS avg_logical_reads,
    rs.last_execution_time
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p
    ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q
    ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
ORDER BY rs.avg_duration DESC;