SQL Migration from SQL2017 to SQL2022

1. Run DBCC CheckDB on AG1.If AG1 passes CHECKDB, any page corruption on AG2 would surface as redo errors in the SQL error log.
2. Monitor Error logs and track for error number 824 and 829. If there is any corruption, these errors will be thrown.
3. Monitor synchronization health on AG2

SELECT db_name(drs.database_id) AS DatabaseName, drs.synchronization_state_desc, drs.synchronization_health_desc, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.last_redone_lsn, drs.last_hardened_lsn, drs.last_received_lsn FROM sys.dm_hadr_database_replica_states drs JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id WHERE db_name(drs.database_id) = 'YourDatabaseName';

4. Take a backup on AG1 and restore it on sql 2022 which is not in AG. Run DBCC CheckDB.

The standalone SQL 2022 restore and DBCC CHECKDB approach is the best approach.

Verify the data integrity of the database

To verify the data integrity of a SQL Server database in "Synchronized/In Recovery mode," you can use the following methods:

Check the SQL Server Error Log: Look for progress updates indicating the recovery phase, such as "Recovery of database 'DatabaseName' is 50% complete."

1

Use DBCC CHECKDB: This command checks the logical and physical integrity of all objects in the database. It runs checks on the database, tables, and views, and validates the contents of indexed views.

1

Restore from Backup: If recovery is not progressing, restoring from a known good backup is usually the safest outcome.

1

These methods help ensure that the database is transactionally consistent and that the data is not corrupted. If recovery is not progressing, it is important to take action to prevent data loss or corruption.

Here's what AI suggests:

SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%RECOVERY%';

Ensure disk latency is low and CPU is not maxed out.

If log backups are missing or corrupted, you may need to restore from a good backup and rejoin the AG.

When adding a database to an AG, ensure it is fully recovered and log backups are up-to-date before joining. This minimizes "In Recovery" delays.