August 18, 2017 at 3:10 am
I want to upgrade my SQL Server 2014 to SP2 which is involved in SQL Server FCI and Alwayson. Below is my environment details:
Please let me know how should I go about patching both my SQL Server clustered instances and any precautions to be taken.
August 18, 2017 at 9:26 am
I would probably approach this task similarly. The key is always ensuring that NO instances or Ags are running on the node you are upgrading until after it is upgraded.
Step 1 - Ensure all databases on all instances involved have a current FULL backup taken prior to beginning the upgrade.
Step 2 - Manually failover the AG for Instance-A to its Secondary
Step 3 - Failover Instance-A to Server-B
Failover Instance-B to Server-B
Upgrade - Server-A (no instances or Ags should be running on it)
Step 4 - Manually failover the AG for Instance-A back to its Primary
Step 5 - Failover Instance-A to Server-A (Databases on Instance-A will be upgraded at this time)
Failover Instance-B to Server-A (Databases on Instance-B will be upgraded at this time)
Upgrade - Server-B (no instances should be running on it)
Step 6 - Failover Instance-C to Server-D
Manually failover the AG from Server-C to Server-A
Upgrade Server-C
Fail back over Instance-C back to Server-C
Upgrade Server-D
NOTE - For the AlwaysOn AGs - The Failover Mode to "Manual" should prevent any failovers while the instance (node) is being upgraded. The synchronization of the Ags should remain in tact once the instance is upgraded.
Re-balance all the Instances to match the configuration you desire as you mention in your steps 1 - 4.
August 21, 2017 at 7:01 am
SQLPRODDBA - Friday, August 18, 2017 3:10 AMI want to upgrade my SQL Server 2014 to SP2 which is involved in SQL Server FCI and Alwayson. Below is my environment details:
- Nodes: SERVER-A, SERVER-B, SERVER-C, SERVER-D
- SQL Server clustered instances : Instance-A, Instance-B, Instance-C
- SQL Server edition and version : SQL Server 2014 EE SP1 on all 3 clustered instances
- Windows server edition and version : Windows server 2012 R2 STD
- SQL Server clustered Instance-A runs on SERVER-A and is installed between SERVER-A and SERVER-B.
- SQL Server clustered Instance-B runs on SERVER-B and is installed between SERVER-A and SERVER-B.
- SQL Server clustered Instance-C runs on SERVER-C and is installed between SERVER-C and SERVER-D.
- SQL Server clustered Instance-A has Alwayson configured for some DBs between Instance-A and Instance-C. Instance-A acts as Primary replica and clustered Instance-C acts as its secondary replica. Alwayson failover mode for DBs is manual.
Please let me know how should I go about patching both my SQL Server clustered instances and any precautions to be taken.
Ensure you backups and that the databases are synchronised, ensure all AGs are set to synchronous
then use the following rolling upgrade process;
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 22, 2017 at 12:31 pm
Thank you Perry for your reply! I didn't understand why AG failover is required since Clustered instance A would already be running on server B. Can you please clarify?
August 22, 2017 at 12:35 pm
RVSC48 - Friday, August 18, 2017 9:26 AMI would probably approach this task similarly. The key is always ensuring that NO instances or Ags are running on the node you are upgrading until after it is upgraded.
Step 1 - Ensure all databases on all instances involved have a current FULL backup taken prior to beginning the upgrade.
Step 2 - Manually failover the AG for Instance-A to its Secondary
Step 3 - Failover Instance-A to Server-B
Failover Instance-B to Server-B
Upgrade - Server-A (no instances or Ags should be running on it)Step 4 - Manually failover the AG for Instance-A back to its Primary
Step 5 - Failover Instance-A to Server-A (Databases on Instance-A will be upgraded at this time)
Failover Instance-B to Server-A (Databases on Instance-B will be upgraded at this time)
Upgrade - Server-B (no instances should be running on it)Step 6 - Failover Instance-C to Server-D
Manually failover the AG from Server-C to Server-A
Upgrade Server-C
Fail back over Instance-C back to Server-C
Upgrade Server-DNOTE - For the AlwaysOn AGs - The Failover Mode to "Manual" should prevent any failovers while the instance (node) is being upgraded. The synchronization of the Ags should remain in tact once the instance is upgraded.
Re-balance all the Instances to match the configuration you desire as you mention in your steps 1 - 4.
Thank you for your reply! Why do we need AG failovers in step 2 and 4. Wouldn't the entire cluster failover of Instance A to server B(step 3) would make sure that my DBs are available?
August 22, 2017 at 12:37 pm
Perry Whittle - Monday, August 21, 2017 7:01 AMSQLPRODDBA - Friday, August 18, 2017 3:10 AMI want to upgrade my SQL Server 2014 to SP2 which is involved in SQL Server FCI and Alwayson. Below is my environment details:
- Nodes: SERVER-A, SERVER-B, SERVER-C, SERVER-D
- SQL Server clustered instances : Instance-A, Instance-B, Instance-C
- SQL Server edition and version : SQL Server 2014 EE SP1 on all 3 clustered instances
- Windows server edition and version : Windows server 2012 R2 STD
- SQL Server clustered Instance-A runs on SERVER-A and is installed between SERVER-A and SERVER-B.
- SQL Server clustered Instance-B runs on SERVER-B and is installed between SERVER-A and SERVER-B.
- SQL Server clustered Instance-C runs on SERVER-C and is installed between SERVER-C and SERVER-D.
- SQL Server clustered Instance-A has Alwayson configured for some DBs between Instance-A and Instance-C. Instance-A acts as Primary replica and clustered Instance-C acts as its secondary replica. Alwayson failover mode for DBs is manual.
Please let me know how should I go about patching both my SQL Server clustered instances and any precautions to be taken.
Ensure you backups and that the databases are synchronised, ensure all AGs are set to synchronous
then use the following rolling upgrade process;
- Fail instance-B to Server-A
- Reboot Server-B to ensure the installer is not blocked by a pending reboot, and then apply SP2 to both instances, you may need to reboot Server-B again
- Reboot Server-D and then apply the SP2 patch, you may need to reboot again
- Fail Instance-A and Instance-B to Server-B
- Fail Instance-C to Server-D
- move the AG from Instance-A to Instance-C
- Reboot Server-A and Server-C then apply SP2, you may need to reboot
- Move instances back to where you want them
Thank you Perry for your reply! I didn't understand why AG failover is required since in the above steps we are moving entire clustered instance A to server B. Can you please clarify?
August 22, 2017 at 1:18 pm
The reason we both mentioned AG failover is because your configuration sounded like you had both FCIs (Failover Clustered Instances) and AGs (Availability Groups) in which case you would want to failover both your clustered instances and your Availability Groups all on the same cluster. Is this not the case?
August 23, 2017 at 5:08 am
SQLPRODDBA - Tuesday, August 22, 2017 12:31 PMThank you Perry for your reply! I didn't understand why AG failover is required since Clustered instance A would already be running on server B. Can you please clarify?
I didn't design it so i cannot clarify, contact your application architect.
If you're asking what valid reason could one have to integrate an FCI into an AG then the obvious reason is that ther may be other databases on the replica that require HA outside of an AG and at the instance level
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply