Full disclosure, definitely not a DBA. However, I've put together the following solution design to try to achieve a Blue/Green SQL Deployment.
See for Definition of Blue/Green: https://martinfowler.com/bliki/BlueGreenDeployment.html
Back Story: We currently have 2 servers already setup in AlwaysOn, and my design is based upon a modification of what we currently have. The Goal here would be to add a 3rd Replica, another AlwaysOn Group with Dedicated Blue and Green endpoints.
I do not know if this is physically feasible though.
- Can I have two Groups like this on the same server?
- Can I remove a Replica w/o destroying it?
- How long does it take to remove a Replica?
- Can I add a Replica as a Primary, w/o destroying it?
- How Long does it take to Add a Replica?
- How Long does it take for Replication from Primary to Secondary take? (This is obviously a loaded question w/o knowing how large are DBs are.)
- The off-color database is still accessible, but read-only
- Green has a dedicated endpoint, that does not change.
- Blue has a dedicated endpoint, that does not change.
- I want to be able to Use a Replica from one Colored AG as a seed for the Primary of the next AG.
- Transactions stop at Step 01, and resumes after Step 05.
- As an administrator, I want my blue/green databases at sync at all times (except during the release), so that I can deploy at any given time, without the need to backup and restore.
- As an administrator, I want the ability to switch databases sync direction in under 5-10 minutes, so that I can release without prior notice and little downtime.