Redmond Review

SQL Server Data Tools: a Model Toolset

Using SSDT means we don't just shout orders at the database; instead, we plan what we're going to say.

• By Andrew J. Brust
• 12/01/2011

For almost 13 years, Microsoft has tried to make Visual Studio a place where developers can do their database work. Repeatedly, Redmond has tried to make the need to switch over to specialized tools, like SQL Server tools, obsolete. The success has been mixed at best, and yet Microsoft is trying again. When SQL Server 2012 comes out, so, too, will SQL Server Data Tools (SSDT), code name "Juneau," which will provide full-fidelity database design and development experience that can work offline or on live databases. Also, it will -- Microsoft hopes -- once and for all make it unnecessary to Alt+Tab over to Management Studio.

Can SSDT be any more successful than previous such attempts? Does SSDT establish a new theme, or provide another variation of the old one? It turns out things might be different this time. To understand why, let's review the history of database tooling in Visual Studio, and compare SSDT to its predecessors.

It's All Visual
Well before SSDT, there was VDT: Visual Database Tools. VDT consisted of Access-like, GUI-based table, query and database diagram designers. It was originally shipped with Visual Studio 6 in 1998. The VDT designers were hosted in the Visual InterDev shell and introduced a new database project type that served as a container for Transact SQL (T-SQL) scripts generated by the tools. Before VDT, most interactions with SQL Server were done through T-SQL commands, and visual database work in the Microsoft world was relegated to Access and FoxPro. VDT changed that, which was good.

But it needed to get better. Microsoft tried: in the Microsoft .NET Framework era, things in the database tooling department grew, but also meandered a bit:

• The simple script-container database projects were brought forward into the early .NET versions of Visual Studio
• SQL CLR projects arrived in Visual Studio 2005, in tandem with the introduction of SQL Server 2005
• Visual Studio Team Edition for Database Professionals ("Data Dude") was introduced in 2006, bringing a new database project type, refactoring, unit tests and more
• SQL Server 2008 R2 gave us data-tier applications ("DACs") and yet another Visual Studio project type

All these database project types can get pretty confusing. And the funny part is that you're still using the same database diagram, table and query designers that you've had since the 1990s when VDT came out. You need something better; you've certainly been patient enough.

Something Completely Different
It looks like SSDT is that better way. Yes, it does introduce yet another project type, but things really are different this time. To begin with, Visual Studio now provides nearly the same UI and power of Management Studio Object Explorer. Plus, you finally have a new table designer. This one provides a simultaneous GUI and T-SQL interface; make a change in one view and the other is updated accordingly. Using it is a lot like opening the ASP.NET Web Form designer and looking at the HTML source and rendered design views side-by-side. The very idea that T-SQL can be treated as a markup language that describes objects, rather than a command grammar that imperatively creates, alters or queries them, is clever and powerful.

And that little sleight of hand, where we move from imperative to declarative, is emblematic of the bigger reason that SSDT isn't just another GUI toolset for building databases. Instead of just giving us designers that generate scripts, SSDT instead maintains an entire model of the database, and keeps it in memory. This model-based approach has wonderful byproducts: You get deep IntelliSense support, because the database model works just like an object model when it comes to syntax completion. You also get real-time error checking support that doesn't require executing a script. And you can snapshot any given database design, because the model can be easily serialized.

The SSDT model-based approach changes versioning, deployment to multiple versions of SQL Server (including SQL Azure) and management of dependencies from stressful burden to thoughtful craft. Using SSDT means you don't just shout orders at the database; instead you plan what you're going to say. Remember Microsoft's big (and now defunct) "Oslo" initiative? It was all about modeling, but its scope was ultimately too wide to yield success. Meanwhile, SSDT shows, in the narrower scope of SQL Server, that a modeling approach can have transformative effects.