5

We are moving to SQL Server 2014 Expensive Edition Q1 next year. Right now we are on SQL Server 2005 Standard Edition, so we don't have access to the oh-so-wonderful MIRROR TO option for our backups (We use Ola Hallengren's scripts).

Right now we are using robocopy to copy files over to a network share just in case our backups stored locally become corrupt, lost, or decide to take a nice long vacation. My question is, when we use the MIRROR TO option, is it any more/less resource intensive than just using robocopy, xcopy or a Powershell solution? What exactly happens when we use MIRROR TO in SQL Server? Looking for a bit of a 'nuts and bolts' answer, if possible.

  • It seems you are asking out of curiosity. Or are you trying to make a decision? Are you asking for advice on what to do? – usr Dec 31 '14 at 17:35
  • 1
    This is, primarily, out of curiosity. However, since I will have this function available to me once we upgrade, I thought I would ask and see if anyone knew any advantages or disadvantages to it and try to gain some understanding to the 'under the hood' working of this feature. – Kris Gruttemeyer Dec 31 '14 at 17:37
7
+50

I'm guessing here based on how I'd implement MIRROR TO:

Probably, SQL Server streams the backup data to two backup devices if you use MIRROR TO. This means that every block they read is written two times. The data stream has a fork.

What they are certainly not doing is write the data once and then copy it over. That would a) incur an additional read pass over the data and b) also open a window for data corruption while only a single copy of the backup exists. From an implementors standpoint it is less work to fork the stream and it has a better result. That's why I think they're doing that way.

This means that MIRROR TO is less resource intensive than copying the data files after the backup has succeeded. It also has less room for data corruption. Data corruption is rare but a practical concern. Memory, disk and network all can have undetected bit flips. (Yes, TCP does not 100% protect from that.)

Advice given the fact that Enterprise Edition will become available to you: Use MIRROR TO. It results in less resource usage and less potential for (undetected) backup corruption. It also automates stuff that you can get wrong when you are doing it manually. SQL Server is certainly tested better than most in-house script development. The only reason not to use it would be if your backup process cannot easily be modified to use it.

One additional point: If the mirror cannot be written the backup will fail. This can be a downside (you probably exceed your RPO objective). It can be an upside because errors are more likely to be detected.

As mentioned by Paul White in the comments: If one of the backup destinations is slow (or all data is flowing over the same saturated network link) the backup might take a lot longer than before. In case of a saturated network link it might be faster to locally duplicate the backup file on the target server.


This theory can be tested: Back up the database to two IO devices that have identical perf characteristics using MIRROR TO. If I'm correct you should observe only a tiny slowdown because the writes are streaming and parallel.

I performed this benchmark myself. Backing up from SSD (very fast) to two (almost) identical magnetic drives. Using data compression (but the CPU was not maxed out).

  • Mirror: 1:37.
  • No mirror: 1:10 to drive 1.
  • No mirror: 1:14 to drive 2.

This slowdown (1:37 vs. 1:14) is well within the tolerance of my theory. There seem to be some overheads but there certainly is no separate mirroring/copy phase. Using Process Explorer I observed IO measured in MB/sec. It was fairly constant. This also hints that there is no separate phase. Writes seem to be parallel to both drives.

  • I really would love to mark this as answer but is there any sort of documentation or resource that can confirm this or back it up? – Kris Gruttemeyer Dec 31 '14 at 17:58
  • 1
    I can find only superficial coverage of MIRROR TO on the web. You can test my theory: Back up the database to two IO devices that have identical perf characteristics using MIRROR TO. If I'm correct you should observe only a tiny slowdown because the writes are streaming and parallel. – usr Dec 31 '14 at 18:23
  • 1
    Actually, I tested this now (curiosity took over): Backing up from SSD (very fast) to two (almost) identical magnetic drives. Using data compression (but the CPU was not maxed out). Mirror: 1:37. No mirror: 1:10 to drive 1. No mirror: 1:10 to drive 2. This slowdown is well within the tolerance of my theory. – usr Dec 31 '14 at 18:31
  • Perfect, that definitely backups what you've said. Thanks so much! I will award the bounty once I am able to (22 hours) – Kris Gruttemeyer Dec 31 '14 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.