🔷 Step-by-Step Upgrade Process (Rolling Upgrade Approach) Part II

🔹 Step 1: Pre-Upgrade Checks

✅ Check Always On AG Health

Ensure all replicas are in a healthy synchronized state.

✅ Suspend Data Movement on Secondary Replicas

✅ Back Up System & User Databases

Verify backup integrity using:

RESTORE VERIFYONLY FROM DISK = 'E:\Backup\YourDB_Full.bak';

✅ Script Out AG Configuration & SQL Agent Jobs

EXEC sp_help_availability_group 'YourAGName';
EXEC sp_help_availability_replica 'YourAGName';
EXEC sp_help_database 'YourDB';

🔹 Step 2: Upgrade Secondary Replicas

✅ Remove Secondary Replica from Always On AG (on the 2016 node)

✅ Install SQL Server 2025 on the Secondary Node

Run SQL Server Setup → Choose Upgrade from a previous version.

Select SQL Server 2016 instance to upgrade.
Ensure all feature selections match the existing installation.
Install latest cumulative updates for SQL Server 2025.

✅ Upgrade Databases on the Secondary Replica
Start SQL Server 2025 instance.
Run the Post-Upgrade Data Consistency Check:

DBCC CHECKDB('YourDB') WITH NO_INFOMSGS;
✅ Rejoin the Secondary Replica to Always On AG

A✅ Resume Data Movement on Secondary

ALTER DATABASE [YourDB] SET HADR RESUME;

🔹 Step 3: Perform a Failover to the Upgraded Secondary

✅ Manually Fail Over to the Upgraded Secondary

ALTER AVAILABILITY GROUP [YourAGName] FAILOVER;
✅ Validate Functionality on the New Primary

Check Application Connectivity.
Run test queries and stored procedures.
Verify SQL Agent Jobs are functioning properly.

🔹 Step 4: Upgrade the Old Primary (Now Secondary)
✅ Remove the Old Primary from AG

ALTER AVAILABILITY GROUP [YourAGName] REMOVE REPLICA ON 'OldPrimaryNode';

✅ Upgrade SQL Server 2016 to 2025 on the Old Primary
Repeat the SQL Server Setup & Upgrade process.
Rejoin it as a secondary replica.
✅ Rejoin to Always On AG

ALTER AVAILABILITY GROUP [YourAGName] ADD REPLICA ON 'OldPrimaryNode'
WITH (ENDPOINT_URL = 'TCP://OldPrimaryNode:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
✅ Resume Data Movement

ALTER DATABASE [YourDB] SET HADR RESUME;
✅ Ensure Everything is Synchronizing

SELECT * FROM sys.dm_hadr_database_replica_states
WHERE database_id = DB_ID('YourDB');

🔹 Step 5: Final Validation & Clean-Up
✅ Verify AG Dashboard for Health Status
Check for replication latency.
Validate error logs & Extended Events for issues.
✅ Reconfigure Jobs & Maintenance Plans

EXEC msdb.dbo.sp_update_job @job_name = 'YourJobName';
✅ Update Statistics for Performance Optimization

EXEC sp_updatestats;
✅ Enable Backups on the New Primary

BACKUP DATABASE [YourDB] TO DISK = 'E:\Backup\PostUpgrade_Full.bak';
✅ Monitor for Performance Issues in First 24 Hours
Check for deadlocks & blocking.
Run performance baseline comparisons.
🔷 Post-Upgrade Checklist
✅ Ensure AG Automatic Failover Works
✅ Monitor SQL Server Error Logs for Issues
✅ Check DBCC CHECKDB for Any Integrity Issues
✅ Perform Application Testing with End Users
✅ Document the Upgrade for Auditing & Compliance