Q: Deploying SQL Server database changes from test to production server 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:


*

*backup the production server and pause it

*restore it into the testing server

*make all database changes in the testing server

*Backup the testing server

*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?

A: 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:


*

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

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

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

A: For just schema changes, it seems quite wasteful to disrupt production and make them wait while you back up the whole database (even if only one table has changed?), restore and test changes in a different environment, then back up and restore again. Get a tool to do real-time schema compare/deploy to keep your two environments in sync without all that extra heavy lifting and by only changing the objects that have actually changed. My choice is Red Gate SQL Compare, but there are many others with various features and price points:


*

*http://thebertrandfamily.com/2012/04/20/re-blog-the-cost-of-reinventing-the-wheel/
You can minimize the amount of thorough testing you need to do by making your schema changes backward compatible and/or deploy them in phases. I gave some tips on this a couple of years ago:


*

*Adding a new column

*Dropping a column

*Renaming an entity

*Changing a relationship

A: You will need a tool to create scripts that synchronize certain tables in your production database with the tables you have changed in your test environment. 
Visual Studio 2013 in pro, premium and ultimate editions have a schema and data compare feature.
I personally use Red Gate SQL compare.

A: Continuous integration for the database can be implanted through version control and merging. There are a number of ways continuous integration can be implemented depending on the configuration of your development team and the change management processes used. Regardless, DBmaestro (which I work for) has the ability to generate and or execute database changes in an automated fashion, thus becoming an integral part of any database oriented Continuous Integration solution.
A build process can request updates from version control, merge automatically create merge scripts to update the target database change, deploy the database changes, and then execute any tests defined directly against the database, or include the application code as part of the process for a complete integration test.
The problem is that simple compare & sync does not utilize the version control repository when performing the compare and generating the DDL script. For that reason the version control repository does not act as the single source of truth.
Another problem that simple compare and sync runs into is, that because the information is stored in the ALM, CMS, or version control repository, which is external to the compare & sync tool, it compares the entire database and shows the differences, generating relevant and irrelevant deployment scripts.
Most critically, simple compare and sync does not ensure the deployment script handles conflicts and merges them.
Database enforced change management on the other hand, combines enforcement of version control processes on the database objects with generation of the deployment script when required, based on the version control repository and the structure of the environment at that time.
I would suggest reading this whitepaper for more info
