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
- Add/modify/delete columns on table
- Add/modify/delete table foreign key constraints
- Add/modify/delete clustered and non-clustered indexes
- Add/modify/delete default constraints
- Add/modify/delete stored procedures and functions
- Add/modify/delete triggers
- 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)