Solved

SQL Server 2012 AlwaysOn

Posted on 2013-10-31
6
358 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
ID: 39615351
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
ID: 39621622
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
ID: 39621698
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Accepted Solution

by:
Favorable earned 0 total points
ID: 39622027
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
ID: 39622128
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
ID: 39791939
Great responses. Thank you very much
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help Required 2 30
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 31
What is the proper way to use for criteria in left join? 7 25
Show Results for Latest DateTime in a View 27 24
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

785 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