0

Please is there a SQL server High Availability configuration that is similar to Oracle RAC with respect to scalability? In Oracle RAC, in order to avoid overloaded resources, application requests can be directed to another node in the cluster. All instances are looking at the same database in ASM.

With SQL Server Failover Cluster Instance, only 1 instance can be active at any one time. If there is a failover scenario, the ownership of the cluster resources failover to another node in the cluster while the services on that active server shut down. The new owner of cluster resources starts the SQL instance on its node, so the previously passive node now becomes an active node.

My question is, is there a SQL HA configuration that will route application queries to another node accessing the same database, to avoid resource overloading (which really is not a failover condition)?

Thank you,

1 Answer 1

3

This is one of the main benefits of AlwaysOn Availability Groups. Not only do they provide secondary nodes that are already online and ready for disaster recovery, they also provide high availability by being concurrently readable.

So you can direct your application to a specific replica or you can even leverage the Availability Group Listener as a single endpoint your applications can reference and it'll automatically distribute the workload across your replicas for you.

Note AlwaysOn Availability Groups result in copies of your database files themselves (though all copies are automatically synchronized), and the secondary replicas are read-only. Some background:

Oracle RAC is a shared database solution - i.e. several nodes access the same db files simultaneously while SQL Server AlwaysOn Availability Groups are a shared nothing solution & only one node can write to its copy of the data - these changes are then propagated to the other nodes - it is possible to set the application up to read from these secondary nodes. This is the flimsiest summary but I would encourage you to do some web research to understand the differences properly & how these different architectures will impact your application. - Stephen Morris Mo64

Also note RAC is Shared-Database and Shared-Cache. Responsibility for reading database blocks is distributed across nodes, so if you need to read another node's data, the query plan makes a cross-node request to read the block. So as a scalability solution it's not that great. It was designed in the era of small (1-4 CPU) commodity servers to provide some scalability. But in today's world of dozens of cores and hundreds of GB of RAM, scaling up just works better. It's now more of an HA solution than a scalability solution. - David Browne - Microsoft

If you need to be able to distribute the write workload too, then skip down to my section on Merge and Peer-to-Peer Replication.


Another option that can be used as a high availability solution is Transactional Replication. This will automatically synchronize your data to other subscriber servers in near real-time. Though there's no automated way to distribute the load across the servers, you'd have to manually maintain that in your applications or use some feature / tool outside of SQL Server.


Finally, if you need to distribute the write workload across multiple servers, you can look into Merge Replication or Peer-to-Peer Transactional Replication as they function similarly to Transactional Replication except you can write changes to any subscriber server and they'll automatically synchronize across all servers. This option still has the same drawback as Transactional Replication of no automated workload distribution.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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