Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

How to failover Database Mirroring in SQL Server 2005 using T-SQL

I've previously shown how to setup Database Mirroring in SQL Server 2005 with T-SQL, but how do you failover to the mirrored databases using T-SQL?  Here's how:

--Run on principal
USE master
GO

ALTER DATABASE dbName SET SAFETY FULL
GO
ALTER DATABASE dbName SET PARTNER FAILOVER
GO
--Run on new principal
USE master
GO

ALTER DATABASE dbName SET SAFETY OFF
GO

If you are using synchronous database mirroring, just ignore the "SET SAFETY" commands. 

We use asynchronous database mirroring for performance reasons as our sites are 300 miles apart.  Even though we have a fast network in between the two sites, the latency is too high when we tried it in synchronous mode.

 

Print | posted on Sunday, May 04, 2008 2:50 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: How to failover Database Mirroring in SQL Server 2005 using T-SQL

What if the original Principal db is no longer available (ie physical server completely crashes)...Can we just run the "--Run on new principal" T-SQL statements? or are there additional statements with this form of failover?

My apologies for the elementary question...
5/5/2008 2:51 PM | Jr Lee
Gravatar

# re: How to failover Database Mirroring in SQL Server 2005 using T-SQL

Good question Jr Lee. In that case you'd have to use the "FORCE_SERVICE_ALLOW_DATA_LOSS" option. The command would be run on the mirror server:

--Run on mirror if principal isn't available
USE
master
GO

ALTER DATABASE dbName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO
5/5/2008 3:03 PM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET