I want to configure a SQL Server 2012 Mirroring. I know that I have to create a full backup of the Principal server and restore it at the Mirror server. After that, I will backup the transaction log of the Principal and restore it at the Mirror server.
My doubt is: if my Principal database is online (in production) and receiving many insert/delete/update requests, is it a problem? The mirroring and sync can be done without the need of stop my Web application and the Principal Database will be 100% available during this process? I'm worried because the transaction log that will be restore at the Mirror will not represent all transactions because some of then will happen between the backup-restore and the start of the mirroring sync.
Best Answer
After you take your transaction log backup and restore it on the mirrored pair, you might have taken more transaction log backups. If so, try to catch up on the backups then init the mirror. At that point all the old transactions that are not replayed with be queued up and you will endure a
DB_MIRRORINGevent on your log file which will stop the transaction log from truncating those transactions that have not replayed yet. Do note if your network is really slow those transactions might continue to get queued up, it's critical to have an appropiate IO subsystem and network on the 2nd pair so it can keep up.I'm not sure if you can in your environment, but if the transaction log backups happen too fast, perhaps then you have a IO subsystem problem you need to review on the 2nd pair or a networking problem. Worst case, you could pause transaction log backups if it isn't going to cause a bad growth issue. Otherwise you could look at using a DIFF backup to fully catch up then do 1 transaction log restore.
Also, consider high performance mode over high safety mode if you can, but if you can't just make sure your network and diskIO are production quality. Also set up monitoring for log delays, it will cause massive growth on the primary if you don't.