SQL Server Database Projects
Tue, Jul 30 2013 11:54
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:
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:
For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.