Back in 2012, I had the pleasure and privilege of working with one of the sharpest developers I’ve ever met. He was a .NET lead of the application development team and I was a BI guy responsible for developing the backend database. Over the course of this 8-month project, thanks in large part to Jonathan, I was introduced to 2 very-cool technologies: SQL Server Service Broker and the SQL Server Database Project. The first provided me with a very new and interesting way of designing reliable asynchronous batch processing solutions, while the latter changed my life forever.
The goal of this blog post is to introduce you to the SQL Server Database project, convince you that it is a better (maybe the best) way to develop databases, and possibly change your life forever.
What is a SQL Server Database Project?
In simple terms, a SQL Server database project is just a collection of SQL files wrapped up inside a Visual Studio project that, together, define the structure of a SQL Server database. Each major database object – every table, view, stored procedure, function, everything – is defined as a CREATE-statement in a separate file. Where things get magical is when you unlock a bundle of development-oriented capabilities via tight integration with the following 3 components:
Together these components allow you, as the developer, to treat the database as if it was an application. As someone who took a handful of Java/C/C++ programming classes back in high school and college but focused primarily on databases and SQL ever since, the concept and associated advantages of viewing the database as an "application" were not immediately self-evident. However, I can assure you that 1) the advantages are many and 2) the methodologies and tooling for application development are way ahead of those for database development.
Benefits of using a SQL Server Database Project
Before diving into the "how", let's take a moment to explore and appreciate the "why". As mentioned earlier, there are many benefits of using a SQL Server Database Project. Here are a few of the most important…
Congratulations if you’ve made it this far!
Hopefully, you are now strongly considering how best to start using SQL Server database projects for current or future database development. There are many considerations that have been left out such as handling database users and roles, pre and post deployment scripts, and references and project variables, and many other areas that need to be fully considered and understood before diving in head-first into the deep end.
As with any new technology, it’s best to start small and build a comfortable working experience. A good place to start that process is the World-Wide Importers sample dataset (which can be found here).
Powered by IDERA