The Chamber of SQL Part 3

“The Always Highly Available Failover Cluster On Instance”. The project Stephanie was working on was nearly finished. Her new architecture was built, scripts were being created and databases were migrated. She was doing a wrap up for herself. It had been a great experience, working with Richard and Marc. Two people she would not soon forget. She had learned a ton of new things from them. On her way to the machine pouring black gold, she heard on of the developers talking about Always On. She stopped and eavesdropped. Technically it was not eavesdropping as the developer was sitting in the middle of the room talking loudly.

She listened for a while and interrupted him. “Dave, sorry to interrupt but I think you’re mixing up two things here.” Dave stopped and said: “Oh really, yeah well, I’ve never quite understood the differences actually. I’m not a DBA kinda person.”

Stephanie chuckled: “haha, that is happening al the time. I’ll explain you if you want.”
“Ow yeah sure thing”, said Dave. He always loved it when Stephanie was explaining something.

“Ok here we go”, Stephanie said. She picked up a white board marker and started her story. After one sentence she stopped. Be right back she shouted and vanished.
2 minutes later she arrived with a big cup of coffee, no coffee no SQL she smiled.

What does Always On mean?
“Basically”, Stephanie started, “we have 2 different options under the umbrella what we call Always On. We have on one side Always On Availability Groups and on the other side we have Always On Failover Cluster Instances. Hence the confusion. Microsoft has put both solutions under the same name, but they are totally different when setting these up.”

Everybody with me, lets get into this.

Always On Failover Cluster Instances
“Always On Failover Cluster Instances is actually the fancy name of the traditional failover clusters as we know them,” Stephanie said. “The most basic outline for guaranteeing high availability is a 2 node cluster. I’m not going into details about all the technical specifications but in short. You have 2 SQL server instances running both sharing the same storage. When one node goes down, the other node will be automatically started and the shared storage will be ‘moved’ to that node. Almost instant, given the time for the SQL services to start up, the other node will take over the queries and load from the first node. This is what an Always On Failover Cluster Instance is doing.”

Dave who was all ears nodded enthousiastic: “Yeah, that make sense. I’ve built a lot of those back in the early 2005 days. oh boy. ”

Always On High Avaibility groups
Stephanie smiled and continued. “Now since SQL server 2012, Microsoft introduced something they called Always On High Avaibility groups.”

“Basically”, Stephanie said and sipped her coffee, “it boils down to this. In its simplest form for high availability, you have 2 separate SQL servers which are being installed. We put a Windows Failover Cluster on top of them to make sure they can communicate and we enable the Always On High Availability feature, but for the story, this is not important.

In this scenario, we have a primary replica and a secondary replica. In the end they actually act like two separate SQL servers. They have their own storage, their own jobs,… but what makes it a piece of art is the fact that we can group databases in an availability group and that we can query the secondary replica for read-only queries. We have a so called readable secondary.

From the moment on that databases are participating in a group and something happens, the group will failover to the secondary replica instantaneous. From now on, the secondary replica became the primary replica and the applications are happily working further.”

“Euuurhhm”, Dave said, “how does the application know that the group just moved to the secondary replica as you call it…”

Creating a Listener
“Aha that, my dear Watson is a very good question”, Stephanie smiled. “We create a so called Listener. A listener is nothing more than a virtual name that will always guide you to the primary replica. He knows who is acting as primary and who is acting as secondary. We can even force read-only connection to always be rerouted to the readable secondaries. And with the introduction of SQL 2017 we can have up to 3 synchronous replica’s and we can even load balance among our replica!”

“Cool he! I could go on and talk for ages about Always On Availability Groups, but I really need to continue my script. I’m trying to automate a virtual machine deployment in Azure hence the amount of coffee I’m drinking today”, Stephanie said smiling.

“Thanks for the small update Stephanie”, Dave said and raised his cup of coffee!

“Always a pleasure Dave!” Stephanie smiled.

See you all on a next adventure about the three headed dog called kerberos!

The Chamber of SQL – part I
The Chamber of SQL – part 2

Frederik Bogaerts is SQL server DBA bij Kohera.