Setup fresh/Scriptout Existing Mirroring on SQL Server

In my current organization, we have lot of servers where database mirroring is used as a disaster recovery option. Either we are setting up mirroring from scratch or dropping the existing mirroring and re-establishing it, it is going to take from 30 min to an hour. So in order to reduce the overall mirroring setup time, I have created below script that will provide TSQL Code to create database mirroring from scratch or will script out existing mirroring setup on server. Scripting out existing setup can be useful in cases where we need database refresh from other environments.

/*	Created By:			Ajay Dwivedi
	Created Date:		Jan 26, 2016
	Purpose:			The script can be used to script out current mirroring of database, 
						or, Will create entire script to create mirroring on server
*/ 
SET NOCOUNT ON; 

DECLARE	@databaseName SYSNAME								= 'AjayDwivedi'	-- Database
		,@principalServer SYSNAME							= 'SQL_A'	-- Principal
		,@mirrorServer SYSNAME								= 'SQL_B'	-- Mirror
		,@witnessServer SYSNAME								= NULL--'SQL_C'
		,@localBackupPath_Certificate	VARCHAR(255)		= 'E:\TSMSQL\ADHOC\'	-- Path ending with '\'

IF (@@SERVERNAME NOT IN (@principalServer,@mirrorServer,ISNULL(@witnessServer,'Wrong Server')))
	PRINT	'ERROR: Something is wrong. Either wrong Principal or Mirror Server, or query is being run in wrong server window.'
ELSE
BEGIN	-- Main Begin Block
		
DECLARE	@isMirroringFoundOnServer TINYINT = 0
		,@isMirroringFoundOnDatabase TINYINT = 0
		,@contextServer VARCHAR(20)
		,@version VARCHAR(20)
		,@productVersion VARCHAR(20)
		,@sqlServerServiceAccount VARCHAR(100)
		,@mirroringCertificate NVARCHAR(500)
		,@mirroringCertificateID BIGINT
		,@mirroringCertificateSubject VARCHAR(200)
		,@partnerCertificate_01 NVARCHAR(500)
		,@partnerCertificate_02 NVARCHAR(500)
		,@certificatePath_01 VARCHAR(255)
		,@certificatePath_02 VARCHAR(255)
		,@partner_01 VARCHAR(20)
		,@partner_02 VARCHAR(20)
		,@networkBackupPath_Certificate	VARCHAR(255)		= REPLACE(@localBackupPath_Certificate,':','$')
		,@sqlLoginName SYSNAME
		,@sqlUserName SYSNAME
		,@isSQLLoginUserExisting TINYINT = 0
		,@mirroringEndPoint NVARCHAR(500)
		,@mirroringEndPointID BIGINT
		,@mirroringTCPPort BIGINT
		,@sqlQueryText NVARCHAR(4000)

IF (@@SERVERNAME = @principalServer)
	SELECT @contextServer = 'Principal'	,@partner_01 = 'Mirror', @partner_02 = 'Witness';
ELSE
	IF (@@SERVERNAME = @mirrorServer)
		SELECT @contextServer = 'Mirror'	,@partner_01 = 'Principal', @partner_02 = 'Witness';
	ELSE
		SELECT @contextServer = 'Witness'	,@partner_01 = 'Principal', @partner_02 = 'Mirror';

IF OBJECT_ID('tempdb..#database_mirroring') IS NOT NULL
	DROP TABLE #database_mirroring;
SELECT	DB_NAME(database_id) as database_name, * 
INTO	#database_mirroring
FROM	sys.database_mirroring as dm 
WHERE	dm.mirroring_state = 4	--SYNCHRONIZED

