Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2012 AlwaysOn

Posted on 2013-10-31
6
Medium Priority
?
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 2000 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 2000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

618 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