Post reply

how should a team use a database project?

  • August 11, 2015 at 11:28 am

    #301836

    Hi, how should a team use a database project? I've created the database project in Visual Studio through the standard create project navigation. Now I'm trying to figure out how a team should use this database project.

    The project team that I'm currently on has been using sql scripts for database changes. These scripts are stored in SVN for source control. A custom deployment tool deploys these scripts from SVN to the different database environments.

    Here are a few scenarios for our current DB change management process. Please let me know how these scenarios should be handled in the context of a database project:

    1. Modify a stored proc. Include drop if exists SQL at top. Then CREATE SPROC.

    2. Create a table: Check sys.objects to see if table exists. If not then CREATE TABLE.

    3. Add a column: Check information_schema.columns to see if column exists. If not then CREATE COLUMN.

    Also, is there an ordering mechanism to ensure that scripts are executed in the correct order based on dependency or is a DB proj smart enough to figure this out automatically?

  • August 12, 2015 at 5:26 am

    #1819261

    The SQL Server Database Tools (SSDT) can do most of the work it sounds like you're doing manually. You just have to use the CREATE PROCEDURE syntax to put the procedures into SSDT (same with tables, etc.). It will then figure out the dependencies and deploy them in the correct order. It's what the tool is designed to do. The only issue you'll hit with it is when you have changes are potentially destructive, let's say you're adding a not null column without a default value, or splitting a column into two new ones. There, you'll need to use the pre-deployment scripts to ensure you manage the data move without letting the tool do it for you. But for the vast majority of changes (97% or more) will just work.

    Here's a blog from an ALM MVP describing the basics.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply