SQL Server Reference Guide

Before I describe the experience I've had with the Visual Studio Team Edition (VSTE) for Database Professionals from Microsoft, I'll spend a moment on some background. That will set the stage for my evaluation of the product.

Background

I've written extensively on InformIT about my thoughts for the programming process. Although my tutorials hold true for all development efforts, there are two caveats: they reflect the biases and knowledge level I have for the discipline, and they contain more information for database programming than say, game programming.

Of course, I'm not the only person who has a vision for software development. There are dozens of methods of arranging programming projects from the Agile to ICONIX. All of them have strengths and weaknesses. One of the best software development methods I've seen is from Microsoft. It's the best because it is used and proven by one of the largest, if not the largest, software development firms in the world, and because when you're working with SQL Server, you're working with a Microsoft program. It follows that the programming methodology they used to create the product works well with the product itself.

This method of programming Microsoft implements and recommends is called the Microsoft Solutions Framework. I won't reproduce the entirety of the methodology here, but it is important to understand it to take full advantage of VSTE for Database Professionals. I have a link at the bottom of this section for the Microsoft Solutions Framework, and I highly recommend you take a moment and read the information it points to. Microsoft is quite successful at selling software, and you can do worse than emulating how they create their products.

With that background, let's take a look at what VSTE for Database Professionals can do for you.

Purpose

Visual Studio Team Edition for Database Professionals is a project modeling tool, and optionally version control system for the database part of the development process. That process is based around the "database development lifecycle," which is a continuation of the Microsoft Solutions Framework.

You can use VSTE for Database Professionals to create, compare, edit and baseline your database schema within a development project. You can optionally put this information under source control, so that you can track changes to your database, its objects and its code just like the other code in a project. This is something that has been sorely lacking in the Microsoft development area. In the past the DBA or database developer created a database and either scripted it out or created a backup. This information was sometimes stored in the same source control as the main software project that used it or it was placed in another source control system designed for all databases. Having this separation is dangerous at best, and can be devastating at its worst.

Once your database is created, you can perform "Schema Refactoring." This has to do with changing an object's name throughout the database consistently and safely. For instance, suppose you create a database schema and embed the version or name of your product within the tables, rules or stored procedures. During the development process (or even after the code is released) you may need to change the way you've named all of your tables. This is difficult because changing them might break stored procedures that reference a table or perhaps violate a foreign key. VSTE for Database Professionals handles all the relationship complexities, and safely renames all of your objects. Not only that, it records those changes for journaling or legal requirements.

Another area that is difficult to perform is database unit testing. VSTE for Database Professionals fits seamlessly into the testing harnesses created in other Visual Studio projects and it can create very extensive unit tests for the database alone. The tests I tried were similar in complexity to other advanced testing tools I've used in the past such as Mercury LoadRunner.

VSTE for Database Professionals also has a Data Generator for unit testing. It can create an amazing array of data sets and has complex rules around what you can have inserted. In my testing I had two tables, one referencing another in a foreign-Key relationship. I had the generator create three Foreign Key entries for every Primary Key entry in the other table, since this mimics what I see on the job. This section had the knobs and switches that I played with the most.

VSTE for Database Professionals also has a Transact-SQL (T-SQL) editor, but I'm so familiar with SQL Server Management Studio I found that it wasn't quite as robust. My suspicion is that developers will feel the same way and just use the object browser in Visual Studio.

Requirements

You'll need a "normal" development system, which I define as one with around 1GB of RAM and a few hundred megabytes of drive space. Even before you load VSTE for Database Developers, you'll need to get and install a full copy of Visual Studio Team Edition or Visual Studio Professional to work, since it uses that shell to live in. That might be the biggest barrier to adoption that Microsoft will encounter in pushing this product to database professionals. If they use other languages, they likely won't have an issue with this, but if they develop using only T-SQL, they might not be willing to install one of those larger projects to use this one.

