How do you manage sqlserver database projects for new builds and migrations?
How do you manage your sql server build / deploy / migrate database for visual studio projects?
We have a product that includes a reasonable part of the database (~ 100 tables, ~ 500 procs / functions / views), so we need to be able to deploy new databases of the current version, as well as upgrade old databases to the current version. We currently support separate scripts for creating new databases and migrating between versions. Not perfect at all, but how does anyone else handle this?
This is difficult for us as many clients have their own db instance, not just having dev / test / live instances on our own web servers, but the processes involved in managing dev / test / live for others should be similar.
UPDATE. I would rather not use any proprietary products like RedGate (although I've always heard they are really good and would consider this as a solution).
a source to share
We are using Red-Gate SQLCompare and SQLDataCompare to handle this. The idea is simple. Both comparison products allow you to save a complete image of a schema or data from selected tables (for example, configuration tables) as scripts. Then you can compare any database with scripts and get the script change. We store the scripts in our Mercurial source controller and tag (label) of each release. Support can then get the script for any version and use Redgate tools to build from scratch or upgrade.
Redgate also has an API product that allows you to perform a comparison function from your code. For example, this will allow you to have an automatic update feature in your installer or in the product itself. We often use this for our hosted web applications as it allows us to more fully automate the deployment process. In our case, we have an MSBuild task that support can execute for automatic deployment and updates. If you distribute to third parties, you must pay a small additional license fee for each distribution that includes the API.
Redgate also has a tool that automatically packages the database for installation or upgrade. We do not use this method as we have found that comparing against scripts for the version gives us a lot of flexibility.
Redgate tools also help us with development because they make it trivial to initially manage schema and configuration data in a very granular way (each database object can be put into its own file).
a source to share