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»»

Issue:Mirroring Sync in Asynchronous Mode Expand / Collapse
Author
Message
Posted Monday, March 26, 2012 1:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:32 PM
Points: 23, Visits: 88
Hi All,

We need a favour from your side. It is regarding mirroring setup in our environment what happens if for any reasons like n/w fluctuation mirroring get disconnected it’s not giving retry for synchronising with mirror database if we do the manual failover of resources at DR side then it get restarted but automatically it’s not retry for syncing.

Please provide your expert comment regarding in this matter.

Regards,
Sachin
Post #1272470
Posted Monday, March 26, 2012 10:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:32 PM
Points: 23, Visits: 88
Please provide your valuable comments that can help,
Post #1273164
Posted Monday, March 26, 2012 10:31 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 20,705, Visits: 32,357
If you force a failover, you will most likely lose transactions that had not made it over to the mirror database. Can't really tell you more than that.



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 #1273168
Posted Tuesday, March 27, 2012 4:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 8:53 PM
Points: 114, Visits: 1,231
I am only aware about that manual failover requires that safety be set to FULL, and the principal and mirror databases should be in the SYNCHRONIZED state.

SQL Database Recovery Expert
Post #1273359
Posted Wednesday, March 28, 2012 9:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:31 PM
Points: 535, Visits: 877
If you switched to high safety mode, does it still occur?
I know high speed mode is nice but there could be a network issue that is causing the disconnect.



Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
Post #1274406
Posted Wednesday, March 28, 2012 11:14 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 20,705, Visits: 32,357
From Books Online.


When transaction safety is set to OFF, the session operates asynchronously. Asynchronous operation supports only one operating mode—high-performance mode. This mode enhances performance at the expense of high availability. High-performance mode uses just the principal server and the mirror server. Problems on the mirror server never impact the principal server. On the loss of the principal server, the mirror database is marked DISCONNECTED but is available as a hot standby.

High-performance mode supports only one form of role switching: forced service (with possible data loss), which uses the mirror server as a hot standby server. Forced service is one of the possible responses to the failure of the principal server. Because data loss is possible, you should consider other alternatives before forcing service to the mirror; for more information, see "Responding to Failure of the Principal," later in this topic.


It may not be a manual failover as in the case of syncronous (high safety mode) but it can be done.



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 #1274491
Posted Wednesday, April 4, 2012 7:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 PM
Points: 1,618, Visits: 1,548
To sum things up:

If you are running asynchronous (safety off) mirroring, the only way to fail over manually is to change the operating mode to synchronous (safety full), wait for it to be in a synchronized state, and then issue the failover command.

If you are running asynchronous (safety off) mirroring, and the principal goes offline, the best option for bringing the mirror online is to force service (ALTER DATABASE ... SET FORCE_SERVICE_ALLOW_DATA_LOSS). This will allow the mirror to come online. Any transactions that had committed on the principal but had not been hardened to the log on the mirror may be lost.

If the original principal comes back online after you have forced service, database mirroring will stay in a suspended state. The mirroring session will not automatically resume. This gives you time, if you so choose, to try to recover data missing on the new principal because data loss does not occur until the mirroring session is resumed. You could drop mirroring and recover the original principal to identify any extra data and manually insert it into the live database.

For a full explanation of the potential for data loss with asynchronous mirroring, have a look at the article I wrote for SQL Server Pro magazine: http://www.sqlmag.com/article/data-mirroring/database-mirroring-disaster-recovery-139794




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1278015
Posted Thursday, April 5, 2012 4:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:32 PM
Points: 23, Visits: 88
If both principal and mirror are synchronized then manual failover is not the issue but in case if databases are not synchronized and for drill or in distar we need to bring mirror force fully up in that case user will start using mirror which is principal now once of old principal is up we want that to take load.How we can achieve that without data loss that is the main issue by what methods we can overcome this situation.

Please help.

Regards,
Sachin
Post #1278672
Posted Friday, April 12, 2013 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:42 AM
Points: 6, Visits: 241
If data loss happened when failed over to the mirror server, and there were lots of transactions happened on the mirror server during the downtime of principal server, HOW TO FAILOVER back to the principal server on keeping Consistency of data?
Post #1441750
Posted Friday, April 12, 2013 10:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 PM
Points: 1,618, Visits: 1,548
dbasachinkumar (4/5/2012)
If both principal and mirror are synchronized then manual failover is not the issue but in case if databases are not synchronized and for drill or in distar we need to bring mirror force fully up in that case user will start using mirror which is principal now once of old principal is up we want that to take load.How we can achieve that without data loss that is the main issue by what methods we can overcome this situation.

Please help.

Regards,
Sachin


If you force service, when the principal comes back online, mirroring will not restart automatically. You have to issue the RESUME command. The data on the principal that was not synched to the mirror when you forced service is not lost until the mirroring session is resumed. You have the option to remove mirroring instead or create a database snapshot and bring the original principal online so you can query it for data not on the original mirror (current principal) and manually insert the missing data yourself into it.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1441783
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse