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... Generate scripts to add or remove DB mirroring.sql (32k) Andy Hughes, Jul 19, 2012, 8:20 AM
|
