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:
We 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
Through 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