I've been assigned a project where we have an active secondary environment that I'm confident that Availability Groups would address but have some questions that I hope someone can answer for me.

The situation :

I'm trying to resolve isn't the standard business continuity or disaster recovery but to expedite the release of an upgraded (schema changed) environment.

Currently the databases/UI/cubes that support our customer facing systems get an new release every 8 weeks and on that release weekend it will take 24 hours to apply new schema, apply data backfills, and to do system maintenance like rebuilding index and restarting servers.

The hope is to have a duplicate environment that we could update prior to the release date and 'swap' over minimizing the downtime to our customers. I'm primarily concerned with having a form of replicated databases that stay current with regards to data but that the secondary could have schema modifications made to it and I'm not sure if Availability Groups are the best answer for that.

Besides re-architecting the environment is there another technology I should consider to address my need to have 'hot swap' secondary environment that can be upgraded?


Since you mentioned about Availability Groups, this means that you are using SQL Server Enterprise edition (and possibly SQL Server 2012 or 2014).

You can use database snapshots, but in our case there were lot of dependencies on different components and so using database snapshot was ruled out.

Below will require a bit of more planning and testing.

Blue-Green Concept :

The gist of Blue-Green Concept is to divide your production into 2 environments and they are identical all times (data synchronization) wherein

  1. The Blue (Current) will have the current version of the schema/build or product and will be your "LIVE" environment.

  2. At the same time Green will be your staging/testing environment wherein you will upgrade your schema/build or product to the NEXT release, do a full regression test and get signed off by your business users. Once happy, during a cut-over period, you will promote the Green to be your "LIVE" environment and demote the Blue to be a preprod/staging or testing for the next release.

This way, you have a very less downtime and the risk of deployment failure on a live system (which is in maintenance window, since you are doing upgrade) will be highly minimized. Also, following the Blue-Green approach, you will be oscillating between LIVE and PREVIOUS version which will be staging for the next version.

Again, this will require more hardware/licensing as well as planning and testing.

Most of the steps can be automated using DACPACs and PowerShell. Also, if you are installing multiple instances on one server, make sure to re-balance the Memory settings when switching between Blue and Green. The LIVE environment gets more memory than the Passive environment.

In my current environment, we have implemented Blue/Green Model for Agile Code Deployment that allows us to promote code every 2 weeks with ample amount of time for testing and business sign-off. Also, its a breeze to rollback in case something goes horribly wrong. We have automated majority of the deployment stuff using Dacpacs and PowerShell.

enter image description here (Image source)

Also refer to Grant Fritchey's article on Rollback and Recovery Troubleshooting; Challenges and Strategies

| improve this answer | |
  • thanks, that makes sense. So the only question I have, expanding on your example, is that there is an OLTP system that's outside this application but is the source of data. Would it then be that you duplicate the ETL so that both Green/Blue are getting data pushed to them? I know that may complicate things a little but thinking that's the simplest solution vs. trying to replicate/mirror etc. – Jeff A Aug 28 '15 at 20:42
  • Would it then be that you duplicate the ETL so that both Green/Blue are getting data pushed to them? We are in the same situation and we do duplicate the ETL. This is the best architecture that we are enhancing e.g. introducing tripple active, using windows service bus and making both the environments loosely coupled. This way we can do continuous delivery, minimize downtime and be able to rollback with minimal impact. – Kin Shah Aug 28 '15 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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