This theory can be tested: Back up the database to two IO devices that have identical perf characteristics using MIRROR TO. In case of a saturated network link it might be faster to locally duplicate the backup file on the target server. It can be an upside because errors are more likely to be detected.Īs 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. This can be a downside (you probably exceed your RPO objective). One additional point: If the mirror cannot be written the backup will fail.
The only reason not to use it would be if your backup process cannot easily be modified to use it.
SQL Server is certainly tested better than most in-house script development. It also automates stuff that you can get wrong when you are doing it manually. It results in less resource usage and less potential for (undetected) backup corruption. (Yes, TCP does not 100% protect from that.)Īdvice given the fact that Enterprise Edition will become available to you: Use MIRROR TO. Memory, disk and network all can have undetected bit flips. Data corruption is rare but a practical concern. It also has less room for data corruption. This means that MIRROR TO is less resource intensive than copying the data files after the backup has succeeded. That's why I think they're doing that way. From an implementors standpoint it is less work to fork the stream and it has a better result. 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. What they are certainly not doing is write the data once and then copy it over. This means that every block they read is written two times. Probably, SQL Server streams the backup data to two backup devices if you use MIRROR TO. I'm guessing here based on how I'd implement MIRROR TO:
0 kommentar(er)
