Does a mirroring configuration requires the database do stop receiving requests?

Question

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.

Answer 1

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_MIRRORING event 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.

Answer 2

I had the same issue with configuring mirroring as my partner server was located in a different hosting center with no cross connect. What I had to do was monitor my database usage. This then showed me the quietest time of day to do the Full and log backup. My company generally has about 20000 transactions every min. I ended up having to configure my mirroring late at night. Lucky for me I had everything scripted and had it scheduled. Next morning woke up and all was done. I created scripts using TSQL for creating the backup and log backup which once done sent the backup files to my DR server. Using .net I created a little folder monitor application which would start the restore as soon as the file placement was detected. After all of this was restored I simply executed the TSQL scripts to setup the PARTNERS