The SQL Data Tools in Orcas

August 27, 2007 @ 4:40 pm- from Hood River, Oregon

The SQL data tools in Orcas are the first time I've actually looked at the database extensions for Visual Studio. When DataDude was released sometime last year it went straight into the Visual Studio DataBase Edition, but it wasn't part of Team Suite so I never actually had a chance to look at it until it showed up in Orcas as part of Team Suite.

So here's a real quick look what's actually there.

The first and probably biggest features are the schema and data comparing features. These features are pretty cool, especially if you don't already use other tools for this task. Personally I use Red Gate's SqlCompare and SqlDataCompare (and SqlPackager!) which have been a life saver for me on many occasions.

The features in VS 2008 compare well with Red Gate's offerings although I still prefer Red Gate's stuff as they are easier to use and visualize changes and provide more options. But VS.NET 2008 has all the core features in place. Here's what the Schema Compare looks like in Visual Studio:

The view shows all database objects that have changed or don't exist on either end of the comparison. The bottom pane lets you review the actual code that makes up each object's state and the diff marks to show what's different between the two. Each option can be set to 'do it' (ie. Update, Drop, Create etc.) or 'Skip' which lets you bypass the changes.

It's pretty straight forward and invaluable when you're working on a local database and need to get schema changes to the database.

Data Compare works on a similar premise: It shows tables and views and shows what's changed in the data of the databases. It shows the differences as well as a detail that lets you actually browse the differences in the tables:

Oddly I found that in many situations the Diff would flag column values that are identical (as above in bold). The rows in have differing values in other places but oddly these values are getting flagged which is a little strange (especially since I just updated them a few days ago). <shrug> Then again I have different data sources connecting to the data so it's possible that the difference is actually spacing. <shrug>

The feature works as you would expect although I find the user interface above a little confusing to deal with. You have to click on each of the update values to change

There's also SQL Refactoring which allows you to rename tables, views, stored procedures etc. and update any dependencies in the database accordingly. I feel like a real Noob just about now, because I actually could not get SQL Refactoring to work and couldn't find any sort of documentation. SQL Refactor wants to work of a Schema View, but while I was able to find the Schema View Window in VS, I have no idea on how to load the database schema into it. Duh. Some searching around on the Web also yield nothing useful. Lots of discussion of the features, but nothing that describes how it works. That doesn't say much for the UI does it? So I need to defer discussion of the usefulness for another day.

While Sql Refactoring surely is a great idea, even if it works really well in the database it still seems like a real scary affair because the database changes are likely going to break middle tier and/or client code. So I'm not so sure that Sql Refactoring is such a great idea in the first place unless you entirely encapsulate your business logic through stored procedure access. <shrug>

Finally, there's also a fully integrated T-Sql Query window, which is basically the same view that you get inside of Sql Management Studio. You can run queries and get both a results pane as well as a grid display of the resulting data:

Data can also be displayed in text mode. One thing missing is the Execution Plan display, but most other features of the full SQL Management Console query window seem like they're there.

This is a welcome addition and I find myself using the query window in Visual Studio quite a bit - avoiding loading of SQL Management Studio as a separate application. I much prefer the QA type window to the Server Explorer data windows which have always been painful to use from a UI perspective - or maybe it's just long time habits that die hard. <g>

All this stuff is a welcome addition to Visual Studio. I certainly am already getting a lot of use out of the integrated T-SQL editor now. The other tools are nice as well although I suspect I'll probably stick with Red Gate's tools for synchronization, just because the tools are easier to use and cleaner.

Posted in Visual Studio Sql Server

Feedback for this Weblog Entry

re: The SQL Data Tools in Orcas
by Andras Belokosztolszki August 28, 2007 @ 2:48 am

SQL Data Tools for Orcas makes the life of database administrators and developers much more enjoyable. I particularly like the shift to developing on script files, as opposed to developing on the live database directly. The data and schema comparison tools are prerequisites to this development style.

I'd like to comment on refactoring databases. In Orcas, renaming a database object will actually update the C# code for in your unit tests. I agree with you that in production databases you have a lot of external code that may access objects directly. Such objects cannot be easily renamed. However, in a development environment where the dependent code is available, renaming is quite useful, and these refactorings save time by identifying the dependencies. It is a pity that in Visual Studio using the refactorings is not straight forward. However, like there are alternatives from Red Gate for the schema and data comparison, there is a tool called SQL Refactor. In addition to the renaming it contains refactorings like formatting your code (lay out), cleaning up unused variables and encapsulating as stored procedure.

Regards,
Andras

Andras Belokosztolszki, MCSD, PhD
Red Gate Software Ltd.

re: The SQL Data Tools in Orcas
by Markus Buhmann August 28, 2007 @ 3:09 am

Rick,

Like you I'm a big Red Gate fan, but I would be interested in having a closer look at this set of features in Orcas. What do you feel is the gap between Red Gate and MS in this space? How long do you think it will be before MS catchup, pass or buy Redgate?

Cheers,

Markus

re: The SQL Data Tools in Orcas
by Scottt Williams August 28, 2007 @ 5:41 am

I have been using the data tools in Visual Studio since 2003. I always found them lacking and had to augment them with the Red-Gate products. (I also like SQL Prompt) I am glad to see that we will ALMOST have one tool to do most of our programming in.

re: The SQL Data Tools in Orcas
by Rick Strahl August 28, 2007 @ 11:36 am

@Markus - functionality wise (at least the functionality I use) of the comparison tools in Orcas are very similar to Red Gate tools. I just think that the Red Gate stuff is a little easier to work with - the UI is smoother and it's easier to see what needs to be updated/changed and removed. In my experience, I rarely update everything so being able to quickly see the changes and being able to quickly check/uncheck them makes some difference.

That said though it's nice to have the VS.NET integration with the toolset right along with the rest of the development tools. It's definitely a good thing, but at the same time you have to wonder what took Microsoft so long. These sort of tools are vital for database development IMHO.

re: The SQL Data Tools in Orcas
by Richard Deeming August 29, 2007 @ 11:39 am

In your screen-shot, the "identical" column values flagged by the data compare tool appear to have trailing spaces in the target table which aren't present in the source table. (Notice the "..." at the end of the target column.) Is it possible that you have different ANSI_PADDING settings on the two tables?

re: The SQL Data Tools in Orcas
by Rick Strahl August 29, 2007 @ 11:57 am

@Richard - no I don't think the tables are different although they are on SQL 2005 here locally and SQL 2000 over on the server, but I think the padding values are updated by the schema compare.

What's odd too is that it's not all the rows. The first row in the screen shot is not affected.

I think the issue is that I have another application (an older FoxPro app) that accesses the data as well and it pads strings. But if that was the case I'd see it on all character rows. I have to check that out when I get a chance.

re: The SQL Data Tools in Orcas
by Simon Harvey August 30, 2007 @ 5:40 am

Hi guys,

Does anyone know of a feature list for what will be included in the various versions of Visual Studio?

It would be absolutely awseome if this stuff was coming in Visual Studio Professional edition but I have a feeling that it's only going to be included in the Teams Suites Database Edition product. Does anyone know if thats definately the case?

Many thanks

Simon

re: The SQL Data Tools in Orcas
by Gurb August 30, 2007 @ 6:56 am

From the opening paragraph of the article...

"the Visual Studio DataBase Edition, but it wasn't part of Team Suite so I never actually had a chance to look at it until it showed up in Orcas as part of Team Suite"

Why? The Database edition can be downloaded and installed for free into an existing Team Suite installation.

re: The SQL Data Tools in Orcas
by Rick Strahl September 02, 2007 @ 1:26 am

<blush> - that never actually occurred to me. I figured that it was a full VS version install like the other specific installations.

re: The SQL Data Tools in Orcas
by Chandra September 11, 2007 @ 7:58 pm

I'm using SQLDiff from Adept SQL, but to compare with this product, what Microsoft has done is really great though. I have not explore thouroughly, but the schema compare seems like one way only right? I mean from source to target, so we can only update the target database, while SQLDiff is reversable, update to database 1 or database 2.

re: The SQL Data Tools in Orcas
by Nilesh Gambhava September 17, 2007 @ 6:44 am

This feature is really gr8.. It will save lots of hours..

From Where I can download Orcas Version of SQL Server?