SQL Server Database Projects

Posted Tue, Jul 30 2013 11:54 by Deborah Kurata

Visual Studio 2012 introduced yet another new database project. This new database project not only manages your scripts, it also provides GREAT features for deploying your database changes.

In my prior posts on the SQL Server Data Tools (SSDT), I focused on the SQL Server Object Explorer. The SQL Server Object Explorer allows you to create and update databases, tables, stored procedures, data, and many other types of SQL Server objects and apply those changes directly to the database.

See this link for an introduction to SQL Server Object Explorer.

Using the SQL Server Object Explorer to implement and maintain your database design works well for a demo or when doing some debugging, but in a real development scenario you need better control over your database scripts.

  • What if you make an error or the customer changes their mind and you need to back out a change? If you committed your database changes directly to SQL Server, you don't have a way to track what you changed.
  • What if you need to share the database changes with your team members?
  • How will you track the set of database changes that need to be deployed to the Test database and then to the Production database?

That's where database projects come in. Visual Studio provides a special database project type to help developers manage their database scripts and check them into source control, such as Team Foundation Server (TFS).

NOTE: Visual Studio provides a conversion tool to convert your Visual Studio 2010 Database Projects to Visual Studio 2012 Database Projects. I would not recommend using it. Rather, ensure your development database has all updates applied, then create a Visual Studio 2012 Database Project from your database as defined in the first link below.

You can use the SQL Server Database Projects with SQL Server 2005 or above:

image

There are many benefits of using a Database Project to manage your database scripts:

  • Each script can be checked in and managed with your source control product such as Team Foundation Server (TFS).
    • This makes it easier to share changes with your team.
    • This allows changes to be tracked.
    • Changes can be reversed if necessary.
  • You can work disconnected from the actual database.
    • You can design your database on a plane and then publish the database to your development SQL Server when you reconnect.
  • Deploying database changes are significantly easier.

The following are links to posts on using SQL Server Database Projects:

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

Filed under: , , , , , ,

Comments

# Database Projects and Refactoring

Friday, August 02, 2013 11:56 AM by Deborah's Developer MindScape

There are often times that you need to update a database schema. But if that database is already in production

# Scripting Reference Data with SQL Server Object Explorer

Monday, August 05, 2013 10:31 AM by Deborah's Developer MindScape

Database projects are great for scripting the tables and stored procedures for a database, but what about

# Creating a Merge Script for your Reference Data

Monday, August 05, 2013 2:04 PM by Deborah's Developer MindScape

When you create a database project data script using the SQL Server Object Explorer, it provides a set

# Publishing Your Data Scripts

Tuesday, August 06, 2013 10:14 AM by Deborah's Developer MindScape

An earlier post detailed how to publish the schema and stored procedure scripts in your SQL Server Database

# SSDT SQL Menu Options

Wednesday, August 07, 2013 9:09 AM by Deborah's Developer MindScape

The SQL Server Data Tools (SSDT) in Visual Studio include features in three areas of Visual Studio: SQL

# SQL Server Data Tools (SSDT)

Monday, August 12, 2013 9:40 AM by Deborah's Developer MindScape

We all use some type of data in our applications: configuration information, game scores, map coordinates

Leave a Comment

(required) 
(required) 
(optional)
(required) 
If you can't read this number refresh your screen
Enter the numbers above: