Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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?

share|improve this question

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
this is the solution i had hoped would work but I get a msg 1468, the database is involved in mirroring, alter statement failed. – dat_girl Oct 14 '11 at 13:24
@dat_girl can you first try setting it as single user with rollback immediate and then try the above statement? – Thomas Stringer Oct 14 '11 at 13:26
That didn't work either @shark, same error. I think I would have to stop mirroring before any 'state' changes can be made to the db. – dat_girl Oct 14 '11 at 14:38
up vote 1 down vote

For mirroring to make sense, you'll need at least a second instance of SQL-Server: One for the "principal" and one for the "mirror".

How to install a further instance, see: Instance configuration.

For automatic failover to take effect, you'll also need a third instance (the "Witness"), which purpose is to monitor the principal's and mirror's (connection)states ("... the witness does not serve the database. Supporting automatic failover is the only role of the witness").

To setup the whole mirror with failover see: Automatic failover and Synchronous Database Mirroring (High-Safety Mode).

Once completed the steps, you can test your configuration by stopping one of the SQL-Server services (principal or mirror). The mirrored database gets available then.

I strongly suggest not to run the instances on the same, real server since this will not secure you from any hardware-disasters!

share|improve this answer
Thanks @vorax, but I already have mirroring setup and it works fine. I also don't want to stop the service as that would bring down all the databases on the instance. I just want to stop 1. – dat_girl Oct 14 '11 at 13:08
up vote 0 down vote

Hmm, alter database whateverdb set partner failover ought to do it, but it might take the entire server with it, I don't have one to hand that I can experiment with, and the docs annoyingly seem to use server and database interchangeably.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.