Avatar of marrowyung
marrowyung
 asked on

upgrade SQL server in Always on group.

hi,

For SQL server always on group, if I have to upgrade any SQL server nodes inside the group to latest service pack, what is the correct procedure and order to do it?

e.g. do i evne need to remove the nodes from the Always on Group before upgrading ?
Microsoft SQL ServerSQL* Alwayson

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
"To prevent the AG from unintended failovers during the upgrade process, remove availability failover from all synchronous-commit replicas before you begin."

so that AOAG do not have any member at all but only primary ? so only primary replicas serving applicaiton and users?

or ONLY remove AUTOFAILOVER but allow manual failover?
SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
so is still have failover but all manual! just do not use automatice failover DURING upgrade, right?
SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
"Of course you can't let ANY automatic failover in place between primary and secondary replicas because during the upgrade you will need to restart the node that was just upgraded and that will trigger a failover that would disrupt the upgrade and cause potentially much bigger headaches"

yep! exactly that. the process/procedure want everything under control.

"Aside what "Netminder" posted above you really need to read and understand each step to be performed during a rolling upgrade as noted"

that's why I have reply here as i read them, I want to understand the idea behind !

"Aside what "Netminder" posted above you really need to read and understand each step to be performed during a rolling upgrade as noted

that's right.

this is normal, but backup on primary nodes .

"Also be aware that after the initial failover to a secondary replica running a newer version of SQL Server, the databases in that Availability Group will run through an upgrade process to bring them to the latest version"

this is interesting too ! I think when ONE OF secondary replica upgraded, all user DB on that secondary replica ALREADY upgraded and we just need to wait for this upgraded secondary  replicas to sync all data from primary, the upgrade process should not wait when primary failover, right?

"During this time, there will be no readable replicas for any of these databases."

this also amazing me, imagine that there are 2 x secondary replicas and I only upgrade one, and there is 1 x secondary replica left, why no readable replicas for any of these database?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
"hat refers to ALL dbs from that Secondary Replica that's being upgraded right? Not ALL the Secondary Replicas..."

it say no readable replicas, so I think all replicas goes down.

"that refers to ALL dbs from that Secondary Replica that's being upgraded "

so ONLY all user dbs ON THAT upgrading secondary replicas is not accessiable ? and all other secondary replicas which is ALREADY upgraded or not upgraded yet still functioning and the read only operation from these secondary replicas which is not UPGRADING still serve the READ ONLY operation ?
marrowyung

ASKER
any updates for me ?
marrowyung

ASKER
tks both
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.