SQL 镜像与复制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/434982/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mirroring vs Replication
提问by Bashar Kokash
How to decide whether to choose a Replication or Mirroring in SQL Server 2005 to provide data availabilty and performance at the same time.
如何决定在 SQL Server 2005 中选择复制还是镜像以同时提供数据可用性和性能。
To be more specific about my SQL server architecture, I have an active/active cluster of 2 nodes which are used for load balancing, and I have another server for replication, which will be used only for reporting, I want to make sure which technology best provides both availabilty and performance, Transactional replication or Database Mirroring?
更具体地说明我的 SQL 服务器架构，我有一个由 2 个节点组成的主动/主动集群，用于负载平衡，还有另一个用于复制的服务器，仅用于报告，我想确定哪种技术最好同时提供可用性和性能、事务复制或数据库镜像？
采纳答案by Bashar Kokash
It turns out that Database mirroing prevents data to be accessed directly, mirrored data are only accessable through a database snapshot, so reports from snapshot data will not be up to date so, I will use Database Transactional Replication to provide high availabilit and load balancing.
事实证明，数据库镜像会阻止直接访问数据，镜像数据只能通过数据库快照访问，因此快照数据的报告不会是最新的，因此，我将使用数据库事务复制来提供高可用性和负载平衡。
回答by Mitch Wheat
It depends on the level (hot, warm, cold) of standby availability you require.
这取决于您需要的备用可用性级别（热、暖、冷）。
Various mechanisms in SQL Server provide database-level redundancy, such as backup/restore, log shipping, and database mirroring (in SQL Server 2005 and later). Database mirroring is the only mechanism that provides a real-time, exact copy of the protected database with the guarantee of zero data loss (when the mirror is synchronized).
SQL Server 中的各种机制提供了数据库级别的冗余，例如备份/恢复、日志传送和数据库镜像（在 SQL Server 2005 和更高版本中）。数据库镜像是唯一一种提供受保护数据库的实时、精确副本并保证零数据丢失（当镜像同步时）的机制。
Database mirroring operates in either synchronous or asynchronous mode. Under asynchronous operation, transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.
数据库镜像以同步或异步模式运行。在异步操作下，事务提交无需等待镜像服务器将日志写入磁盘，从而最大限度地提高性能。
This MS Whitepaper, Providing High Availability using Database Mirroring, covers a range of scenarios.
此 MS 白皮书使用数据库镜像提供高可用性，涵盖了一系列场景。
You should probably read this TechNet article, Database Mirroring Best Practices and Performance Considerations.
您可能应该阅读这篇 TechNet 文章，数据库镜像最佳实践和性能注意事项。
回答by Karsten
I don't know SQL Server 2005, but for general SQL Usage i would always prefer Replication. You have to separate reads/writes in your application (for MySQL there is MySQL Proxy which can do this in a proxy way for you), but gain a scalable system. (reads to slave(s), writes to master)
我不知道 SQL Server 2005，但对于一般的 SQL 用法，我总是更喜欢复制。您必须在您的应用程序中分离读/写（对于 MySQL，有 MySQL 代理可以以代理方式为您执行此操作），但获得一个可扩展的系统。（读到从站，写到主站）
Mirroring means master-master replication which leads to concurrency/transaction issues. Even in master-master scenarios you should NEVERsend write queries to different servers. Depending on your project's size, the next steps would be adding more slaves, and then add another master+its slaves for redundancy.
镜像意味着主-主复制，这会导致并发/事务问题。即使在主-主场景，你应该永远不会发送写查询到不同的服务器。根据您的项目大小，接下来的步骤将是添加更多的从站，然后添加另一个主站+它的从站以实现冗余。
master --- master
| |
slave slave
| |
slave slave
| |
slave slave
Even then you would only send write queries to one master, but in case of a failing master you could automatically promote the second master to your new write-query-target.
即便如此，您也只会向一个 master 发送写查询，但是如果 master 失败，您可以自动将第二个 master 提升到新的 write-query-target。
