1

I'm looking at Peer-to-Peer Transactional Replication and it looks like a perfect fit for my requirements, but there's one unclear part - load balancing (see picture below). Load Balancing

How it is implemented in SQL Server? Is it programmable? Or it's something that I should implement myself? If so, what's the best way of doing that?

My business rules are:

  • Server A and Server B are in different geographical locations.
  • Data is replicated between both servers constantly.
  • If Server A becomes unavailable, its users requests are redirected to Server B. Some data loss is acceptable in this case.
  • If user performed some writes on Server A - he must keep using Server A (with only one exception, when Server A becomes unavailable). The user can't perform some writes on Server A and some writes on Server B. But he may perform reads from both servers.

Both servers uses MS SQL Server 2012 Enterprise.

AlwaysOn Availability Groups and SQL Server Cluster for some reasons is not an option for me.

  • SQL Server doesn't have a "turn load balancing on" switch - you'll need to do this externally (the last time I did this we used F5 gear - but I'm sure you can do this with other products as well). – Aaron Bertrand Feb 27 '15 at 4:08
  • In production we'll use F5, but now we need some simpler solution. Something like Application Request Routing (ARR) in IIS, but for databases. Any suggestions? – Andrew Feb 27 '15 at 17:47
  • I don't know of any, sorry. – Aaron Bertrand Feb 27 '15 at 18:44
0

I ended up using F5 BIG-IP LTM Virtual Edition, because I didn't find any other free solutions, that fits my business requirements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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