Mirroring Failover/Failback

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Per Schjetne - 27 Apr 2006 08:42 GMT

I have been using Mirroring now for a while and put the system into
production now after applying SQL sp 1.

We have a setup with a witness, principal and mirror with certificates (the
witness is on a webserver in a different domain, so we can't use integrated
security).

Automatic failover works fine if we take down the principal. But when the
principal is up and running again, no automatic failback occur. If this by
design, or is there something I've missed out in the configuration?

Per Schjetne

Reply to this Message

Tibor Karaszi - 27 Apr 2006 11:05 GMT

The roles for mirroring are soft roles. So, you cannot define a preferred node (like you can for a
cluster). If you want to fail back, you have to ask for it (ALTER DATABASE).

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

>I have been using Mirroring now for a while and put the system into production now after applying
>SQL sp 1.
[quoted text clipped - 7 lines]
>
> Per Schjetne

Reply to this Message

Michael Hotek - 28 Apr 2006 04:19 GMT

That's not exactly correct.

The reason it did not failback, is because that database is no longer the
principal. Failover causes a role reversal. When it fails over to the
mirror, that database is promoted to a principal and begins serving the
database. When the other database comes online, its role is automatically
demoted to mirror and the transactions start flowing back to it.

Signature

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.

> The roles for mirroring are soft roles. So, you cannot define a preferred
> node (like you can for a cluster). If you want to fail back, you have to
[quoted text clipped - 12 lines]
>>
>> Per Schjetne

Reply to this Message

Russ Kaufmann [MVP] - 27 Apr 2006 16:27 GMT

>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
[quoted text clipped - 6 lines]
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?

Sorry, there isn't an option for automatic failback. You will have to do
that manually.

Signature

Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp

Reply to this Message

Roger Wolter[MSFT] - 27 Apr 2006 16:50 GMT

If you think about it, failing back will cause all client connections to be
dropped again so you probably don't want SQL Server to decide when is a good
time to fail back. It's probably much better if you pick a time when the
change won't affect too many users.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
[quoted text clipped - 8 lines]
>
> Per Schjetne

Reply to this Message

Per Schjetne - 27 Apr 2006 19:02 GMT

I agree on that. Is there a way to query any system tables telling me which
database is acting as principal vs mirror? Is the witness aware of this, or
is it just monitoring which server is replying, being able to do a failover?

> If you think about it, failing back will cause all client connections to
> be dropped again so you probably don't want SQL Server to decide when is a
[quoted text clipped - 13 lines]
>>
>> Per Schjetne

Reply to this Message

Roger Wolter[MSFT] - 27 Apr 2006 22:28 GMT

There's an event that you can monitor to be notified when the state changes.

The sys.dm_database_mirroring view can be used to determine the mirroring
state of all the databases of an instance. The
sys.dm_database_mirroring_witnesses can be used from the witness to see
which server is the primary.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

>I agree on that. Is there a way to query any system tables telling me which
>database is acting as principal vs mirror? Is the witness aware of this, or
[quoted text clipped - 19 lines]
>>>
>>> Per Schjetne

Reply to this Message

Per Schjetne - 28 Apr 2006 08:12 GMT

Thanks! Just what I needed. By the way, the correct tablename is:
sys.database_mirroring_witnesses

Per :-)

> There's an event that you can monitor to be notified when the state
> changes.
[quoted text clipped - 27 lines]
>>>>
>>>> Per Schjetne

Reply to this Message