SQL Database Projects

In Visual Studio, you can create SQL Database Projects to handle deploying / updating databases. When deploying, it will modify the target database to match the state of the source database. Meaning: you don’t set up a script to create a table. Rather, the database project knows the schema of the table, and the deployment will determine what differences exist and alter the target table as necessary, adding fields, constraints, etc.

There are some of walkthroughs available from MS:
http://msdn.microsoft.com/en-us/library/xee70aty%28VS.100%29.aspx

There is also a codeplex project with additional documentation:
http://vsdatabaseguide.codeplex.com/

 

Following are a couple other notes, in no particular order:

Data

The deployment will update table schemas and objects, but not data. You can certainly add a post-deployment script to insert data, but the script should be written in such a way so that it can be run multiple times without issue.

Bill Gibson has a good post on this with sample code:
http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx

 

Post-Deployment Scripts

To add a post-deployment script, r-click on the project à add à Script.

To change the type or disable a script, select it in the solution explorer, and change the “BuildAction”.

Post-Deployment scripts are run after the schema changes have been made, and before the check constraints have been re-enabled.

 

Publish

In the publish dialog, after filling in the form appropriately, and before hitting publish, click on “create profile”. The next time you run the publish, just click “load profile” and select the file (the file name should be projectname.publish.

One of the advanced options is “Generate Smart Defaults”. For example, if an int field was added with no default value, the smart default option will set that field to zero. If that field is a foreign key, it will still be set to zero and re-enabling the check constraint will fail, so set proper field defaults.

 

 

 

 

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s