Centralny rejestr instancji SQL Server — własny mini-CMDB dla DBA

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

CREATE SCHEMA inventory;
GO

CREATE TABLE inventory.SqlInstance
(
    SqlInstanceId int IDENTITY(1,1) NOT NULL,
    ServerName sysname NOT NULL,
    InstanceName sysname NULL,
    Port int NULL,
    EnvironmentCode varchar(20) NOT NULL,
    ProductVersion varchar(50) NULL,
    ProductLevel varchar(50) NULL,
    Edition nvarchar(200) NULL,
    TechnicalOwner nvarchar(200) NULL,
    BusinessOwner nvarchar(200) NULL,
    MonitoringEnabled bit NOT NULL
        CONSTRAINT DF_SqlInstance_MonitoringEnabled DEFAULT (1),
    LastScanAt datetime2(0) NULL,
    LastConnectionStatus varchar(30) NULL,
    IsActive bit NOT NULL
        CONSTRAINT DF_SqlInstance_IsActive DEFAULT (1),
    CreatedAt datetime2(0) NOT NULL
        CONSTRAINT DF_SqlInstance_CreatedAt DEFAULT (SYSDATETIME()),
    CONSTRAINT PK_SqlInstance PRIMARY KEY CLUSTERED (SqlInstanceId),
    CONSTRAINT UQ_SqlInstance_Server_Instance
        UNIQUE (ServerName, InstanceName)
);
GO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE inventory.InstanceScan
(
    InstanceScanId bigint IDENTITY(1,1) NOT NULL,
    SqlInstanceId int NOT NULL,
    ScanStartedAt datetime2(0) NOT NULL,
    ScanFinishedAt datetime2(0) NULL,
    ScanStatus varchar(30) NOT NULL,
    ErrorMessage nvarchar(4000) NULL,
    ProductVersion varchar(50) NULL,
    Edition nvarchar(200) NULL,
    DatabaseCount int NULL,
    CONSTRAINT PK_InstanceScan PRIMARY KEY CLUSTERED (InstanceScanId)
);
GO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    SERVERPROPERTY('MachineName') AS MachineName,
    SERVERPROPERTY('ServerName') AS ServerName,
    SERVERPROPERTY('InstanceName') AS InstanceName,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    host_platform,
    host_distribution,
    host_release
FROM sys.dm_os_host_info;