Is it possible to use MS VS Database Project as a complete solution for database versioning?

Question

In our project we have several production databases and many devs. Each production database represents some "sub-project/localization version". We use SQL Server 2008.

So, I need to develop database versioning strategy using MS Visual Studio Database Project. I have read a lot of articles about database versioning and database projects, but I still have a lot of questions:

1. How developers should implement theirs changes to db project? (Best practice)

2. How to generate the 100% workable "latest version" deploy script without human intervention (skipping some objects, rewriting some changes, etc)?

3. How to manage data changes with MS Visual Studio Database Project? I know about pre-/post-deploy scripts, but I think it cant resolve this problem. (Example: I need to remap some table into another).

The "ideal solution" would be:

1. Developers generates and maintains Database Project for database [ProductionDB].

2. With new release I deploy Database Project to the [ProductionDB] with all necessary changes.

3. Developers changes Database Project and writes some data manipulation scripts for concrete changes.

4. With new release I deploy Database Project to the [ProductionDB] with all necessary changes.

So, The final question: Is it posible to use Database Project for the purposes described above or somebody uses similar scenario/solution?

PS: I have already read following discussions:

1. Database changes versioning [closed]
2. How do I version my MS SQL database in SVN?
3. Looking for solution for database versioning
4. Is there a version control system for database structure changes?

Answer 1

Database project is precisely used for most of the reasons that you have mentioned here -

1. Developers just checkout the database script files, make the changes, and check them back in. Mind you they will be changing .sql files and not directly the objects present in any dev database. So if you need to add two columns to a database table, you will modify the create table script for this table, and NOT write an alter script for this table.

2. If you have the target old version DB schema - you can just deploy this project with the latest files to that database and a deployment script will get created (with the necessary alter statements). There is a project setting that allows you to opt whether the deployment script should also be run against the db when you 'deploy'.

3. The deployment script can be a deliverable that is separately tested against a prod copy and then applied to prod as a patch.

About data manipulation scripts I am not very sure, however for all other purposes that you mentioned, a database project is perfect.

Answer 2

What you've described is the reason the database project exists.

To answer your questions:

1. It's up to the developer. You can either work in visual studio and edit the project files directly OR edit a live copy of the database in Mgmt Studio and use schema compare in VS to sync the changes back to the project. I've been using it for a few months and find both work fine, though I tend to edit the project files directly more often.

2. Deploying the project generates latest version if you check the box to drop the destination db first. You can do it with no human intervention by calling VSDBCMD during your build

3. The pre/post deployment scripts are meant for just what you allude to. An example would be to select all the data from a table into a temporary table and truncate it during the pre-deploy, let the project handle the schema migration and then add it back during post-deploy. That can get tricky, but that problem has never been easy to solve.

Hope this helps.

Answer 3

The data issue is a difficult one to solve. Here at Red Gate we've had many requests for this for our SQL Source Control tool, which has parallels with VS Database Projects. The good news is that we're actively adding support for this, and should have an early build to try out before Christmas. If you're interested, simply sign up for Early Access notifications at http://www.surveymk.com/s/SqlSourceControl_EapSignup . We'd love to get your feedback.

Answer 4

You may want to have a look at the open-source bsn ModuleStore toolkit, which tries to implement this workflow (and it actually does also do more then that).