最好的方式来提高性能（包括与故障转移不知何故） [英] Best way to improve performance (and include somehow failover)

问题描述

我们已经在那里运行IIS和SQL在同一台机器上的应用程序。这是一个windows2003standard服务器与虚拟机上运行的RAM 4gigs。

We have an application running where IIS and SQL are on the same machine. It's a windows2003standard server, with 4gigs of RAM running on a VM.

现在的用户数量也在不断上升。有一些大的统计也可以由用户来运行，但有其他用户的表现的非常多的影响。因此，我们需要以某种方式提高性能。

Now the numbers of users are rising constantly. There are some huge statistics also, which can be run by the users but has very much impact of the performance for other users. So we need to improve the performance somehow.

我想用的Windows2008 64位分离2个不同的机器IIS和SQL，并且至少6gigs RAM对每一台​​机器，但它也应该有一个故障转移解决方案。

I thought of separating IIS and SQL on 2 different machines with windows2008 64bit and at least 6gigs RAM for each machine, but it also should have a failover solution.

您可以推荐如何解决性能和故障转移问题一些场景？

Can you recommend some scenarios for how to solve the performance and the failover issue?

感谢

PS：

只是为了信息：我们现在使用的是InProc状态管理在IIS中，但我认为这将是更好地改变sqlstatemanagement

Just for info: we are now using inproc state management in IIS, but i think it will be better to change to sqlstatemanagement.

修改

我已经扩大了问题故障点。作为我们的客户不希望花太多钱在服务器和SQL许可证。难道是确定只是复制到第二个SQL服务器，并以此作为故障转​​移？你知道一些更好的便宜的解决方案？

I've broadened the question to the point of failover. As our client doesn't want to spend too much money on server and SQL licenses. Would it be "ok" to just have a replication to a second SQL server and use this as a failover? Do you know some better "cheap" solutions?

该应用程序是仅用于内部，而现在越来越多的部门都参与了这个项目。

The application is just for internal use, but now more and more departments are involved in this project.

推荐答案

现在你已经在虚拟机上我认为32位操作系统。由于标准版不允许 AWE 两服务器（IIS和SQL）的SQL Server将加载的最大它能约1.8 GB，并留下足够的内存来IIS和操作系统。但是，一旦你迁移到64位操作系统的东西会作为SQL Server更改将采取所有的RAM为它的缓冲池（5GB〜6GB如果可用），然后开始给它回到OS时的通知。这种行为可以通过配置SQL Server 内存选项进行调整。由IIS和SQL拆分到不同的虚拟机您将所有的SQL VM的内存为它的缓冲池，这是不错的。理想情况下，你应该有足够的RAM，这样可以SQL整个数据库（S）加载到内存中（包括tempdb中），只有触摸日志写入磁盘，当它具有执行检查点数据库（S）。换句话说，更多的内存意味着更快的SQL。这是迄今为止最重要的硬件资源SQL要求的性能和将给予降压最大的爆炸。

Right now you have 32 bit OS on the VM I assume. Since Standard Edition does not allow AWE the two servers (IIS and SQL) the SQL Server will load up the maximum it can about 1.8 GB and leave plenty of RAM to IIS and OS. But once you move to 64 bit OS things will change as the SQL Server will take all the RAM for its buffer pool (~5GB if 6GB available) and then start giving it back to OS when notified. This behavior can be tweaked by configuring SQL Server memory options. By splitting the IIS and SQL onto separate VMs you leave all the memory on the SQL VM for its buffer pools, and that is good. Ideally you should have enough RAM so that SQL can load the entire database(s) into memory (including tempdb) and only touch the disk for log writes and when it has to checkpoint the database(s). In other words, more RAM means faster SQL. It is by far the most important hardware resource SQL requires for performance and will give the biggest bang for the buck.

现在回到对故障的广泛的问题。在SQL Server的解决方案 高可用性 分为两类：的自动的和的手动的。对于的自动的故障转移你真的只有几个解决方案：

Now back to the broad question on 'failover'. In SQL Server the solutions for high availability split into two categories: automatic and manual. For automatic failover you really have only a few solutions:

• 集群 。传统上，这是因为它支持集群硬件成本高的实施相当昂贵，但与虚拟机，这是一个不同的故事。标准版SQL支持双节点集群。集群是有点难以部署，但很容易操作，不需要更改应用程序来支持。具有集群故障转移的单位是一个完整的SQL Server实例（即每个数据库，包括主/ tempdb中/ model和msdb，所有登录，所有SQL代理作业等等等等）。集群是的不的性能解决方案，因为备用服务器只是坐在空闲的情况下，主要的一个崩溃。您可以通过部署所谓的主动 - 主动群集利用备用虚拟机。这是什么意思是，你部署的两个的集群，一个在VM1活跃和待机上VM2，其他活动上VM2和待机上VM1。在故障转移的一个VM的情况下，将不得不采取的两个的实例的负荷，这就是为什么主动 - 主动部署有时令人难以接受的。既然你计划在虚拟机部署不是（贵）金属，我建议你反对它，因为没有巨大的成本为'ammortize。


