SQL Server

SQL Mirroring, Preserving the Log Chain During Database Migrations9 min read

Database mirroring has been deprecated since SQL Server version 2012 and now with the advent of SQL 2016 SP1 which happens to include two node Always On in Standard edition, surely there’s no place in the World for it, right?

Consider this scenario for a moment, you’ve got a 2TB database that lives on Server A which for argument sake also happens to be part of an availability group and you need to migrate it to Server B.  Simple right, back it up and restore it to the new box?  If down time’s an issue, you can always restore it ahead of time with NO RECOVERY and keep it up to date with log backups?




Well….. no!  There’s a piece to this puzzle that I forgot to mention, you also happen to be log shipping this database to a customer.  It’s 2TB remember and any break in the log chain means that you’ve got to some how get a full backup out to them to reseed from.  A restore from backup just so happen to break the log chain.  It’s too big to send electronically so we end up having to get some poor soul to transfer the backup onto an external hard drive and hot foot it half way across the country to deliver it to the customer.  While it might be a nice jolly outing for our engineer, it leaves our unhappy customer without an up to date set of data which they may be relying on until we can get all this sorted out.

This is the exact situation that I’ve found myself in a few times and had me wishing, if only there was a way that I could move this database and keep the log chain in tact.  Yes, I could detach and reattach the database but that would mean some considerable down time while I copied 2TB of data files, unless I could do something funky the underlying VHDs…  which I couldn’t.  What could I do?

Time for our old, unappreciated and unloved friend, database mirroring to step up to the plate.  See, when you fail over a database mirror the log chain remains in tact.  Would it be possible to mirror this database across to it’s new home, fail it over, remove the mirror and log shipping to just carry on?  Well, yes actually it is and it also has the rather nice side effect that downtime from your point of view is virtually zero.

OK, OK I know that there are people out there shouting that Always On can do this too.  Well, firstly in this case these servers are already part of separate clusters so Always On isn’t an option and secondly, do you really want to go through all the hassle of setting up Always On just for this?

Rightio, enough waffle, shall we see this in action?

So on SQL3 you can see we’ve got an imaginatively named database, Database1

Which we need to migrate to SQL4

(yes I know the AG doesn’t look in a happy state, let’s just ignore that for the time being).

Restore Database1 to SQL4

First things first, we’re going to get the most recent transaction log backup restored over on SQL4.  Remember to restore this with NO RECOVERY (yes, I’ve managed to forget that bit myself once or twice).

So there we have it, our database is restored onto sql4.

Create The Mirror

The next step is to get the mirror created, the beautiful thing about database mirroring is that it’s so quick and easy to set up.  I agree, Always On is far simpler than clustering was in the old days but still, mirroring is far easier.

The first thing that you’ll need to do is head into the properties of your database and go to the mirroring options.

Click on Configure Security (I’ve always thought this button should really be called ‘Configure Mirroring’, but hey ho) and SQL Server will guide us through the steps that we need to go though to get everything setup.

We’re not going to be using this for high availability so there’s really no need to worry about a witness server.

The next thing we’re going to need to do is setup the end point, it’s interesting to note that if you’ve got Always On setup on the server, mirroring will use the Always On endpoint.  More proof if it was ever needed that Always On is just piggy backing on Mirroring’s technology.  One gotcha here, make sure that you’ve got your firewalls setup to allow traffic on port 5022 (or whatever you choose) otherwise you’ll spend many frustrating hours swearing at SQL while you try to figure out why mirroring isn’t working.

Once the endpoint of the old server is done, we’ll need to setup the end point on the server that we’re mirroring to.  Pop the server name into the Mirror server instance box and hit connect.  Again, if you’ve got Always On setup on that box, it’ll decide to use it’s endpoint.

Once we’re done with that, we’ll need to tell SQL Server what services the SQL is running under on each box, this allows it to setup the permissions that it needs to run mirroring.

If you don’t know the service accounts off the top of your head, there’s a nice simple query that will tell you.

Pop that in, hit next and we’re done.  You’ll get an option to start mirroring, go ahead and start it up.

If all’s gone well, we’ll have a nice mirror setup between our two servers.

The beauty here is that all this can be done way ahead of time of the actual migration.

The Fail Over

So, we got our mirror all setup and now it’s the big day, it’s ‘Migration Day’.  Everyone’s flapping around changing their connection strings, checking everything while you my wise and trusty DBA sit back and chill out with a cup of tea and a Brent Ozar video.  When the time does come for you to do your bit, in the past you’ve have been checking that users were out of the system, making sure that the latest transaction log had been played in but today, nah none of that worry.  Bring up your mirroring options and hit the ‘fail over’ button.

And you’re done.  The migration is finished as easily as that, all that’s left to do is remove the mirror.

But what about log shipping?

But wasn’t the whole point of this to figure out a way of preserving log shipping when carrying out a database move?  Well yes and because you’ve failed the mirror over and not broken the log chain, log shipping will just carry on happily working.

OK, OK, let’s prove it.  I took a transaction log backup before and after the migration, lets take a little look at those shall we…

Take a look at the last LSN from the backup on SQL3 and compare it to the first LSN from the backup on SQL4.  As you can see, they match.  The log chain is intact, you’ve successfully migrated a 2TB database with virtually zero database downtime and most importantly of all, those days of sending an engineer all over the country with disks full of backups in order to reseed a customer’s log shipping are over.

Leave a Comment