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.

image

Then, we follow the wizard…

image

Taking defaults…

image

image

Now, I choose my schema.

image

Press Start…

image

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.

image

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:
image 

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

 
image 
 

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.

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Comments

  1. Good post, will frequent your site.

  2. I am always prompted with a connection to database dialog when i first try to run a script inside a database project 2008, can’t we set a default connection I tried everything and i couldn’t do it. (When I imported the schema i provided a connection but this doesn’t seem to be kept as default after…)

  3. venu peralA says:

    iHAVE Vs STUDIO 2008 TEAM SYSTEM,
    BUT i DON’T SEE DATABASE PROJECT.
    wHAT TO INSTALL

  4. @Plazma – See my blogpost on fixing this issue http://edferron.blogspot.com/

    Also make sure you have the datbase sku for Visual Studio installed. The database sku used to require a seperate purchase but it is not included with the professional sku for Visual Studio. You still however have to install it seperately if it is not installed. If it is installed then you have to install the GDR R2 update for Visual Studio 2008 to get this work properly.

    HTH,
    Ed

  5. I have visual studio 2008 and sql server 2008 installed on mhy pc but i am not getting “Database Projects” option. can anyone please wht can be wrong here?

Trackbacks

  1. […] file to deploy my database (or any change to it).  For those of you that don’t know the DataDude project, I have to say it’s one of the coolest tools that has come out the Microsoft Visual Studio Team […]

Follow

Get every new post delivered to your Inbox

Join other followers: