1

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?

|improve this question
4

Could someone explain, what the actual consequences are, if any?

Sure.

On larger tables schema changes seem to cause a lot of blocking, which lead us to not applying some of our changes.

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).

We got the advice to disable ReadOnly connections to the secondary replicas for the schema migrations.

This won't help the blocking, it could lessen it but only if the secondary is in synchronous commit mode and only if the workload on the secondary is causing extra HADR_SYNC_COMMIT waits. 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 synchronous replicas you may want to look at making them asynchronous for the time being depending on if the schema changes are size of data operations or not.

From what I have read, I would guess that it doesn't, because reads at the secondary should not block dml queries on the primary.

Correct.

|improve this answer
  • Thank you for your detailed answer! Just to sum it up and check if I understand somewhat correctly: So if a query on the secondary holds a schema stability lock, a schema change can still be hardened to the log? Doesn't it need a schema modification lock for this, and if so can it be acquired even though a schema stability lock is held? – timcbaoth Jan 17 '17 at 8:35
  • 1
    @timcbaoth It will be written to the log, the locks happen during redo. The log blocks will come across the network and be hardened to the log. The redo thread will attempt to redo the log blocks - it'll try to take a schema-m lock and become blocked. This will show up as redo thread blocked. Eventually the blocking will clear and redo continue. When the previous happens, your redo queue will grow. Redo and log hardening are not the same thing and work independent of each other. – Sean Gallardy Jan 17 '17 at 13:50
  • can you describe this statement more? "If it isn't, then it's not going to help the primary for blocking." If its not going to help the primary for blocking, then what should I do? Please place in answer, thanks- – user129291 Jun 26 '18 at 4:50

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.