We have SQL Server 2014 Enterprise installed to run a database which should be available 24/7. Our database is huge enough (200gb +). Also we have a lot of services that hits our database every minute to read, update or insert new data. We want to provide a "hot" redeploy feature for our clients and make our daily updates (.net and schema updates) transparent to clients. We have found a solution based on cluster with load balancer to update binaries of our app, but we still have some misunderstanding about database`updates deployment process and what are the best practices to solve this issue.
For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance.
Can it be accomplished with SQL Server tools, and is this a common approach? How sync data after server is backed up? Or am i completely thinking into the wrong direction and are there better solutions?
Our common Schema changes: add/drop column, add/delete stored procedure
Best Answer
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
The Blue (Current) will have the current version of the schema/build or product and will be your "LIVE" environment.
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.
Also refer to Grant Fritchey's article on Rollback and Recovery Troubleshooting; Challenges and Strategies