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

DB mirroring question Expand / Collapse
Author
Message
Posted Sunday, June 9, 2013 11:47 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 298, Visits: 1,109
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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:43 AM
Points: 20,697, Visits: 32,331
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 6,416, Visits: 13,797
in asynchronous mode the only way to perform a failover between partners is to use the following

ALTER DATABASE [somedb] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

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