January 11, 2013 at 1:08 pm
Hi all,
I am looking for the information regarding how to setup a load-balancing cluster in SQL Server 2012 and description how it works. We are trying to have operational Datawarehouse which will sit on 3 machines which will be part of Load-balancing cluster with shared RAID storage. it will spread out the work load and in case of failure of any machine remaining 2 will take over the load. Please let me know if I am correct and if you can suggest any information.
Thanks.
Irina
January 14, 2013 at 9:17 am
Microsoft SQL Server Clustering is not used for Load Balancing in the way that I believe you are thinking. It is used for High Availability. For example. Instance 1 is running on node 1. Node 1 has a hardware failure. Node 2 then takes over the shared storage from Node 1 and starts up the SQL Service on itself, effectively taking over.
Now, AlwaysOn availability groups do allow you to Scale Out in a limited fashion. You can host your primary copy of the database on one server, and a secondary that is available for readonly queries on a second node in the cluster.
January 14, 2013 at 9:26 am
Scaling out a SQL instance is not trivial. It's not a matter of adding a cluster and being done (and clustering's for HA, not performance). Multiple active SQL instances cannot share storage. With clustering, the storage is active and visible to one node at a time.
You need to consider a lot of things to successfully scale out a SQL database (and you'll likely be using something like peer-to-peer replication to keep the multiple DBs in sync, though there's a time delay). It works better when the system is read-heavy not write heavy and when you can have a 'preferred' location for specific data.
This is for SQL 2005, but most is still relevant: http://msdn.microsoft.com/en-US/library/cc917714.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2013 at 1:35 pm
Interested in this thread. We have just taken on some consultancy hours from an Azure MVP via Microsoft regarding moving a write heavy application to Azure. Turns our table storage works well for writes but not aggregate reads. Also we have been steered away from using AzureSQL again due to the number of writes and nature of the way the storage is shared meaning we cannot guarrentee performance to name one of the issues. It was recommended we use several of largest Azure VMs we can get (soon to be larger) and use SQL Server 2012 active active active clustering. Now i did not query this in too much detail due to time constraints but I was aware it was not possible to share access to an MDF/LDF (storage) via multiple SQL server instances on any previous version. Reading your post describing that scaling out SQL server 2012 works best for reads i am wondering where this leaves us. To date i have an active passive cluster on the best hardware we can afford i.e scaled up to date. I need to be more flexible than this , the largest Azure VM is not nearly good enough , using SQL active active active (multiple instances and dbs) means a lot of redesign.
So the only way to go is to scale out to multiple dbs and host these dbs on multple VMs each using isolated storage ?
And even if i do this it works best for reads according to your post ?
What SQL server 2012 architecture works best to scale out an application where quick writes are important ?
I understand there is not a lot of detail on application to make a full analysis.
Thank you.
July 21, 2016 at 8:06 pm
É possivel ter um cluster SQL ativo/ativo?
É posível ativar o balanceamento de carga nos dois nós?
Consigo alta disponibilidade balanceamento?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply