Lately I’ve been utilizing schemas more frequently in my databases, however the first time I did this in VSDBPro was a pain.  The reason for this was I didn’t know how to do it, so hopefully this post will save someone else the struggles I went through.

First I want to highlight the differences between Solution Explorer and Schema View.  Solution Explorer is the physical file structure, it doesn’t know about relationships other than the folder structure:

Then there is the schema view which does understand the relationships within the database and models very closely the Management Studio layout:

As you would imagine there are pros and cons to both.  For example if you have invalid syntax for defining a table, you won’t even see it in schema view, you’ll only see it in solution explorer.  However if you have a foreign key relationship that points to a missing column you’ll see it in schema view with the warning symbol.  Also the schema view has appropriate icons for different object types.

However back to the issue of schema management.  I’m very particular about naming conventions and file placement so not trusting the tool (sorry Gert) I figured I new better and would just go into solution explorer and create the new schema myself.  No problem:

And then I tried to create objects under that scheme however the folder structure wasn’t there!!  What a pain, so I went out to Windows Explorer copied the file structure from an existing schema, removing all the objects and added them into the project via Solution explorer, all the time wondering about the person who thought this was a good idea.

However lo and behold had I just switched over to schema view which understands what a schema is and what it can have, I would have been fine.

What’s more is that when I created my first table, it automatically not only created the table sql file, it created the entire folder structure, seen below back in solution explorer:

So while we’re all used to utilizing Solution Explorer for our projects, for Database projects please be aware and use Schema view unless you have to deal with the actual files themselves.

So I’ve been using Visual Studio for Database Professionals (aka VSDBPro, aka DataDude) consistently this year for primarily new database development, both for Data Warehouses and OLTP Databases.  This will be the first in what hopefully will become a series of posts about Database projects.

In Database Professional there is are two types of projects, the database project and the database server project.  The database project would be the user database while the database server project would be the master database.  Neither one is specific to a particular server or instance, it’s a Visual Studio project which can be deployed on a single or multiple instances as you need.

You can also create dependencies between projects so you can have a database project which had a dependency on a database server project.  Why would you do this you ask?  The most straight forward example would be the login to user relationship.  You could have a database server project which creates your logins, and a database project which then creates the users in that user database mapped back to the logins in the server project.

You can reference a database project one of three ways:

1. Project within the solution
2. .schema file (DB projects don’t create .dll files)
3. Server and database variables

The first two are pretty straight forward as you would just select a project or a .schema from your workspace, so let’s look at how you would utilize variables for reference.  First here’s the screen to create a dependency:

Easy enough, just enter the variables and you would have:

You’ll notice in the variables the characters %(), this is what tells the Project that these are variables, and I didn’t enter the variables this way, I just entered ServerName and when I tabbed out of the filed the $() were automatically added.  So now you can see the variables in your command variables dialog (under project properties).  The first two are created with every project and you’ll see since I specified a value up above, the value is already used, instead of needing to set it at deployment time.

So now that we have the variables, how does this actually get used when we go to deployment?  I’m glad you asked, on deployment a .sql file is created that utilized sqlcmd mode.  So the output at deployment time time will be:

Notice that there is no value for the DefaultDataPath, as I never did set it at deployment time.  So there you have it, you can now parameterize your project dependencies!

You will soon be able to utilize Visual Studio Team System for Database Professionals for DB2.  See Gert’s blog here.  I really like DataDude and hope this gives it some more exposure.  However the biggest hurdle seems to be it’s distribution.  It’s only available through Team System Suite, this is a huge frustration in trying to get people to even try it, let alone adopt it.

In other DataDude news you can get the VSTS 2008 Database Edition GDR June CTP here also Andy Leonard published "Mastering VS2008 Team System Database Edition, Vol 1: Creating and Deploying Database Projects" today, which you can get here.

Also just found this, it’s a Visio for SQL Server Add-In that was updated in March.  Haven’t tried it yet, but interested in anyone else’s experience.