What is a good practice/tool for MSSQL database design and versioning?

Question

We work with a small development team and a system engineer who deploys the software at certain times.

At the moment we don't have any leading document for database changes. Changes are done in the staging database and added manually to production when the system engineer deploys a new release.

We started to work with DBDesigner Fork to create the design and generate the SQL create script, but we are still not happy with this solution. The solution is incomplete, because it's impossible to create views and stored procedures in DBDesigner Fork for example.

We would like to find a practice/tool to design the database, stored procedures, views, etc. and version the changes in Subversion.

When a new software release is created (tagged in SVN), we should be able to create a patch file for the database (a SQL script for example).

What is the best way to handle the issues described above?

Answer 1

We are a $2.5B solar manufacturing company that is using Visual Studio Database Edition for all our database management and versioning needs. It has been a very good tool for us. It version controls our databases with TFS integration, does schema and data compares (like Red-Gate), database validations and much more. We couldn't live without it now.

Answer 2

We use source control for versioning. All databases changes must only be made in scripts and placed in source control as part of the branch of the code that goes to production with the rest of the code changes for that version. Since devs don't have any rights to create or alter objects in Prod, this works out well for us as your changes don't get moved up unless they are in a script.