• 镜像 。这将保持一个热备份您的数据库的（而不是实例）的镜像。镜像是preferred聚类，因为成本更低的部署（无特殊硬件），降低故障时间（秒而不是聚类分钟）和geodistribution能力（镜像支持在单独continets节点的分布，集群只支持一个短几百米之遥的距离节点之间）。但由于故障转移的单位是的数据库的，反映不提供易于使用聚类的。很多由应用程序所需要的资源做的不的驻留在数据库：登录，代理作业，维护计划，数据库，邮件等等等等。因为只有数据库故障转移，故障转移，必须精心策划，以使应用程序继续故障转移（如登录不得不转移）后工作。该应用程序还必须知道镜像部署，以便它连接正确。有了标准版，您将只能在高安全性模式。


• 硬件镜像。我不打算进入这一个细节，它需要能够磁盘级别镜像专门的SAN硬件。

• Clustering. Traditionally this is rather expensive to implement because of the high cost of hardware that supports clustering, but with VMs this is a different story. Standard Edition SQL supports two node clusters. Clustering is a bit hard to deploy, but is quite easy operate and requires no application changes to support. With clustering the unit of failover is an entire SQL Server instance (ie. every database, including master/tempdb/model and msdb, all logins, all SQL Agent jobs etc etc). A cluster is not a performance solution, as the stand-by server is just sitting idle in case the main one crashes. You can leverage the stand-by VM by deploying the so called 'active-active' cluster. What that mean is that you deploy two clusters, one active on VM1 and stand-by on VM2, the other active on VM2 and stand-by on VM1. In case of failover one of the VMs will have to take the load of both instances, and this is why active-active deployments are sometimes frowned upon. Given that you plan to deploy on VMs not on (expensive) metal, I'd recommend against it since there is no huge cost to 'ammortize'.
• Mirroring. This will keep a hot stand-by mirror of your database (not instance). Mirroring is preferred to clustering because of lower cost to deploy (no special hardware), lower failover time (seconds as opposed to minutes in clustering) and geodistribution capabilities (mirroring supports distribution of nodes on separate continets, clustering only supports a short distance of few hundred meters between nodes). But because the unit of failover is a database, mirroring does not provide the ease of use of clustering. A lot of the resources needed by an application do not reside in the database: logins, Agent jobs, maintenance plans, database mail messages and so on and so forth. Because only the database fails over, the failover has to be carefully planned so that the application continues to work after the failover (eg. logins have to transferred). The application also has to be aware of the mirroring deployment so that it connects properly. With Standard Edition you will only be able to deploy mirroring in high-safety mode.
• Hardware mirroring. I'm not going to enter into details on this one, it requires specialized SAN hardware capable of disk level mirroring.

如果你正在考虑手动故障转移解决方案，然后有夫妇更多的选择：

If you're considering manual failover solutions then there are couple more alternatives:

• 日志传送 。日志传送基本上是出来带外镜像。相反，在实时在专用TCP连接传输日志记录，日志是通过文件复制操作转移。有极少数的理由选择登录送货镜像：备用数据库可以查询用于报告，待机可位于零星连接性的位置，待机可以通过一个非常低功率机被安置

• Log Shipping. Log shipping is basically out-of-band mirroring. Instead of transferring log records in real-time over a dedicated TCP connection, the log is transferred via file copy operations. There are very few reasons to choose log shipping over mirroring: the stand-by database can be queried for reporting, the stand-by can be located on a location with sporadic connectivity, the stand-by can be housed by a really low powered machine.

复制。这实在不是一个高可用性解决方案。复制是一种解决方案，以提供数据的副本和/或站点之间交换的数据更新。虽然它可以用于部署某种形式的高可用性化妆移解决方案中，有许多问题与基本没有优势。相比这两个日志传送和镜像，它有许多的其他的缺点，因为故障转移的单位竟然没有一个数据库，是数据库中的数据只切片（一些表）。如用户和安全权限元数据不会进行故障切换，模式变化都在做复制感知模式有的变化甚至不能被复制。合同双方镜像和日志传送提供了备用的生产数据库副本相同，可自动覆盖的任何的变化做数据库。

Replication. This is really not a high availability solution. Replication is a solution to provide data copies and/or to exchange data updates between sites. While it can be used to deploy some sort of high-availability make-shift 'solution', there are many problems with that and basically no advantage. Compared to both log shipping and mirroring, it has a number of additional disadvantages because the unit of failover is not even a database, is only slices of data within a database (some of the tables). Metadata like user and security permissions are not failed over, schema changes have to be done in a replication aware mode and some changes cannot even be replicated. By contract both mirroring and log shipping provide stand-by copy identical with the production database that automatically cover any change done to the database.

您提到您关心许可证成本：你其实并不需要许可对任何的 被动的任何与这些技术的服务器除了的复制。待机服务器需要一个许可证，只有当他们变得活跃，运行超过30天的数据库。

You mention that you are concerned about license costs: you actually don't need a license for any of the passive servers with any of these technologies except replication. The stand-by servers require a license only if they become active and runs the database for more than 30 days.

考虑您打算在VM上我的选择是群集部署。如果你想在金属上部署，我建议mirroing而不是因为集群硬件成本的。

Considering you plan to deploy on VMs my choice would be clustering. If you would deploy on metal, I'd recommend mirroing instead because of the cost of clustering hardware.

这篇关于最好的方式来提高性能（包括与故障转移不知何故）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！