Single Server (Replica) Availability Group
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.
sql-server availability-groups
asked Nov 15 '18 at 2:28
Select 'DBA'Select 'DBA'
334
add a comment |
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.
sql-server availability-groups
asked Nov 15 '18 at 2:28
Select 'DBA'Select 'DBA'
334
add a comment |
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.
sql-server availability-groups
asked Nov 15 '18 at 2:28
Select 'DBA'Select 'DBA'
334
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.
sql-server availability-groups
sql-server availability-groups
asked Nov 15 '18 at 2:28
Select 'DBA'Select 'DBA'
334
asked Nov 15 '18 at 2:28
Select 'DBA'Select 'DBA'
334
asked Nov 15 '18 at 2:28
Select 'DBA'Select 'DBA'
334
asked Nov 15 '18 at 2:28
Select 'DBA'Select 'DBA'
334
asked Nov 15 '18 at 2:28
Select 'DBA'Select 'DBA'
334
334
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 15 '18 at 2:47
Sean GallardySean Gallardy
16.6k22653
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
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222591%2fsingle-server-replica-availability-group%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 15 '18 at 2:47
Sean GallardySean Gallardy
16.6k22653
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
add a comment |
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.
answered Nov 15 '18 at 2:47
Sean GallardySean Gallardy
16.6k22653
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
add a comment |
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.
answered Nov 15 '18 at 2:47
Sean GallardySean Gallardy
16.6k22653
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.
answered Nov 15 '18 at 2:47
Sean GallardySean Gallardy
16.6k22653
answered Nov 15 '18 at 2:47
Sean GallardySean Gallardy
16.6k22653
answered Nov 15 '18 at 2:47
Sean GallardySean Gallardy
16.6k22653
answered Nov 15 '18 at 2:47
Sean GallardySean Gallardy
16.6k22653
16.6k22653
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
add a comment |
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
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
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
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222591%2fsingle-server-replica-availability-group%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
