|
Home > Archive > MS SQL Server > March 2006 > SQL Server 2005 Mirroring - Witness Failover Multiple DBs
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Server 2005 Mirroring - Witness Failover Multiple DBs
|
|
| Don Kitchen 2006-03-05, 8:24 pm |
| I'm trying to figure out the best way to handle this scenario:
We're mirroring 3 DBs with a witness server. What I want to do is have
the Witness automatically fail over ALL 3 DBs to the other if any one
of the 3 DBs fail. I know mirroring is intended to be at the DB level
rather than the SERVER level, so I don't believe there is anything
built in to support this.
We want to do this because each of the 3 DBs have stored procedures
that reference the other DBs, so if one of the 3 were to be down, the
sprocs wouldn't work because they'd still be targeting the local
server.
Hopefully this is something that can be done pretty easily on the
witness with built in tools (maybe DTS?)
Thanks in advance for any responses!
Don
| |
| Michael Hotek 2006-03-15, 1:23 pm |
| Can't be done. Mirroring works at a database level and has no facility to
handle cross database dependencies.
The only way you could cause this to happen on your own would be to monitor
the sys.database_mirroring DMV for a change in state. In your case, the
mirroring_role column wouldn't be the same for the 3 databases. You would
then have to issue an ALTER DATABASE .... FORCE_SERVICE_ALLOW_
DATA_LOSS to
kick the other databases over.
This sounds like a pretty good feature request.
http://lab.msdn.microsoft.com/ProductFeedback
--
Mike
http://www. solidqualitylearning
.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Don Kitchen" <donkitchen@gmail.com> wrote in message
news:1141422469.294634.108670@p10g2000cwp.googlegroups.com...
> I'm trying to figure out the best way to handle this scenario:
>
> We're mirroring 3 DBs with a witness server. What I want to do is have
> the Witness automatically fail over ALL 3 DBs to the other if any one
> of the 3 DBs fail. I know mirroring is intended to be at the DB level
> rather than the SERVER level, so I don't believe there is anything
> built in to support this.
>
> We want to do this because each of the 3 DBs have stored procedures
> that reference the other DBs, so if one of the 3 were to be down, the
> sprocs wouldn't work because they'd still be targeting the local
> server.
>
> Hopefully this is something that can be done pretty easily on the
> witness with built in tools (maybe DTS?)
>
> Thanks in advance for any responses!
>
> Don
>
|
|
|
|
|