SQL Mirroring, Preserving the Log Chain During Database Migrations

This is a reblog of a post that I originally wrote back in January.  After using this technique again last night to migrate and maintain the log chain of a large database and remembering how easy it makes the whole process, I thought that perhaps it deserved a re-post.  Whether clusterless AGs in SQL 2017 could replace database mirroring when migrating databases is something that I’ll be looking into in the near future.

SQL Undercover

Old Mirror Standing Against Wall

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…

View original post 1,098 more words

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: