如何改进SQL Server镜像

(if the question is more appropriate for RackOverflow please let me know)

(如果问题更适合RackOverflow,请告诉我)

I've setup SQL server mirroring, using 2 SQL server 2005 standard editions. When the application is being stressed, response times increase 10-fold. I've pinpointed this to the mirror, because pausing the mirror shows acceptable response times.

我使用2个SQL Server 2005标准版本设置了SQL Server镜像。当应用程序受到压力时,响应时间增加10倍。我已经将它指向镜子,因为暂停镜子显示可接受的响应时间。

What options are available for achieving better performance? Note that I'm using Standard Edition, so the excellent High Performance Mode is unavailable.

有哪些选项可以实现更好的性能?请注意,我使用的是标准版,因此无法使用出色的高性能模式。

The server are in the same rack, connected to a gigabit switch.

服务器位于同一机架中,连接到千兆交换机。

Here's the code used to create the endpoints:

这是用于创建端点的代码:

CREATE ENDPOINT [Mirroring] 
    AUTHORIZATION [sa]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)

2 个解决方案

#1

First you need to look at your redo queue on the mirror, how big is. This is the most likely culprit and indicates that your mirror machine is underpowered. More exactly, it cannot apply and write the log as it receives it from the principal fats enough to keep up, causing flow control to propagate back to the principal and delay transaction commits. In fact you should look at all the counters in the Mirroring Object, on both machines.

首先,您需要查看镜像上的重做队列,有多大。这是最可能的罪魁祸首,表明您的镜像机功能不足。更确切地说,它不能应用和写入日志,因为它从主要的脂肪中接收它足以跟上,导致流量控制传播回主体并延迟事务提交。实际上,您应该查看两台计算机上镜像对象中的所有计数器。

Unless you find measurements to back up suspicion on the endpoint settings, leave them as they are. The mirroring communication bandwidth is very very seldom the culprit.

除非您发现测量结果以备份端点设置上的怀疑,否则请保持原样。镜像通信带宽很少是罪魁祸首。

#2

Given that the servers are in the same rack do you really need Encryption turned on? RC4 is a relatively weak algorithm, so the benefit is low. And presumably the 1 Gigabit network is private between the servers?

鉴于服务器位于同一机架中,您真的需要打开加密吗? RC4是一种相对较弱的算法,因此效益很低。大概1千兆网络是服务器之间的私有网络?

ENCRYPTION = DISABLED

In response to @Remus Rusanu's comment : Saying that "RC4 is a strong algorithm" is totally wrong. This is what the MSDN page has to say:

回应@Remus Rusanu的评论:说“RC4是一个强大的算法”是完全错误的。这就是MSDN页面所说的内容:

Though considerably faster than AES, RC4 is a relatively weak algorithm, while AES is a relatively strong algorithm. Therefore, we recommend that you use the AES algorithm.

虽然比AES快得多,但RC4是一种相对较弱的算法,而AES则是一种相对较强的算法。因此,我们建议您使用AES算法。

#1