I saw some new database scoped configurations like MAXDOP and legacy cardinality for secondary options and have few questions or doubts i am not able to understand

enter image description here


How do i setup MAXDOP, say for primary OLTP it needs to be 8 but on readable secondary i want the value to be 4? This is to make sure after AG failover i don't need to manually change those MAXDOP again, should i be setting these values on P and S both?

Legacy CE

If db compat is changed to 140 but performance for Primary OLTP its better when Legacy CE option is ON and on readable secondary's due to different DW workload works better when Legacy CE OFF. How i change such settings so that during failover they are unchanged or we do not need to keep a track of things changing and go about manually changing it


These settings are stored inside the database. When a secondary becomes a primary, it is no longer a secondary, and it will adapt the settings for the primary.

Also, since this setting is stored inside the database and since a secondary is read only, you can only set these on one place: the one which currently is the primary.

  • Thanks so from what I understand these settings are just something to be setup on primary and secondary will start using it. I thought u need to change these on secondary databases – Newbie-DBA May 4 at 20:31
  • You can't set this on the secondary since the secondary is read-only and the setting is stored inside the database, hence it need to modify the database which isn't possible on a secondary. So, yes, set on the primary and the secondaries adapts to the setting. – Tibor Karaszi 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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