Sql-server – Deploying SQL Server database changes from test to production server

sql serversql server 2014sql-server-2012

I have a high availability production server that runs SQL Server. It is constantly being read/write to by hundreds of users.

I have an exact copy of the database on a testing server and do all the development in that environment. Every few weeks, there will be major changes made to the front-end application and the database itself (e.g. new stored procedures, new columns, new tables).

Because the data on the production server is always the latest, I don't want to lose that data when I want to make changes to the database schema. The current process for deploying changes is:

  1. backup the production server and pause it
  2. restore it into the testing server
  3. make all database changes in the testing server
  4. Backup the testing server
  5. Restore the new version to the production server and un-pause it

The steps seem convoluted and is causing unnecessary downtime on the production server. What I would like to do is update the production server database in the background while still allowing users to continue reading/writing the database.

So far I can see that you can use copy database wizard or use scripts to make changes, but they seem to want to drop/create the database on the production server to make changes. How could I achieve a less severe way of updating the production server's database?

Best Answer

There was excellent tool Adept SQL Diff, lightning fast, but it's not supported anymore and does not work on Windows 2008 onwards. So Redgate is the choice.

Maybe it's a good time to consider Continuous Integration (CI) and Agile methodology. You will need SSDT (SQL Server Data Tool) and a build software -Team Foundation Server, Bamboo, Teamcity etc. We use TeamCity, it allows you to have up to 20 projects for free. And of course, you will ned source control software, like Git, SVN or TFS.

It's a bit tricky to setup that kind of environment but once you are done and all is tested and works, your deployment will be flawless and literally "at one click".

First of all you will need to do reverse engineering and covert your production database into SSDT project. If you have many databases, you need to convert each one separately. After that you create your CI projects using your chosen build software.

You will need 3 or 4 separate environments:

  1. Integration or test. If you do no want to run unit tests you don't need it. But most people in modern world have it to reduce regression testing. So here fresh sparkling database is created at each build and there you automated tests are run with some predictable results. So you need to fill that database with some test data. We usually do it by SQL scripts or SSIS packages. If you have small project you can do build every hour, if there are commutes (changes). Or run it on nights, if you have 20K unit tests to do.

  2. UAT or Staging. This is where you test your deployment on live data. Before making deployment you restore production backup to the UAT database and run your deployment there. You may want to distort your sensitive customer data somehow. All errors of the deployment are noted and fixed. QA team does user acceptance testing there, if necessary. You run your deployments until you have green tick against run results.

  3. Production. After all acceptance stuff and testing is done you can roll out your changes to production. Since you tested everything before all should go smooth. At the time of deployment you cut access to the website or database, but usually deployment the schema and small data changes take seconds.

Your typical deployment project comprises many steps, so you can deploy your website(s) and database(s) altogether and the best thing out there is project templates for centralized setup.

I did not mention 4th type of environment which is ‘Development’. Every developer has his own copy of everything (it is actually Integration environment) so can he can run/check deployment and unit tests on local workstation before he commits code to the central repository.

You don’t need 3 physical servers to have all above. You can either use virtual machines, SQL sever instances or just name your databases differently for each environments using project parameters.

Related Question