Credits:
Author: Farhan F Sabzaali
Date: 17 mar 2009
Description
This will be one of the few scripts that I publish without first testing as I don't currently have a server setup on which I can perform the tests, but visually the scripts look fairly complete.
The instructions within the scripts are comprehensive, and there is a recommendation to run the resulting TSQL step-by-step, so any errors would immediately be highlighted.
NOTE: Running the scripts below makes no changes to the SQL Server.
Code
/******************************************************************************************** * Purpose: Generate steps to setup mirroring in SQL 2005 * * Author: Farhan F. Sabzaali * * Date Created: March 17, 2009 * * Last Updated: * * Updates Made: * * Instructions: 1. Modify section marked "CHANGE ME" * * 2. Run Query - Change Result to Text as output * * 3. Execute one step at a time * ********************************************************************************************/ DECLARE @DatabaseName NVARCHAR (250) ,@Principal NVARCHAR (1000) ,@Mirror NVARCHAR (1000) ,@DomainAccount NVARCHAR (500) ,@DefaultLanguage NVARCHAR (250) ,@ListenerPort NVARCHAR (50) ,@PrincipalBackupFolder NVARCHAR (1000) ,@DatabaseDataFileName NVARCHAR (250) ,@MirrorDataPath NVARCHAR (1000) ,@MirrorLogPath NVARCHAR (1000) ,@PrincipalTCP NVARCHAR (1000) ,@MirrorTCP NVARCHAR (1000) ,@MirrorSafety NVARCHAR (50) ,@MirrorMonitorJobName NVARCHAR (250) ,@DBDeleteDate NVARCHAR (100) ,@Step1 NVARCHAR (MAX) ,@Step2 NVARCHAR (MAX) ,@Step3 NVARCHAR (MAX) ,@Step4 NVARCHAR (MAX) ,@Step5 NVARCHAR (MAX) ,@Step6 NVARCHAR (MAX) ,@Step7 NVARCHAR (MAX) ,@Step8 NVARCHAR (MAX) ,@Step9 NVARCHAR (MAX) ,@AllSteps NVARCHAR (MAX) ,@NewLine CHAR (1) ,@APOS CHAR (1)
/* CHANGE ME START */ SET @DatabaseName = 'AdventureWorks' --Database Name SET @DatabaseDataFileName = 'AdventureWorks_Data' --Logical Name of Data File SET @Principal = 'SERVERA\INSTANCE1' --Principal Server \ Instance Name SET @Mirror = 'SERVERB\INSTANCE1' --Mirror Server \ Instance Name SET @DomainAccount = 'Domain\SQLAGENT' --Domain Account used to setup mirror - should have access on both SET @DefaultLanguage = '[us_english]' --Default Language SET @ListenerPort = '5512' --Default TCP \ Mirror Listening Port SET @PrincipalBackupFolder = '\\ServerA\Backup_Instance1\' --Shared Drive on Principal that can be accessed by Mirror SET @MirrorDataPath = 'M:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\' --Folder on Mirror where Data files are saved SET @MirrorLogPath = 'N:\Microsoft SQL Server\MSSQL.1\MSSQL\LOGS\' --Folder on Mirror where Log files are saved SET @PrincipalTCP = 'TCP://ServerA.corporate.domain.com:5512' --Principal TCP Configuration - Refer to BOL for details SET @MirrorTCP = 'TCP://ServerB.corporate.domain.com:5512' --Mirror TCP Configuration - Refer to BOL for details SET @MirrorSafety = 'FULL' --Mirror Safety - FULL OR OFF /* CHANGE ME FINISH */ SET @MirrorMonitorJobName = 'Database Mirroring Monitor Job' SET @NewLine = CHAR(13) SET @APOS = CHAR (39) SET @DBDeleteDate = CONVERT(NVARCHAR (100), GETDDATE() - 1, 101) SET @Step1 = '' SET @Step2 = '' SET @Step3 = '' SET @Step4 = '' SET @Step5 = '' SET @Step6 = '' SET @Step7 = '' SET @Step8 = '' SET @Step9 = '' SET @AllSteps = '' SET @Step1 = @Step1 + '--SQL Server Mirror Setup Script - Step 1: Add Login For EndPoint' SET @Step1 = @Step1 + @NewLine + '--Run On Principal Server' SET @Step1 = @Step1 + @NewLine + @NewLine + '--Step 1 Start' SET @Step1 = @Step1 + @NewLine + ' USE [Master]; GO IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name=' + @APOS + @DomainAccount + @APOS + ') BEGIN CREATE LOGIN [' + @DomainAccount + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master],DEFAULT_LANGUAGE=' + @DefaultLanguage + '; PRINT ' + @APOS + 'Create Windows login [' + @DomainAccount + '].' + @APOS + ' END GO
IF NOT EXISTS (SELECT * FROM sys.database_mirroring_endpoints WHERE name=' + @APOS + 'MirrorEndpoint' + @APOS + ') BEGIN CREATE ENDPOINT MirrorEndpoint AUTHORIZATION [' + @DomainAccount + '] STATE=STARTED AS TCP ( LISTENER_PORT=' + @ListenerPort + ', LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = SUPPORTED, ROLE=ALL ); PRINT ' + @APOS + 'Create mirroring endpoint [MirrorEndpoint].' + @APOS + ' END GO'
SET @Step1 = @Step1 + @NewLine + @NewLine + '--Step 1 Finish'
SET @Step2 = @Step2 + '--SQL Server Mirror Setup Script - Step 2: Check Database Recovery Mode' SET @Step2 = @Step2 + @NewLine + '--Run On Principal Server' SET @Step2 = @Step2 + @NewLine + @NewLine + '--Step 2 Start' SET @Step2 = @Step2 + @NewLine + ' USE [Master]; GO IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = ' + @APOS + @DatabaseName + @APOS + ' AND recovery_model = 1) BEGIN ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY FULL; PRINT ' + @APOS + 'Set full recovery for database [' + @DatabaseName + '].' + @APOS + ' END GO' SET @Step2 = @Step2 + @NewLine + @NewLine + '--Step 2 Finish'
SET @Step3 = @Step3 + '--SQL Server Mirror Setup Script - Step 3: Backup Database' SET @Step3 = @Step3 + @NewLine + '--Run On Principal Server' SET @Step3 = @Step3 + @NewLine + @NewLine + '--Step 3 Start' SET @Step3 = @Step3 + @NewLine + ' USE [Master]; GO
BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.bak' + @APOS + ' WITH DESCRIPTION = ' + @APOS + 'Back up ' + @DatabaseName + ' database Full' + @APOS + ', FORMAT, INIT, NAME = ' + @APOS + @DatabaseName +'_Step3_Principal.bak' + @APOS + ', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [' + @DatabaseName + '] TO DISK = ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.trn' + @APOS + ' WITH DESCRIPTION = ' + @APOS + 'Back up ' + @DatabaseName + ' database Log' + @APOS + ', FORMAT, INIT, NAME = ' + @APOS + @DatabaseName +'_Step3_Principal.trn' + @APOS + ', SKIP, REWIND, NOUNLOAD, STATS = 10 GO' SET @Step3 = @Step3 + @NewLine + @NewLine + '--Step 3 Finish'
SET @Step4 = @Step4 + '--SQL Server Mirror Setup Script - Step 4: Restore Database' SET @Step4 = @Step4 + @NewLine + '--Run On Mirror Server' SET @Step4 = @Step4 + @NewLine + @NewLine + '--Step 4 Start' SET @Step4 = @Step4 + @NewLine + ' USE [Master]; GO
RESTORE DATABASE [' + @DatabaseName + '] FROM DISK = ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.bak' + @APOS + ' WITH MOVE ' + @APOS + @DatabaseDataFileName + @APOS + ' TO ' + @APOS + @MirrorDataPath + @Databasename + '.mdf' + @APOS + ', MOVE ' + @APOS + @DatabaseName + '_Log' + @APOS + ' TO ' + @APOS + @MirrorLogPath + @DatabaseName + '.ldf' + @APOS + ', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 GO RESTORE LOG [' + @DatabaseName + '] FROM DISK = ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.trn' + @APOS + ' WITH NORECOVERY, NOUNLOAD, STATS = 10 GO' SET @Step4 = @Step4 + @NewLine + @NewLine + '--Step 4 Finish'
SET @Step5 = @Step5 + '--SQL Server Mirror Setup Script - Step 5: Setup Partner Information On Mirror' SET @Step5 = @Step5 + @NewLine + '--Run On Mirror Server' SET @Step5 = @Step5 + @NewLine + @NewLine + '--Step 5 Start' SET @Step5 = @Step5 + @NewLine + ' USE [Master]; GO
ALTER DATABASE ' + @DatabaseName + ' SET PARTNER = ' + @APOS + @PrincipalTCP + @APOS + ' GO' SET @Step5 = @Step5 + @NewLine + @NewLine + '--Step 5 Finish'
SET @Step6 = @Step6 + '--SQL Server Mirror Setup Script - Step 6: Setup Partner Information On Principal' SET @Step6 = @Step6 + @NewLine + '--Run On Principal Server' SET @Step6 = @Step6 + @NewLine + @NewLine + '--Step 6 Start' SET @Step6 = @Step6 + @NewLine + ' USE [Master]; GO
ALTER DATABASE ' + @DatabaseName + ' SET PARTNER = ' + @APOS + @MirrorTCP + @APOS + ' GO
ALTER DATABASE ' + @DatabaseName + ' SET SAFETY ' + @MirrorSafety + '; GO
USE [msdb]; GO
IF EXISTS (SELECT * FROM dbo.sysjobs WHERE name LIKE ' + @APOS + @MirrorMonitorJobName + @APOS + ') BEGIN PRINT ' + @APOS + @MirrorMonitorJobName + ' already exists on server instance [' + @Principal + '].' + @APOS + '; END GO
IF NOT EXISTS (SELECT * FROM dbo.sysjobs WHERE name LIKE ' + @APOS + @MirrorMonitorJobName + @APOS + ') BEGIN EXECUTE sp_dbmmonitoraddmonitoring; PRINT ' + @APOS + @MirrorMonitorJobName + ' has been added to server instance [' + @Principal + '].' + @APOS + '; END GO' SET @Step6 = @Step6 + @NewLine + @NewLine + '--Step 6 Finish'
SET @Step7 = @Step7 + '--SQL Server Mirror Setup Script - Step 7: Verify Principal Setup' SET @Step7 = @Step7 + @NewLine + '--Run On Principal Server' SET @Step7 = @Step7 + @NewLine + @NewLine + '--Step 7 Start' SET @Step7 = @Step7 + @NewLine + ' USE [Master]; GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name=' + @APOS + @DomainAccount + @APOS + ') PRINT ' + @APOS + 'Windows Login [' + @DomainAccount + '] does not exist.' + @APOS + ' GO
IF NOT EXISTS (SELECT * FROM sys.database_mirroring_endpoints WHERE name=N''MirrorEndpoint'' AND type=4) PRINT ' + @APOS + 'Mirroring endpoint [MirrorEndpoint] does not exist.' + @APOS + ' GO
IF NOT EXISTS (SELECT * FROM sys.database_mirroring A INNER JOIN sys.databases B ON A.database_id = B.database_id WHERE B.name=' + @APOS + @DatabaseName + @APOS + ' AND A.mirroring_state=4 AND A.mirroring_role=1 AND A.mirroring_partner_instance=' + @APOS + @Mirror + @APOS + ') PRINT ' + @APOS + 'Principal Database [' + @DatabaseName + '] does not exist or may not be synchronized or in principal role.' + @APOS + ' GO' SET @Step7 = @Step7 + @NewLine + @NewLine + '--Step 7 Finish'
SET @Step8 = @Step8 + '--SQL Server Mirror Setup Script - Step 8: Verify Mirror Setup' SET @Step8 = @Step8 + @NewLine + '--Run On Mirror Server' SET @Step8 = @Step8 + @NewLine + @NewLine + '--Step 8 Start' SET @Step8 = @Step8 + @NewLine + ' USE [Master]; GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name=' + @APOS + @DomainAccount + @APOS + ') PRINT ' + @APOS + 'Windows Login [' + @DomainAccount + '] does not exist.' + @APOS + ' GO
IF NOT EXISTS (SELECT * FROM sys.database_mirroring_endpoints WHERE name=N''MirrorEndpoint'' AND type=4) PRINT ' + @APOS + 'Mirroring endpoint [MirrorEndpoint] does not exist.' + @APOS + ' GO
IF NOT EXISTS (SELECT * FROM sys.database_mirroring A INNER JOIN sys.databases B ON A.database_id = B.database_id WHERE B.name=' + @APOS + @DatabaseName + @APOS + ' AND A.mirroring_state=4 AND A.mirroring_role=2 AND A.mirroring_partner_instance=' + @APOS + @Principal + @APOS + ') PRINT ' + @APOS + 'Principal Database [' + @DatabaseName + '] does not exist or may not be synchronized or in principal role.' + @APOS + ' GO' SET @Step8 = @Step8 + @NewLine + @NewLine + '--Step 8 Finish'
SET @Step9 = @Step9 + '--SQL Server Mirror Setup Script - Step 9: Delete Backup Files' SET @Step9 = @Step9 + @NewLine + '--Run On Principal Server' SET @Step9 = @Step9 + @NewLine + @NewLine + '--Step 9 Start' SET @Step9 = @Step9 + @NewLine + ' USE [Master]; GO EXECUTE xp_delete_file 0, ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.bak' + @APOS + ', ' + @APOS + 'bak' + @APOS + ', ' + @APOS + @DBDeleteDate + @APOS + ', 0 GO
EXECUTE xp_delete_file 0, ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.trn' + @APOS + ', ' + @APOS + 'trn' + @APOS + ', ' + @APOS + @DBDeleteDate + @APOS + ', 0 GO' SET @Step9 = @Step9 + @NewLine + @NewLine + '--Step 9 Finish'
SET @AllSteps = @Step1 + @NewLine + @NewLine + @Step2 + @NewLine + @NewLine + @Step3 + @NewLine + @NewLine + @Step4 + @NewLine + @NewLine + @Step5 + @NewLine + @NewLine + @Step6 + @NewLine + @NewLine + @Step7 + @NewLine + @NewLine + @Step8 + @NewLine + @NewLine + @Step9 + @NewLine + @NewLine
SET NOCOUNT ON SELECT @AllSteps SET NOCOUNT OFF
/* START OF SECOND SCRIPT */
/******************************************************************************************** * Purpose: Generate script to remove mirroring in SQL 2005 * * Author: Farhan F. Sabzaali * * Date Created: March 17, 2009 * * Last Updated: * * Updates Made: * * Instructions: 1. Run script at server \ instance to identify all mirrored database * *********************************************************************************************/ DECLARE @DatabaseName NVARCHAR (250) ,@SQL NVARCHAR (MAX) ,@NewLine CHAR (1) ,@APOS CHAR (1) SET @NewLine = CHAR(13) SET @APOS = CHAR (39) SET @SQL = '' DECLARE CurDatabase
CURSOR FOR SELECT B.Name FROM sys.database_mirroring A INNER JOIN sys.databases B ON A.database_id = B.database_id AND A.Mirroring_Guid IS NOT NULL
OPEN CurDatabase
FETCH NEXT FROM CurDatabase INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = @SQL + ' ALTER DATABASE ' + @DatabaseName + ' SET PARTNER OFF; GO RESTORE DATABASE ' + @DatabaseName + ' WITH RECOVERY; GO' SET @SQL = @SQL + @NEWLINE
FETCH NEXT FROM CurDatabase INTO @DatabaseName END
CLOSE CurDatabase
DEALLOCATE CurDatabase SET NOCOUNT ON SELECT @SQL SET NOCOUNT OFF
|
 Updating...
Andy Hughes, Jul 19, 2012, 8:20 AM
|