What are the real benefits of Visual Studio Team System Database Edition (GDR)?

Question

Interested if anyone has used VSTS Database Edition extensively and, if so, which features did you find the most useful over the standard Visual Studio database projects?

What are the most compelling features as opposed to alternative schema management options or tools like RedGate's SqlCompare etc?

Edit: Microsoft just released the RTM version of Database Edition (GDR) which adds support for SQL Server 2008 - link is here. I've previously blogged (briefly) about it here.

Has anyone had a chance to do any real work with the GDR? It looks like there are some real enhancements including refactoring support. I'd be really interested to hear if people are using it with SQL Server 2008...

Download From: [http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en]

Answer 1

You can do database versioning for one. That is useful.

The other thing that is really useful is the ability to define type of seed data for testing. Through this Visual Studio will populate the database with random data and this is great for testing purposes.

There are other benefits as well of course.

Answer 2

If you compare it to tool like RedGates, that are specifically taylored for SQL Server, the benefits are that if you have the proper MSDN subscription you do not have to spend more money for other tools (but keep in mind that RedGate tools are much more mature) and it covers some points (like regression tests and unit tests at the DB level) that other tools do not cover and it make so in a integrate manner with other testing tool of VSTS, so that you can record results in Team System.
Compared to a tool like Embarcadero ErStudio (my solution of choice) it misses the cross database features, and this is a big problem, at least for me.
If you are a "all Microsoft" shop with the proper MSDN subscription it could be worth spending time on it.

Answer 3

We use the database edition functionality of Team Suite on Stack Overflow. As Vaibhav said, mostly it is useful because it gives you a one-click way to reverse engineer a database into source control, and keep it up to date.

Note that it also has decent Data and Schema compare tools as well. You can compare projects to physical databases and vice-versa. This makes it pretty easy to keep your database up to date, no matter where you make changes -- in the filesystem database project, or in the physical database itself.

Answer 4

The VSDB test integration is so painful to configure that we abandoned it, and that's the only thing it's got that Red-Gate doesn't.

Red-Gate's tool is miles more useful. It does live DB and scripts in folders, but also has "snapshots." The aspect of Red-Gate SQL Compare that gives it the win is its Snapshot ability and the fact that your license allows you to deploy their assemblies and use them to perform database maintenance at customer run-time.

It has made upgrades in the COTS application that I develop a breeze. A Snapshot is a binary schema representation. You can package them as resources in an assembly, then use the snapshot in a customer run-time schema compare to bring an existing database up to the current rev.

Answer 5

It is always useful to put everything under the same source control, so your data-dude can be shelving, checking in, compare with history, and even resolve workitems and bugs using the same tools that other team members are using.

Also to be able to have one versionning mechanism across the whole application, in other words, it doesn't make sense to say that my source control has all the versions of my project while your database can't fit with any of these old versions, unless you take a backup or a snapshot of the database with each build.

Answer 6

Probably the best advantages are around being able to version control individual DB schema objects (which you could do with the older "Database Projects"), but have the power to "build"/deploy the project and convert those individual scripts into a complete database.

The ability to import scripts and have the Wizard covert individual schema items into separate files is very handy if you've inherited a DB schema.

Given that recently the licensing model changed, it makes it even more enticing because it's included with the Developer edition SKU. It also provised support for "Database Unit Tests" which might be useful.

From the 2008 GDR, I understand that they now support SQL Server 2008.

Answer 7

We are currently using the GDR 2008 projects for managing our entire database development and deployment on a greenfield system. We use a TFS build script to call out to the MSBuild task for deploying the databases along with executing the data generation plans for pre populating the testing environment with data.

The key with the data generation plans was finding the build task to use which is :

TaskName="DataGeneratorTask"
AssemblyName="Microsoft.Data.Schema.Tasks, Version=9.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" 

All of this gdr project work has been unbelievably helpful and I think it is well worth the learning curve to get to know these project types. The value they provide is astronomical in productivity and visibility.

It allows us all to view the entire system in a single visual studio solution along with allowing us to start with a clean slate of our system at any point in time with either a click of the deploy command or a custom build configuration.

This blog will help with getting the TFSBuild script to run if you're interested.