HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Is there an easy way to trigger automatic failover of a single mirrored database?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
mirroredtriggerwaydatabaseeasysinglefailoverautomaticthere

Problem

I have 3 mirrored databases on a single instance of sqlserver2008-R2 in a high safety with automatic failover config. Is there a way to trigger automatic failover of just 1 database?

I want to simulate where the database actually is unreachable and not just hit the 'Failover' button from SSMS. I can stop the endpoint on the primary but that would make all 3 databases unreachable as they use the same endpoint.

How do I disable just 1 db?

Solution

If your mirroring is configured correctly for high safety/automatic failover, then I would be inclined to think that you could just take the principal database offline with this:

alter database YourDb set offline
with rollback immediate
go


I believe by taking the database offline, you will have automatic failover without affecting the other two databases that you don't want to failover.

Code Snippets

alter database YourDb set offline
with rollback immediate
go

Context

StackExchange Database Administrators Q#6756, answer score: 3

Revisions (0)

No revisions yet.