Our company would like to implement Always On Availability Groups. The Final PRODUCTION Plan is for Primary Replica in one state, and Secondary Replica in another state location. The secondary replica will be Read-only Asynchronous.

When creating the Devops Previous Instances, do you typically setup Alwayon Availability Groups in the Dev, QA, or Staging Environment. So Should I setup another SQL Server Instance/Availability Database for any environment? I believe our Test Staging deployments should always be setup to closely be mirroring Production, and at the very Least, I should setup AlwaysOn for Staging Env (we can skip Dev and QA if required). Financial and getting resources approved is always a balance in any company.

By the way, the QA or Staging Secondary Replica data would transfer from the QA or Staging Primary Data Replica



It's a balancing act between risk and cost. The least risk highest cost is to have the exact same everything between production and your development environments (Dev, QA, Staging, Whatever you want to call them). Everything the same means same hardware, same san, same network, same QoS (if in place), same everything.

The highest risk, lowest cost is to have the minimum possible for dev (less environments (or no environments, I've seen developers just use production), less hardware, no replication, etc). and have an educated guess for what will happen in production.

Risks involve things like how long will it take for a task to finish on live AND replicate, what does that do to the log file? Will this script work in an availability group? Can it complete in the time required for a maintenance window? Will the replication complete within a maintenance window? What happens when we do Windows Patching?

Costs involve hardware, but also time. A lot of companies have some sort of process to restore (and sanitise) data from production back to dev/test environments. How long will it take to restore back from a live environment to a dev environment (assuming you do)? How does that process work? How is it different between a single instance dev environment and an AAG environment? What's the cost in maintaining and developing against multiple AAGs?

As you can see there are many different things going in to your question, and it's not a one case fits all scenario. The less your dev environment looks like live, the cheaper it is to build and maintain, but the risk is you don't know what is going to happen when something goes live.

My preference is to always have 1 environment that replicates live as close as possible, and that everything you do on live is tested there first. From there you need to work out the risk/cost ratio, or to put it another way, how much is the business willing to spend to reduce it's risk, and what risk is acceptable (there's always risk).