asked
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 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?| This example is only for Secondary Read-only Asynchronous. Also What if the Secondary Replica is Read-Only Synchronous Thanks,
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][1] [1]:
https://msdn.microsoft.com/en-us/library/jj542414.aspx
17 People are following this question.
