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:
- Project within the solution
- .schema file (DB projects don’t create .dll files)
- 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!