Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Manually failover database mirroring Expand / Collapse
Author
Message
Posted Monday, August 12, 2013 2:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:02 AM
Points: 211, Visits: 842
We have mirroring set up over a WAN and as such opted for manual failover to avoid network issues causing automatic failover and rendering connected clients unusable.

My question is, if the primary site was destroyed and connection to the principle is lost...is it still possible to failover or do you have to have that connection?

Many thanks
David


'Only he who wanders finds new paths'
Post #1483188
Posted Monday, August 12, 2013 2:56 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:02 AM
Points: 211, Visits: 842
Can I do this on the mirror?

ALTER DATABASE<Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS


'Only he who wanders finds new paths'
Post #1483190
Posted Monday, August 12, 2013 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 7,001, Visits: 8,439
Did you have a look at BOL ?

Forced Service (with Possible Data Loss) http://technet.microsoft.com/en-us/library/ms189977(v=sql.100).aspx

it has a ref How to: Force Service in a Database Mirroring Session (Transact-SQL). which states the command you mentioned for the purpose you intended.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1483205
Posted Monday, August 12, 2013 4:08 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:02 AM
Points: 211, Visits: 842
Yes of course I looked on BOL, I was just after some confirmation really after posting the command. However, I didnt put any of that in my 2nd post and left it rather brief relying on everyones psychic knowledge which I apologise for! Thanks for the reply and yes, its exactly what I was looking for.

Thanks again.


'Only he who wanders finds new paths'
Post #1483210
Posted Monday, August 12, 2013 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 7,001, Visits: 8,439
Of course, testing it is fairly easy to do and the preferable way before implementing it into production.

Walk through all scenarios and test the actions before you need to apply them in production.

I strongly advise to monitor your mirrored databases as mentioned in BOL:
Monitoring Database Mirroring

and prepare your DRP inventory scripts


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1483225
Posted Tuesday, August 13, 2013 12:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 6,162, Visits: 13,305
david.alcock (8/12/2013)
Can I do this on the mirror?

ALTER DATABASE<Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Losing the principal in either async mode or sync mode with no witness requires you to use the above query to bring the mirror online.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1483887
Posted Wednesday, August 14, 2013 3:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:19 AM
Points: 1,044, Visits: 2,926
Hi

ALTER DATABASE<Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Instead of above query, can we run as below query for make it online in MIRROR Database..? Mirror database always sychronizing/Restoring mode. wheather High performance and High safty mode configured


USE master
GO
ALTER DATABASE <MIRROR DATABASE> SET PARTNER OFF
GO
RESTORE DATABASE <MIRROR DATABASE> WITH RECOVERY
GO


Post #1484145
Posted Wednesday, August 14, 2013 3:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 7,001, Visits: 8,439
ananda.murugesan (8/14/2013)
Hi

ALTER DATABASE<Databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Instead of above query, can we run as below query for make it online in MIRROR Database..? Mirror database always sychronizing/Restoring mode. wheather High performance and High safty mode configured


USE master
GO
ALTER DATABASE <MIRROR DATABASE> SET PARTNER OFF
GO
RESTORE DATABASE <MIRROR DATABASE> WITH RECOVERY
GO




That would turn it back into a non-mirrored database !

If you would want to re-enable mirroring for that database, you would have to perform the full setup for it.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1484148
Posted Wednesday, August 14, 2013 3:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:19 AM
Points: 1,044, Visits: 2,926
I have doubt.. I have Mirror setup manual failover without witness server

If Principal server is down due to N/W or H/W issues, In this cause mirror database condition is Restoring only...so I want to make online mirror database which command need to be executed?

Is it correct as below command? only one command

ALTER DATABASE <MIRROR DATABASE> WITH RECOVERY

rgds
ananda


Post #1484153
Posted Wednesday, August 14, 2013 6:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 6,162, Visits: 13,305
ananda.murugesan (8/14/2013)
I have doubt.. I have Mirror setup manual failover without witness server

If Principal server is down due to N/W or H/W issues, In this cause mirror database condition is Restoring only...so I want to make online mirror database which command need to be executed?

Is it correct as below command? only one command

ALTER DATABASE <MIRROR DATABASE> WITH RECOVERY

rgds
ananda

As i said, whether you use synch or asynch mirroring without a witness, if you lose the principal the only way to bring the mirror online is by using

ALTER DATABASE &lt;Databasename&gt; SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1484251
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse