6

First time caller, long time listener.

I have a situation where I need to stand up a single server Availability Group for around 2 months (i.e. move DBs to a new server, rebuild the current server, and add the rebuilt current server to the AG). Both servers are physical and have roughly the same specs. Windows Server 2016, SQL Server 2014.

My thought was to stand up the AG with the listener now so that when the current server is rebuilt and added to the group, we wouldn't need to add the listener then and update all of our apps/clients (i.e. that would force two migrations, one to the new server, one for the new listener).

I've tested this situation on a few test servers and its working as expected. I also can't foresee a reason not to do this, but I thought it would be worth the community ask since we're talking about Production DBs. My question is has anyone tried this and had any issues? Would anyone recommend against this solution. Thanks.

7

First time caller, long time listener.

Ha! Love it! Welcome :)

I've tested this situation on a few test servers and its working as expected.

You are correct, it should work without many if any issues. Since clustering will now be part of the scenario you don't get a free single node lunch... you're still beholden to any cluster or AG level issues that may arise (I'm not saying they will, just that it's now in the issues list).

My question is has anyone tried this and had any issues?

I have, in fact, tried this. It does work, the 2-months part does give me a little bit of uneasiness... but assuming the configuration doesn't drift, you should be alright.

You can also pre-stage a listener by creating a DNS entry with the same name so connection strings don't change, use a network appliance or hardware load balancer, etc. The largest issue is enabling the Always On manager and the service restart required for it, in my opinion. All else (except the listener unless you do the DNS/HLB thing) should or can be transparent.

Would anyone recommend against this solution.

I only have the reservations of getting used to the single server performance characteristics and then adding in a synchronous replica or such afterwards. Since the single node AG won't have sync_commit waits and such, you really won't be in for much of a shock... though this could change once the other node is added. Just something to think about.

  • Sean, thank you for the detailed response. I've been using AGs for a number of years, and this answer touches on many important topics that everyone should consider. The staged DNS alias is a very good idea too. – Select 'DBA' Nov 16 '18 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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