VSTE for Database Professionals, like all Visual Studio Team Editions, works with Team Foundation Server. This is where the real power for the product comes into play. By arming the database developers with the same tool as the rest of the development team, the entire project is managed using a single application. Team Foundation Server also works with Microsoft Project, so if your organization uses that make sure you surface that information to them.

VSTE for Database Professionals works with SQL Server 2000 or SQL Server 2005.

Evaluation Process

I downloaded and installed "CTP 5" which is Microsoft-speak for Customer Technical Preview, release 5. I like this new paradigm, where it isn't just a few beta-testers that will get to see and comment on the program. I noticed during the installation that the product automatically sends feedback to Microsoft. I have no problem with that, since I normally turn that option on anyway, and if it's a true "CTP," I think they have a right to the information. If you're nervous about that, use a test system to try it out on your own. The installation was a simple "Next, Next, Finish." I chose all options and took all the defaults.

I have the full-blown enterprise edition of Visual Studio on my development system, so I'm not sure if any of the features I used don't exist using one of the "lower versions" of Visual Studio.

In my first test I created a simple project where I reverse-engineered a database, made one change and saved that change to "production" (really another database on the system). I felt this would be the primary entrance to the product that most developers will have.

To start the process I opened a new project, and found a new stub in the Visual Studio environment called Database Projects. You can see that in this screenshot:

Figure 228

I chose the Wizard approach to get up and running quickly, and asked the Wizard to import the schema from a database. This is where I ran into the first glitch. When I created a new connection, it wouldn't populate the server names, even though the test system has the Developer Edition of SQL Server 2005 installed with all protocols enabled and the SQL Browser Service running. I had the same trouble with the database name, even after I typed in the server name manually. Once I did, the Wizard continued, but I had to take on faith that my connection was valid. Hey, it's a CTP, so some bugs are going to be there.

Figure 229

Once I made all my choices in the Wizard, the process ran very smoothly. It took about 8 minutes to bring in the entire Adventureworks database on my system, which has 1GB of RAM and a 60GB drive. The system created an entire copy of the Adventureworks database on my system, where I named it "Evaluation." This is the database I worked with for the rest of the process. That kind of process is common in large development shops, where each developer gets a copy of the project and "folds" his or her changes into the production database.

Figure 230

With that change made, I created a table in the database. Interestingly, the system displayed not the graphical representation of the table but its text. I like this because it removes an ambiguity of what is happening.

I also added a task to the project as you can see in this screenshot:

Figure 231

Once I completed the changes I performed a "Build and Deploy" of the changes from the Evaluation database to the Adventureworks database on my test system.

I also ran a series of data-generation tests against a simple unit test. This was the most exciting part of the process, and I didn't take screen shots because I jumped from section to section. On the Visual Studio links I have at the bottom of this review, Microsoft has a series of demonstration videos that run through the entire process. I highly recommend you download and review those.

Recommendations

VSTE for Database Professionals definitely works for Developer-DBAs in large development environments, especially if that environment is a Microsoft-centric shop with Foundation Server in place. Large environments have a necessity for this type of tool, because making changes always risks the integrity of the code. VSTE for Database Professionals helps control and track that change.

Is this tool useful for the single developer in less complex environments? It certainly won't hurt to learn and understand how this product fits into the development lifecycle, but without the collaboration benefits it isn't as useful as it could be. And if you're in a mixed environment with Open-Source tools already in place, I recommend a manual process for code tracking and changes. It just seems a bit too difficult to fit with the Eclipse framework or other OS tools to make it worth the effort.

I don't know what the pricing will be for this tool, since Microsoft hasn't released that yet. It should be in-line with other Visual Studio Team Editions, somewhere less than a thousand dollars US to purchase outright. Of course upgrades and your current licensing status with Microsoft makes a big difference.

InformIT Articles and Sample Chapters

I've put my ideas about development in this section of the Guide.

Online Resources

You can download the CTP, read the documentation and watch demonstration videos here.

The Microsoft Solutions Framework is described here.