Getting Started with SQL Server Data Tools (SSDT)

After the SSDT product shipped, I spent some time converting an existing project ( dbproj / Data Dude based project) to SSDT. Actually, I decided not to convert the project, even though I was prompted to do so. Instead, I decided to re-import the SQL 2012 database. My reason to do so was the fact that the old project was breaking down each table into multiple files, one per object. So, if you have a table with a bunch of indexes and/or some foreign key(s), you can end up with a dozen files per table. Under the SSDT, you only have one sql file per table.

To use new functionality, just install SSDT first using Web Platform Installer 3.0.

To re-import the database you have to do the following:

Create a new project and select SQL Server Database Project.

Once a project is created, right click on the project and select Import –> Database menu.

You can create a new connection if necessary. I de-selected logins because I want to deploy database to test environment and Azure. I am going to handle logins separately. Just click Start and wait now.

Here is what my project looks like.

Now the cool part. Double-click on a table. In the old project you got a script, but in the new one, you get a split screen. You can either use designer or script.

I am planning to deploy to Azure, so I am going to set the target to Azure in the project properties.

Now I need to create publishing profiles, one for local development, one for Azure, and one more for local QA environment. To create one, just right click on the project and select Publish.

Click on Edit button and change your database connection string

Click on Save Profile As button and type in a name. I set my local one as Local.publish.xml. Then I include that file in the project and add it to source control. After that, I copy it and re-name the copy into Azure.publish.xml. If you now double-click on the file, you will get the same publish dialog. Next, I can change the connection string to point to Azure. That is it, now I have two profiles. I can now make a local change in the editor of a table, then double-click on local.publish.xml and publish the change locally to test.

I am going to publish to Azure from MSButild, using nightly build. Prior to SSDT this task was either impossible or very hard. Now with SSDT, it is as simple as the following new task:

The same code will work in QA as well, just change the publish.xml file.

To summarize, I really love SSDT. This solution is head and shoulders above the old database project. Use it and enjoy. The solution for pre and post-deployment scripts is exactly the same as in the old dbproj, just create a script and mark it as post-deployment. You can also just add new item and select the appropriate item from the dialog.

If you want to split that into multiple files, you can do that, just add new .sql script files (do not flag them as post-deployment), then use r: trick to call them from main post-deployment script.

Thanks.