Mirroring

  • Working on setting up SQL Server mirroring.
    Following are the steps I am thinking to do..

    Take Full Backup and Transaction Log backup at Principal server.
    Restore the Full and transaction log backup in recovery mode to mirrored server.
    However, I have a question So the backup and restore will take approx. 10 hrs. How does mirroring knows all the changes made after backup is taken. So how does mirror pushes all the changes made since that backup. How does it keep the track of all changes? Does this happen through LSN number?  Please advise?

    Thanks

  • Admingod - Thursday, August 23, 2018 12:25 PM

    Working on setting up SQL Server mirroring.
    Following are the steps I am thinking to do..

    Take Full Backup and Transaction Log backup at Principal server.
    Restore the Full and transaction log backup in recovery mode to mirrored server.
    However, I have a question So the backup and restore will take approx. 10 hrs. How does mirroring knows all the changes made after backup is taken. So how does mirror pushes all the changes made since that backup. How does it keep the track of all changes? Does this happen through LSN number?  Please advise?

    Thanks

    You would want to restore using norecovery.
    It uses the transaction log and the LSNs to track the transactions needed. If you have done any other differential or log backups during that 10 hours, you should restore those with norecovery since those transactions would be needed on the mirror.  Use the last differential since the full and any of the subsequent transaction log backups.

    Sue

  • So how does the mirror will catch up after the restore. How does it push the changes? Do i need to stop the backups on principal after that? Please advise?

  • Admingod - Thursday, August 23, 2018 5:45 PM

    So how does the mirror will catch up after the restore. How does it push the changes? Do i need to stop the backups on principal after that? Please advise?

    The mirror catches up by restoring everything you need (with norecovery). Anything else would be in the log that it reads. After you do the full backup on the principal that you are going to restore to the mirror, you don't not have to stop the backups on the principal.
    While the restore is then happening on the mirror, you may log backups that run on the principal. If there are a lot of log backups during that time, you might want to consider doing a differential on the principal towards the end of the restore of the full on the mirror.
    After the restore of the full backup is done on the mirror restore, you restore any differential if it applies and the logs since the last differential or the last full backup if you did not do a differential. You restore all of these with norecovery. It's really not that much different from restoring to a point in time.
    One you have restored any of those other needed backups from the principal, then any other transactions would be in the transaction log for the principal, not in the backups. You would stop the log backups on the principal once everything is restored and you have already applied the last of all of the backups to the mirror. And now you finish configuring your mirroring and start the mirroring process. The principal and the mirror communicate using the endpoints. The transaction log records are streamed from the principal to the mirror.

    Sue

  • Thanks! You're awesome.

  • We have seen the issue with creating snapshots on the mirror server. Do you know why this could happen? Does restarting the Endpoints help any?

  • I am planning to do Database mirroring failover. What do you think i should ensure other than just doing failover. Is anything i would need to worry about before or after doing the failover?

    Thanks

  • Admingod - Wednesday, August 29, 2018 12:07 PM

    I am planning to do Database mirroring failover. What do you think i should ensure other than just doing failover. Is anything i would need to worry about before or after doing the failover?

    Thanks

    Sorry...was out most of last week.
    With your previous question about the snapshot on the mirror, did you get the error you couldn't create it? Restarting endpoints may help but you should also check the mirror to make sure it's caught up or the snapshots will fail.

    When testing out the failover, logins and jobs would be important to make sure the role switch works properly. Need to prepare for it, manage it before and after:
    Management of Logins and Jobs After Role Switching (SQL Server)

    Sue

  • How to break the mirroring?

  • Admingod - Thursday, September 6, 2018 4:42 PM

    How to break the mirroring?

    On the principal, ALTER DATABASE <database name> SET PARTNER OFF

    Sue

  • We are doing DR test. In order to do the DR test i have to break the mirroring instead of failover, that way the databases will be available on both locations. 

    So breaking mirroring means removing the mirroring from GUI or ALTER DATABASE <database name> SET PARTNER OFF. What would be the difference between this? So once i break the mirror i need to reconfigure the mirroring?

    Thanks

  • Admingod - Thursday, September 6, 2018 5:23 PM

    We are doing DR test. In order to do the DR test i have to break the mirroring instead of failover, that way the databases will be available on both locations. 

    So breaking mirroring means removing the mirroring from GUI or ALTER DATABASE <database name> SET PARTNER OFF. What would be the difference between this? So once i break the mirror i need to reconfigure the mirroring?

    Thanks

    It should be the same using the GUI. The two different ways are documented in this article: 
    Remove Database Mirroring (SQL Server)

    Once the mirror is broken, if you left everything else in place (endpoints) you can usually set it back up by restoring again to the mirror server with no recovery and then do the Alter database DBName set partner..... .on both of the servers.

    Sue

  • Hi Sue,

    I have a question about database mirroring. We are upgrading the database server which have mirroring configured. The upgrade would happen on different server with different name(basically it's a side by side upgrade). So old server would be taken down during the upgrade which has mirroring configured. In case If the upgrade doesn’t go well and then we have to bring the old server back online. Wanted to check with you how the mirroring should be take down in case if the upgrade doesn’t go well? We do create snapshots so how does the snapshot would work. What are the steps I need to follow so that in case the upgrade won’t work, then I can bring the old server back online and so than I can enable the mirroring safely? Actually how to we reboot the mirrored servers?
    Please advise?

  • Any advise?

Viewing 14 posts - 1 through 13 (of 13 total)

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