November 21, 2019 at 2:29 pm
Suppose an environment has a two node, active-passive cluster and users connect using the virtual name of the cluster. The main reasoning behind the setup is to safeguard against things like a hardware failure on the primary node so it can automatically fail over to the secondary and to also reduce downtime when patching. DR is a separate component and will be left out for this discussion. To change this to AlwaysOn Availability Groups it is my understanding that shared storage will go away and each node will have its own storage. Having the secondary available as a read-only replica would be a benefit but not necessary. Is it possible to use AGs in a way to function exactly as the FCI did (keeping automatic fail over and the ability to manually fail over to patch while allowing users to use the same virtual node in their connection)? It's also my understanding that only user databases can be part of an AG so I'm not sure how things such as logins, jobs, etc would be transferred since the system databases aren't replicated. It seems as though the objective is to still protect at the instance level but leverage using AGs.
November 21, 2019 at 4:39 pm
Is it possible to use AGs in a way to function exactly as the FCI did (keeping automatic fail over and the ability to manually fail over to patch while allowing users to use the same virtual node in their connection)?
Answer -
Yes, AGs use Windows Clustering as the back-end technology. You will create a listener (a unique virtual network name) which determines what node is the Primary. The listener will also be how the application(s) will connect to the instance and databases. You can manually fail the AG over to the other node when Windows patching is occurring. Both the AG and the listener will appear in the Failover Cluster Manager as a resource after they are created.
It's also my understanding that only user databases can be part of an AG so I'm not sure how things such as logins, jobs, etc would be transferred since the system databases aren't replicated.
Answer -
Yes, correct, logins, jobs, etc. will have to be manually created on the secondary from the primary usually done by scripting.
It seems as though the objective is to still protect at the instance level but leverage using AGs.
Answer -
Correct - It's the best of both worlds, you are providing protection at the node (machine) level and at the database level. One of the keys with AG's is that you are providing HA for a group of databases rather than only one, as the old mirroring did.
November 21, 2019 at 7:35 pm
That's a bunch of good information. Thanks for helping to clear that up, rvsc48.
Regarding scripts to keep logins, jobs, etc on the secondary up-to-date, doesn't that go against the idea that no, or close to no, data will be lost during a fail over? Maybe my confusion stems from FCI where both instances are identical copies of each other where this seems like the user database will be but any changes to the system databases can be missed depending on the timing of the scripts. Is that fair to say?
November 21, 2019 at 8:36 pm
Yes, that is fair to say and very true. There could very well be a case where either a login or new job could get missed due to timing of scripts or manual intervention without scripts. However, if you did set up a job to sync these items, ran every few minutes, I would say you would be in great shape. FCIs (Failover Clustered Instances) were nice in that the Dba did not have to worry about keeping the logins and jobs on the Primary and Secondary in sync being there was only once copy of the system databases (shared storage).
November 22, 2019 at 1:54 pm
Sounds like a small trade off in the grand scheme of things. Thanks for all the pointers.
November 27, 2019 at 12:34 pm
Just a small addition. With availability groups, on Standard Edition, you can only have one database per availability group (You can have more than one availability group though). You also can't run queries on the non active database.
Not necessarily an issue, this is just meant as some information that might help.
November 27, 2019 at 6:03 pm
What is the purpose of moving to AG? You already have failover - and all the shared storage has been setup and configured - and it is working...why change it?
If the goal is to be able to setup a read-only secondary, then there is no reason to get rid of the FCI. You can add a new node to the cluster without access to the shared storage. Present non-shared storage with the same size/configuration as the shared storage - install SQL Server as standalone instance - and configure AlwaysOn for the clustered and standalone instances.
In the cluster configuration - you would change the node weight of the secondary node to 0 to prevent that node from having any quorum votes. You would also keep the disk/file witness so you still have 3 votes for quorum...
A couple other items in cluster configuration:
In this configuration - you don't need (or want) a listener as read-only users will access the secondary node directly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply