SSDT: Why Use SQL Projects?

What Are SQL Projects and why use them?
SQL Server has always been lacking in a good solution to control versions of your database. We make changes directly to the database and if we’re good, we remember to take backups. If we’re really good, we save scripts so we can make those changes elsewhere. Too often, we resort to tools for schema differences or try to save up all of the scripts to replay them later. Both have some advantages. Schema differences guarantee that you can make two environments look the same. Scripts can be replayed in order to keep the systems in sync. However, a schema difference doesn’t handle data changes well and can’t handle making partial changes when not everything is ready to promote. Sets of scripts can also work well, but fail if you end up needing to push sub-sets of those scripts because certain functionality isn’t going to be released yet.
Borrowing from Gert Drapers’ excellent discussions on Database Development Challenges:

• Databases are inherently stateful
  • ALTERs are generally run instead of DROP/CREATE
  • Dependencies can greatly complicate the changes and scripts
  • Data needs to be persisted
• Databases are often not integrated well with the application development life cycle
• DB Versions are frequently not persisted or managed well
• SQL Scripts may not handle different SQL Server versions well

Sequential Change Scripts

• Required to run in a set order
  • Script 1, Script 2, Script 3, …
  • Can’t do Script 1, Script 4, Script 9
• Validating the end state after script runs vs. an expected end state can be complex
• Could easily miss a script in the sequence without knowing
• Handling a release where scripts need to be “skipped” can cause cascading dependency problems
• Can require extensive use of IF NOT EXISTS statements
• Manual process
• Point in time model
• Used by systems such as DBDiff

Declarative Model

• The model is the desired end state
  • Compare the current state of the target against the desired end state
  • Use the differences to generate an upgrade plan
  • Use the upgrade plan to generate a change script, correctly ordered to bring the target up to the desired end state
• Generated / Programmatic process
• Always current
• Used by Red Gate, Microsoft, DB Ghost

Microsoft and Red Gate have both provided solutions to store versions of database schemas. While Red Gate offers a good product, it also requires some external tools to provide a complete solution for schema control. Until recently, Microsoft’s solution wasn’t accessible to everyone because it required a copy of Visual Studio to handle even the basics. This improved in Visual Studio 2010, but still required a separate purchase. With SQL Server 2012, Microsoft introduced SQL Server Data Tools and SQL projects.
With SQL projects, developers can store a version of their database objects in a source control system of their choice. Tables, stored procedures, views, and even permissions can be stored in such a way as to provide upgrades to databases and even build a new set of databases from scratch. This could even be used to build and release these databases continuously in an automated fashion to release new code.