by jrob5756

The Forgotten Database Project

I’ve observed a wide variety of methods for managing and deploying SQL (Sprocs, Schema, etc). I’ve seen methodologies from extremely elaborate home grown frameworks to managing custom DDL scripts like shown below.

IF NOT EXISTS
(
    SELECT
      *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dbo'
      AND TABLE_NAME = 'Part'
)
BEGIN
    CREATE TABLE [dbo].[Part]
    (
    ...
    )
END

On a recent project I’ve worked on, I decided to go back to the basics. Visual Studio supports SQL Server database projects. These projects have been supported for a while now but, I’m always shocked to find how many people are unaware they exist. That said, I thought I’d give a high-level overview of how these projects work and how to set them up.

Creating your database

First you will need to create a new project.

File | New Project | SQL Server Database Project

New Project

Once this is complete, you can begin creating your database. If you right click anywhere in your project you will notice you have the ability to create a number of SQL objects.

Most of these options such as Stored Procedures will provide you with a sql window where you can define your sproc. For the ‘Table’ option, you can either script out your table definition or use the designer.

New Table

Deploying your database

Once you are completed with your database, you need to deploy it somewhere. It doesn’t do much good sitting in visual studio. There are a couple ways to accomplish this. First to view differences between your database project and where you would like to deploy, you can perform a Schema Compare by right-clicking your project. This allows you to observe all detected differences with a nice diff view as well.

Schema Compare

From here you CAN select update to push out your changes but, I don’t necessarily recommend that. This will bypass a couple features such as Pre/Post Deployment scripts. The Publish feature is available by again right-clicking the database project. This will popup the Publish Database dialog.

Publish

From here you can publish your database anywhere you’d like of course including SQL Azure.

What are some other tools?

Of course there are many other tools. For example, if you are using Entity Framework, maintaining your database can be done through your entity classes with Entity Framework Migrations.

Another tool I’ve used in the past is FluentMigrator. This tool essentially allows you to create migrations with… you guessed it, a fluent interface. I’ve had a lot of success with FluentMigrator and would recommend this if you are interested in maintaining schema changes in C# classes.

Summary

The point I wanted to get across is you don’t have build some elaborate deployment system or settle with some unmaintainable sql scripts. There are plenty of options in between.

If you are using SQL Server, how are you managing your schema changes?