12

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.

Improve this question
1

2 Answers 2

Reset to default
10

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.

Improve this answer
7
  • Contained logins should get carried with the database, correct? (I assume you meant server logins.)
    – Jon Seigel
    Jul 24, 2012 at 17:21
  • 1
    @JonSeigel contained users, yes. No such thing as contained logins. Not being picky, just want to make sure the expectation is correct. Of course this requires that all nodes have contained database authentication enabled and that the databases are, in fact, set to CONTAINMENT = PARTIAL.
    – Aaron Bertrand
    Jul 24, 2012 at 17:22
  • Ah, I see now. Thanks, I haven't worked with the new 2012 goodies very much.
    – Jon Seigel
    Jul 24, 2012 at 17:28
  • @JonSeigel I updated the answer to call those out explicitly.
    – Aaron Bertrand
    Jul 24, 2012 at 17:28
  • Thanks Aaron. Some concerns were raised about schema changes breaking replication and I wanted to confirm that AlwaysOn (mirroring as you described) does not exhibit that behavior. I'm guessing this is a misunderstanding or related specifically to replication.
    – Matt O'Brien
    Jul 24, 2012 at 18:25
0

More answers here from Remus, users are asking to possibly remove queries on secondary replica and check redo queue size status in table: sys.dm_hadr_database_replica_states AlwaysOn DDL and Schema Changes

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.