Post reply

Re-establishing a Mirror with zero downtime

  • January 10, 2019 at 10:54 am

    #416621

    Hi Guru's

    We have a situation. Our Principle (Production SQL Server)  and Mirror (DR SQL Servers) are in different Data centres.

    The network connection between the two is not that fast and establish a mirror has been problematic in copying the large db backup and transaction logs across the network.

    Currently the SQL Mirror between the two is broken. Can I confirm that there is no way of establishing a Mirror with zero downtime and that indeed in order to establish a mirror in the first place both the transactions on the principle and the mirror need to be identical?

    Is there any way to create a mirror with zero downtime?

    Thanks

  • January 10, 2019 at 11:39 am

    #2018602

    SQL Bandit - Thursday, January 10, 2019 10:54 AM

    Hi Guru's

    We have a situation. Our Principle (Production SQL Server)  and Mirror (DR SQL Servers) are in different Data centres.

    The network connection between the two is not that fast and establish a mirror has been problematic in copying the large db backup and transaction logs across the network.

    Currently the SQL Mirror between the two is broken. Can I confirm that there is no way of establishing a Mirror with zero downtime and that indeed in order to establish a mirror in the first place both the transactions on the principle and the mirror need to be identical?

    Is there any way to create a mirror with zero downtime?

    Thanks

    You do not need downtime on the principal to establish the mirror. 
    One you perform the restores on the secondary, you start the mirroring.  

    Is there something that you are leaving out possibly??

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • January 11, 2019 at 4:43 am

    #2018674

    Hi Michael

    Thanks for the reply.

    I did a little test on my lab Mirror. 
    1. Broke the Mirror , 2. updated a load of records , 3 Establish the partners again with SET Partner
    I was surprised that the mirror auto established. But hey you learn something new everyday.

    That aside.

    I try to establish the partners again in Production/ DR 
    and I got an ..

    The remote copy of database  has not been rolled forward to a point in time that is encompassed in the local copy

    So what I've researched is that all transaction log backups need to be applied to the mirror in order to establish a mirror.

    'Generally you have to backup one log file and restore it on the mirror before you can setup mirroring. Also, any log backup you do since the full backup must be restored on the mirror.

    But basically the error message says it needs more log. Go ahead and backup the log again, restore it and try again.'

    So in conclusion you can establish a mirror and that the principle still online and increasing in transactions as long as you restored all backups to the mirror.
    So what you chould do if you wanted to give yourself some time to establish a mirror is to perform a db backup , restore that backup then pause or extend the next run of a transaction log backup.
    Or better still , perform a db backup and then a transaction log backup and then pause/extend further transaction log backups intil you've established the mirror?

    Correct? That will allow for zero down time? and no maintenance window?

    Thanks

  • January 11, 2019 at 8:51 am

    #2018707

    SQL Bandit - Friday, January 11, 2019 4:43 AM

    Hi Michael

    Thanks for the reply.

    I did a little test on my lab Mirror. 
    1. Broke the Mirror , 2. updated a load of records , 3 Establish the partners again with SET Partner
    I was surprised that the mirror auto established. But hey you learn something new everyday.

    That aside.

    I try to establish the partners again in Production/ DR 
    and I got an ..

    The remote copy of database  has not been rolled forward to a point in time that is encompassed in the local copy

    So what I've researched is that all transaction log backups need to be applied to the mirror in order to establish a mirror.

    'Generally you have to backup one log file and restore it on the mirror before you can setup mirroring. Also, any log backup you do since the full backup must be restored on the mirror.

    But basically the error message says it needs more log. Go ahead and backup the log again, restore it and try again.'

    So in conclusion you can establish a mirror and that the principle still online and increasing in transactions as long as you restored all backups to the mirror.
    So what you chould do if you wanted to give yourself some time to establish a mirror is to perform a db backup , restore that backup then pause or extend the next run of a transaction log backup.
    Or better still , perform a db backup and then a transaction log backup and then pause/extend further transaction log backups intil you've established the mirror?

    Correct? That will allow for zero down time? and no maintenance window?

    Thanks

    Correct. 
    Assuming it was a brand new mirror, you would restore the full backup, and the subsequent log backups, and then start the mirror. 
    Depending upon the bandwidth, that may be very slow.  
    In your case, you may simply(!) be able to restore the log backups from when the mirror was broken, and re-set up the mirroring.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 4 posts - 1 through 3 (of 3 total)

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