Sql Server Data Tools (SSDT)

Quite a few years ago stored procedures were very common. With the advent of ORM (object relational mappers) and TDD (test driven design), they are less common. Well what might be termed “misuse” of views and stored procedures is. I have worked on some systems where the majority of the data access logic and business logic are partly in stored procedures, partly in the app. These systems are very difficult to test, no matter what methods are used (and can be difficult if for example a requirement appears to store some of the data on a different database for example). I could go on but I wont…

However, even though a thinner database layer is both common and usually a good thing, it is still a good idea in my opinion to keep the database scripts used to create the database under source control. This way, the code can be versioned with the app using the app’s source control system and a history of changes and comments, along with all the benefits of the app’s source control system can be used for the database files as well. Even though we don’t have big stored procedures as much any more, many systems can still contain a lot of views, and of course tables.

Prior to using the tools I am talking about in this blog post, keeping full track of changes was hard. I few customers I worked for have had systems where object create (table, view, stored procedure) and also change scripts were maintained in a set of folders, so that when you changed the database, you changed the folder structure/contents as well to match. A little time consuming but these systems tended to work very well.

The first tool I came across a few years ago was SQL Compare and SQL Data Compare from Redgate. These are powerful, useful tools, that saved me time because when I made a change, I updated the release notes but didn’t have to make a change script. Both these tools can very easily make change scripts at release time, provided you have a copy, or have access to the test database at release time (you are using test and dev databases aren’t you, please don’t tell me you develop on live…).

However what really got my attention was Sql Server Data Tools (SSDT) from Microsoft, which is now an integral part of Visual Studio. Installing this add on, makes available a new project type, the SQL Server Database Project as shown below.

Once the project is created, the following page appears, upon which you can import an existing database, or you can create the SQL files to create tables, views etc. using full Visual Studio Intellisense.

The immediate thing that I like about Sql Server Data Tools (SSDT) is that it automatically creates and saves CREATE TABLE scripts, but when working with it, it uses CREATE or ALTER as appropriate. Just a detail that is surprisingly useful.Sql Server Data Tools (SSDT) compiles the Visual Studio solution just like other solutions, and has compare tools built in (I still like Redgate’s tools in production environments but SSDT are good for dev’s). It keeps an empty copy of your dev database in LocalDb storage, so you can either update the dev database and compare with SSDT, or you can update SSDT and publish your change to your dev database. It can also be used for updating test and live database although I tend to use Redgate’s tools for that at the moment.

The only problem I have found personally with Sql Server Data Tools (SSDT) is that because it compiles the solution, some complex production databases, particularly ones that link to other databases inside of views, stored procedures etc., do not properly compile. In these situations I have used RedGate’s SQL Source Control which is easier to set up in these situations. With SQL Source Control, you have a development database as before, which is linked in SSMS to the source system and no solution compilation is involved, so it works fine in even the most complex of situations. This is slightly different to Microsoft because they use Visual Studio for dev’s, SSMS for admins, but as a dev I have used SSMS as well so this is no problem at all for me.

A good tutorial on Sql Server Data Tools (SSDT) can be found in this Mssql Tips article on Sql Server Data Tools (SSDT). Hope this helps. I am surprised that a lot of developers haven’t heard about these tools. Well worth checking out.