SQL Serverenvironment assessment guide

SELECT
    SERVERPROPERTY('ServerName') AS server_name,
    SERVERPROPERTY('MachineName') AS machine_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('ProductUpdateReference') AS product_update_reference,
    SERVERPROPERTY('IsClustered') AS is_clustered,
    SERVERPROPERTY('IsHadrEnabled') AS is_hadr_enabled,
    osi.sqlserver_start_time,
    osi.cpu_count,
    osi.committed_kb / 1024 AS committed_mb
FROM sys.dm_os_sys_info AS osi;

SELECT
    name,
    value,
    value_in_use,
    description,
    is_dynamic,
    is_advanced
FROM sys.configurations
WHERE name IN (
    N'max server memory (MB)',
    N'min server memory (MB)',
    N'max degree of parallelism',
    N'cost threshold for parallelism',
    N'backup compression default',
    N'xp_cmdshell',
    N'clr enabled',
    N'Ad Hoc Distributed Queries',
    N'remote admin connections'
)
ORDER BY name;

SELECT
    d.name AS database_name,
    d.state_desc,
    d.user_access_desc,
    d.recovery_model_desc,
    d.compatibility_level,
    SUSER_SNAME(d.owner_sid) AS database_owner,
    d.is_read_only,
    d.is_auto_close_on,
    d.is_auto_shrink_on,
    d.page_verify_option_desc,
    d.log_reuse_wait_desc
FROM sys.databases AS d
WHERE d.database_id > 4
ORDER BY d.name;

SELECT
    DB_NAME(mf.database_id) AS database_name,
    mf.type_desc,
    mf.name AS logical_name,
    mf.physical_name,
    CONVERT(decimal(18,2), mf.size * 8.0 / 1024) AS size_mb,
    CASE
        WHEN mf.max_size = -1 THEN N'Unlimited'
        ELSE CONVERT(nvarchar(30), CONVERT(decimal(18,2), mf.max_size * 8.0 / 1024))
    END AS max_size_mb,
    CASE
        WHEN mf.is_percent_growth = 1 THEN CONVERT(nvarchar(30), mf.growth) + N'%'
        ELSE CONVERT(nvarchar(30), CONVERT(decimal(18,2), mf.growth * 8.0 / 1024)) + N' MB'
    END AS growth_setting
FROM sys.master_files AS mf
WHERE mf.database_id > 4
ORDER BY database_name, mf.type_desc, mf.file_id;

WITH LastBackups AS (
    SELECT
        bs.database_name,
        MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup,
        MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_differential_backup,
        MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup
    FROM msdb.dbo.backupset AS bs
    WHERE bs.backup_finish_date >= DATEADD(day, -30, SYSDATETIME())
    GROUP BY bs.database_name
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    lb.last_full_backup,
    lb.last_differential_backup,
    lb.last_log_backup,
    DATEDIFF(hour, lb.last_full_backup, SYSDATETIME()) AS hours_since_last_full,
    DATEDIFF(minute, lb.last_log_backup, SYSDATETIME()) AS minutes_since_last_log
FROM sys.databases AS d
LEFT JOIN LastBackups AS lb
    ON lb.database_name = d.name
WHERE d.database_id > 4
ORDER BY d.name;

SELECT TOP (100)
    rh.destination_database_name,
    rh.restore_date,
    rh.restore_type,
    rh.replace,
    rh.recovery,
    bs.database_name AS source_database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bmf.physical_device_name
FROM msdb.dbo.restorehistory AS rh
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.backup_set_id = rh.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
ORDER BY rh.restore_date DESC;

SELECT
    j.name AS job_name,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    j.enabled,
    j.date_created,
    j.date_modified,
    s.step_id,
    s.step_name,
    s.subsystem,
    s.database_name,
    s.command
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobsteps AS s
    ON s.job_id = j.job_id
ORDER BY j.name, s.step_id;

SELECT TOP (100)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_status,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_datetime,
    h.run_duration,
    h.sql_severity,
    h.sql_message_id,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_status IN (0, 2, 3)
  AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(day, -14, SYSDATETIME())
ORDER BY run_datetime DESC;

SELECT
    a.name AS alert_name,
    a.enabled AS alert_enabled,
    a.message_id,
    a.severity,
    a.delay_between_responses,
    a.has_notification,
    o.name AS operator_name,
    o.enabled AS operator_enabled,
    o.email_address,
    n.notification_method
FROM msdb.dbo.sysalerts AS a
LEFT JOIN msdb.dbo.sysnotifications AS n
    ON n.alert_id = a.id
LEFT JOIN msdb.dbo.sysoperators AS o
    ON o.id = n.operator_id
ORDER BY a.name, o.name;

SELECT
    sp.name AS principal_name,
    sp.type_desc,
    sp.is_disabled,
    sp.default_database_name,
    spr.name AS server_role
FROM sys.server_principals AS sp
LEFT JOIN sys.server_role_members AS srm
    ON srm.member_principal_id = sp.principal_id
LEFT JOIN sys.server_principals AS spr
    ON spr.principal_id = srm.role_principal_id
WHERE sp.type IN ('S', 'U', 'G')
ORDER BY sp.name, spr.name;

SELECT
    DB_NAME() AS database_name,
    dp.name AS database_principal,
    dp.type_desc,
    dp.authentication_type_desc,
    dp.default_schema_name,
    rp.name AS database_role,
    sp.name AS matching_server_principal
FROM sys.database_principals AS dp
LEFT JOIN sys.database_role_members AS drm
    ON drm.member_principal_id = dp.principal_id
LEFT JOIN sys.database_principals AS rp
    ON rp.principal_id = drm.role_principal_id
LEFT JOIN sys.server_principals AS sp
    ON sp.sid = dp.sid
WHERE dp.type IN ('S', 'U', 'G')
  AND dp.principal_id > 4
ORDER BY dp.name, rp.name;

SELECT
    s.name AS linked_server_name,
    s.product,
    s.provider,
    s.data_source,
    s.is_linked,
    s.is_remote_login_enabled,
    ll.local_principal_id,
    SUSER_SNAME(ll.local_principal_id) AS local_principal_name,
    ll.uses_self_credential,
    ll.remote_name
FROM sys.servers AS s
LEFT JOIN sys.linked_logins AS ll
    ON ll.server_id = s.server_id
WHERE s.server_id <> 0
ORDER BY s.name, local_principal_name;

SELECT
    c.name AS credential_name,
    c.credential_identity,
    p.name AS proxy_name,
    p.enabled AS proxy_enabled,
    sp.name AS proxy_login
FROM sys.credentials AS c
LEFT JOIN msdb.dbo.sysproxies AS p
    ON p.credential_id = c.credential_id
LEFT JOIN msdb.dbo.sysproxylogin AS pl
    ON pl.proxy_id = p.proxy_id
LEFT JOIN sys.server_principals AS sp
    ON sp.sid = pl.sid
ORDER BY c.name, p.name, sp.name;

SELECT
    ag.name AS availability_group_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    ar.availability_mode_desc,
    ar.failover_mode_desc
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
    ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS ars
    ON ars.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name;

SELECT
    primary_database,
    backup_directory,
    backup_share,
    backup_retention_period,
    monitor_server
FROM msdb.dbo.log_shipping_primary_databases
ORDER BY primary_database;

SELECT
    secondary_database,
    primary_server,
    restore_delay,
    restore_mode,
    disconnect_users
FROM msdb.dbo.log_shipping_secondary_databases
ORDER BY secondary_database;