patternsqlModerate
Do schema changes "break" Availability Groups or are they handled transparently?
Viewed 0 times
groupshandledtransparentlyarechangestheyavailabilitybreakschema
Problem
My organization is planning to adopt SQL Server 2012 Availability Groups and I am trying to understand what impact (if any) it will have on our application upgrade process.
We release application updates on an 8 week cycle and any release could include schema changes and/or data migrations.
What I am trying to understand is whether or not the HA/DR solution handles the schema changes transparently (new columns, indexes get added to secondaries) or is manual intervention required to create the schema on each instance and then turn Always On back on.
The data migration piece I'm assuming is handled transparently but would like to confirm that as well.
I guess I am also making a blanket assumption that there is no difference in these behaviors based on the Availability Groups configuration which may be false as well. Please let me know.
In a nutshell; In any given release of my application I may change a very large table (10s to 100s of millions of records) by adding columns to it. Some columns may be "net new" so they can make use of the Enterprise Online schema change functionality. Other columns may be a refactoring of an existing column (FullName gets split into FirstName and LastName) and a migration will be run for each row in the table to populate these fields. Do any of these behaviors require DBAs to change the AlwaysOn configuration or is this handled by default and all secondaries get the DDL and DML statements "for free"?
Thanks for any clarity you can provide.
We release application updates on an 8 week cycle and any release could include schema changes and/or data migrations.
What I am trying to understand is whether or not the HA/DR solution handles the schema changes transparently (new columns, indexes get added to secondaries) or is manual intervention required to create the schema on each instance and then turn Always On back on.
The data migration piece I'm assuming is handled transparently but would like to confirm that as well.
I guess I am also making a blanket assumption that there is no difference in these behaviors based on the Availability Groups configuration which may be false as well. Please let me know.
In a nutshell; In any given release of my application I may change a very large table (10s to 100s of millions of records) by adding columns to it. Some columns may be "net new" so they can make use of the Enterprise Online schema change functionality. Other columns may be a refactoring of an existing column (FullName gets split into FirstName and LastName) and a migration will be run for each row in the table to populate these fields. Do any of these behaviors require DBAs to change the AlwaysOn configuration or is this handled by default and all secondaries get the DDL and DML statements "for free"?
Thanks for any clarity you can provide.
Solution
Schema changes and data changes are essentially the same. It works like traditional mirroring today : what happened in the log on the primary happens on the secondary. Not everything that happens in Vegas has to stay in Vegas. :-)
Where you may want to be careful, is when you have an application that points to the primary, and you update that to match schema changes. But you may have a different application that points to the secondary (e.g. with read-only intent), and that app change would have to be synchronized as well.
Another potential gotcha is when your database that is part of an availability group has references to objects in other databases (e.g. a static lookup table that is stored in a utility database). If those change and the AG depends on those objects, you will have to push those changes manually. The same is true for jobs, server-level logins, linked servers etc. - anything that lives outside of the database and/or is not transactionable. Database users can get orphaned (contained users aside). I know this is probably obvious but wanted to list it explicitly for completeness.
Where you may want to be careful, is when you have an application that points to the primary, and you update that to match schema changes. But you may have a different application that points to the secondary (e.g. with read-only intent), and that app change would have to be synchronized as well.
Another potential gotcha is when your database that is part of an availability group has references to objects in other databases (e.g. a static lookup table that is stored in a utility database). If those change and the AG depends on those objects, you will have to push those changes manually. The same is true for jobs, server-level logins, linked servers etc. - anything that lives outside of the database and/or is not transactionable. Database users can get orphaned (contained users aside). I know this is probably obvious but wanted to list it explicitly for completeness.
Context
StackExchange Database Administrators Q#21404, answer score: 10
Revisions (0)
No revisions yet.