Database mirroring a live database


问题 (Question)

I've been reading about database mirroring on SQL Server 2008 R2 / 2012 and I have a question: does it matter if the database being mirrored is live? Do I have to do anything special or different if my database is live?


  • I have a (one) database on premises that's our production database. A few hundreds transactions happens per second, and 99.999% are read operations. Our plan is to mirror this database (single database) to SQL Azure (IaaS) as a "backup" structure, if the on premises environment crash for any reason, we just point to the cloud.
  • I would like a zero loss, but I settle for a 5 seconds loss
  • Latency wont be a problem... slow running is better than no running...
  • I have just one node and one database

我一直在阅读有关数据库镜像对SQL Server 2008 R2 / 2012和我有一个问题:是否镜像数据库是活的吗?我得如果我的数据库是生活做了什么特别的或不同的?


  • 我有一个(一)的前提,是我们的生产数据库。几百每秒交易发生,和99.999%的读操作。我们的计划是镜像数据库到SQL Azure(单个数据库)(IaaS)作为“备份”的结构,如果物业环境崩溃的任何理由,我们只是点云。
  • 我想一零的损失,但我定为一个5秒的损失
  • 延迟会是一个问题…慢跑比没有运行…
  • 我只有一个节点和一个数据库

最佳答案 (Best Answer)

The biggest thing to consider if you run in either high-performance or high-safety mode.

Basically, in high-performance the transaction is committed locally and hopefully on the remote side. The mirrored server asynchronously applies the transactions and data loss may be a factor. The upside is not having latency in the local server and you only need two machines.

In high-safety, the transaction is witnessed by a third server and has to be committed on both sides. This creates latency which would not be desirable.




答案 (Answer) 2

There are a lot of things to consider when using mirroring with a live DB.

  • How many nodes do you have?
  • You need to know the the approximate load you expect.
  • Consider the appropriate transaction safety level. The transaction safety level determines whether the changes on the principal database are applied to the mirror database synchronously or asynchronously.
  • Another is your network setup. Are your nodes on LAN, WAN, etc.
  • How many DBs are you mirroring. Based on this answer, your choices for the above might differ.
  • What sort of Disaster recovery do you need should the worst happen?


  • 你有多少个节点吗?
  • 你需要知道你所期望的近似负荷。
  • 考虑适当的交易安全水平。交易安全水平决定是否对主数据库的更改应用到镜像数据库同步或异步。
  • 另一个是你的网络设置。你的局域网,广域网的节点,等。
  • 你有多少数据库镜像。基于这样的回答,你对上述的选择可能不同。
  • 什么样的灾难恢复你需要的最坏的情况发生?