SQL Server 2005 Mirroring – disable/enable T-SQL Scripts
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
How to audit Database Design Modifications using a DDL Trigger
Recently we identified the need to create a system for tracking changes made to our databases by developers and internal systems. Although the database itself is using FULL logging, which allows you to view changes made, we found that we’d like to see all modifications made over the entire history of the database in question. This prompted me to develop a table containing all changes Read more
Rollin’ On!
In honor of the 4chan RickRoll’D Internet Meme that was taken offline for around 24 Read more
HowTo: Grow a very large MS-SQL Log File
Microsoft SQL Server 2005 has a nasty habit of creating thousands or hundreds of thousands of logical log files even though you set Autogrowth to a large size, say 8GB. The following SQL code automates manually growing your log file by 8GB increments. When using the ALTER DATABASE command to modify log file size manually like this, SQL Server seems to properly create logical log files of around 1GB each resulting in far less logical log files overall. This should increase log file performance. Read more
U.S. Hegemony, Oil, and Precious Metals
The Underground Investor has an interesting interview concerning the Federal Reserve and the banks that own it, and how they have been controlling the world economy since 1913 using leveraged futures contracts on the oil, silver, and gold commodities markets in New York and London. The interview goes on to discuss how Europe, in concert with Russia and China could break Read more
