Solved

SQL Server 2012 AlwaysOn

Posted on 2013-10-31
6
349 Views
Last Modified: 2014-01-19
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?
0
Comment
Question by:Favorable
  • 3
  • 3
6 Comments
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 500 total points
Comment Utility
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
 

Author Comment

by:Favorable
Comment Utility
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
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 500 total points
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Accepted Solution

by:
Favorable earned 0 total points
Comment Utility
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
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Favorable
Comment Utility
Great responses. Thank you very much
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now