How to Auto Check SQL Server Configurations and Fix Non-compliances

You can complete a quick audit of SQL Server’s configuration on all servers in your Company. The script will be helpful to auto fix configurations that are non-compliant

Create three tables:

SQL Server 2014We are going to create 3 tables. In the first table, we will be saving SQL Server Configuration name and its recommended value. This table will act as source table for comparison. If you are using SQL 2014, do not miss to take care of the configuration item namely “backup checksum default”. By default the value is set as zero, but the recommended value is 1 and you can easily set it using EXEC sp_configure command. If this is set to 1, whenever SQL Server reads pages for the backup, it will also verify any existing page checksums.

In the second table, we will be saving server names from our SQL environment. The last table will be used as the log table and this will store configuration data from remote servers and compare with our source table i.e., the first table.

Auto fix

SQL Configuration ManagerThrough a cursor, the script will read each server name from the second table and creates a Linked server for each server. With the help of the linked server, the configuration data is read from each server and loaded into the log table i.e., the third table. Configuration and its value from each row of the log table is compared against the source table. If the value is not compliant, the configuration is auto fixed with recommended value through sqlcmd. In this script, the autofix is set only for max server memory. The same rule can be applied for other configurations.

However in case of corrupted SQL Server db you would not be able to automatically audit SQL Server configurations. In such a case you can use SQL server recovery tools such as Datanumen SQL recovery tool.

CREATE TABLE TBL0 (
    cname VARCHAR(200)
    ,cvalue BIGINT
    )

INSERT INTO TBL0
SELECT NAME
    ,cast(value AS BIGINT)
FROM sys.configurations

CREATE TABLE TBL1 (cservername VARCHAR(200))

--Insert into TBL1 values ('SRVR1')
CREATE TABLE TBL2 (
    csvrname VARCHAR(200)
    ,cname VARCHAR(200)
    ,cvalue BIGINT
    ,cminvalue BIGINT
    ,cmaxvalue BIGINT
    ,cvalinuse BIGINT
    ,cstatus VARCHAR(200)
    )

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

SET @vsvrname = 'SRVR1'
SET @vuser = 'linkedserveruser'
SET @vpwd = 'linkedserveruserpassword'
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)

INSERT INTO TBL2 (
    csvrname
    ,cname
    ,cvalue
    ,cminvalue
    ,cmaxvalue
    ,cvalinuse
    )
SELECT @@servername AS 'Servername'
    ,NAME
    ,cast(value AS BIGINT)
    ,cast(minimum AS BIGINT)
    ,cast(maximum AS BIGINT)
    ,cast(value_in_use AS BIGINT)
FROM [CPH-MSSQL07-T].master.sys.configurations

SELECT *
FROM TBL2

DECLARE @cur1svrname VARCHAR(200)
DECLARE @cur1name VARCHAR(500)
DECLARE @cur1value BIGINT

DECLARE CUR1 CURSOR
FOR
SELECT csvrname
    ,cname
    ,cvalue
FROM TBL2

OPEN CUR1

FETCH NEXT
FROM CUR1
INTO @cur1svrname
    ,@cur1name
    ,@cur1value

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sname VARCHAR(500)
    DECLARE @svalue BIGINT

    SET @svalue = (
            SELECT cvalue
            FROM TBL0
            WHERE cname = @cur1name
            )

    IF @cur1value <> @svalue
    BEGIN
        DECLARE @cmd2 VARCHAR(1000)

        SET @cmd2 = 'SQLCMD -S ' + @cur1svrname + ' -E -Q "EXEC sp_configure ''max server memory (MB)'',' + cast(@svalue AS VARCHAR(100)) + '"'

        -- Print @cmd2
        EXEC xp_cmdshell @cmd2

        UPDATE TBL2
        SET cstatus = 'WAS NON COMPLIANT. NOW FIXED'
        WHERE csvrname = @cur1svrname
            AND cname = @cur1name
    END

    FETCH NEXT
    FROM CUR1
    INTO @cur1svrname
        ,@cur1name
        ,@cur1value
END

CLOSE CUR1

DEALLOCATE CUR1

Author Introduction:

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

Leave a Reply

Your email address will not be published. Required fields are marked *