Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

DB mirroring question Expand / Collapse
Posted Sunday, June 9, 2013 11:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 1, 2016 7:51 AM
Points: 308, Visits: 1,254
In asynchronous mode of db mirroring, to manually failover, why do we have to change the transaction safety to FULL?
Post #1461388
Posted Sunday, June 9, 2013 11:57 PM



Group: General Forum Members
Last Login: Yesterday @ 4:04 PM
Points: 22,548, Visits: 35,835
This is to prevent data loss between the principal and mirror databases. Asynchronous commits data to the principal without waiting for it to be committed on the mirror. If you fail-over while in asynchronous mode there is possibility of data loss. If you are able to switch to a synchronous mode first, then once the databases are synchronized you can fail-over without loosing data.

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1461393
Posted Monday, June 10, 2013 4:49 AM



Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 7,964, Visits: 15,989
in asynchronous mode the only way to perform a failover between partners is to use the following


As Lynn says, data could be lost so a switch to FULL safety and synchronisation of the mirror session first is required.

Note that even with FULL safety, if you lose the principal database and have no witness the only way to bring the mirror up is to use the T-SQL command above


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

Add to briefcase

Permissions Expand / Collapse