Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are using SQL Server 2008 R2 Standard edition for mirroring.

But we found out that network latency is too high. It takes nearly four times time to complete a transaction while mirroring is active. Is it possible to connect these two servers with a direct crossover cable and force sql server to use this link for mirroring? Will that help?

We have already setup a crossover cable between these two servers. But how do we configure sql server to use this link for mirroring?

Any help will be appreciated.

share|improve this question

migrated from stackoverflow.com May 17 '13 at 11:06

This question came from our site for professional and enthusiast programmers.

1  
What is your existing network infrastructure like? Maybe your network team needs to take a look at the state of things. If everything is okay, it's likely that running direct cables won't gain you much, if anything. –  Jon Seigel May 17 '13 at 13:30

3 Answers 3

For a weak network I wouldn't personally use Mirroring. But if you really want mirroring for the specific case when there might be a slow connection an option would be to use the Asynchronous mode (or High Performance mode). This would imply some data loss would be possible, but you will be able to finish transactions faster.

Details on MSDN: Async mirroring. Some details about it:

This mode enhances performance at the expense of high availability. High-performance mode uses just the principal server and the mirror server. Problems on the mirror server never impact the principal server. On the loss of the principal server, the mirror database is marked DISCONNECTED but is available as a warm standby.

Now this comes with a caveat, the transaction on the Principal is just put in queue, it's not committed immediately on the Mirror, so in case of problems with the Principal, you may have data loss. But at least you won't wait for the transaction to finish on both servers. Check if it's an option.

Regarding specifying different NICs for the connections, please see these questions:

In theory it should be simple enough as to configure both NICs with different IP and use the IP to connect to the SQL Server (not the server name). Also I'd think @Nathan's answer might lead you to a fix. No idea if it can be done with the existing mirroring, my guess is that you need to redo it.

The first sentence is more important if it's an unreliable network. In that case I'd totally advice to use a different technology for HA (log shipping or maybe replication).

PS: Following @Nathan's mention about reading the real requirements (ty, @NathanJolly), then your option remains to configure the NICs so you can connect via the direct link cable. Follow the other answers and questions and see if it's good enough for your environment. If it's still too slow, then I'd suggest you move over to log shipping.

share|improve this answer
2  
Asynchronous mirroring may be a problem for @Virappon specifically, as it's only supported in Enterprise Edition (which is vexing). Log shipping is a brilliant idea if commit times for mirroring are too high. –  Nathan Jolly May 17 '13 at 13:20
    
@NathanJolly Oh, my bad, I missed the Standard part. I'll edit the answer, thanks! –  Marian May 17 '13 at 15:39

You should be able to bind mirroring to a specific IP address (that of your crossover cable) by using the LISTENER_IP option of CREATE ENDPOINT / ALTER ENDPOINT.

Presumably this will involve reconfiguring your mirrored databases by using ALTER DATABASE SET PARTNER... to point to your other endpoint via IP instead of hostname.

I'm not sure if this can be safely done without breaking mirroring, so hopefully someone else can shed some more light on this, or you can test it in a dev environment (or on VMs) first.

share|improve this answer

Read Step by Step Guide to Setup a Dedicated SQL Database Mirroring(DBM on dedicated Nic card)

It shows you how to configure Mirroring on Dedicated NIC card. Network configuration plays an important role in the performance and safety provided by database mirroring.

Also, worth reading Database Mirroring Best Practices and Performance Considerations whitepaper.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.