Sql-server – AlwaysOn DDL and Schema Changes

availability-groupssql server

Can anyone discuss how to exactly incorporate DDL Schema changes with Always On Availability Groups, step by step? We have a Primary Replica in one state, and Secondary Replica in another state location. The secondary replica will be read-only asynchronous.

If I were to make schema changes, including any wide variety…

Examples

  1. Add/modify/delete columns on table
  2. Add/modify/delete table foreign key constraints
  3. Add/modify/delete clustered and non-clustered indexes
  4. Add/modify/delete default constraints
  5. Add/modify/delete stored procedures and functions
  6. Add/modify/delete triggers
  7. Add/modify/delete view

… what would I need to ensure Primary Replica DDL flows to the Secondary Replica smoothly?

My Hypothesis

Read queries on secondary replica will not affect Primary DML (data modification, inserts, updates, deletes) and will flow smoothly, since secondary replica is set to Read Snapshot Isolation.

Read queries on secondary replica will affect primary DDL (schema Changes, table structure changes), since read queries will place a schema lock.

The solution

Stop all queries on secondary replica, conduct primary replica DDL, and then DDL changes will flow over to secondary replica.

  • Are there any other steps needed in this process?
  • What else needs to carefully be done?
  • What is best way to close all read queries on secondary replica? Kill/Close Spid, SET SINGLE_USER WITH ROLLBACK IMMEDIATE?

This example is only for secondary read-only asynchronous. What if the secondary replica is read-only synchronous?

Other Background:
The Primary Replica is OLTP, transactions all day and night. Our company has a deployment change management time window. Say I only have 1 hour during the morning to conduct schema and DDL changes. I cannot wait for long queries to finish. In asynchronous mode, the log redo will never be current.

I remember reading the Microsoft article, Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)

Best Answer

The redo thread that services the AlwaysOn log stream will block trying to acquire the required SCH-M lock (is a SCH-M for any of the cases you cite, it makes no difference what DDL it is).

New queries on the secondaries that reference the locked object(s) will coalesce behind the waiting SCH-M lock (they cannot sneak ahead, to prevent starvation of the waiter). Eventually the last query holding the SCH-S will drain and unblock the redo thread. The redo thread will get the lock and resume, applying the log records.

When the log record of the DDL commit will be replayed the the lock will be released and the secondary queries will resume. All this is described also in AlwaysON – HADRON Learning Series: lock_redo_blocked/redo worker Blocked on Secondary Replica.

Sync or Async AlwaysOn make no difference, since the sync part applies to received log durability, not to replay (redo) of the received log.

Normally there is no need to stop queries on the secondary. The mechanism described will handle the situation. In the rare event that you have secondary queries that may last a really long time (+30 min) with a potential to block log redo, you should monitor for the blockage and maybe take measures.

You need to ensure that log redo is current (ie. not much log received and pending redo), but that is the normal state. Then just apply the DDL. If you notice redo blockage on the secondaries you can kill the individual SPID blocking the redo. Note: The reason why I say 'redo is current' is simply to have control when things occur. If the redo is, say, 30 minutes behind, then you apply the DDL now and it will be replayed in 30 minutes, so killing secondary queries now is rather useless. Is not that you have to be exactly up to date, a small delay (redo queue size) should do no harm.

In async mode log redo can get current. It all depends on how fast is the primary writing. Think, what if the primary has no writes for 1h, will the secondary refuse to apply the last log received?

Related Question