2

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.

Questions:

  • 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.)

Acceptance Criteria:

  • 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.

User Story:

  • 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.

enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here

closed as too broad by mustaccio, LowlyDBA, Colin 't Hart, Marco, MDCCL Jan 10 at 17:10

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Almost everything under your Questions would be best answered by spinning up a dev environment with the same cpu, memory, etc as your prod environment and test these--especially the ones about "how long". "Yes" is the answer to the other questions that weren't about "how long". Removing a replica just breaks it's sync. It doesn't disappear or anything. Your Dev environment would allow you somewhere to test your steps, which you'll want to do before putting this kind of architectural design into production. – scsimon Jan 9 at 20:49
  • I know you can't answer the how long does replication part, but I would imagine, the how long does it take to add/remove a replica answer, should be pretty standard? – Devin Gleason Lambert Jan 9 at 20:56
  • And yes, I am working on duplicating my prod environment now to test this out, but was more looking for any obvious indicators, where someone might say, nope that's not possible, or if I'm way off base on my understandings before I get to far. – Devin Gleason Lambert Jan 9 at 20:57
  • Removing would be pretty quick (seconds). Adding depends because you need a copy of the primary which means a backup and restore and then wait for the sync to catch up after you add it to the AG. Depending on the size of the DB this takes longer of course... and how many transactions happened in the process. – scsimon Jan 9 at 21:01
  • And I’m purposely not commenting on “am I off base” because there are far, far smarter people on this subject than I which I expect will answer :) – scsimon Jan 9 at 21:07