Andre's Blog

Personal blog of Andre Perusse

Microsoft Adds Powerful Tool For Database Developers

I've been dying to try out Microsoft's latest addition to the Visual Studio Team Suite set of products - Data Dude, or more properly know as Visual Studio 2005 Team Edition for Database Professionals. Recently I finally took some time to watch a series of webcasts (at 1.5x speed - the only way to learn!) on MSDN and figure out how this thing works. After spending a couple of hours watching these webcasts, I am able to present you with the Coles Notes version of what Data Dude does and how it works.

Data Dude essentially represents your entire database as a series of text files. You can think of this as the "source code" for your database. Individual .sql files (which are just text files) containing DDL (data description language - SQL commands that create database objects) are stored within the new "Database" project type in Visual Studio. So, if you want to create a new table called "Customer" for example, Data Dude creates a new .sql file called "Customer.sql" which contains the DDL commands to create that table. In this respect, it's not a whole lot different than the old Visual Studio "Database" projects where you might have manually stored .sql scripts to keep them under version control.

What Data Dude brings to the table, however, is a lot more automation and validation to this entire process as well as the ability to "build and deploy" your database to any SQL Server instance. So, you have this big collection of .sql scripts to create tables, views, indexes, procedures, and functions and Data Dude does this wicked cool thing where it "parses" all the SQL in these files and validates them. So, if you a .sql file for your Customer table and it contains a field called "customerID" and in the .sql file for a stored procedure that SELECTs from that table you have written the field as "custID", you'll get a build error! Nice stuff.

You can either start your database project from scratch, or you can reverse engineer from an existing database. Once you have a your database project in a state where you're ready to deploy it, you can perform a deployment operation against any SQL Server instance that you can connect to. Data Dude will examine the differences between the source files it has and the existing database on the target and only deploy those updates that are required. For a new database this would obviously be everything, but for a database that your reverse engineered and only changed one stored procedure for example, only that stored procedure is changed on the target. Sweet!

Now that your database is represented as simple text files, you can easily place your database definition under source control. One of the problems that I've experienced working with a team of developers is that while the procs and functions might be under source control, the schema (tables, views, etc.) generally isn't. So, one developer might make a schema change on their local SQl Server instance, update a proc too, check-in the proc, but forget to tell other developers about the schema change. Naturally, when another developer tries to apply the latest version of that proc against their development database, it blows up. Data Dude does away with that by making it oh-so-easy to put your entire schema under source control. So now, another developer can get the latest version of the database project from source control, do a deploy against their local development SQL server, and the schema change AND the proc will get updated.

In addition to providing features for comparing schemas, it also allows to perform data comparisons between tables in different databases. So during development when you're adding new rows to lookup tables, for example, you can now easily deploy those updates to another database (such as a testing or production system) by performing a data compare.

But wait! There's more!

Data Dude also adds the ability to run unit tests against your database. Using Visual Studio 2005's existing unit testing framework, you can create tests that run against your tables, procs, or just about anything. To help you with this, you can create Data Generation Plans that automatically fill your tables with random data. You have a lot of control over how the data is generated, including number of rows and minimum and maximum values. So, the ability to run unit tests combined with the automatic population data makes for a very powerful combination to help you write quality database code.

If you can, I'd recommend that you spend a few hours with Data Dude learning how to take advantage of its capabilities. It may be a "release 1" product and while it's not perfect, it's still a great tool for database development in a team environment. And when they add ERD diagramming features in the next release or two, it will be the only way to develop databases.

Comments (2) -

  • Darth Mac

    4/9/2007 5:04:03 PM |

    So are you finally giving up your lust for Embarcadero products?

  • Andre Perusse

    4/10/2007 2:46:00 PM |

    No, I still lust over Embarcadero's stuff - but I've come to the realization that no employer I'm likely to work for in the near future has the cash to buy it for me. Man, my entire friggin' KINGDOM for an ERD tool.....