Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

What I'm trying to do is set up a failover system between two MS SQL Servers. It needs to be active/passive, and allow weighting (so Site 1 is preferable, before Site 2).

I've done this between our sites before, but on Linux, using Keepalived and HAProxy to use a VIP and proxy MySQL connections.

I'm looking for something similar that can run on Windows servers, and provide roughly the same functionality.

Bonuses would be to prevent flapping.

Application wise, we are using mostly ODBC connections stored on each server, so I could always do it from the application level. However, our subsystems can rely on each other, so we'd need everything to switch at once.

Environment

  • Windows Server 2003
  • MSSQL 2005
  • Two machines
  • On different sites, with a 10-20ms routed link between the subnets.
share|improve this question
1  
I have a failover system currently set up between two MSSQL servers that mirrors every database and if one were to fail the other would kick into place and become the new live site. Is this what you're looking for? – GMitch Jul 26 '11 at 21:47
@Gmith Yes, sort of. We already have replication working between the two, so that's fine. I just need something to failover. – giggsey Jul 27 '11 at 9:08

1 Answer

up vote 0 down vote

SQL doesn't have a mechanism for this natively. The only way to do this that I know of would be to set up merge replication between the 2 SQL servers and round robin DNS requests to each.

share|improve this answer
Thanks for the answer, however, we don't want to go down the RR DNS route, as we want one as primary, and then the other as a backup until the primary fails. – giggsey Jul 27 '11 at 9:10
That's a different deal then. If you don't want the clients to be able to use both databases in a hot/hot scenario then you have a lot of options. Database mirroring with a witness will give .NET and java applications automatic failover. You could alos use mirroring with no withness for a manual (or automated through the application) failover. A traditional read only "standby" database on the secondary could be set up using log shipping, then read could re directed to that server instead of the primary. – Jason Cumberland Jul 27 '11 at 18:44

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.