Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My organization is planning to adopt SQL Server 2012 AlwaysOn 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 AlwaysOn 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 AlwaysOn 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.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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 schema outside of the AG 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.

share|improve this answer
    
Contained logins should get carried with the database, correct? (I assume you meant server logins.) –  Jon Seigel Jul 24 '12 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 '12 at 17:22
    
Ah, I see now. Thanks, I haven't worked with the new 2012 goodies very much. –  Jon Seigel Jul 24 '12 at 17:28
    
@JonSeigel I updated the answer to call those out explicitly. –  Aaron Bertrand Jul 24 '12 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 '12 at 18:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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