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