June 13, 2014 at 11:10 am
Is there such a thing called 'Load balancing' on fail over cluster sql 2008? any input helps
June 15, 2014 at 8:35 pm
In Windows clustering, any given instance (and the databases in it) are only live on one of the servers in the cluster at a time. No built-in load-balancing is possible.
Clustering is not for scaling out, it's to eliminate single points of failure.
Eddie Wuerch
MCM: SQL
June 16, 2014 at 5:13 am
If you want to do load balancing over multiple identical copies of a SQL Server database then you have 3 main options. All options require you to put a load balancing solution in place prior to getting to SQL Server.
a) Always-on availability groups. This is probably the simplest option, both in initial setup and on-going administration. The main limitation is all write activity has to be directed to a single server, while the other copies are read-only. You can failover to any node, and you should connect to the write server via a DNS vanity name that can be pointed to the current write server.
b) P2P replication. This is more complex both in setup and administration. In theory all nodes can support write activity, but your application has to be designed from the ground up to support this. The other alternative is to direct all writes to a single server, and treat the other nodes as read-only. You can failover to any node, and you should connect to the write server via a DNS vanity name that can be pointed to the current write server. You can also use SQL Merge Replication in place of P2P, but this mainly just adds to the complexity.
c) SQL Transactional Replication. The main limitation with this option is you are limited to writing to a single server. The other servers are and can only be read-only, so you have to work out how you are going to do DR. Transactional replication is a bit simpler then P2P to set up and administer, but when you overlay a DR solution on top of this then things get a lot more complex.
My employer currently uses P2P replication, writing to a single node. We are planning to upgrade to SQL2014 (from SQL2012) later this year and move to Always-on availability groups to take advantage of the additional read-only nodes and simpler administration.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 18, 2014 at 12:21 pm
Thanks for your input
June 18, 2014 at 12:22 pm
Thanks for your input
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply