I finally got around to installing Data Dude on my PC to try it out.  Having used a number of tools in the past, I was looking forward to having one that works well, is in the same toolset as SSMS, and is supported by MSFT.  Below is my first experience with the tool. 

Installation

At first I tried to install just the Database Professional Tools without installing VS2005 - no luck (received errors about DBProj application not installed).  You really have to install VS Team System.  I used the trial version which time bombs in 180 days.  After installing VSTS, I again tried to use the SQL 2005 Project - again no luck (Cannot Find MSSQL$Express).  There is an option to install SQL Server Express Edition with VSTS - This is a requirement for Data Dude.  I had elected not to install it because I already had a SQL 2005 developer edition running as well as a couple of SQL 2000 and Analysis Services instances running (yes, it is slow!). I was hoping to avoid yet another instance, but I gave in, killed a 2000 MSDE instance and installed the VSTS SQL 2005 Express.  Voila! Success!!!

Schema Import

This is a feature I'm very excited about.  It would be nice to have a source controlled version of schema that is up to date with my data modeles, that I can use to compare against a live database to generate ALTER scripts.  For those of you that have used ERWIN or ER/Studio, you know what a pain it is to get a ALTER TABLE command created from those tools (and don't get me started ERWIN bashing - it won't stop!).

This feature is really intuitive - all you need to do is right click on the Schema Objects folder and the context menu prompts you to import the schema. You can select an existing connection or make a new one.  For me this was the first time I'd used it, so I made a new connection.  Once that is done, the import tool works away at importing the schema. Unfortunately, this feature still has some bugs.  Essentially all the keys and stored procs were imported with errors, an '!' next to them.  The tables, views, and indexes seem to be fine, but constraints, not so much.  I expect this to get better in future versions.

Schema Compare

This comes in 3 flavors - Project to Database, Database to Database, and Project to Project.  In order to use the Project features, you first must import a schema.  Since I had a schema imported (albeit with errors), I used that, comparing it to a database similar (nearly identical) to it but on a different server from where I imported.  Since the keys were imported with errors, there were a lot of hits on missing PKs, FKs, and indexes.  Otherwise, it seemed to work OK.  I tried the reverse with the Project as the target and the Database as the source.  It did the comparison and got what seemed to be accurate results. However when I right clicked and tried to update the project, the changes seemed to go into the ol' bit bucket.

Next I tried the database to database comparison. This worked quite well, though not perfectly.  Again I tried a development database and its corresponding system test database (I knew they were identical - I built them myself yesterday :) ).  Surprisingly it came up with some hits due to '[]' around owner names that were in test and not development.  There was no way to filter that out unfortunately.  There are, however, other filters for comparison, including whitespace, case sensitivity, owners, and the like.  Unfortunately, they don't seem to be implemented yet, as nothing happened when I clicked them. The readme.htm that comes with the download confirms that filters will be included in a later build.  Since there were some diferences, I wanted to see what the output would be.  The layout has a diff window below the list of objects which is scrollable like VSS or Red-Gate SQL Compare - very nice.  Below that is a running change script window.  You can view and edit the script that Data Dude puts out.  It also provides a button at the top to export to the SSMS Query Editor - again nice work.  The script output was concise and accurate, just as I would write it by hand.

The comparison tool does seem to work very well.  I must say it is on par with tools like Embarcadero Change Manager and Red-Gate SQL Compare.  The GUI is intuitive and the results seem to be accurate.

Data Compare 

This again is a feature I was highly interested in. Red-Gate Data Compare is a fine tool, but it would be nice to have it in the box.  I again compared my DEV and TEST databases, not knowing what data the testers had mucked up. Although the database was small, this tool was fast and produced great results.  The script again was clean and accurate, and I could export it to a Query Editor window for review, saving, source control, etc.  Nice!  This will be great to have in the toolbox.

Test Data Generation

Again this is interesting to me.  I have looked mulitiple times at tools to sample full-size environments, scrub sensitive data, and populate test environments.  None of them works well and all of them require a lot of work and training (from my experience).  This tool is not one of those.  Essentially, it will generate random data to stuff into tables.  If it's a number column, the generator will produce a random number.  If it's a varchar column, you will get a randomly generated string, none of which made any sense to me.  I'm not sure this would be great for system testing and user acceptance testing, but maybe unit testing or performance testing.  Hey, it's data!

Stored Procedure Testing

I was at a bit of a loss as to how to start with this piece, so I watched the video of it on the website.  To me it seemed like a really hard way to test a stored procs for accuracy, but it seemed to have capabilities of ramping up and performance testing.  This could have promise, so I'll take some time and drill down on that later.  For now, the jury is out with me on this feature.

All in all this is a great first try from Microsoft.  I would use the DB to DB compare tool.  The data compare tool is also handy.  Other than that, I’m waiting for some things to be added or fixed before the tools are GA.

Jon