More Thoughts on Visual Studio Team Edition for Database Professionals

Wow! Judging from the number of hits on my last post regarding the new Visual Studio Team Edition for Database Professionals, there must be a lot of interest in this upcoming product! So I'm gonna dive right in with another post and give you some additional ideas on how this cool new product can really change the way you look at database development and the database development lifecycle!

What is the Database Development Lifecycle?

If you've developed any serious corporate or personal .NET applications in the past, you know that the development never really ends and that ALL development is an iterative process. This is especially true for the databases developed for your applications, since these databases continue to grow and change as your application is put to use, modifications are made and features are extended. I find myself tweaking tables, indexes, user defined functions and especially stored procedures more and more as my corporate applications mature and grow. This is the typical database development lifecycle.

So How Does Visual Studio Team Edition for Database Professionals Change This?

Working Off-Line

I've found the single biggest change (paradigm shift) that "TeamData" enforces is working "off-line" in a "sandbox" environment rather than working on-line like you do using the Query Analyzer or SQL Management Studio. This "reality shift" can really take some getting used to and I suspect it will be the main issue for most developers at first. However, once you get used to the idea of NOT MUCKING AROUND with a live database (sorry for shouting) it really becomes second nature and provides a much greater sense of control during database development. The only downside of this is that the new SQL Editor included with "TeamData" does not (yet) provide Intellisense for T-SQL so you'll need to brush up on your T-SQL programming skills and have the SQL Books Online (docs) open when you're writing T-SQL code.

Unit Testing & Data Generation

The second largest change is that "TeamData" provides the ability to unit test SQL user defined functions and stored procedures just like we can with our C# (or VB.NET) code. I believe this feature more than makes up for the lack of Intellisense in the SQL Editor since we can now test our code in a much more thorough and controlled manner. I can't tell you how many times in the past that I "thought" I had thoroughly tested a sproc only to have a user find an obvious error that a simple unit test would have uncovered during development. For those of you concerned about data privacy and sensitivity (if you're not, you should be), "TeamData" also includes an awesome data generation feature so that you can test representative data without using your actual (sensitive) data.

Schema and Data Comparison

This one's my favorite and after using Red Gate's tools for some time now, I'm very very impressed with how "TeamData" handles these vital tasks. If your development team is anything like ours, not everyone likes to work in the same manner and some developers will still want to use the Query Analyzer and SQL Management Studio to "tweak" your development or staging databases from time to time. Using the "TeamData" Schema and Data Compare features you can keep your data projects "in sync" with your development databases even if not all the members of your team are using the same tools! This is really vital until you can convince ALL your developers to work "off-line" in a "sandbox" environment, unit test their changes, build them for verification and check them into Team Foundation Server's source control.

Figure 1: Schema Compare

Build

The final and possibly most important paradigm shift that "TeamData" brings to database development is the concept of the BUILD. While not Earth shattering to most .NET developers, the concept of running a regular build cycle for database development can seem completely foreign to most database developers, who like me, were used to doing these things on an "ad-hoc" basis. Believe me, hand your dba a well written and fully verified build script and he'll fall over in a dead faint!

Source Control

One last thought! In the past, I never had a good way to store my SQL source code. I hate VSS (and I'm not alone) and storing SQL scripts on a file system makes maintenance a nightmare. Now I use "TeamData" and Team Foundation Server and I'm sleeping a lot better at night knowing that my SQL databases are under source control.

Some Conclusions

I've been really fortunate to be working as a "TeamData" TAP (Technology Access Program) customer for the past few months. I've gotten to know some of the "TeamData" Program Managers, Product Managers and Developers along the way. This team has been one of the most open, listening and responsive teams I've ever worked with at Microsoft and I truly believe this new product will change the way you look at database development.

It won't be easy. It will challenge many of your current database development beliefs. It will force you to look at how you currently do database development and you may not like what you see. I know I didn't! But in the end, this product can really enhance your database development skills, make you a more professional developer and put a sparkle in your dba's eyes!

As always, your comments are encouraged and welcome! Don't be shy. Tell me what you think!

[tags: Visual Studio, Team Foundation Server, DataDude, TeamData]

This entry was posted in Visual Studio 2005. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

2 Responses to More Thoughts on Visual Studio Team Edition for Database Professionals

  1. jlynch says:

    Randy,

    Just remember to “inspect” the SQL script generated by the build process very carefully before you deploy any changes to your production database. I would always recommend deploying to some sort of staging database and running some additional tests just to make sure your changes work as you think they should.

    Jeff

  2. Randy Minder says:

    You mentioned the downside to VSTSDBP will be developers having to brush up on their T-SQL. I feared this was going to be the case. The last thing I wanted to have to do was to have to edit multiple .SQL files just to add a column, index and constraint on a table. However, with CTP6, VSTSDBP will allow us to synchronize a database to a project. This means that I can make all my schema changes in my local (scratch/development) database using Management Studio (or whatever) and then synch those changes back to my project, and then synch them to our Production database. Thus, no need to modify any .SQL files directly! One caution howerver. CTP6 has a confirmed bug whereby when synching from a database to a project, and the project is under source control, the project will not get updated.

Leave a Reply