SQL Server 2005 Mirroring – disable/enable T-SQL Scripts

October 10, 2012 · Posted in SQL 

USE [MagellanSQLMonitor]
GO
/****** Object: StoredProcedure [dbo].[EnableMirroring] Script Date: 05/16/2012 13:59:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[EnableMirroring]
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER = ''TCP://machine1.domain.inet:5022'''
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE=1;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine2.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER = ''TCP://machine2.domain.inet:5022'''
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE=2;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine1.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET WITNESS = ''TCP://witness.domain.inet:5022'''
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE>0;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine1.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER TIMEOUT 30'
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE=1;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine1.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

--SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER = ''TCP://machine2.domain.inet:5022''' FROM SYS.DATABASES D WHERE STATE=1
--SELECT 'ALTER DATABASE ' + D.NAME + ' SET WITNESS = ''TCP://witness.domain.inet:5022''' FROM SYS.DATABASES D WHERE STATE=1
--SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER TIMEOUT 30' FROM SYS.DATABASES D WHERE STATE=1
--select * from sys.database_mirroring
SELECT d.Name AS [Database], dm.mirroring_state_desc AS State, dm.mirroring_role_desc AS Role, dm.mirroring_partner_name AS Partner, dm.mirroring_witness_name AS Witness, dm.mirroring_connection_timeout AS Timeout
FROM sys.database_mirroring dm
INNER JOIN sys.databases d ON dm.database_id = d.database_id
WHERE dm.MIRRORING_STATE > 0
ORDER BY d.Name
--ALTER DATABASE DatabaseName SET PARTNER TIMEOUT 30
END

USE [DatabaseMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BreakMirroring]
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);

DECLARE CUR CURSOR FOR
SELECT 'ALTER DATABASE ' + D.NAME + ' SET PARTNER OFF'
FROM machine2.MASTER.SYS.DATABASES D
WHERE STATE=1;
OPEN CUR;
FETCH NEXT FROM CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@SQL,0,1) WITH NOWAIT;
EXEC machine2.master.sys.sp_executesql @SQL;
FETCH NEXT FROM CUR INTO @SQL;
END
CLOSE cur;
DEALLOCATE cur;

END

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Please answer the anti-spam question: * Time limit is exhausted. Please reload CAPTCHA.