如何创建SQL Server 2005数据库的实时副本？(How do I create a realtime copy of my SQL Server 2005 database?)

如何创建SQL Server 2005数据库的实时副本？(How do I create a realtime copy of my SQL Server 2005 database?)

我有一个在多个大洲使用的网络应用程序。 运行大型报表时，基础事务DB将锁定。 我要：

将SQL Server 2005数据库实时转换为报告数据库 将所有报告直接发送到报告数据库

理论上，这将阻止插入和更新的事务锁定。 只要事务继续，报告数据库就会暂时锁定，这是可以的。 报告数据库可以在较轻负载时接收新事务。 维护事务数据库的近实时副本的最佳方法是什么？ 我应该使用复制，镜像还是两者的组合？ 我应遵循哪些一般准则？

谢谢！

I have a web app that is used on multiple continents. The underlying transaction DB is locking up when large reports are run. I want to:

make a realtime of the SQL Server 2005 database to a reports DB direct all reporting to the reports DB

In theory this will prevent transaction locking for inserts and updates. It is OK if the reports DB gets locked temporarily as long as the transactions continue. The reports DB can receive the new transactions when it is under a lighter load. What is the best way to maintain a near realtime copy of the transaction DB? Should I use replication, mirroring or a combination of the two? What general guidelines I should follow?

Thanks!

最满意答案

使用Sql Server复制来实现此类体系结构。 例如，将数据从主数据库服务器复制到报告服务器。 它不是很实时（很少有系统）但可以非常接近。

有几种版本的Sql Server复制，您可能需要服务器到服务器。 另请参阅有关数据仓库和报告的文章。 它描述了您遇到的确切情况以及您的目标：

按照设计，事务复制解决了此方案的主要要求：

交易一致性 低延迟 高吞吐量 最小的开销

Use Sql Server replication for implementing this kind of architecture. E.g. replicate data from your main db server to the reporting server. It's not quite real time (few systems are) but can be very close.

There are several flavors with Sql Server replication and you'll probably want server-to-server. Also see this article about data warehousing and reporting. It describes the exact scenario that you're up against and your goals:

By design, transactional replication addresses the principal requirements for this scenario:

Transactional consistency Low latency High Throughput Minimal overhead