Sql Server 分布和配置以获得最佳性能
我想用 silverlight 设计和实现一个企业软件。我为此使用 sql server 数据库。许多用户在 sql server 数据库上运行 sql queireis。 如何配置 sql server 数据库以获得最佳性能？ 如何分布 sql server 数据库以获得最佳性能？ 我如何在一些服务器之间分配 sql server 数据库以获得最佳性能？ 那么我可以在 sql server 中使用哪些技术来获得最佳性能呢？
I want design and implement an enterprise software with silverlight.I use sql server database for this.many useres run sql queireis on sql server database.
how can i configure sql server database for best performance?
how can i distribute sql server database for best performance?
how can i distribute sql server database between some servers for best performance?
and so what technologies can i use in sql server for best performance?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除了复制之外，您还可以使用镜像或日志传送来实现此目的。请注意，我仅讨论扩展读取，而不是写入。因此，报告等可以从数据库的副本运行，但写入必须写入主副本（除非您使用合并复制，这对我来说很可怕）。当然有一些注意事项。
通过数据库镜像，您可以将辅助数据库用作只读报告通过拍摄快照来获取源。可以镜像的数据库数量是有限制的，当然还需要进行维护来管理快照。这里的资源分配并不完全正确，但它有助于卸载一些负载。在 SQL Server (Denali) 的下一版本中，您将能够将辅助数据库设置为只读，这样您就可以避免维护快照。
通过日志传送，您基本上可以保留数据库的过时版本用于报告，并通过恢复日志来定期更换它。与复制或镜像相比，您在这里拥有更大的灵活性，因为您实际上可以定义延迟（例如每 6 小时或每天一次，您刷新副本）——这也可以用作“从射击中恢复”脚下”的场景。缺点是要恢复数据库的新副本，您需要将所有当前用户踢出，因为数据库需要处于单用户模式才能恢复。
这些只是帮助扩展读取的一些想法，但在内心深处我同意@gbn - 你正在解决一个你还没有解决的问题吗？设计可扩展性是一回事，但很容易越过这条线并完全过度设计。
In addition to replication you can use mirroring or log shipping for this. Note that I am talking only about scaling out reads, not write. So reports etc. can be run from the copies of the database but writes must go to the main copy (unless you are using merge replication, which is frightening to me). There are some caveats of course.
With database mirroring, you can use the secondary as a read-only reporting source by taking a snapshot. There are limits here to how many databases you can mirror and there is of course maintenance to manage the snapshots. It is not quite true distribution of resources here, but it can be helpful to offload some of the load. In the next version of SQL Server (Denali), you will be able to set secondaries as read-only, so you can avoid the maintenance of snapshots.
With log shipping, you can essentially keep a stale version of the database around for reporting, and replace it periodically by restoring logs to it. You have a lot more flexibility here compared to replication or mirroring, as you can actually define a delay (like every 6 hours or once a day, you refresh the copy) - which can also serve as a "recover from a shoot-yourself-in-the-foot" scenario. The downside is that to restore a new copy of the database you need to kick all the current users out, as the database needs to be in single user mode in order to recover.
Those are just a couple of ideas for helping scale out reads, but deep down I agree with @gbn - are you solving a problem you don't have yet? It's one thing to design for scalability, but it's very easy to step over that line and completely over-engineer.
嗯，SQL Server 本身并没有真正的负载平衡机制。然而，它支持的是主动/被动节点配置和复制。
我们在我支持的一个应用程序中使用复制策略。您可以在这里阅读更多相关信息：
http://msdn.microsoft.com/en-us/library/ms151198.aspx
在我们的配置中，我们基本上有一个事务数据库和一个报告数据库。我们将数据从事务数据库复制到报告数据库。任何报告都是针对此报告数据库完成的，因此我们不会因为某些长时间运行的报告而减慢在事务数据库上完成的工作。
请注意，复制并不是真正实时的。换句话说，将数据从事务数据库复制到报告数据库需要一些时间，尽管时间量非常小。但如果您想平衡工作负载，复制无疑是您可以考虑的一种策略。
您可能会考虑的其他事项是对大型表进行分区以获得更好的性能。
不过，正如 gbn 在他的评论中指出的那样，最好在实施这些策略之前确定您是否确实需要这些策略，因为它们增加了很多复杂性和维护工作，而这可能根本不需要。重要的是要正确分析您认为您将拥有多少数据，以及针对该数据将发生多少活动，以确定是否需要我刚才描述的策略。
此外，您还可以参考此链接以获取其他一些有用的信息以及一些可能对您有用的白皮书的链接：
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/05cf41b7-c558-44bf-86c6-12f5c2b2ffe2
Well, SQL Server doesn't really have a load balancing mchanism in and off itself. What it does support, however, is an active/passive node configuration and also replication.
We are using the replication strategy in one application I support. You can read more about it here:
http://msdn.microsoft.com/en-us/library/ms151198.aspx
In our configuration, we basically have a transactional database and a reporting database. We replicate the data from our transactional DB to the reporting DB. Any reporting is done against this reporting DB, so that we don't slow down work being done on the transactional DB due to some long running report.
Note that the replication isn't truly real time. In other words, there's some time involved in replicating the data from the transactional to the reporting DB, albeit a very small time amount. But replication is certainly one strategy you could consider if you are trying to balance workload.
Other things you might consider are partitioning large tables for better performance.
As gbn pointed out in his comment though, it's better to determine if you actually need these strategies before implementing them, because they add a lot of complexity and maintenance efforts, which may not even be needed. It's important to properly analyze how much data you think you will have, and how much activity will be occurring against that data to determine if strategies such as the ones I just described are even needed.
Also, you can refer to this link for some other helpful information and some links to whitepapers you may find helpful:
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/05cf41b7-c558-44bf-86c6-12f5c2b2ffe2