Sql-server – Database Mirroring setup does not work when the database is above a certain size, logs out of synch

mirroringsql-server-2012transaction-log

I have a Microsoft database running in production of about 300gb, I need to transfer and set it up in a mirrored configuration on two different servers.

This has worked fine in my tests using smaller databases, but now I run into the following issues while trying to use the actual production database. It works about 1 out of 3 times, the rest of the times it ends in errors.

When trying to enable mirror on the primary it gives this error:
"The remote copy of database "myDatabase" has not been rolled forward to a point in time that is encompassed in the local copy of the database log."

If I then try and take a new backup of the database log and restore on the mirror:
"Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 64612000000218000001, which is too recent to apply to the database. An earlier log backup that includes LSN 64612000000217800001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally"

Here is what I am doing, it is also very time consuming and will require a lot of downtime, so if there is an easier way I would be very happy:

Server A is the current production server where a single instance of the db is running.
Server B is the initial primary database server
Server C is the initial mirror database server

I break the mirror from Server B and delete the databases on Server B and C.

ALTER DATABASE produktion SET PARTNER OFF

I take a backup from Server A and store it in a shared folder on Server B.

BACKUP DATABASE [myDatabase] TO  DISK = N'\\Server B\share\myDatabase.bak' WITH NOFORMAT, INIT,  NAME = N'myDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

I restore from the backup on Server B.

RESTORE DATABASE [myDatabase]    FROM DISK = '\\Server B\share\myDatabase.bak'    WITH 
MOVE 'mdflogicalfilename' TO    'D:\DEFAULT_2012_DATA\mdffile.mdf',
MOVE 'extralogicalfilename_extra' TO    'D:\DEFAULT_2012_DATA\extralogicalfilename_extra.mdf',
MOVE 'logfilename_log' TO    'E:\DEFAULT_2012_LOG\logfilename_log.ldf', 
NOUNLOAD, REPLACE, STATS =5;

I take a new backup on Server B (unsure if this can be avoided?)

ALTER DATABASE 'myDatabase' SET RECOVERY FULL;
BACKUP DATABASE 'myDatabase'
TO DISK = '\\Server C\share\myDatabase_full.bak' 
WITH INIT, FORMAT, STATS = 10;

BACKUP LOG 'myDatabase'
TO DISK = '\\Server C\share\myDatabase_log.bak'
WITH INIT, FORMAT, STATS = 10;

I restore from the new backup and log on Server C with NORECOVERY

RESTORE DATABASE "myDatabase"    FROM DISK = '\\Server C\share\myDatabase.bak'    WITH 
MOVE 'mdflogicalfilename' TO    'D:\DEFAULT_2012_DATA\mdffile.mdf',
MOVE 'extralogicalfilename_extra' TO    'D:\DEFAULT_2012_DATA\extralogicalfilename_extra.mdf',
MOVE 'logfilename_log' TO    'E:\DEFAULT_2012_LOG\logfilename_log.ldf', 
NOUNLOAD, REPLACE, NORECOVERY;


RESTORE LOG "myDatabase"    FROM DISK = '\\Server C\share\myDatabase_log.bak'  WITH
NORECOVERY, STATS = 10;

Enable Mirroring on the initial mirror:

ALTER DATABASE myDatabase SET PARTNER = 'TCP://Server_B_FQN:myPort'

Enable Mirroring on the initial primary (this is where it fails):

ALTER DATABASE myDatabase SET PARTNER = 'TCP://Server_C_FQN:myPort'

The whole procedure takes about 7-8 hours, I have created each step as agent jobs to start the next one, but if something can be done faster it would help a lot.

It works about 1 out of 3 times, the other times I get the above errors from which I cannot seem to do anything but start over and hope it works.

Issue doesn't happen with small databases, so the issue is probably that the restore takes so long on Server C, that the DB on Server B some times creates new logs that are then out of synch, this would also explain that it works some times, but there are no changes comitted to the database doing the time.

Is there any way to restore all incremental logs and make sure the databases are in synch for mirror without restoring a full backup of the database?

Best Answer

As has already been noted there is likely to be a third party tool taking backups which you weren't aware of. Stopping that (or at least having control over it) is going to assist no end (obviously be aware that stopping log backups may invalidate the RPO/RTO targets and taking adhoc full and log backups will invalidate the log chain and may make dr recovery more complex or result in larger data loss during this period).

You've mentioned that you are keen to reduce the downtime required; I believe you have a few options available to you to assist.

I note that you state SQL Server 2012. Backup compression is available in standard edition upwards, enabling this will reduce IO (and therefore time) for both your backup and restore at a relatively small cost of CPU. I have seen backups reduce by nearly half using compression (depending on what is stored in the DB). Simply add compression to your with options. This can be used in conjunction with the below to reduce the outage window further.

Option 1 - Use a differential backup

Rather than taking a full backup at your outage time, take a full backup (or last nights if done daily) and restore that during the day of migration to your other servers. You could then take a differential at your maintenance window. As a differential backup only contains the changes since the last full backup it will be much smaller and faster than a full backup. It also doesn't have any dependency on log backups so as long as your full backup is good you can apply a differential to get it in sync. Be aware though, the differential must be taken before the next full backup so either know that the planned window is before the nightly backups or suspend the full backup for this database.

Therefore you could:

  • Take the full backup on A and restore it to both B (NORECOVERY) and C (NORECOVERY) with the application still up (and log backups continuing).
  • Wait until your outage time, stop log backups and take a differential backup
  • Update your connection string (or CNAME)
  • Restore differential to A (with RECOVERY) and B (with NORECOVERY)
  • Initiate the mirror between B and C
  • Start log backups on B

Option 2 - Mirroring

As you are not in a mirrored configuration currently you could look to create the mirror between A and B first; even if going up SQL Server versions mirroring will still work one way (from older to newer versions). Restore and mirror would be done with the application still online, without requiring downtime.

This might look something like:

  • Backup Server A and restore to B (NORECOVERY)
  • Stop log backups on A, take differential and restore to B
  • Initiate mirror (A --> B)
  • Restart log backups
  • When your outage window comes up, stop the application services, change the DNS CNAME (or connection string)
  • Failover the mirror (from server A alter database [mydb] with partner failover;
  • start the application services again
  • Start log backup schedule on B
  • Backup full db on B
  • Restore to C (NORECOVERY)
  • If log backup has been taken in time between backup and restore from B to C then take differential on B and restore to C
  • Initiate mirror

Option 3 - Log shipping

By utilising log shipping you can actually seed both B and C simultaneously.

The process would be similar to below:

  • Take full backup and restore to B and C (NORECOVERY)
  • Stop log backups (permanently on third party tool)
  • Take and restore differential to B and C (NORECOVERY)
  • Start log shipping configuration with 2 secondaries (B and C, NORECOVERY). This will be creating the log backup files so may need to mention this to the backup guys so they capture the location, note that the SQL Agent services will need to have read/write permissions over the share(s) for this to work.
  • When downtime outage comes, take final log backup and restore to secondaries (jobs found in agent for backup/restore jobs on respective instances)
  • Change CNAME (or connection string)
  • Bring server B database out of restoring state restore database [mydb] with recovery
  • Initiate mirror
  • Start log backups on Server B
Related Question