SQL SERVER – Availability Groups Can Propagate Database Compatibility Level

Propagation of Compatibility Level occurs through the availability group’s database synchronization. Change the database setting on the primary replica.

SELECT @@SERVERNAME AS InstanceName, name, compatibility_level,
       state_desc, is_read_only
FROM sys.databases WHERE name=N'YourAvailabilityDatabase';

My earlier answer incorrectly recommended failover to change every replica separately. Compatibility level belongs to the database. It isn’t an independent configuration of each SQL Server instance. The database change flows to the secondary through data movement.

Jim Evans documents this behavior in a SQL Server 2019 availability-group migration. The firsthand checklist changes compatibility level on the primary, then checks the synchronized secondaries. That evidence contradicts the original blanket answer.

Connect directly to each intended replica and compare the database name and compatibility level. Also check its role and synchronization state. Paused movement, replay lag or a different database can explain an apparent mismatch. Refreshing SSMS isn’t a propagation test.

Test the target level against a representative workload before an approved primary change. Monitor plans and performance afterward. Failover isn’t required solely to apply this database setting on every replica. Engine upgrades follow their own supported sequence.

References: Jim Evans: SQL Server 2019 migration and secondary propagation; Availability-group log transport and database synchronization; Compatibility-level scope and supported levels.

Related reading

• Comprehensive Database Performance Health Check
• Pinal Dave on YouTube
• Forwarded Records and Performance – SQL in Sixty Seconds #155
• Hide Code in SSMS – SQL in Sixty Seconds #154
• Zoom in SSMS – SQL in Sixty Seconds #153
• Transfer Schema of Table – SQL in Sixty Seconds #152
• Find a Table in Execution Plan – SQL in Sixty Seconds #151

A compatibility change is not a reason to cycle through replicas, it is a database change to validate through synchronization.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.