Order of upgrading OS & SQL Server

Question

Is the following order fine for us to proceed with?

Back Up of both WS & SQL DBs

1. Upgrade SQL 2014 Enterprise RTM to SQL 2014 SP3 Enterprise
2. Upgrade WS 2012 R2 to WS 2019 DC
3. Upgrade Roles & File server, SCCM
4. Upgrade SQL 2014 Enterprise SP3 to SQL 2022 Enterprise as per here.
5. Upgrade WS 2019 DC to WS 2022 DC
6. Upgrade Roles & File server, SCCM

SSMS & SSDT - can it be a direct install of latest versions of them?

Any specific services that needs to be stopped before initiating the upgrade?

Are there anything else that we need to pay attention to?

Answer 1

Yes, the proposed order is appropriate. Ensure you back up both web servers and SQL databases first. Then, proceed with the upgrades in this order:

SSMS & SSDT: You can directly install the latest versions.

Before initiating the upgrades, it is recommended to stop the following services to prevent data corruption and ensure a smooth transition:

1. SQL Server Services: Stop the SQL Server service and SQL Server Agent service to avoid any database operations during the upgrade.
2. Application Services: Stop any services related to applications that interact with the SQL Server or the operating system.
3. SCCM Services: Stop SCCM services to ensure no configuration changes are made during the upgrade.

Refer: https://learn.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server?view=sql-server-ver16

https://learn.microsoft.com/en-us/windows-server/get-started/upgrade-overview

Answer 2

Hi @Radhai Krish ,

Thank you for reaching out and welcome to Microsoft Q&A.

Is the following order fine for us to proceed with?

Yes, the order is suitable, you can follow the order to upgrade your SQL Server!

SSMS & SSDT - can it be a direct install of latest versions of them?

Of course you can!

• SSMS 20.1 is the latest generally available (GA) version. If you have a preview version of SSMS 20 installed, uninstall it before installing SSMS 20.1. Installing SSMS 20.1 doesn't upgrade or replace SSMS 19.x and earlier versions. You can install the latest version directly!
• SSDT is backwards compatible, so you can always use the newest SSDT to design and deploy databases, models, reports, and packages that run on older versions of SQL Server.

Any specific services that needs to be stopped before initiating the upgrade?

Please ensure the Pre-upgrade checklist in the link you provided are completed and then check out the Sreeju's suggestions.

Feel free to share your issue here if you have any confusions.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 3

Thank you @Sreeju Nair & @LucyChenMSFT-4874 !!

Answer 4

To ensure a successful upgrade of your OS and SQL Server, it’s crucial to follow a detailed and structured plan. Here's a comprehensive step-by-step guide:

Preliminary Steps

1. Back Up of both WS & SQL DBs:
   • Full Backups: Perform full backups of all SQL Server databases and system databases (master, model, msdb).
   • Transactional Log Backups: For databases in full recovery mode, take transactional log backups to ensure point-in-time recovery.
   • File System Backups: Backup the file system, including the Windows Server and any application-specific files.
   • Validation: Verify the backups to ensure they are complete and can be restored if needed.

Upgrade Plan

2. Upgrade SQL 2014 Enterprise RTM to SQL 2014 SP3 Enterprise:
   • Why First?: This step ensures your SQL Server is up to date with the latest service packs before any major OS upgrades, which can minimize compatibility issues.
   • Steps:
     • Download the SP3 update from Microsoft's official site.
     • Test the SP3 upgrade in a non-production environment.
     • Apply the SP3 update to the SQL Server instance in production.
     • Verify the SQL Server functionality post-upgrade.
3. Upgrade WS 2012 R2 to WS 2019 DC:
   • Why Next?: Upgrading the OS provides a stable platform for further upgrades and newer versions of SQL Server.
   • Steps:
     • Ensure all drivers and applications are compatible with WS 2019.
     • Run the Windows Server Upgrade Advisor.
     • Perform an in-place upgrade or a clean install of WS 2019, depending on the environment.
     • Verify system functionality and connectivity post-upgrade.
4. Upgrade Roles & File Server, SCCM:
   • Why Now?: Upgrading these roles and services ensures they are compatible with WS 2019 before further SQL Server upgrades.
   • Steps:
     • Upgrade individual roles and services, verifying each step to ensure no disruptions.
     • Confirm that all critical services are operational and stable.
5. Upgrade SQL 2014 Enterprise SP3 to SQL 2022 Enterprise:
   • Why Now?: Ensures the SQL Server is upgraded to the latest version supported by the upgraded OS.
   • Steps:
     • Perform a compatibility check for databases and applications.
     • Test the upgrade process in a non-production environment.
     • Execute the upgrade process, ensuring minimal downtime.
     • Run post-upgrade scripts and verify database integrity and functionality.
6. Upgrade WS 2019 DC to WS 2022 DC:
   • Why Now?: Finalizing the OS upgrade ensures the latest features and security enhancements.
   • Steps:
     • Run the Windows Server Upgrade Advisor for WS 2022.
     • Perform the upgrade or clean install as per your environment's requirements.
     • Verify all system services and roles post-upgrade.
7. Upgrade Roles & File Server, SCCM:
   • Why Again?: Ensures that roles and services are compatible with WS 2022 and take advantage of the latest features and security updates.
   • Steps:
     • Sequentially upgrade each role and service, ensuring each is fully operational before moving to the next.
     • Validate the functionality of all upgraded services.

Additional Considerations

• SSMS & SSDT:
  • You can directly install the latest versions of SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT). These are designed to be backward compatible and provide the latest features and improvements.
• Stopping Services:
  • Before initiating the upgrade, stop the following services to prevent conflicts:
    • SQL Server services
    • SQL Server Agent
    • Any application services that interact with SQL Server
    • Dependent services that could interfere with the upgrade process
• Compatibility and Dependencies:
  • Ensure that all third-party applications and services are compatible with the new versions of SQL Server and Windows Server.
  • Check for any deprecated features in SQL Server 2022 that might affect your applications.
• Testing Environment:
  • Perform the entire upgrade process in a testing environment first to identify potential issues.
  • Document the process and any troubleshooting steps taken.
• Rollback Plan:
  • Have a robust rollback plan in case the upgrade fails. This includes having recent backups and a plan to restore the system to its previous state.
• Communication and Downtime:
  • Communicate the upgrade plan and expected downtime to all stakeholders.
  • Schedule the upgrade during a maintenance window to minimize impact on users.

By meticulously following these steps and considerations, you can ensure a smooth and successful upgrade of your OS and SQL Server environments.