We help IT Professionals succeed at work.
Research Question

upgrade a SQL server 2016 FCI To SQL server 2019 PCI

33 Views
Last Modified: 2020-11-11
hi,

 what is the correct/tested procedure to upgrade SQL 2016 standard edition of a 2 x node FCI to  SQL 2019 standard edition of a 2 x node FCI ?
  What OS have SQL 2019 standard edition has to run on ?
any change in SQL 2019 standard edition on 2 x nodes FCI? or FCI is not supported any more?
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
IF the current OS where your SQL 2016 Standard (active/passive I presume) Cluster is running is "Windows Server 2016 or greater" then you can "Perform a rolling upgrade or update" as detailed here: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance?view=sql-server-ver15#perform-a-rolling-upgrade-or-update
Essentially easy steps to upgrade your Passive node first, then failover onto it and test make sure everything is fine on new SQL version then upgrade the other node and fail back onto it to make sure it is all good on it as well.

IF the current OS version is lover than Windows Server 2016 then you need to build a new SQL 2019 Cluster on Windows Server 2016 or greater then migrate all into this new cluster, logins, jobs, SSIS packages, db's etc. as detailed here https://datamigration.microsoft.com/scenario/sql-to-sqlserver?step=1 however is good practice to run the DMA (former called DUA - Database Upgrade Advisor) on your current SQL 2016 to see if by any bad chance you have any breaking changes that must be fixed before the migration/upgrade
marrowyungSenior Technical architecture (Data)

Author

Commented:
Cluster is running is "Windows Server 2016 or greater" t

it is Windows 2012 R2.
Essentially easy steps to upgrade your Passive node first, then failover onto it and test make sure everything is fine on new SQL version then upgrade the other node and fail back onto it to make sure it is all good on it as wel

this is rolling upgrade, right?

IF the current OS version is lover than Windows Server 2016 then you need to build a new SQL 2019 Cluster on Windows Server 2016 or greater then migrate all into this new cluster,

lower than right? as we are using Windows 2012 R2 then we have to upgrade to Windows server 2016 standard edition with latest SP and then install SQL server 2019 standard edition FCI starting from passive node ?

but upgrading from existing nodes from windows 2012 R2 to Windows 2016 server WITHOUT upgrading SQL server 2016 is ok ? AFTER that upgrade SQL server only ?

and existing SQL server 2016 standard edition , can only have 2 x nodes failover cluster? can't do 4 x nodes FCI ?


marrowyungSenior Technical architecture (Data)

Author

Commented:
any idea? 
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
as noted above if OS is lower than required I advised creating a new environment/cluster with desired versions (and I would go to Win 2019+SQL 2019 combo) rather than trying to perform rolling OS + SQL upgrades and this is due to the various issues we (DBA + systems) encountered every time we tried to perform such an upgrade we ended up on broken single cluster node from were we had to migrate to new WFCS + SQL Cluster anyway so if you'd like to try the rolling upgrade path you need to do more research and be ready for anything.
More details about what and how to go from Windows 2012 R2 running SQL cluster to 2019 can be found here but again please be aware of the potential consequences and be prepared to act upon the unexpected:

https://sqlha.com/2019/08/15/ags-and-wsfc-os-rolling-upgrades-what-works-and-what-doesnt/
marrowyungSenior Technical architecture (Data)

Author

Commented:
one similar situation,

we have a SQL server 2016 2 x VM nodes standard edition FCI and we try to add more CPU power to the existing FCI cluster.

We found out an idea which might be very good and which is , on the 2 x nodes VM  SQL server 2016 FCI cluster we swap each existing VM nodes with NEW nodes (we have 2 x ) for each existing FCI passive VM node, ONE BY ONE,

is it possible now to ONLY swap the existing passive VM node with new bare metal by taking out one SQL server passive node and add back the new bare metal node do the cluster and it can failover again ?

what is the installation procedure on both Windows 2012 R2 standard and SQL server 2016 standard edition BEFORE it join the SAME cluster again?

possible ?

we use the same SAN closure so data path do not change.


marrowyungSenior Technical architecture (Data)

Author

Commented:
this is due to the various issues we (DBA + systems) encountered every time we tried to perform such an upgrade we ended up on broken single cluster node from were we had to migrate to new WFCS + SQL Cluster  

it seems telling me that OS+ SQL upgrade on FCI is a joke, usually OS upgrade on MSCS is easily , like upgrade passive node first and then primary node!

so this time with OS +SQL together the whole upgrade can be a failure ?

marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

any update for me ?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions