SQL Advisor

SQL Server Data Tools: A Work in Progress

I've been playing with the new SQL Server Data Tools, and I'm impressed by the way Microsoft has gotten the product out for review and is improving it with user-requested functionality.

• By David Ramel
• 06/05/2012

I've been playing around with the new SQL Server Data Tools, and although many have complained about missing features compared to the "Data Dude" Visual Studio projects and other tools that SSDT somewhat replaces, I'm impressed by the way Microsoft has gotten the product out there for review and is improving it with functionality requested by those same users.

SSDT, if you're not familiar with it, is the hodgepodge of improvements to database development in Visual Studio that was formerly known by the codename "Juneau." It comes free with the new SQL Server 2012 and just-released Visual Studio 2012 Release Candidate and can be installed from the Web. Even describing SSDT is somewhat complicated, as it consists of so many different "things." For example, besides replacing and improving the Visual Studio for Database Professionals ("Data Dude") product, it's also the new place to find former Visual Studio Business Intelligence Design Studio (BIDS) functionality such as Analysis Services, Reporting Services and Integration Services. It's also kind of a Visual Studio in-house replacement for SQL Server Management Studio so you don't have to keep switching back and forth between the two. It also adds a bunch of new features and improved functionality across many areas. Basically it enhances and simplifies the (admitted by Microsoft) difficult database development experience in Visual Studio.

I'll explore some of the individual SSDT components later, but here I'll discuss some of the features that didn't make it into the initial SSDT release and what Microsoft is doing about it. For whatever reason, SSDT shipped somewhat incomplete with the release of SQL Server 2012 in April. For example, commenting on a SSDT beta release, a user in May of last year offered up the following list of missing features as compared to VS2010 Database Projects.

• Schema View
• Dependency Viewer
• Data Compare
• Database Unit Testing
• Data Generation
• Data Compare
• Extensibility in General

"Is this the state in which it will ship?" the user asked. "Will VS2010 continue to evolve?"

Microsoft's Jeff Welton replied:

"A very thorough list for the CTP gap compared to TSData, though I'd also hasten to add the number of features that Juneau currently provides that TSData does not, such as Power Buffer, Server Explorer integration for declarative work, Find All References/Goto def, and Table Designer.

In the coming weeks and months we'll have more announcements over our offerings, projected timeframes, and plans for the future. All of the features you mention are on our minds."

In further discussion about the first item on the "missing" list, Schema View, Welton said: "I don't have a definitive answer to provide you on the Schema View at this point in time; however, we do recognize the need. ..."

And he wasn't kidding. Notice this announcement from a couple months ago -- one day after the release of SQL Server 2012 -- from Microsoft's Janet Yeilding:

"I am pleased to announce the first release of SSDT Power Tools. The SSDT team will be leveraging Power Tools as a mechanism to deliver new functionality and experiences through frequent updates, of which this is the first example. In this first iteration, we focused on addressing the feedback that SSDT does not offer the equivalent of the Schema View tool window found in the Visual Studio 2010 Database Project. This release of the SSDT Power Tools will add the Schema View capabilities back by extending the SQL Server Object Explorer, providing a logical view over the schema hosted inside your projects."

Installing the Power Tools resulted in a new "Projects" node in SSDT's new SQL Server Object Explorer, or SSOX, (with SSMS-like functionality), where you can see the schema of your database and add, edit or refactor the various objects. Fig. 1 shows what it looks like in Visual Studio 2010. (This also worked in the Visual Studio 11 Beta, but I couldn't immediately get it to work with the Release Candidate, and I have a question in to Microsoft about that.)

Figure 1. The new Schema View.

With the Schema View, you can drill down into the database structure -- or rather a model of the database structure -- and add or edit objects. For example, you can change the name of a column and get a preview of the script to make the change. After executing that, you can hit F5 to deploy the project and see the change reflected in the actual database, displayed in the SSOX under the SQL Server node (above the Projects node in Fig. 1).

You ask for Schema View, you get Schema View. Now, how's that for service?

And there's surely more to come. Gert Drapers, the original "Data Dude" and data platform guru at Microsoft, chimed in, "The Data Comparison, Database Unit Testing and Data Generation functionality will be added to SSDT post Visual Studio 11 RTM + 3 months." Seeing how the (now renamed) Visual Studio 2012 Release Candidate just went live, we can look forward to trying these out fairly soon.

Another user provided his own must-have wish list for SSDT on The Code Project:

1. Resolve references to other production and system databases like MSDB
2. Resolve references to other production databases connected by a linked server
3. Support of migration scripts to enable data preservation and to add static data
4. Fast and reliable build of deployment scripts
5. Possibility of executing and deploying a single file
6. Detection of isolated changes in the target database
7. Responsive SSDT'S GUI as compared to SQL Server Management Studio
8. Comfortable T-SQL writing
9. Extendable usage of snippets

He wrote about his thorough evaluation of SSDT and concluded that "There was no feature in my list not supported in SSDT, but some features still have room for improvement."

But SSDT won't get all of the functionality enjoyed by database developers who are used to using previous tools. For example, one user in an SSDT forum requested "a Query tool like the one we have in the old Server Explorer 'New Query' option." But there will be no graphical query designer. Microsoft's Janet Yielding replied: "A query designer or robust query editor is not in our future plans for SSDT.... As a workaround, I'd recommend using SSMS for your query authoring." Note, however, that a query designer, database diagrams and reference data support had been reported to be "on the horizon" in presentations such as at the TechEd conference about a year before Yeilding's post (see the PowerPoint slides).

Also, integration with Entity Framework, which actually worked in the Visual Studio 11 Developer Preview, was removed in the Visual Studio 11 Beta, as the SSDT blog explained in March. A post said: "Based on the feedback received and the remaining engineering work that would have been required to complete the experience, the decision was made to not include this functionality in Visual Studio 11."

And with last week's release of the Visual Studio 2012 Release Candidate, be warned that there can be some installation issues if you have SSDT installed.

For a summation of missing/included SSDT features, the team blog last fall provided a (now slightly outdated) comparison of SSDT CTP4 and VS2010 Database Projects features.

You can also find other SSDT feature requests on the SSDT forum (search for "feature request").

What features would you like to see in SSDT? Share your thoughts on the new database development experience in Visual Studio by commenting here or dropping me a line.