Quick Overview of Code-Named "Juneau" SQL Server Database Project which is one of the most awesome features of Visual Studio 11 beta
@ 04-07-2012
by Tugberk Ugurlu


I think one of the most awesome features of Visual Studio 11 beta is SQL Server Database Project (code-named "Juneau"). It is simple, robust and makes it really easy for us to check the database code into our source control system.

I hear you yelling at me "Dude, ever heard about NoSQL? MongoDB? RavenDB?". Yeah, I am aware of those technologies and I think you can achieve lots of things that you can do with a relational database system with a NoSQL database system. On the other hand, there are quite a lot of other things that you cannot do with a NoSQL database system. At least, with the comfort of SQL. What? Did you say "Comfort? Who favors comfort over performance?"? I would like to remind you that all Stack Exchange family sites run on SQL Server.

To sum up, SQL Server will not go anywhere at all. It will always be around and we will keep using it. So, I‘d pay attention to this post Smile

This project template carries lots of features inside it and despite the fact that it is so easy to get started with it, you might drown inside its options very quickly. Honest to say, I am not so experienced with this feature but I try to use this on some personal projects for now as much as I can.

Let’s first have a look at Visual Studio 2010 because if you play with SQL Server project templates with VS 2010, you will be in panic because lots of them are now gone.

image

Ok, then. What do we do now? Well, it is simple: they are now all in one project template. From database projects to SQL Server CLR assembly projects, SQL Server Database Project template can do all of them (I have never tired creating a SQL Server CLR project with VS 11 Database Project template so far. So, I might not be entirely correct here).

Let’s come back to VS 11 beta. Creating a SQL Server Database Project is very straight forward. File > New > Project and navigate to SQL Server language tab or search against "SQL Server". SQL Server Database Project option will be available.

image

When we create the project, we get not many things out of the box which is nice.

image

From this point, you can either start a brand new project or import your existing database.

When you double-click on the Properties, we will get properties window which we can set lots of configuration about the database, build process and so on.

image

When you are working on a project, make sure you set the target properly as shown below.

image

While we are working on our database project, we will be creating files for Tables, User Defined Functions, Stored Procedures, etc. Don’t worry about where you put them. VS is smart about your files and it orders them properly on your deployment. This is the experience I have had so far.

As usual, we can add new items via Add New Item dialog box and Database Project ships with several item templates.

imageimageimage

User experience is also incredibly high. For example, you can create your tables through design surface or directly with T-SQL code. No matter which one you pick, VS will always keep those in sync.

As you can see below, we have a file named Products.sql for Products table.

image

Let’s create a foreign key constraint for CategoryId column by referencing a table which does not exist yet.

image

Save the file and build the solution. Now, we are getting errors since the Categories table does not exist.

image

When we add the necessary Categories table and its columns, the build process should run smoothly without any errors.

Let’s now open up the Products.sql table file again. Did you notice the right side of the design surface? Yes, it really knows what is going on outside of this file and it keeps the information in sync. As soon as you make a chance anywhere inside your project, proper places get updated.

image

You can also create script files which will be executed at particular point of your deployment. When you go Add New Item dialog box and navigate to User Scripts tab, you will see some options there. Let’s add Post-Deployment Script file to our project.

image

This file is nothing but a regular file except for the fact that its build action has been set to PostDeploy.

image

What about IntelliSense? Well, it is quite nice as you can see from the below screenshots.

I don’t know why but at some points, I was unable to kick off the intellisense, though.

imageimage

One more thing to mention in this post would be the deployment process but I thought it would be too much for a blog post. So, I will (hopefully) try to blog about that separately by demonstrating different scenarios.

I am very excited about this feature and I am sure you have something to benefit from it as well.

Useful References




Hi 🙋🏻‍♂️ I'm Tugberk Ugurlu.
Coder 👨🏻‍💻, Speaker 🗣, Author 📚, Microsoft MVP 🕸, Blogger 💻, Software Engineering at Deliveroo 🍕🍜🌯, F1 fan 🏎🚀, Loves travelling 🛫🛬
Lives in Cambridge, UK 🏡