Claus on Code

One of the new things in the development tool to SQL server 2012 (SSDT) is the database project as shown in the picture below. Before 2012 it was a part of visual studio, but now you can get it for free in the data tools.

I’m guessing that that is one of the reasons for the name change from BIDS 🙂

But I’m rambling. Let me start by describing a typically way of database development.

• You develop your database  in a development environment with management studio
• When you want to deploy, you create a change script that will be executed on the production server.(There may be servers in between like test, preprod etc., but let’s not add those into the mix)

Well, that’s pretty much it.

The updated database might be used by a lot of other applications. Maybe the database is even the main part of a specific application.

There are a lot of problems in regards to this way of developing. Some of them are

• There is no test of references in Management studio. If you change a table that is used by for instance a view, you won’t automatically catch it.
• The change script might be error prone. Especially if there is no connection between develop and production servers, so you can’t compare them with a tool like Redgate or Atlantis.
• There may be a “drift” in the production database, which mean that a DBA for instance can have added an index to the production server, because he wrongly thinks he knows more about it than you 🙂 or Maybe someone created a view to be used for reporting etc.
• You don’t have source control.

That’s just some of the problem you will have with that kind of database development. The above problems and many others are addressed in the SQL server database project.

Let me demonstrate some of it with an example.

Let’s first create the project. If you look at the screenshot below you’ll see that there are 2 SQL server menus. The reason for that, is that I have visual studio 2010 installed along side with data tools. The project that is marked is the new one from data tools, and is of course a newer and better version. Select that one.

When you have created the project, you have an empty project. In my case I want to continue working on the Azure SQL project I started on in a previous blog post. So I just right click the project, click import, and type in the connection to my Azure database, and click start.

And badabim. Now I have my database in my development tool, and I can add it to source control alongside my other projects. All my database objects are now shown in the solution explorer. There are about 100 different object types. Right now, I only have one table, but that is fine for my example.

If I double click the product table, I get the view as shown below.

You can edit the table either in the designer at the top, or in SQL at the bottom.

You can build your project, and you will get errors, if you are using keywords that are not available in the target platform or have an invalid reference etc.

And regarding the target platform. You can change it in the properties window. As shown below.

If you change the target to Azure, you will get errors if you don’t have a clustered index on all of your tables, since that is a requirement in SQL Azure. Likewise, if you use file groups in Azure, you will get a compile error since that of course make no sense in Azure etc.

In my example, I would like to work with stocks. I delete the product table, and add two new, so I get what is shown below.

I can now from my development tool, do a schema compare and synchronize with the development/production server.

In my example, I just have the Azure database, so I just synchronize that.

I can look at my azure database and see that it has been synchronized.

So the conclusion: I must admit I’m really exited about the update of the database project, and the fact that it is now free to use along with the other data tools.

It addresses a lot of common problems when doing database development that you would normally have.

I can’t imagine myself starting a 2012 database project, and then not use this tool. No matter if it is a DWH, BI or application project I will be working on.