AlwaysOn DDL and Schema Changes

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


(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?| This example is only for Secondary Read-only Asynchronous. Also What if the Secondary Replica is Read-Only Synchronous


more ▼

asked Jul 09, 2017 at 09:27 PM in Default

avatar image

0 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

DDL operations on the Primary will not be affected by any reads on the secondary. They will be applied as and when the objects are free to be locked as such.

Read-only workloads will take schema-stability locks, but this would only block any schema modifications on the secondary, these locks never affect the primary. Async/Sync defines whether the log is hardened to disk before the transaction commits on the primary, not whether the operation has actually been applied - that's the job of the REDO task. The REDO thread can be blocked by schema locks - you can monitor this with the log_redo_blocked XEvent.

For further reading I would suggest this document from Microsoft : AlwaysOn Solution Guide: Offloading Read-Only Workloads to Secondary Replicas

more ▼

answered Jul 10, 2017 at 09:34 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 09, 2017 at 09:27 PM

Seen: 411 times

Last Updated: Jul 10, 2017 at 09:34 AM

Copyright 2018 Redgate Software. Privacy Policy