Sql-server – How to improve SQL server mirroring performance

database-mirroringendpointmirroringsql serversql-server-2005

(reposted from stackoverflow)

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.

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)

Best Answer

Does the link between the principle and mirror have sufficient bandwidth to cope with the transaction log generation at the principle?

Are your log files optimised? See these links:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx

Related Topic