I won't go into the details of how to create and use Visual Studio 2010 database projects here (since I am developing a thorough document on the subject) but what I will do is tell you why they are indispensable. In the end it comes down to one thing - PRODUCTIVITY.
If you are thinking about using Visual Studio 2010 database projects but aren't quite sure why you should, read below. If you find these arguments compelling enough than go and give it a try.
1) Source code structure - Visual Studio database projects force the organization of all database source code into an intuitive and somewhat customizable structure that makes all objects easy to find, modify, and version.
2) Dependency checking - The Visual Studio build engine performs dependency checks. If a column name is changed it will immediately identify any dependent database objects such as views and stored procedures that have been adversely affected, saving the time of tracking down bugs after the code has been deployed which eliminates the time necessary to fix the bug(s) and redeploy the database to the affected environments. It can also perform dependency checks between databases. If you have a core application database as well as other system databases such as an audit/logging database, the tool can perform cross-database dependency checks whether or not the other database exist as Visual Studio database project.
3) Deployment scripts - The Visual Studio database project allows for pre and post-deployment scripts that can do things like move files to certain places in preparation for a database deployment, check versions to ensure compatibility prior to deployment and stop deployment if something is not right, populate tables with reference/lookup data after the deployment.
*Keep in mind that deployment includes both the initial creation/installation of the database as well as the upgrade of an existing database that is out of date and even the rollback of a database to a past version.
I have used this tool in conjunction with InstallShield to create install/upgrade processes for a commercial software product. It worked great.
4) Parameterization of any script values - The project allows for the parameterization of just about anything within the source code such as database name, database server, database file locations, etc. so that the same set of source code can be deployed to any environment, without any code modification, by passing in environment specific parameters. Any number of configurations and corresponding sets of variables can be created (e.g. Dev1 Local Dev, Dev2 Local Dev, QA, UAT, PROD) allowing for a two-click (choose the environment and click deploy) deployment to any existing environment.
5) No need for deployment “sub-system” code - The Visual Studio deployment engine removes the need for an abundance of code that would otherwise be necessary in a manual deployment environment. Code for things such as object (table, view, stored procedure, index, function, etc.) existence checks and drops, foreign key drops and recreation, wrapping of scripts in transactions, etc. is automatically generated by the tool. All that is needed is a single “create” script for each object. This dramatically reduces the complexity of source code by eliminating the need to manually create all the deployment scripts necessary to create or upgrade a database.
6) Fast database deployment on any machine - Visual Studio comes with freely distributable executables that provide the ability for non-database developers (ETL, Report, Dashboard developers) to deploy a complete data warehouse (or any other kind of database) with a single command line, .bat, or PowerShell script without the need for a Visual Studio license.
7) Complete build and deployment solutions - Using build automation tools such as NANT or MSBuild we have the ability to package up the above command line deployment capability with tools like the freely distributable SSIS dtutil executable that automates the deployment of SSIS packages and/or RS.exe for report deployment. Taken to this level, we can deploy many if not all SQL Server related software artifacts using a single call to the automated build (NANT, MSBuild) or install (InstallShield, Wise) tool of choice.
I'm sure this doesn't cover everything but it should give you an idea as to how Visual Studio database projects can make all things database source code related much easier and more efficient. Getting started takes about a half hour if you have a pre-existing database and use the import wizard. I strongly urge you to conduct your own experiment so that you can begin to visualize just how powerful a tool this can be.
