SQL Server 2012 AlwaysOn

If you set up AlwaysOn and Availability Group and have on Synchronous mode with two to three replica; what happened during changes? for example, during deployment of changes to production, you want to make sure the changes perform as expected before they are copy to the other replicas and how to rollback after your replicas has been affected?

How do you handle this?

Can you change to Asynchronous during the change implementation and then flip back to Synchronous mode after the changes perform as expected in production?

I've noticed in our environment, changes perform well in QA; however, when implemented in production they turns out to perform differently. This makes me curious and wanting to know what will happen when we implement AlwaysOn?
FavorableAsked:
Who is Participating?
 
FavorableConnect With a Mentor Author Commented:
My question is, how are deployment changes handle in AG?  
Let picture a situation of database or application schema change?  
How will the changes apply to the replicas? What if things don't go the way you expected? How can you roll back with replicas being involved?
0
 
PadawanDBAConnect With a Mentor Operational DBACommented:
Synchronous commit mode requires the changes to be hardened on the secondary replicas' transaction logs before they can be committed to the primary.  So your latency increases with this mode, but at the benefit of protection in the event of a failover.  Asynchronous commit mode means that the primary does not wait for confirmation of the transaction logs being hardened before committing the change, improving your latency at the cost of potential data loss in a failover scenario.  

I would not recommend using high availability groups for this purpose.  If you are wanting to roll back changes, you need account for this in the change scripts.  For instance, if you are updating columns in a table, write the old values to holding table along with the primary key for their associated value.  If it's a schema change, rename the old table to a holding table, create the new table and then backfill the data into the new table.  Again, I don't like using high availability for this purpose, as the very function of it is "High Availability" not "Change Management.", but if you are dead set on this, you could probably remove the database from the availability group and leave it out of the group until you are sure that the changes are desirable.  

QA performance could be linked to many different things.  It could be on better hardware (more RAM, better CPU, better/less contended storage).  It could also have less contention on it for data.  Contention means locking, which means lock waits.  I think we would need more information on the QA vs. Prod environments to accurately give you ideas on that one.

Edit:

Forgot to give you a link for more exhaustive information on the availability modes in AlwaysOn: http://technet.microsoft.com/en-us/library/ff877931.aspx
0
 
FavorableAuthor Commented:
PadawanDBA

Is it possible to set up AlwaysOn without availability group, so it can just similar to regular cluster and avoiding the issue of having to roll back transaction during change deployment implementation?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
PadawanDBAConnect With a Mentor Operational DBACommented:
I'm not sure I quite understand the comparison.  The availability group concept, to highly simplify it, moves the level of granularity to the database(s) and not the entire instance/single database (as you would find in a failover cluster and/or database mirroring - availability groups are a marriage of failover clustering and mirroring).  A cluster is based on shared storage, so when the changes are checkpointed into the datafiles (when the transaction log is hardened, really), they would be there on all instances in the failover cluster.  I would recommend taking a look at other options to satisfy this requirement.  My first suspect to investigate to see if it would meet your needs would be database snapshots: http://technet.microsoft.com/en-us/library/ms175158.aspx
0
 
PadawanDBAConnect With a Mentor Operational DBACommented:
Sorry if I'm not answering the questions in the right context.  I think we're getting there though!  

Changes will be replicated out in either the synchronous or asynchronous commit methods.  The replicas will be exact copies of the primary (minus any changes that weren't hardened in the logs in asynchronous commit mode - for a failure scenario).  It replicates changes made to the primary replica to the secondary replica(s) and either does or does not wait for the transaction log(s) of the secondary replica(s) to hardened and then commits those changes.  If the change that is deployed fails on the primary replica, it will roll back any changes that may have been begun on the secondary replica(s) just the same.  If you were to manually rollback changes on the primary replica, those changes replicate to the secondary replica(s).  I think the main point to understand, if I get what you're asking about, is that AlwaysOn Availability Groups' purpose is to maintain the secondary replica(s) being exact copies of the primary replica - you only need to make changes to the primary (in fact, you can only make changes to the primary) and let AlwaysOn handle the rest.

Edit: Also, for all intents and purposes, DML and DDL changes will be handled just fine by AlwaysOn
0
 
FavorableAuthor Commented:
Great responses. Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.