How to Diagnose and Collect Health Information for Multiple Remote SQL Servers from a Central SQL Server

Collecting and analyzing health and event related information from SQL Servers is very important. This can be automated with this SQL script. 

SQL server diagnosis:

Use Dynamic Management ViewsWhen it comes to SQL Server diagnosis, database administrator always uses dynamic management views to collect key information from SQL Server instances. The diagnosing task can be made easier with the less commonly used stored procedure called sys.sp_serverdiagnostics. The only drawback is, this stored procedure is available only from SQL Server 2012. If you have SQL Servers running on versions older than SQL 2012, you cannot use this stored procedure.

Script 1:

The script1 has to be executed on a Central SQL Server. This script creates two tables. The first table is to store server names from which we will be collecting the health and event related information. The second table is to store the collected information. Through a cursor, we will be creating a linked server for each server in the first table. On each linked server, we will create a new database and name it as DBA. In this database, a new table would be created and this table stores health and event related information locally.

CREATE TABLE TBL1 (cservername VARCHAR(200))

CREATE TABLE CENTRALDIAGTBL1 (
    c_servername VARCHAR(200)
    ,c_crtime DATETIME
    ,c_cmpnnttype SYSNAME
    ,c_cmpnntname SYSNAME
    ,c_stte INT
    ,c_sttedescription SYSNAME
    ,c_xmldata XML
    )

DECLARE @cmd NVARCHAR(2000)
DECLARE @cmd2 NVARCHAR(4000)
DECLARE @vsvrname VARCHAR(200)
DECLARE @vuser VARCHAR(100)
DECLARE @vpwd VARCHAR(100)

DECLARE cur1 CURSOR
FOR
SELECT cservername
FROM TBL1

OPEN cur1

FETCH NEXT
FROM cur1
INTO @vsvrname

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @vsvrname

    SET @vuser = 'linkedserveruser'
    SET @vpwd = 'linkedserveruser'
    SET @cmd = 'EXEC sp_addlinkedserver @server=''' + @vsvrname + ''''

    PRINT @cmd

    EXEC (@cmd)

    SET @cmd = 'EXEC sp_addlinkedsrvlogin ''' + @vsvrname + ''', ''false'', NULL, ''' + @vuser + ''', ''' + @vpwd + ''''

    PRINT @cmd

    EXEC (@cmd)

    SET @cmd2 = 'IF  NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N''''DBA'''')
begin
CREATE DATABASE [DBA]
end
use [DBA]
CREATE TABLE DIAGTBL1
(
      c_crtime DateTime,  
      c_cmpnnttype sysname,  
      c_cmpnntname sysname,  
      c_stte int,  
      c_sttedescription sysname,  
      c_xmldata xml
)
CREATE TABLE DIAGTBLLOG
(
      c_crtime DateTime,  
      c_cmpnnttype sysname,  
      c_cmpnntname sysname,  
      c_stte int,  
      c_sttedescription sysname
)
INSERT INTO DIAGTBL1
EXEC sp_server_diagnostics
TRUNCATE TABLE DIAGTBLLOG
INSERT INTO DIAGTBLLOG
SELECT  c_crtime,c_cmpnnttype,c_cmpnntname,c_stte,c_sttedescription from DIAGTBL1'
    SET @cmd = 'EXEC (''' + @cmd2 + ''') at [' + @vsvrname + ']'

    PRINT @cmd

    EXEC (@cmd)

    FETCH NEXT
    FROM cur1
    INTO @vsvrname
END

CLOSE cur1

DEALLOCATE cur1

Script 2:

The script2 will collect the health and event related information from each linked server and stores it in the second table that was created using Script1

DECLARE @vsvrname VARCHAR(200)
DECLARE @cmd NVARCHAR(2000)

DECLARE cur1 CURSOR
FOR
SELECT cservername
FROM TBL1

OPEN cur1

FETCH NEXT
FROM cur1
INTO @vsvrname

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'INSERT INTO CENTRALDIAGTBL1(c_servername,c_crtime,c_cmpnnttype,c_cmpnntname,c_stte,c_sttedescription) Select @@servername, c_crtime,c_cmpnnttype,c_cmpnntname,c_stte,c_sttedescription from [' + + @vsvrname + '].DBA.DBO.DIAGTBLLOG'

    EXEC (@cmd)

    FETCH NEXT
    FROM cur1
    INTO @vsvrname
END

CLOSE cur1

DEALLOCATE cur1

Points to remember:

SQL Server 2012XML datatype is not supported in distributed queries. That is why on each linked server we would be creating 2 tables. One table would hold XML column and the other table would hold without XML column. You can modify the script to include the remote mode in the sys.sp_serverdiagnostics. This will help you to view periodic data. Disaster can happen at any time. Ensure that there is a scheduled backup job running in your SQL Environment. A backup will always help you to quickly repair SQL Server.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.