| Groups | Blog | Home |
sql server clustering : Mirroring Failover/Failback
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. -- 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 [quoted text, click to view] "Per Schjetne" <newsuser1@gdconsult.no> wrote in message news:uo3Ac4caGHA.3736@TK2MSFTNGP04.phx.gbl... >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 >
[quoted text, click to view]
"Per Schjetne" <newsuser1@gdconsult.no> wrote in message news:uo3Ac4caGHA.3736@TK2MSFTNGP04.phx.gbl... >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? Sorry, there isn't an option for automatic failback. You will have to do that manually. -- Russ Kaufmann MVP - Windows Server - Clustering ClusterHelp.com, a Microsoft Certified Gold Partner Web http://www.clusterhelp.com Blog http://msmvps.com/clusterhelp
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
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). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Per Schjetne" <newsuser1@gdconsult.no> wrote in message news:uo3Ac4caGHA.3736@TK2MSFTNGP04.phx.gbl... >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 >
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. -- 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 [quoted text, click to view] "Per Schjetne" <newsuser1@gdconsult.no> wrote in message news:OLn03SiaGHA.2368@TK2MSFTNGP03.phx.gbl... >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? > > > "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message > news:%23J5%23JJhaGHA.1192@TK2MSFTNGP04.phx.gbl... >> 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. >> >> -- >> 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 >> >> "Per Schjetne" <newsuser1@gdconsult.no> wrote in message >> news:uo3Ac4caGHA.3736@TK2MSFTNGP04.phx.gbl... >>>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 >>> >> >> > >
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? [quoted text, click to view] "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message news:%23J5%23JJhaGHA.1192@TK2MSFTNGP04.phx.gbl... > 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. > > -- > 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 > > "Per Schjetne" <newsuser1@gdconsult.no> wrote in message > news:uo3Ac4caGHA.3736@TK2MSFTNGP04.phx.gbl... >>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 >> > >
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. -- 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. "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:eDV9lIeaGHA.4936@TK2MSFTNGP05.phx.gbl... [quoted text, click to view] > 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). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Per Schjetne" <newsuser1@gdconsult.no> wrote in message > news:uo3Ac4caGHA.3736@TK2MSFTNGP04.phx.gbl... >>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 >> > >
Thanks! Just what I needed. By the way, the correct tablename is:
sys.database_mirroring_witnesses Per :-) [quoted text, click to view] "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message news:%23bsLDGkaGHA.3720@TK2MSFTNGP03.phx.gbl... > 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. > > -- > 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 > > "Per Schjetne" <newsuser1@gdconsult.no> wrote in message > news:OLn03SiaGHA.2368@TK2MSFTNGP03.phx.gbl... >>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? >> >> >> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message >> news:%23J5%23JJhaGHA.1192@TK2MSFTNGP04.phx.gbl... >>> 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. >>> >>> -- >>> 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 >>> >>> "Per Schjetne" <newsuser1@gdconsult.no> wrote in message >>> news:uo3Ac4caGHA.3736@TK2MSFTNGP04.phx.gbl... >>>>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 >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |