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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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:
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.
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".