Issue:Mirroring Sync in Asynchronous Mode

  • 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

  • Please provide your valuable comments that can help,

  • 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.

  • This was removed by the editor as SPAM

  • 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.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • 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.

  • 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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • 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 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?

  • 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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • houming1982 (4/12/2013)


    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?

    If a fail over is issued either manual or automatic, there is no chance for data loss. Data loss will not occur. Data loss is only possible when you use FORCE_SERVICE_ALLOW_DATA_LOSS to force the mirror to come online. When the mirroring session is resumed after forcing service, and data that was on the principal and not yet synched to the mirror at time of failure will be lost and the new mirror (original principal) will be synched with the new principal.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • I agree with Robert Davis, once mirroring is suspended, it is not started automatically, you have to issue "ALTER DATABASE DBName SET PARTNER RESUME", then only the mirroring will start again.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply