First Experience with Visual Studio 2008 Database Edition, I love it!!!

As a developer who has spent much of my life doing DBA type work, I really appreciate the simplicity and elegance of what Microsoft Visual Studio Team has put together with the Database Edition and can be seen for sale here as the

Microsoft Visual Studio 2010 Premium with MSDN Download - Download Direct from Microsoft

I'll first try and explain in a nutshell what it is.  Then I'll go through an example of creating a Visual Studio Database Project out of a site I've been working on to show the steps.  Finally, I'll give a short summary reliving the experience and giving you some other pointers that may help you get through the experience.

What is VS2008 Database Edition?

Basically, what VS2008 does is to first process your existing schema into hundreds (maybe thousands) of little files.  That is, each file is one database thing. That thing might be a table definition, a stored procedure, a trigger, a constraint, a foreign key, etc.  The beauty of this is that now, each thing can be tracked separately.  Say for example, you want to update just one table and someone else on your team wants to update another table in the same schema.  Since the files are now separate files, it's no problem.  When you grab the latest changes from source control, you will get your friends changes and he will get yours.  Very very clean!

Also, it has a very powerful comparison engine.  Say you update your data outside of this project.  VS2008 Database Edition will compare your definitions in all these little files with either a sql script you generate, or it will connect to an external database and compare to that.  Me personally, I like to use a tool called SqlManager to manage my data.  I can continue to do that, then when I've made my changes, I can simply connect VS2008 Database Edition to my updated development database and it will automatically figure out the changes.

So, you may ask, what version do I need to run this of Visual Studio?  The answer is in this link:  http://msdn.microsoft.com/en-us/vs2008/products/cc149003.aspx

Here We Go, Let's do an Example

So, first, I will open my existing solution which has a web project in it, a data access project as well as other projects.  Then, I create the new Database solution by saying File/Open/New Project/Database Project.

Then, we follow the wizard...

Taking defaults...

Now, I choose my schema.

Press Start...

Once you've done that, you'll now have your schema spread out across your project in little files.  Below is what the "file based" view of your schema looks like.

Notice that the file we are looking at also includes comments for both the table and the columns using the standard extended property documentation format as follows:

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = N'tracks all changes from each camp
title;link;guid;pubDate;category;description’, @level0type = N’SCHEMA’,
@level0name = N’dbo’, @level1type = N’TABLE’,
@level1name = N’CampFeed’;
GO
EXECUTE sp_addextendedproperty @name = N’MS_Description’,
@value = N’if true, this is the main news feed from the site.

there should only be one main news feed from any site.  this would
not be feeds like twitter’, @level0type = N’SCHEMA’,
@level0name = N’dbo’,
@level1type = N’TABLE’, @level1name = N’CampFeed’,
@level2type = N’COLUMN’, @level2name = N’PrimaryFeed’;

Now, when you actually build the project as follows:

it will generate the full script combining all the files back into one again. Here is where it puts it:

So, that's about it for now. I haven't gone into any of the ways to compare and update your schema based on external changes, but we can leave that for another post.

To see a great video by Richard Hundhausen, President of Accentient, a Team Systems Consulting company, go to this link:  http://msdn.microsoft.com/en-us/vsts2008/cc659682.aspx

Also, if you are using Sql Server 2008, make sure you install visual studio 2008 sp1, then go to this link and install the download:  http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en

Conclusions

From what I can tell, this Visual Studio Database Edition will be a huge help in building database schema's and database programs (sp's, triggers,etc.) in a collaborative environment.   If you have the license to use this, I strongly recommend it.  It is definitely going to be part of my tool kit for now on.  Even if I'm the only author in the project.