I have a 2 node AG in a non-prod environment and we are looking to shut down the secondary node for cost savings, leaving this with just the primary node. I have some questions based on this process, and my research hasn't answered all of them. Could somehow fill in the blanks here on what is possible / best to do?

  1. is it possible to keep just a single node AG? if so, is there a reason to actually do it? As I understand it, there is no benefit to keeping the AG at all if you remove all the secondary nodes, as it then becomes just a stand alone instance. If i kept the AG, then the transaction logs of the DB's will not truncate, as its trying to find a place to send the logs to, correct?

  2. The AG Listener - Moot point if we remove the AG altogether, but if i kept the AG as a single node, the listener would not function correct? My app team will need to make code changes to use something else since listener name is coded to be used, and they would like to avoid that if possible. i don't think this is possible, but wanted to check.

  3. Secondary node down the road - we may spin up the secondary node on demand down the road, so is there an issue with leaving the cluster resources in place after we remove the AG, or does the removal delete the cluster objects? I think they stay, but I have not found an answer on this.

  4. SSRS question - we are in a scale-out deployment, do I need to remove the secondary from the scale out? I would prefer to leave it since we may need to recreate the secondary down the road. I would need to update connection strings, etc to use the primary node and not the listener.

My assumed workflow is like this based on what I found

  1. Connect to primary replica
  2. Delete the entire AG (this removes AG settings from all DB's, primary and secondary), and throws secondary DB's into restoring state, while leaving primary DB's accessible and completely separate.
  3. log backups will continue on primary like normal and log truncation will happen as expected.

1 Answer 1

  1. Yes. Primary benefit is that you can keep the listener online. Downside is that there is still a dependency on the cluster service to keep the listener online, but that shouldn't be a significant risk since there is only one node as it will always own quorum, assuming you remove all quorum votes other than the remaining node's vote.
  2. The listener is a component of the AG, so if you delete the AG, it will delete the listener. Options are to 1) just remove the DBs from the AG, 2) you can delete the AG and then create a new cluster role that just has the network name and IP address (which is really all that a "listener" is), or 3) delete the AG and add a DNS record for the host name that clients are currently using. You could also do as you suggest to modify client connections, but I don't know how painful that would be for you.
  3. If you leave it as a one-node cluster, you can simply join a node to it in the future if needed. If you remove the cluster completely, you simply have to create a new cluster and join the two (or however many) nodes to it.
  4. If you pointed the SSRS instances to the listener name, then you don't need to do anything if you keep the listener or add a DNS record for the listener name.

With your comments about possibly using an AG again in the future, I personally would not delete the AG and cluster. I would remove the databases from the AG, remove the secondary node from the cluster, and remove all quorum votes except for the one node in the cluster. Less work now, less work later.

You haven't mentioned anything about how backups are occurring so there's no way for anyone to know whether your backups will continue as expected.

Also, you'll first have to remove the databases from the AG prior to deleting the AG.

  • Thanks for the feedback. Currently full and log backups are being taken on the primary node using ola hallengrens maintenance solution. Does this help determine what the backups and transaction log behavior will be if I leave it as a 1 node cluster?
    – DBA Greg14
    May 6, 2020 at 15:32
  • Your backups should continue working fine as Ola's scripts will treat them a non-AG DBs once you remove them. May 6, 2020 at 15:50

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.