Sql Server Data Tools (SSDT)

Home » SQL and Databases » Sql Server Data Tools (SSDT)
Photograph of a SSD Drive Array

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.

Shows screen print of new project dialog in Visual Studio

Sql Server Data Tools (SSDT) New Project

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.
Screen print of Empty SSDT Project in Visual Studio

Sql Server Data Tools (SSDT) Empty Project

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.

About Phil

I have been working as a software developer since 1983. This blog could have been called “From Fortran 77, C and Cobol to C# in 20 (not so) easy years”, but it doesn’t sound quite right somehow. Besides I’m talking about what’s happened since 2003, not before!

Leave a Reply

Your email address will not be published.


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">




Top Posts & Pages

Categories

Recent Posts

Recent Comments

Archives

Blogroll

  • Mike Cohn's Blog
  • Scott Hanselman's Blog
- mike@mountaingoatsoftware.com

Scrum teams have too many meetings is perhaps the most common criticism of Scrum. But is it valid? [...]

- mike@mountaingoatsoftware.com

Here are five key lessons I learned that were instrumental in my career. See if learning them could [...]

- mike@mountaingoatsoftware.com

A team’s sprints should be the same length every sprint. Here are four reasons why. [...]

- mike@mountaingoatsoftware.com

Read this special Halloween post about five things in agile transitions that are can seem as scary a [...]

- mike@mountaingoatsoftware.com

If you want to overcome problems and improve how you work with user stories, register now through Oc [...]

- Scott Hanselman

In 2016 and 2015 I made a list of best Christmas STEM Toys for kids! If I may say so, they are still [...]

- Scott Hanselman

I'm glad you have a 1080p 60fps accelerated graphics setup, but I'm old school. Impress me [...]

- Scott Hanselman

Visual Studio Code has a HUGE extension library. There's also almost two dozen very nice Azure [...]

- Scott Hanselman

I got an email this week asking how to download some of my Azure Friday video podcast videos from ht [...]

- Scott Hanselman

There's a couple of great utilities that have come out in the last few weeks in the .NET Core w [...]

Meta