用于报告和 CRUD 操作的单独表/数据库
定期运行报告的用户会阻止用户执行 CRUD 操作并导致超时。我想为报表用户创建当前表的重复位置。
我正在考虑创建一个作业来备份我的应用程序的数据库，并将其恢复到同一服务器上的报告数据库，以便运行报告的用户将与执行 CRUD 操作的用户分开。该作业将每 10 分钟左右运行一次。初步测试显示从开始到结束大约需要 30 秒。磁盘空间不是问题。
这是一个好/坏主意吗？我应该注意哪些陷阱？有更好的方法吗？
Periodically users running reports are blocking users doing CRUD operations and causing time-outs. I'd like to create duplicate locations of the current tables for the report users.
I was thinking of creating a job that backs-up my app's database, and restores it to a reporting database on the same server so that users running reports will be separated from those doing CRUD ops. The job will run every 10 minutes or so. Initial tests show start to finish will be about 30 seconds. Disk space is not an issue.
Is this a good/bad idea? What pitfalls should I watch out for? Is there a better way to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这听起来是个好主意。我唯一担心的是你们需要每 10 分钟更新一次吗？这也可能会在更新运行时减慢速度。通常这些工作会在夜间完成（以尽量减少对他人的影响），或者如果在白天，则仅在 3 个固定时间点（例如上午 10 点、下午 1 点和下午 4 点）完成。
This sounds like a good idea. The only concern I'd have is do you need to update every 10 minutes? This could also slow things down while the update is running. Usually these are done overnight (to have the least impact to others), or if during the day, at only 3 fixed points (say 10 am, 1 pm, and 4pm).
在进行叉车升级之前，您可能会看看进行
报告查询是否可以缓解问题。它至少可以为您赢得一些时间来找出最佳方案。
Before you do a forklift upgrade, you might see if putting
on your reporting queries mitigates the problem. It may at least buy you some time to figure out what is optimal.
当然，对于大多数企业级应用程序，事务数据库始终与报告数据库分开。事务系统针对 OLTP 进行了调整，报告数据库可能会进行非规范化以满足报告场景的需要。所以这几乎是一个自然的建议。
Naturally for most of the enterprise class applications, the transaction database is always kept separated from the reporting database. The transaction system is tuned for OLTP and the reporting database might be denormalised to suit the need of the reporting scenarios. So it is almost a natural suggestion.
频繁进行备份时要小心——这可能会导致大量停机时间！
通用解决方案
创建一个单独的实例用于报告确实是一种常见的做法。
有些人甚至更进一步，将报告放在单独的物理机器或集群上，以进一步隔离该部分负载。
这两者都可以通过复制来处理（这可以避免停机问题）。或者您可以每晚进行一次备份并针对该情况进行报告。
我还想提一下，高端方法是数据仓库，您可以将这个新的报告数据库本质上转换为一个读取优化的存储库，从而更有效地进行报告。实施起来往往非常耗时，因此这不是您正在寻找的快速解决方案。
最后的想法
最后一件事：我看到一些处于这个问题风口浪尖的商店试图避免处理它。要点如下：报告往往会在每月或每年的某些时间激增，因此，如果您通常处于终止数据库服务器的边缘，那么该月的最后一周可能会让您超过边缘！
这个问题非常相似： https://stackoverflow.com/questions/190512/sql-服务器独立数据库用于报告
Be careful with doing frequent backups -- that could lead to a lot of downtime!
Common Solutions
It is indeed a common practice to create a separate instance just for reporting.
Some folks even go a step further and put reporting on a separate physical machine or cluster to further isolate that part of the load.
Both of those can be handled with replication (which avoids the downtime problem). Or you could just do a nightly backup and report against that.
I would also like to mention that the high-end approach to this is data warehousing, where you essentially transform this new reporting database into a read-optimized repository that's more efficient to report against. That tends to be very time-consuming to implement, so it is not the quick fix that you're looking for.
Final Thoughts
One last thing: I've seen some shops on the cusp of this problem try to avoid dealing with it. Here's the takeaway: reporting tends to spike at certain times of the month or year, so if you're normally on the verge of killing your database server, the last week of the month might push you over the edge!
This question is very similar: https://stackoverflow.com/questions/190512/sql-server-separate-database-for-reports