A SQL Server DBA myth a day: (7/30) multiple mirrors and log shipping load delays

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

A short one today as I'm teaching a class on HA strategies and technologies for Microsoft DBAs on the Redmond campus, so let's make it an appropriate topic:

Myth #7: A database can have multiple mirrors.

FALSE

This one's pretty cut and dried – database mirroring only allows a single mirror of a principal database. If you want to have extra copies of the principal database, consider using log shipping. You can have as many log shipping secondaries as you want.

One other cool thing about log shipping is that you can have one of the secondaries set to have a load delay of, say, 8 hours. This means the log backups taken on the principal (don't you love it that the various technologies have different nomenclature:

  • database mirroring: principal – mirror
  • log shipping: primary  – secondary
  • replication: publisher – subscriber

Ok – this parenthetical clause kind-of got a life of it's own…) won't be restored on the log shipping secondary until 8 hours have passed. If someone drops a table in production, it will pretty much immediately get dropped in the mirror (with whatever delay the SEND and WAIT queues have at the time – but you can't *stop it*) but the log shipping secondary with the load delay will still have it intact.

Incidentally, the SQLCAT team wrote a really good article debunking the myth (which stems from Books Online) that you can only mirror 10 databases per instance – see Mirroring a Large Number of Databases in a Single SQL Server Instance. Also take a look at the KB article I wrote for CSS last year which discusses the same thing: KB 2001270 Things to consider when setting up database mirroring in SQL Server.


 

One thought on “A SQL Server DBA myth a day: (7/30) multiple mirrors and log shipping load delays

  1. Thanks for this, it was handy & interesting especially the SQLCAT link, that is very useful info for planning scale-out of systems using mirroring for HA.

    Definitely the best of your mythbusters so far!

    And I’ve got to say I quite like the fact that the mirroring, log shipping, replication server roles have different/inconsistent names, I think it serves to differentiate them, and if we can’t handle it, shame on us!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.