Tuesday, May 10, 2011

Sync Config Settings between Servers

I'm setting up a new Staging Environment and am trying to find ways we can sync Staging with our production environment. My current task is to Sync the configurations and settings. Here is the script I came up with for the Config Settings. Hope it's of use.



/***************************************************
** Title: Sync Config Settings between Servers **
** Author: Eric Zierdt **
** Date: 03/10/2011 **
***************************************************/

/**************************************
** STEP 1: Turn on Advanced Options **
**************************************/
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO


/*******************************************
** STEP 2: Populate the values from Prod **
*******************************************/
-- SELECT * INTO #TMP_PROD_Configs FROM ProductionServer.msdb.sys.configurations C


/*****************************************
** STEP 3: Run Cursor and copy results **
*****************************************/
DECLARE @SQL VARCHAR(MAX)
,@name VARCHAR(300)
,@value SQL_VARIANT

DECLARE SYNC_CURSOR CURSOR FOR
SELECT TOC.name, TOC.value
FROM sys.configurations C
LEFT JOIN #TMP_PROD_Configs TOC ON C.configuration_id = TOC.configuration_id
WHERE C.value <> TOC.VALUE
AND C.configuration_id NOT IN (1543,1544)

OPEN SYNC_CURSOR
FETCH NEXT FROM SYNC_CURSOR
INTO @name, @value
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = '
sp_configure ''' + @Name + ''', ' + CAST(@Value AS VARCHAR(25)) + '
GO
RECONFIGURE
GO
'
PRINT @SQL

FETCH NEXT FROM SYNC_CURSOR
INTO @name, @value
END
CLOSE SYNC_CURSOR
DEALLOCATE SYNC_CURSOR


/*************************************************
** STEP 4: Paste results below, review and run **
*************************************************/





/**********************************************
** STEP 5: Be good and drop your temp table **
**********************************************/
DROP TABLE #TMP_PROD_Configs


/***************************************
** STEP 6: Turn off Advanced Options **
***************************************/
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO


/***************************************
** Query to compare values if needed **
***************************************/
SELECT C.configuration_id, C.name, C.value,C.value_in_use, C.description
, TOC.configuration_id, TOC.name, TOC.value,TOC.value_in_use, TOC.description
FROM sys.configurations C
LEFT JOIN #TMP_PROD_Configs TOC ON C.configuration_id = TOC.configuration_id
WHERE C.value <> TOC.VALUE
AND C.configuration_id NOT IN (1543,1544)