How to Upgrade SQL Server from 2014 to 2022, Apply the Latest Patches Post-Upgrade, and Install the Latest SSMS Tool?

Firstly, ensure that you have completed this on in a non-production environment.

Assume infrastructure is set up for SQL Server mirroring:

- Principal A - Primary

- Mirror B - Secondary

- Commit mode - Synchronous commit.

change ticket.

Before change steps:

1. Create a change ticket.

2. Conduct a peer review of the change ticket.

3. Obtain approval from the Change Advisory Board (CAB).

4. Get QA approval for application testing following the SQL Server version upgrade, installation of SSMS, and the latest cumulative update (CU) patch.

5. Download the latest patch executable from the Microsoft website and transfer the CU to both servers.

6. Move the existing SQL Server 2022 installation media to both servers.

7. Download the latest version of SSMS from the Microsoft website and transfer it to both servers.

8. Schedule stop of alerts for unnecessary tickets from the monitoring tool.

During the change window steps:

1. Create backups of the system databases.

2. Take a snapshot of the operating system and the drive where SQL is installed.

3. Suspend database movement on Server A using the command: 

ALTER DATABASE [DB_Name] SET PARTNER SUSPEND;

4. Install SQL Server Management Studio (SSMS) on Mirror B.

5. Launch SSMS and connect from the newly installed SSMS on Server B.

6. Upgrade the SQL version 2022 on Mirror B.

7. Reboot Server B.

8. Apply the patch on server B.

9. Reboot Server B again.

10. Verify that the SQL Server services are operational on Server B.

11. Check cumulative update (CU) installed on Server B.

12. Ensure that the databases are online on Server B.

13. Resume database movement on server  A using the command: 

ALTER DATABASE [DB_Name] SET PARTNER RESUME;

14. Review the error log on Server B.

 15. Perform a failover from Server A to Server B with the command: 

ALTER DATABASE [DB_Name] SET PARTNER FAILOVER;

 16. Check the failover status by executing the following query on Server B (now the new Principal).

   SELECT

    DB_NAME(database_id) AS DatabaseName,

    mirroring_state_desc,

    mirroring_role_desc,

    mirroring_safety_level_desc,

    mirroring_partner_name,

    mirroring_witness_name,

    mirroring_witness_state_desc,

    mirroring_failover_lsn

FROM    

sys.database_mirroring

WHERE   

mirroring_state IS NOT NULL;

17. Suspend database movement on Server B (new Principal) using the command: ALTER DATABASE [DB_Name] SET PARTNER SUSPEND;

19. Install SSMS on Server A (now the new Mirror).

20. Open SSMS and connect from the newly installed SSMS on Server A (new Mirror).

21. Upgrade the SQL version 2022 on Mirror A (new Mirror).

22. Reboot Server A (new Mirror).

23. Apply the patch on server A (new Mirror).

24. Reboot Server A (new Mirror).

25. Verify that the SQL Server services are running on Server A (new Mirror).

26. Check that the CU and KB are updated on Server A (new Mirror).

27. Ensure that the databases are online on Server A (new Mirror).

28. Resume database movement on Mirror B (new Principal) using the command:

  ALTER DATABASE [DB_Name] SET PARTNER RESUME;

29. Change the compatibility level from 120 to 160 on server B(new principal) with the command: 

ALTER DATABASE [DB_Name] SET COMPATIBILITY_LEVEL = 160;

 After completion of the change: 

1. Perform four-eye review. 

2. Update the status on the change ticket. 

3. Send an email to the QA team for application testing. 

4. Remove alerting.

5. Notify the stakeholders. 

6. Close the change ticket with the appropriate status. 

If any issue comes: 

1. Examine the error log in SQL Server and the event viewer; address the issue based on the findings in the error log. 

2. Restore the database from a backup. 

3.If no solutions are effective, restore the snapshot drive from the backup. 

Note: If you have Always On Availability Groups (AAG), there is no need to suspend and resume data movement; you can simply perform a failover and carry out the necessary tasks.