Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I was thinking if the AlwaysOn architecture in SQL Server 2012 can be leveraged somehow to create a replica for DEV and QA environment properly in addition to setting up HADR using the same architecture?

share|improve this question
Well, you can't write to replicas so it wouldn't make much sense as a dev environment. Even QA would only work for read-only operations. Can you clarify exactly what types of goals you want to accomplish rolling Dev and QA into your production Availability Groups? – Aaron Bertrand May 10 '13 at 21:34
up vote 5 down vote accepted

As Aaron Bertrand mentioned in his comment, AlwaysOn is only designed to support offloading of read-only operations, in addition to its (really neat) redundancy capabilities.

If all you're looking for is a place where your developers can check out the production data without putting load on the production server, then perhaps it's a technology that might be useful - but you can achieve pretty much the same thing with log shipping.

You can build out your dev environment from a standby server - whether it uses AlwaysOn, Mirroring or Log Shipping - by using a Storage Appliance that supports writeable snapshots, or a server that can present writeable snapshots via iSCSI (e.g. a ZFS server). However, you'll probably need extra servers for this, some latitude to mess around with your storage configuration, and possibly someone's shoulder to cry on while you're trying to set it up.

The little "Cloning" video on NetApp's SnapManager website provides a useful overview of how this works - unfortunately I can't seem to find a nice description that's not vendor-specific.

share|improve this answer
Upvoted "someone's shoulder to cry" :) – DiGi May 11 '13 at 8:50

It is tough to determine exactly what you are trying to accomplish here. If by "dev" and "QA" you mean "we only want to be able to run read-only queries against an exact copy of production" then sure, you can use a secondary/replica from your production environment to accomplish that. (Though, how "exact" depends on whether your Availability Group is set up in synchronous or asynchronous commit mode.)

If you are expecting to perform schema changes in dev and/or QA and test those without interfering production, you can't do that - secondary replicas are read only. Perhaps you are thinking that Availability Groups provide some kind of load-balanced merge replication panacea, but that is not what it is for.

How you should proceed depends on exactly what you want to accomplish. For Availability Groups you should absolutely focus on the HA/DR aspects, as those are the problems it is designed to solve. Your QA/DEV requirements - again, unless they are only designed to test read-only operations against essentially a clone of the production database - will need to be satisfied in other ways. Depending on your dev/QA cycle, you may use any of the following or some combination:

  • back up the production database and restore it in dev/QA (and you can use the backups created through Availability Groups of course), say nightly. This keeps your data updated as frequently as you wish. This will typically mean that in the event of schema/code changes, you will need to suspend this process until those changes have been deployed to production (otherwise the next backup will revert your changes).
  • keep a "trunk" in Dev/QA and possibly - in combination with the above - migrate the changes to a restored copy of the production database (allowing you to test your changes against a relatively modern version of the data). You can also bypass the backup/restore and simply apply schema/code changes to production once they've been tested against whatever data is in your trunk. This simplifies the deployment process but introduces more opportunity for regressions / edge cases.

In either case it's possible that the deployment part of migrating changes is your biggest hurdle - there are many tools that can assist with this; I blogged about them here.

share|improve this answer

Restoring database backups from Production to QA and DEV servers is easiest way to manage that.

After restoring you have to apply change scripts. AlwaysOn is "database clone", but for DEV and QA you need "Production DB + DEV changes".

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.