Every now and then I get questions about how to implement load balancing with SQL Server databases. Because organizations want to maximize their IT investments, a standby server is almost always a red flag – it’s an idle asset. Hence, the questions about load balancing workloads on SQL Server databases to maximize the resource utilization on standby servers.
If you’ve read some of my blog posts, you know that I usually start the conversation with customers by identifying what I call the Alphabet Soup of High Availability and Disaster Recovery. But when the customer already made the decision regarding the technology solution, that’s where I explain the difference between the different Microsoft high availability technologies. Here’s one question about failover clustering and SQL Server.
My response:
In a load balanced cluster, each node has it’s own copy of the data being accessed. In SQL Server, you can’t read and write on the same database if it is running on different servers. This applies to both failover clustered instances and Availability Group replicas. This is very much different from Oracle’s Real Application Clusters (RAC.)
It’s the reason why, even if SQL Server Reporting Services is supported to run on Windows Network Load Balanced clusters, you still need to host the Reporting Services catalog in a failover clustered instance.
This video describes the different Microsoft high availability technologies that you can use with SQL Server workloads. Understanding each one of them can help you decide which technology to use to implement high availability to your SQL Server workloads – database engine, SQL Server Reporting Services, SQL Server Analysis Services. This can also help you avoid the confusion of which specific technology can be used for the SQL Server database engine.
Note that the load balancing of Availability Group read-only workloads in SQL Server 2016 is not the same as load balancing the read and write operations in the database. The new feature is only for read-only workloads, not writes.
Feeling helpless and confused when dealing with Windows Server Failover Clustering (WSFC) for your SQL Server databases?
You’re not alone. I’ve heard the same thing from thousands of SQL Server administrators throughout my entire career. These are just a few of them.
“How do I properly size the server, storage, network and all the AD settings which we do not have any control over?”
“I don’t quite understand how the Windows portion of the cluster operates and interacts with what SQL controls.”
“I’m unfamiliar with multi-site clustering.”
“Our servers are setup and configured by our parent company, so we don’t really get much experience with setting up Failover Clusters.“
If you feel the same way, then, this course is for you. It’s a simple and easy-to-understand way for you to learn and master how Windows Server Failover Clusters can keep your SQL Server databases highly available. Be confident in designing, building and managing SQL Server databases running on Windows Server Failover Clusters.
But don’t take my word for it. Here’s what my students have to say about the course.
“The techniques presented were very valuable, and used them the following week when I was paged on an issue.”
“Thanks again for giving me confidence and teaching all this stuff about failover clusters.”
“I’m so gladdddddd that I took this course!!”
“Now I got better knowledge to setup the Windows FC ENVIRONMENT (DC) for SQL Server FCI and AlwaysON.”
Please note: I reserve the right to delete comments that are offensive or off-topic.