1

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.
Improve this question
2
  • 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, 2011 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, 2011 at 9:08

1 Answer 1

Reset to default
0

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.

Improve this answer
2
  • 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, 2011 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, 2011 at 18:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .