Solved

SQL Server 2012 AlwaysOn

Posted on 2013-10-31
6
356 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

930 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

12 Experts available now in Live!

Get 1:1 Help Now