August 30, 2018 at 1:48 am
Hi,
Good day everyone just want to ask if sql server 2012 has a capability of load balancing just like what oracle RAC does? wherein there is a "common" storage of db files and 2 ODA servers that accept connections then do the load balancing on what server has the minimum db usage or is it just 1 primary and 1 (or more) "standby" servers for sql server?
"-=Still Learning=-"
Lester Policarpio
August 30, 2018 at 2:27 am
Yo don't have to run a Cluster in Active / Passive configuration, you can run MSSQL in Active / Active Configurations aswell as long as your application supports it. Asides that you don't have to run a SQL Server Cluster either but you could do an AlwaysOn Avaliablity Group Setup where you can "load Balance" read requests and other funky things.
As long as your application supports it.
August 30, 2018 at 7:02 pm
Thanks DinoRS, sorry as I don't have experience on AlwaysOn Availability but does it work for read requests only? how about DML requests? Also from the sound of it I think AOA Group setup still doesn't have shared data files am I correct?
"-=Still Learning=-"
Lester Policarpio
September 2, 2018 at 8:22 pm
Sorry, there is currently no feature in MS SQL Server that exactly correlates with Oracle RAC. Oracle RAC uses a shared memory system that allows them to do all CRUD operations on both nodes against the shared storage (one copy of the data). This is possible because the two nodes share their memory (think buffer pages) and only let one node update a record at any one moment. Probably think of it as row locking across servers.
The closest thing MS SQL has is AoHA, which allows updates on one node, and reads on any of the other (read only) nodes. This is not true load balancing, and at least in the early days of AoHA, Microsoft were telling us not to see this as load balancing, but the ability to off load reads onto a secondary server. In the MS SQL scenario, there are actually 2 (at least) copies of the database, any inserts/updates/deletes occur on the Primary copy and are replicated to a Readable Secondary. Reads can be done against either node.
The next best "load balancing" is where you have multiple different databases (supporting different apps), and you can have some use ServerA as their Primary, and others use ServerB as their Primary. This balances the server load, but across multiple Apps and databases, not for a single App/DB set. This works for Shared Storage and AoHA type clusters that don't share storage.
My guess is that Microsoft are not far from making a true RAC equivalent available. I'm just not sure if it will get a lot of take up, because generally the biggest bottleneck in most environments is on IO, and the AoHA option separates the IO between two storage units, where the RAC option still has the storage as a potential bottleneck. I acknowledge that IO as a bottleneck is getting less of an issue as storage improves (Solid State, FusionIO, etc. that give microsecond disk latency), and large RAM capability.
Cheers
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 10, 2018 at 9:07 pm
Hi Leo.Miller,
thank you for your comment, I have tried watching sample installation of AoHA on youtube and it states that 1 db is for read and 1 is for DML command. but it also state a listener to segregate the query from 1 server to another so does that mean the listener ip can detect whether the sql command is a select statement or a DML statement and automatically forwards the task on the specific server?
"-=Still Learning=-"
Lester Policarpio
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply