Getting Started with SQL Server Database Projects
By now hopefully all developers understand the importance of keeping their source code under version control and are actually practising this for any non-throwaway code. That's all fine and dandy for your application, but what about your database? In my experience it's pretty rare for databases to be under version control, probably because in the past the tooling has been inadequate or simply off developer radars. There are a number of tools that can help with database version control but one of most readily accessible for Visual Studio developers is the SQL Server Database Project that can be added to a Visual Studio solution. SQL Server Database Projects are part of the Microsoft SQL Server Data Tools ( SSDT) package, which is obviously aimed at developing against SQL Server. You can start with a blank database but most likely you will already have an existing database in which case the database project has the ability to reverse engineer the schema. The result of this process is a series of files containing CREATE statements for the objects that comprise your database (tables, stored procedures and so on), with the files themselves (usually one per object) organised in a folder structure. Since these are essentially text files just like any other code file you can check them in to version control and have any changes recorded just like you would with, for example, a C# file.
In addition to facilitating version control database projects offer a wealth of extra functionality. A declarative approach is used with database projects, ie you state how you want your database to be via CREATE statements and then another process is responsible for making the schema of one or more target databases the same as the schema of your database project. You can also publish your schema to a new database -- ideal if you need to create a LocalDB version on a new development workstation for example. This is really only the tip of the iceberg and I encourage you to use the resources below as a starting point for learning about database projects and SSDT:
- SQL Server Data Tools Homepage
- SQL Server Data Tools Forum
- SQL Server Data Tools Team Blog
- Declarative Database Development with SSDT
- Visual Studio Data Tools for Developers (requires Pluralsight subscription)
- Using SQL Server Data Tools SSDT
- The SSDT way or the highway (sqlproj)
- Real world SSDT
- Who says you can’t deploy your database every day? SSDT as used at Xero
Since SSDT is built-in to Visual Studio 2013 the barrier to getting started is very low. Be sure to check for any updates from within Visual Studio (Tools > Extensions and Updates) before you begin. Finally, anyone who has spotted that the SQL Server installation wizard has an option to install SQL Server Data Tools has every right to be confused, since at one point in time this was also the new name for what was once BIDS (Business Intelligence Developer Studio). If you want to know more then this post and also this one will help clarify. Maybe.
Cheers -- Graham