This is the first in a series of posts about working with Visual Studio Database Projects. I’ve used Database Projects for a long time now, and want to share some of my learnings with others who are just getting started. I will assume that you have been using Visual Studio 2010 and Database Projects (dbproj files) for awhile, and have a basic understanding. I’m going to focus on some of the features where Database Projects have either added value for me, or drove me crazy.
Project Properties (.dbproj)
||Open your solution/project in Visual Studio 2010. In the Solution Explorer, right-click on the Database Project and select Properties. If your database uses one schema more frequently than others, change the Default schema value to save yourself some keystrokes.
||Most of the general database properties are buried behind that Edit button in the lower right corner. Click on Edit, and you will see a long list of options that you won’t change often. However, some of the defaults are NOTthe same as SQL Server default values.Operational DBAs should take note that if their Development DBAs or Developers have created new databases using a Database Project – Page Verify is defaulted to NONE
||For DBAs, the most important take away here is to know that References will be built BEFORE the referencing project. So, if you have a DataOgre project/database that uses objects in the Donkey project/database, in the DataOgre project you would set a reference to the Donkey project to make sure that the Donkey database is always built (and deployed) before the DataOgre database. Personally, I never set my Database Project references from this tab. I always right-click on references within Solution Explorer. It is far more convenient, and opens the same dialog. We’ll cover references in depth in a future post, as there is a lot to explain there.
||There are two very important things here:
1. Treat warnings as errors checkbox – I prefer to leave this checked, so builds fail when there are any warnings. There are some warnings that are aggravating to deal with, but for the most part these should be fixed before putting the code into production.
2. Suppress Warnings: If you want to globally suppress warnings (not recommended), this is the place to do it. We will talk about suppressing individual warnings later.
|| This tab is very important, and I find myself in here tweaking the following:
1. Configure deployment settings for: I think the available values are confusing, so people don’t understand what this is for. Basically, My project settings is the group of settings on this tab that will be used by your entire team. When you check your code in (you are using TFS, right?), these are the set of files that get checked in. My isolated development environment should be used when you want to use a different group of settings for your own workstation. The reasons why you might do this will become clearer later in this section.
2. Deploy action: There are times when I just want to preview the SQL generated by a build, and don’t want it deployed. This is where I can control that.
3. Target connection: In development this is generally pointed to your local development box. However, there are times when it is nice to be able to point to another box and update the schema there from Visual Studio. I have seen cases where vsdbcmd takes longer to ‘upgrade’ a database than a comparable SQL script that I would write, but I don’t ever recall seeing it get something just plain wrong.
4. Deployment configuration file: clicking on Edit here will show you a large list of options, the advanced options are described well and you should be aware that they exist here. Some of the other options warrant further discussion:
– Always re-create database : This means drop my existing database before deploying, so I don’t have to deal with pesky upgrade issues.
– Block incremental deployment if data loss might occur. IF Always re-create database is NOT selected, this option will prevent data loss. As in, if it detects that data loss is possible, it will abort the deploy.
– Generate DROP…: The default is to only put new objects in an existing database (ie, if upgrading an existing database and a bunch of tables exist that were created via SELECT INTO for debugging purposes, go ahead and leave those there)
5. Sql command variables file: This comes in VERY handy if you need configurability in your SQL databases. For example, if you had the need to create many of the ‘same’ database, but configured slightly differently, SQLCMD variables will become your best friend.
||Visual Studio has some Code Analysis rules built into the product than can be verified on build. You should check through the list, and maybe try turning it on to see what it finds lurking in your database. You can always either turn it back off completely, or let it find errors and selectively tell it to ignore those errors in one of 3 ways:
1. Uncheck Enable Code Analysis on Build (or the specific rule that you globally disagree with).
2. As it finds errors during builds, right-click from the Errors Window and tell it to suppress those specific warnings. You are telling Visual Studio ‘I know that I did that, and it’s OK this one time. Warn me again if I do it anywhere else.’ A StaticCodeAnalysis.SuppressMessages.xml file will be created in the project containing all of the suppressions.
3. From Solution Explorer, Navigate to the particular file where you are getting warnings and view its properties. One of those properties is Suppress Warnings, and you can put the specific warning that you want suppressed here as well.