PRINT	'/*	!~~~~~~~~~~~ Analyzing '[email protected]+' server <<'[email protected]@servername+ '>> ~~~~~~~~~~~~~~~~~!';
IF EXISTS (SELECT * FROM #database_mirroring as m)
BEGIN
	SET	@isMirroringFoundOnServer = 1;
	PRINT	'		Mirroring is existing on server';

	IF EXISTS (SELECT * FROM #database_mirroring as m WHERE m.database_name = @databaseName)
	BEGIN
		SET	@isMirroringFoundOnDatabase = 1;		
		PRINT	'		Mirroring is existing for concerned database';
	END
END
ELSE
	PRINT	'		This would be 1st Mirroring setup on server.';


/*	Pre-Implementation Steps	
·         Verify the database is in Full recovery mode.
·         Verify the principal and mirror databases are on separate instances.
·         Verify the principal and mirror servers are running on the same edition of SQL Server.
·         If configuring a witness server, verify it is running version SQL 2005 or later.  (All editions are supported on a witness server).
*/
PRINT	'';

PRINT	'--	Pre-Implementation Steps';
IF	(@contextServer = 'Witness')
	PRINT	'	1) UNKNOWN - Verify that databases on Primary & Secondary databases are in Full recovery mode.';
ELSE
IF NOT EXISTS (SELECT * FROM sys.databases d WHERE d.name IN (@databaseName) AND d.recovery_model_desc = 'FULL') 
	PRINT	'	1) FAILED - Verify the database is in Full recovery mode.'
ELSE
	PRINT	'	1) PASSED - Verify the database is in Full recovery mode.'

--	Verify the principal and mirror databases are on separate instances.
IF (@[email protected])
	PRINT	'	2) FAILED - Verify the principal and mirror databases are on separate instances.'
ELSE
	PRINT	'	2) PASSED - Verify the principal and mirror databases are on separate instances.'

SELECT	@productVersion = CONVERT(VARCHAR(20),SERVERPROPERTY('ProductVersion'));
SELECT	@version =	CASE	WHEN	@productVersion LIKE '10.50%'
							THEN	'2008 R2'
							WHEN	@productVersion LIKE '11.%'
							THEN	'2012'
							WHEN	@productVersion LIKE '12.%'
							THEN	'2014'
							WHEN	@productVersion LIKE '13.%'
							THEN	'2016'
							WHEN	@productVersion LIKE '10.%'
							THEN	'2008'
							ELSE	'2005'
							END;

IF (@contextServer='Principal')
	PRINT	'	3) UNKNOWN - Verify the mirror database is running on SQL Server '[email protected]+ ' '+CONVERT(VARCHAR(30),SERVERPROPERTY('Edition'));
ELSE
	PRINT	'	3) UNKNOWN - Verify the Principal database is running on SQL Server '[email protected]+ ' '+CONVERT(VARCHAR(30),SERVERPROPERTY('Edition'));

--	If configuring a witness server, verify it is running version SQL 2005 or later
IF (@witnessServer IS NOT NULL AND LEN(@witnessServer)>0)
	PRINT	'	4) UNKNOWN - If configuring the witness server '[email protected]+' verify it is running version SQL 2005 or later';
ELSE
	PRINT	'	4) UNKNOWN - If configuring a witness server verify it is running version SQL 2005 or later';

PRINT	'*/

--	Determine/Create Database Master Key';
IF	EXISTS (SELECT * FROM master.sys.symmetric_keys as sk WHERE sk.name = '##MS_DatabaseMasterKey##')
	PRINT	'		-- Master Key already exists on '[email protected]+' Server '[email protected]@servername;

	--	Still print code for master key creation
BEGIN
	SET @sqlQueryText = 'USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''Ox6$Nd4&Vv7&Rn2&Fy7%'';
GO
'
	PRINT	@sqlQueryText;
	SET @sqlQueryText = '';
END

--drop certificate MasterCert
--drop endpoint Endpoint_Mirroring

PRINT	'
--	Determine/Create Certificate';
WITH T_Mirroring_Certificate AS
(	
	SELECT		c.name as CertificateName, c.certificate_id, c.subject
	FROM		sys.database_mirroring_endpoints AS me
	INNER JOIN
				sys.tcp_endpoints AS ep
		ON		ep.endpoint_id = me.endpoint_id
	INNER JOIN
				sys.certificates AS c
		ON		c.certificate_id = me.certificate_id
)
,T_Certificate_Latest AS
(
	SELECT		TOP 1 c.name as CertificateName, c.certificate_id, c.subject
	FROM		sys.certificates AS c
	WHERE		c.pvt_key_encryption_type_desc = 'ENCRYPTED_BY_MASTER_KEY'
		AND		NOT EXISTS (SELECT * FROM T_Mirroring_Certificate)
	ORDER BY	start_date DESC
)
SELECT	@mirroringCertificate = crt.CertificateName
		,@mirroringCertificateID = crt.certificate_id
		,@mirroringCertificateSubject = crt.subject
FROM	(	SELECT		CertificateName, certificate_id, subject
			FROM		T_Mirroring_Certificate
			--
			UNION
			--
			SELECT		CertificateName, certificate_id, subject
			FROM		T_Certificate_Latest
		) AS crt;

IF (@mirroringCertificate IS NOT NULL)
	PRINT	'		-- Certificate already exists on '[email protected]+' Server '[email protected]@servername;
ELSE
BEGIN
	PRINT	'		-- Create a certificate using below TSQL code';
	SELECT	@mirroringCertificate = @@SERVERNAME+'_Cert', @mirroringCertificateSubject = @@SERVERNAME+' Certificate';
END


BEGIN
	SET @sqlQueryText = 'CREATE CERTIFICATE '+ @mirroringCertificate + '
		WITH SUBJECT = '''[email protected]+''',
		EXPIRY_DATE = ''12/31/2049'';
GO
'
	PRINT	@sqlQueryText;
	SET @sqlQueryText = '';
END

--	Find out port no if Mirroring is pre-existing
SELECT		@mirroringEndPoint = me.name
			,@mirroringEndPointID = me.endpoint_id
			,@mirroringTCPPort = ep.port
			--me.name as MirroringEndpointName, me.endpoint_id, me.protocol_desc, me.type_desc, me.state_desc, me.connection_auth_desc
			--,me.certificate_id, ep.port, c.name as CertificateName
FROM		sys.database_mirroring_endpoints AS me
INNER JOIN
			sys.tcp_endpoints AS ep
	ON		ep.endpoint_id = me.endpoint_id
INNER JOIN
			sys.certificates AS c
	ON		c.certificate_id = me.certificate_id
WHERE		me.certificate_id = @mirroringCertificateID

PRINT	'
--	Determine/Create Mirroring Enpoint';
IF	(@mirroringEndPoint IS NOT NULL)
	PRINT	'		-- Mirroring Endpoint already exists using certificate authentication';
ELSE
	PRINT	'		-- Create a Mirroring Endpoint using certificate authentication';

BEGIN
	SET @sqlQueryText = 'CREATE ENDPOINT '+ISNULL(@mirroringEndPoint,'Endpoint_Mirroring')+'
		STATE = STARTED
		AS TCP (
					LISTENER_PORT= '+CAST(ISNULL(@mirroringTCPPort,5022) AS VARCHAR(20))+'
					,LISTENER_IP = ALL
				) 
		FOR DATABASE_MIRRORING 
				( 
					AUTHENTICATION = CERTIFICATE '[email protected]+'
					,ENCRYPTION = REQUIRED ALGORITHM AES
					,ROLE = ALL
				);
GO

'
	PRINT	@sqlQueryText;
	SET @sqlQueryText = '';
END

PRINT	'--	BackupCertificate
BACKUP CERTIFICATE '[email protected]+' TO FILE = '''[email protected][email protected]+'.cer'';
GO
';

SET	@certificatePath_01 = '\\'+CASE @contextServer WHEN 'Principal' THEN @mirrorServer WHEN 'Mirror' THEN @principalServer ELSE @principalServer END +'\'[email protected]_Certificate;
SET	@certificatePath_02 = '\\'+CASE @contextServer WHEN 'Principal' THEN @witnessServer WHEN 'Mirror' THEN @witnessServer ELSE @mirrorServer END +'\'[email protected]_Certificate;

PRINT	'
/*		Take the backed up certificate '''[email protected][email protected]+'.cer'', and copy across partner servers.
		Run following command in elevated Command Prompt/Powershell window:-
				copy ''\\'[email protected]@servername+'\'[email protected][email protected]+'.cer'' '''[email protected]_01+'''
				copy ''\\'[email protected]@servername+'\'[email protected][email protected]+'.cer'' '''[email protected]_02+'''							
*/';

SELECT		@sqlLoginName = s.name
			--e.name as mirror_endpoint_name, s.name AS login_name, p.permission_name, p.state_desc as permission_state, e.state_desc endpoint_state
FROM		sys.server_permissions p
INNER JOIN	sys.endpoints e 
	ON		p.major_id = e.endpoint_id
INNER JOIN	sys.server_principals s 
	ON		p.grantee_principal_id = s.principal_id
WHERE		p.class_desc = 'ENDPOINT' 
	AND		e.type_desc = 'DATABASE_MIRRORING'
	AND		p.permission_name = 'CONNECT'

IF (@sqlLoginName IS NULL)
	IF EXISTS (SELECT * FROM sys.sql_logins as l WHERE l.name = 'endPointUser')
		SELECT	@isSQLLoginUserExisting = 1, @sqlLoginName = l.name
				,@sqlUserName = (SELECT u.name FROM sys.database_principals AS u WHERE u.sid = l.sid)
		FROM	sys.server_principals AS l WHERE l.name = 'endPointUser'
	ELSE
		SELECT @isSQLLoginUserExisting = 0
ELSE
BEGIN
	SELECT	@isSQLLoginUserExisting = 1
			,@sqlUserName = (SELECT u.name FROM sys.database_principals AS u WHERE u.sid = l.sid)
	FROM	sys.server_principals AS l WHERE l.name = @sqlLoginName
END
	
IF	(@isSQLLoginUserExisting = 1)	
BEGIN
	IF OBJECT_ID('tempdb..#MirrorCertificates') IS NOT NULL
		DROP TABLE #MirrorCertificates;

	SELECT	TOP 5 ROW_NUMBER() OVER (ORDER BY certificate_id ASC) AS ID, c.name,  USER_NAME(c.principal_id) AS sqlUserName
	INTO	#MirrorCertificates
	FROM	sys.certificates AS c
	CROSS APPLY
		(
			SELECT sp.name AS login_name
			FROM sys.server_principals sp
			JOIN sys.database_principals dp ON (sp.sid = dp.sid)
			WHERE dp.name = USER_NAME(c.principal_id)
		) as l
	WHERE	l.login_name = @sqlLoginName;
END

PRINT	'
--	Determine/Create SQLUser';
IF	(@isSQLLoginUserExisting = 1)
	PRINT	'		-- SQLUser ['[email protected]+'] with CONNECT permission to Mirroring Endpoint already exists';
ELSE
BEGIN
	SELECT	@sqlLoginName = 'endPointUser', @sqlUserName = 'endPointUser';
	PRINT	'		-- Create a SQLUser for mirroring';
END

BEGIN
	SET @sqlQueryText = 'USE master
GO
CREATE LOGIN ['[email protected]+'] 
		WITH PASSWORD=N''ˆTB‹[1]§YvB¦Ð«H·™»›ÃOâE3Y‡8°¶Ô€'', 
		DEFAULT_DATABASE=[master],
		DEFAULT_LANGUAGE=[us_english], 
		CHECK_EXPIRATION=OFF, 
		CHECK_POLICY=OFF;
GO
CREATE USER ['[email protected]+'] FOR LOGIN ['[email protected]+'] WITH DEFAULT_SCHEMA=[dbo];
GO
'
	PRINT	@sqlQueryText;
	SET @sqlQueryText = '';
END

PRINT	'
/*		NOTE 01:	Repeat all above steps on '+(CASE WHEN @contextServer='Witness' THEN 'Partner Instances' WHEN @witnessServer IS NOT NULL AND LEN(@witnessServer)>3 THEN 'Partner Instance & Witness server '[email protected] ELSE 'Partner Instance' END)+' as well before proceeding further.	*/';

--	\\DBSW8196\E$\TSMSQL\ADHOC\
SET	@certificatePath_01 = '\\'+CASE @contextServer WHEN 'Principal' THEN @mirrorServer WHEN 'Mirror' THEN @principalServer ELSE @principalServer END +'\'[email protected]_Certificate;
SET	@certificatePath_02 = '\\'+CASE @contextServer WHEN 'Principal' THEN @witnessServer WHEN 'Mirror' THEN @witnessServer ELSE @mirrorServer END +'\'[email protected]_Certificate;

SELECT	@partnerCertificate_01 = CASE ID WHEN 1 THEN c.name ELSE @partnerCertificate_01 END,
		@partnerCertificate_02 = CASE ID WHEN 2 THEN c.name ELSE @partnerCertificate_02 END,
		@sqlUserName = c.sqlUserName
FROM	#MirrorCertificates AS c

IF EXISTS (select * from #MirrorCertificates as c WHERE c.name like '%'[email protected]+'%' or c.name like '%'[email protected]+'%' OR c.name like '%'[email protected]+'%')
BEGIN
	SELECT	@partnerCertificate_01 = CASE ID WHEN 1 THEN c.name ELSE @partnerCertificate_01 END,
			@partnerCertificate_02 = CASE ID WHEN 2 THEN c.name ELSE @partnerCertificate_02 END,
			@sqlUserName = c.sqlUserName
	FROM	(select ROW_NUMBER()over(order by id) as ID, c.name, c.sqlUserName from #MirrorCertificates as c WHERE c.name like '%'[email protected]+'%' or c.name like '%'[email protected]+'%' OR c.name like '%'[email protected]+'%') AS c
END


PRINT	'
--	Determine/Associate '[email protected]_01+' Certificate with SQLUser';
IF	(@partnerCertificate_01 IS NOT NULL)
	PRINT	'		-- Partner certificate '''[email protected]_01+'.cer'' is already associated with SQLUser ['[email protected]+']';
ELSE
BEGIN
	SET @partnerCertificate_01 = @mirrorServer+'_Cert'
	PRINT	'		-- Create a certificate from partner certificate copy, assuming that you already copied partner certificate from path '''[email protected][email protected]_01+'.cer'' to '''[email protected]_Certificate+'''';
END

BEGIN
	SET @sqlQueryText = 'USE master
GO
CREATE CERTIFICATE '[email protected]_01+'
		AUTHORIZATION ['[email protected]+'] 
		FROM FILE = '''[email protected][email protected]_01+'.cer'';
GO
'
	PRINT	@sqlQueryText;
	SET @sqlQueryText = '';
END 

IF (@witnessServer IS NOT NULL AND LEN(@witnessServer)>0)
BEGIN
	PRINT	'
--	Determine/Associate '[email protected]_02+' Certificate with SQLUser';
	IF	(@partnerCertificate_02 IS NOT NULL)
		PRINT	'		-- '[email protected]_02+' certificate '''[email protected]_02+'.cer'' is already associated with SQLUser ['[email protected]+']';
	ELSE
	BEGIN
		SET @partnerCertificate_02 = (case when @partner_02 = 'Mirror' then @mirrorServer else @witnessServer end)+'_Cert'
		PRINT	'		-- Create a certificate from '[email protected]_02+' certificate copy, assuming that you already copied certificate from path '''[email protected][email protected]_02+'.cer'' to '''[email protected]_Certificate+'''';
	END

	BEGIN
		SET @sqlQueryText = 'USE master
GO
CREATE CERTIFICATE '[email protected]_02+'
		AUTHORIZATION ['[email protected]+'] 
		FROM FILE = '''[email protected][email protected]_02+'.cer'';
GO
	'
		PRINT	@sqlQueryText;
		SET @sqlQueryText = '';
	END 
END

PRINT	'
/*		After login, user, and certificate for partner instance have been created, grant permissions to the login on the endpoint
*/';

BEGIN
	SET @sqlQueryText = 'GRANT CONNECT ON ENDPOINT::'+ISNULL(@mirroringEndPoint,'Endpoint_Mirroring')+' TO ['[email protected]+'];
GO
'
	PRINT	@sqlQueryText;
	SET @sqlQueryText = '';
END

PRINT	'
/*		NOTE 02:	Repeat all activites till this point on partner instance as well.	*/';

PRINT	'
--		CAUTION: First perform below steps on Mirror Instance <<'[email protected]+'>> using mirror script, and then proceed for Primary instance <<'[email protected]+'>>';

DECLARE @Domain varchar(100), @key varchar(100)
SET @key = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @[email protected],@value_name='Domain',@[email protected] OUTPUT 

BEGIN
IF	(@contextServer='Principal')
	SET @sqlQueryText = 'ALTER DATABASE ['[email protected]+']
	SET PARTNER = ''TCP://'[email protected]+'.'[email protected]+':'+CAST(ISNULL(@mirroringTCPPort,5022) AS VARCHAR(20))+''';
GO
'
ELSE
	SET @sqlQueryText = 'ALTER DATABASE ['[email protected]+']
	SET PARTNER = ''TCP://'[email protected]+'.'[email protected]+':'+CAST(ISNULL(@mirroringTCPPort,5022) AS VARCHAR(20))+''';
GO
'
	PRINT	@sqlQueryText;
	SET @sqlQueryText = '';
END

IF (@witnessServer IS NOT NULL AND LEN(@witnessServer)>0)
BEGIN
	PRINT	'
/*		Execute below code on Principal to Add Witness Server <<'[email protected]+'>>	*/';
	SET @sqlQueryText = 'ALTER DATABASE ['[email protected]+']
	SET WITNESS = ''TCP://'[email protected]+'.'[email protected]+':'+CAST(ISNULL(@mirroringTCPPort,5022) AS VARCHAR(20))+''';
GO
'
	PRINT	@sqlQueryText;
	SET @sqlQueryText = '';
END

PRINT	'
/*		Congrats! As you have reached this step, your database must have got mirrored in high-safety mode.
*/';

END	-- Main End Block
Code to Scripout Existing Mirroring on SQL Server

Sample result from above query is as below:-

/*	!~~~~~~~~~~~ Analyzing Principal server <<SQL_A>> ~~~~~~~~~~~~~~~~~!
		Mirroring is existing on server
		Mirroring is existing for concerned database
 
--	Pre-Implementation Steps
	1) PASSED - Verify the database is in Full recovery mode.
	2) PASSED - Verify the principal and mirror databases are on separate instances.
	3) UNKNOWN - Verify the mirror database is running on SQL Server 2008 R2 Enterprise Edition (64-bit)
	4) UNKNOWN - If configuring a witness server verify it is running version SQL 2005 or later
*/

--	Determine/Create Database Master Key
		-- Master Key already exists on Principal Server SQL_A
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Ox6$Nd4&Vv7&Rn2&Fy7%';
GO

--	Determine/Create Certificate
		-- Certificate already exists on Principal Server SQL_A
CREATE CERTIFICATE SQL_A_cert
		WITH SUBJECT = 'SQL_A certificate',
		EXPIRY_DATE = '12/31/2049';
GO

--	Determine/Create Mirroring Enpoint
		-- Mirroring Endpoint already exists using certificate authentication
CREATE ENDPOINT Endpoint_Mirroring
		STATE = STARTED
		AS TCP (
					LISTENER_PORT= 5022
					,LISTENER_IP = ALL
				) 
		FOR DATABASE_MIRRORING 
				( 
					AUTHENTICATION = CERTIFICATE SQL_A_cert
					,ENCRYPTION = REQUIRED ALGORITHM AES
					,ROLE = ALL
				);
GO

--	BackupCertificate
BACKUP CERTIFICATE SQL_A_cert TO FILE = 'E:\TSMSQL\ADHOC\SQL_A_cert.cer';
GO
 

--	Determine/Create SQLUser
		-- SQLUser [SQL_B_Login] with CONNECT permission to Mirroring Endpoint already exists
USE master
GO
CREATE LOGIN [SQL_B_Login] 
		WITH PASSWORD=N'ˆTB‹[1]§YvB¦Ð«H·™»›ÃOâE3Y‡8°¶Ô€', 
		DEFAULT_DATABASE=[master],
		DEFAULT_LANGUAGE=[us_english], 
		CHECK_EXPIRATION=OFF, 
		CHECK_POLICY=OFF;
GO
CREATE USER [SQL_B_User] FOR LOGIN [SQL_B_Login] WITH DEFAULT_SCHEMA=[dbo];
GO

/*		NOTE 01:	Repeat all above steps on Partner Instance as well before proceeding further.	*/

--	Determine/Associate Mirror Certificate with SQLUser
		-- Partner certificate 'SQL_B_cert.cer' is already associated with SQLUser [SQL_B_Login]
USE master
GO
CREATE CERTIFICATE SQL_B_cert
		AUTHORIZATION [SQL_B_User] 
		FROM FILE = 'E:\TSMSQL\ADHOC\SQL_B_cert.cer';
GO

/*		After login, user, and certificate for partner instance have been created, grant permissions to the login on the endpoint
*/
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SQL_B_Login];
GO

/*		NOTE 02:	Repeat all activites till this point on partner instance as well.	*/

--		CAUTION: First perform below steps on Mirror Instance <<SQL-B>> using mirror script, and then proceed for Primary instance <<SQL_A>>
ALTER DATABASE [AjayDwivedi]
	SET PARTNER = 'TCP://SQL_B.Contso.com:5022';
GO

/*		Congrats! As you have reached this step, your database must have got mirrored in high-safety mode.
*/

Friends, please try the script, and let me know of any bugs by comments. If you like the article, do Like & Share. Happy Coding 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.