4

I was of belief that changing compatibility mode on primary database in database mirroring would make the change for mirror database.

But it proved to be wrong. I queried using sys.databases on Primary where i changed compatibility mode to 120 , however on its mirrored database compatibility level was still 100. Why?

Am i using the incorrect fn here sys.databases?

Also if it does not change what does it mean, do i need to do failover/failback to reflect?

What if i had log shipping secondary database in read only as well where this change needs to reflect? Do i need to rebuild log shipping then?

Thanks

Improve this question
2
  • 1
    AFAIK a compatibility change of principal database should also affect mirror. Can you triple check using sys.databases gain. I hope all is good with your mirroring setup
    – Shanky
    Commented May 29, 2020 at 7:55
  • @Shanky: I doubled "double" check :) for couple of my test servers and still shows the old value of compatibility level when queried via sys.databases on mirror server. Yes mirroring is all good and same applies for LS , they are in sync and healthy
    – BeginnerDBA
    Commented May 29, 2020 at 15:42

1 Answer 1

Reset to default
8
+50

Below

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

is not propagated to secondaries in DB mirroring or AlwaysON since they ship log blocks vs transactions - (logshipping you take log backups and ship it to secondary server and restore them).

There is a reason why they should not change the DB compatibility level as you certify your application based on DB Compatibility level vs a particuliar version of sql server.

This is same as if you change the owner of database .. you have to change it on secondary when it becomes Primary.

how can i really get this changed without breaking LS or mirroring if possible.

You dont have to break mirroring or LS. You have to just failover and failback.

For logshipping, since it is physical log backup-copy-restore, once you restore the database and bring it online or readonly it will reflect the change. For mirroring, you can just failover and failback.

The point is to bring the db to read/write state and do alter database in AG or mirroring configuration.

Improve this answer
5
  • excellent, thanks for clarifying because bases on my test it was not reflecting. My major concern here and will appreciate if you can add as input to your answer how can i really get this changed without breaking LS or mirroring if possible. if not possible then we have to break mirroring and LS which seems too tedious for VLDB's in production
    – BeginnerDBA
    Commented Jun 1, 2020 at 18:46
  • @BeginnerDBA updated my answer.
    – Kin Shah
    Commented Jun 1, 2020 at 21:02
  • thanks: Mirror failover/failback definitely works. But for LS as that is not a DR, just a read only for primary db, doing reverse log shipping might not be approved case in our scenario. so looks like no easy way for LS
    – BeginnerDBA
    Commented Jun 2, 2020 at 0:23
  • it looks like you answer seems not valid unless i misunderstood. Log shipping read only database reflects the change once ls backup,copy restores completes with change. Just tested and it does not need dr activity on LS side
    – BeginnerDBA
    Commented Jun 2, 2020 at 16:29
  • Logshipping is physical copy of transaction log, so it will reflect it when the log backup is restored. I will clarify my answer. (I missed the point that you have db in standby mode in LS).
    – Kin Shah
    Commented Jun 2, 2020 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.