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?

EDIT-1:

  • 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和我有一个问题:是否镜像数据库是活的吗?我得如果我的数据库是生活做了什么特别的或不同的?

edit-1:

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

最佳答案 (Best Answer)

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

http://msdn.microsoft.com/en-us/library/ms189852.aspx

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.

考虑到如果你运行在高性能和高安全性模式最大的事情。

http://msdn.microsoft.com/en-us/library/ms189852.aspx

基本上,在高性能事务提交本地和远端的希望。镜像服务器异步应用的事务和数据的损失可能是一个因素。上面是没有在本地服务器的延迟,你只需要两个机器。

在高安全性的交易,是由三分之一个服务器和见证了必须在双方承诺。这造成了延迟并不可取。

答案 (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?

有很多事情要考虑当使用镜像与活的分贝。

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

本文翻译自StackoverFlow,英语好的童鞋可直接参考原文:http://stackoverflow.com/questions/16925971