Friday, January 12, 2007

Evaluating VSTS for Database Professionals

We recently had an on-site presentation over VSTS for Database Professionals. While I was unable to attend the presentation, there are plenty of online resources in the form of articles, blogs and web casts available from Microsoft.

Microsoft has also recently published a case study on a company using VSTS for Database Professionals.

These two factors prompted me to review our Supply Chain Systems [SCS] developer tool needs again to see if it might be worthwhile to make a change. You see, currently, all the Microsoft developers in our department are using the VSTS for Software Developers edition. This was a somewhat arbitrary decision made late 2005 (post launch). At the time, licensing was a fiasco and very confusing, however, the decision has held up well for our development environment.

What follows are a few thoughts on why we are currently keeping our existing VSTS for Software Developers.

Visual Studio 2005 Database Professional for Team Suite
Since this product was the one that prompted the review, I'll start with it. As I mentioned, all of our SCS Microsoft developers use VSTS Developer. This gives us most of the power we need during development. However, there is overlap in the types of development, most notably operational reporting using Reporting Services, where a license, or three, of VSTS for Database Professionals might make sense. Except that I would probably baulk at the cost.

Pros:
  • Lots of new and useful features everyone will want
Cons:
  • Requires Team Suite, which drives costs
It seems that you can’t purchase VSTS for Database Professionals by itself. You can’t even purchase it as an add-on to the current version of Visual Studio Team Developer edition we all use. You have to purchase Visual Studio Team Suite, then you can add-on the Visual Studio Team System for Database Professionals.

Costs:
· Visual Studio Team Edition for Developers: ~$5,000.00/developer
· Visual Studio Team Suite: ~10,600.00/developer. It does include Team Developer, Test and Architect editions however.

This cost seems a little extreme when considering the functionality offered. The biggest features that the Database Professional version offers over existing functionality in VSTS Team Developer seems to be:
  • Database schema differencing
  • Database schema copying
  • Tighter source control integration
  • Database Refactoring (change a database object, the tool finds all the dependencies in the database model and updates)
  • Data generation tool; populates schema's with ‘realistic’ data for use in testing
Since we are not an ISV and don’t have any large database models that we own, or maintain, we can get by with existing functionality. For example, we don’t have a built in feature of the development tool to ‘suck’ in the database schema into version control; we have the Microsoft SQL Database Publishing tool that will script out both schema and data for us, then that would get checked into source control and treated like any other source asset.

Prior to the SQL Publishing tool, we had maintain the scripts to create and seed any databases we use. Not as convenient, but it allowed for complete source control of the database objects. We've now added a 'createdb' script for each database we own that will drop, create and reseed any of our databases. Its primitive by todays standards, but it works and it meets our needs. Your mileage may vary!

As for source control, once we’ve got the database scripted, existing source control measures work just fine.

Once the database source is under version control, we can ‘refactor’ it the old fashioned way: find in files and search and replace.

We do have a scenario where we have a vendor application that stores all of its code in a database. This makes promoting change very difficult as the vendors recommended practice for deploying 'code' is to copy the database to the new environment. Often this leads to developer code being published beyond an environment it was meant for, or even worse, functionality that hadn't been throughly tested yet being promoted. If you find yourself in this position, it could be that VSTS for Database Professionals would be worth the price. Unfortunately for us, this application is in an Oracle database, so its unlikely we can get any help there!

When it comes to database differencing (schema/content) it might be more cost effective to purchase products from the red-gate line: Sql Compare (schema) or Sql Data Compare (content). Each ‘feature’ can be had for about $295.00 a pop. You would have to leave the Visual Studio IDE and use a separate tool, but at the cost of licensing Visual Studio Team Suite it’s a no brainer.

We don’t have an alternative to data generation beyond good old fashioned creative SQL or custom tools and utilities.

MS offers a trial version that might be worth pulling down to evaluate in your free time. You have to install the 180 day trial version of Team Suite first though, and remember, after 180 days, you either have to pay ~$5,000.00 to upgrade or uninstall it.


Visual Studio 2005 Team System for Software Testers Edition
Initial looks at Team Test as a testing platform looks really promising. It could be potentially be a replacement for our current product, Borland Silk Performer. I had hoped to get a chance to evaluate it as part of the near term testing efforts, but our time lines are too tight to offer much in the way of experimentation.

Pros:
  • Developers are already familiar with the language (C#) and IDE. This greatly reduces the learning curve of anyone who finds themselves pitched into a test effort.
  • Better extensibility; since the language is C#, we can already get around many of the issues we have with other tools. E.g. neither Mercury Load Runner or Borland Silk Performer offer a way to drive ‘load’ via a protocol like MSMQ; wanting to call into COM, SQL, or just about anything other than HTTP is an add on cost. Keeps our costs down if we can handle these ‘premium’ protocols ourselves.
  • Cost of the SKU is even. We have the option to swap our existing Developer licenses for Tester licenses. There is some loss of feature set, that would need to be gauged.
Cons:
  • It’s a 1.0 release. Has a long way to go for maturity, but it might be just the right fit when faced with paying 100% of a testing tool and using 20% of the features.
  • It assumes a dedicated ‘tester’ role. While this is not that big of a restriction, the loss of some features from the Development version might prevent adoption. E.g. Will BizTalk work with a Team Tester edition?

Visual Studio 2005 Team System for Software Architects Edition
I’m still not sure what to make of the Architect edition. I can’t seem to get past some fairly difficult to use modeling tools to see the value for trading a Developer license for it. As well, the models that are generated using the toolset are supposed to be used to create Developer projects which only encompass ‘general’ .NET development: Winform, ASP.NET or class libraries. Unfortunately, most of our development is for a specific server platform: BizTalk or Reporting Services. Producing general development projects, even as nifty as it is, just doesn’t warrant the feature loss.

Pros:
  • Generate some really nice looking models, however, they tend to be too general. I can’t model BizTalk application services directly; I would model the protocols and data formats instead. Kind of makes sense.
  • Extensibility to create your own project models. This is actually available using the VSIP (yes, another version of VS): Visual Studio Integration Partners.
  • Offers validation of architectures before moving into the development phase. This could be powerful, but it would require adoption by your infrastructure architects to be truly valuable.
  • Cost of the SKU is even. If anyone decides they need the Architect version, its an even trade.
Cons:
  • Models can’t be easily shared with others without Architect edition. For example, our Infrastructure team doesn’t even have Visual Studio, so this is doubly expensive for them, which is where we would get the most bang for our buck: sharing models between application and infrastructure teams.
  • Creation of the models are still somewhat confusing, at least for me. They rarely seemed to ‘wire together’ right. Although, recently I’ve felt I’ve come a lot closer to experiencing what the experience should be like.
  • Microsoft Visio is still the modeling standard. Everyone on our team has it, and more importantly, is familiar with it.
If your interested in the Architecture version, you might check out the Guidance and Automation Extensions and Guidance and Automation Toolkit first to see if it might be more of what your looking for. Both of which work with the Developer version of Visual Studio.


Visual Studio 2005 Team Suite Edition
This is what every developer wants and sometimes needs. Many of our developers wear multiple hats so its not uncommon for them to transition into and out of Developer, Tester and Architect roles as needed. The problem is the cost. It is twice as expensive as our current license, though it is actually three products rolled into one.

MS offers a trial version that might be worth pulling down to evaluate in your free time. It’s a 180 day trial version of Team Suite, and remember, after 180 days, you either have to pay ~$5,000.00 to upgrade or uninstall it.

Pros:
  • Access to any tool/feature your likely to ever need on the Microsoft platform
Cons:
  • Cost.

Summary

There doesn’t appear to be an overwhelming need to change our development tools. There might be a case for the Test editions in the future, but beyond that Developer seems to be the best choice given our development focus.