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

  • What's your current layout of SQL? Clustered? AlwaysOn? Mirrored? A single instance? Jun 17 '15 at 15:37
  • Nowadays we have single instance, but we can change it according to requirements, add new server and so on Jun 17 '15 at 15:40
  • 4
    You should follow blue-green approach, this way you can have a live system (green) and do the upgrade on the staging (blue) and then switch roles. This is what we have implemented using AlwaysON and it works for us - nearly same scenario. It does require proper planning, implementation and testing.
    – Kin Shah
    Jun 17 '15 at 15:45
  • Could u give me please more detailed scenario, for example for column deletion schema changes? Jun 17 '15 at 16:13
  • Explanaton of previous comment, i switched primary(S1)/secondary(S2) nodes in AlwaysOn infrastructure, then i decided to remove some columns from schema on Replica (S1, it was primary before switch), but i can still get some replication data from S2 with that column in table .... how can i deal with this issue? Jun 17 '15 at 16:44

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


If your database is not replicated, so, add and drop columns will be run really fast. Because add column is just a empty position that SQL create. Drop column will just erase the reference.

Otherwise if there's some constraint or index on it be careful.

ADD/DELETE procedures just will be effect on the execute itself. The recommendation is before any change on it run the recompile

sp_recompile 'myproc'

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.