We have a db that is replicated on a secondary in an AlwaysOn Availability Group. Doing some schema migrations proves very hard. We have read requests on the synchronous secondary replica basically 24/7. Schema migrations are only applied during deploy. On larger tables schema changes seem to cause a lot of blocking, which lead us to not applying some of our changes. Obviously this is not a desirable state, as the database schema diverges from the schema that we want (and use in our testing and development db).
We got the advice to disable ReadOnly connections to the secondary replicas for the schema migrations. However I am unsure if those can affect the schema changes at all. From what I have read, I would guess that it doesn't, because reads at the secondary should not block DDL queries on the primary. Could someone explain, what the actual consequences are, if any?
Best Answer
Sure.
This is happening on the primary (if it truly is blocking), you'll need to find what sessions are blocking it at why (on the primary for the primary).
This won't help the blocking, it could lessen it but only if the secondary is in
synchronouscommit mode and only if the workload on the secondary is causing extraHADR_SYNC_COMMITwaits. If it isn't, then it's not going to help the primary for blocking.What turning off readable seconaries will do, is stop the workload on the secondary allowing it to have the most speed in keeping up by not having any other workloads to contend with. It may also help with redo blocking on the secondary since you're running DML and could lessen the send queue/redo queue. If you had
synchronousreplicas you may want to look at making themasynchronousfor the time being depending on if the schema changes are size of data operations or not.Correct.