locked
How to increase synchronization duration between principal and mirror db in db mirroring RRS feed

  • Question

  • How to increase synchronization duration between principal and mirror db in db mirroring?

    I need to setup a dr solution for my existing production db server which is in other cloud provider to other cloud service provider. I am thinking log shipping and db mirroring as solution for syncing data between sites. At the same time i need to secure my data over internet.

    My concern is: if i can increase the duration of data sync between between primary and mirror database? As with log shipping we can change the sql jobs schedule as per our requirement to control data sycn between primary and seconday server.

    Thanks for your comment..


    Santosh Singh

    Sunday, September 30, 2012 10:01 AM

All replies

  • Hi Santosh,

    Are you saying the duration of high-safety mode which supports synchronous operation in database mirroring? If so, why do you want to increase the duration?

    To achieve synchronous operation for a session, the mirror server must synchronize the mirror database with the principal database. When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.

    The time required for synchronization depends essentially on how far the mirror database was behind the principal database at the start of the session (measured by the number of log records initially received from the principal server), the work load on the principal database, and the speed of the mirror system. After a session is synchronized, the hardened log that has yet to be redone on the mirror database remains in the redo queue.

    For more details, please refer to this article: Synchronous Database Mirroring (High-Safety Mode).


    Best Regard,
    Ray Chen

    Tuesday, October 2, 2012 8:32 AM
  • Hi Ray,

    I understand what you are saying. However just consider an example wherein somebody mistakenly drops a table and  due to respective sites getting sync between production and dr, our dr site will also be having database wherein table will be dropped very quickly. So this is something which we will definetly not like to have. So just thinking if i need to offer database mirroring as solution, i need to somehow stop the data sync in case somehow i\team knows that somebody dropped table and i need to stop that so that table shouldn't be dropped at dr site as well.

    I know this will be incident and can be taken care in numerous ways like defining this as incident and recent\relevant backup can be restored or something if need be, but if i compare with log shipping, i can easily stop the job or disable till the situation demands in case of log shipping.

    I was thinking if i can somehow stop\disable this data sync with db mirroring, but this will be much faster\quicker to apply data when we compare with log shipping. I hope you will agree with this.

    I was still thinking that we can still do this by updating some db mirroring sys store procedures. If you know any please share or some method.

    Regards,

    Santosh Singh


    Santosh Singh

    Tuesday, October 2, 2012 3:10 PM
  • Database mirroring is a high availability solution that can be used as a disaster recovery solution as well. The case where a user dropped a table is a disaster situation. You have to think of it in these terms. Now, you can use a combination of database mirroring and log shipping to address your requirement - have database mirroring configured in synchronous mode and log shipping with an hour of delay. That way you have a database mirroring pair that can be used for a quick failover for high availability purposes and a log shipping standby that can be used as a reference point for when a user accidentally drops a table, all in the context of addressing your RPO/RTO. The best way, of course, is to prevent it from happening by implementing DDL triggers to roll back those changes 


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    • Proposed as answer by Shulei Chen Monday, October 8, 2012 9:44 AM
    Tuesday, October 2, 2012 3:58 PM
  • So are we saying that there is no possibility of updating means of data sync between production and dr using db mirroring?

    Also I wanted to use only db mirroring not for log shipping and was checking if we can increase\decrease the data sync between dr and prod.

    We should also notice that dr and prod needs to talk over internet so data needs to be secure and configuration should be possible.

    I am sorry if it appears too much.. I am just asking your suggestion.

    Plz suggest...

    Thanks,


    Santosh Singh

    Wednesday, October 3, 2012 4:49 AM
  • One way i have found that we have option to increase the duration for data sync.

    However i too have option to stop, pause the data sync jobs in some situation like i had asked for.

    Yes as you have mentioned we can definitely go for.


    Santosh Singh

    Monday, October 8, 2012 10:32 AM