Developing and deploying databases requires a great deal of work. The work required to deploy a database becomes more difficult because of the persistance of data. The language describing databases and their structures is so different from the language around applications. The differences in the language leads to differences in the way that the people tasked with developing each of the varying objects communicates. The differences in communication cause a great deal of friction when it comes to deploying databases. Anything that can help the DBA, database developer and application developer speak a more common language may be worth quite a small fortune. Visual Studio Team System Database Edition (VSTS:DB) proves this statement. This addition to the Visual Studio environment enables the people responsible for developing and deploying databases to work in an environment and use a language, at least in part, that's very tightly coupled with the language of developers. This shared language allows for a smoother process in the development and deployment of application and database code.
While a lot of in-house development is shifting over to object oriented persistance layers generated by tools like nHibernate or leveraging tools like Microsoft's CRM, there is still a lot of more traditional database development projects being done. This article introduces how I've been working to integrate database development with the application development and streamline deployments using VSTS:DB.
Projects
Prior to VSTS:DB, the DBA could create a database project, but those projects had very limited capabilities. They really only offered a mechanism for storing scripts in source control. There was little to no facility for deploying the projects to a database, let alone to multiple environments with varying security requirements, and different versions of the database structure. Now, things have changed. You can create three different project types and use them in your deployments. The first is the standard database project, no different than before. I've taken to calling these baseline projects. Here's where you put the objects that are common across all environments, basically, tables, views, procedures, functions, triggers, certain roles and users, whatever should exist in Development, QA, Staging and Production. You also get server projects. These allow you to define how the server should look, however, they're strictly for reference. Don't try deploying them to the server. It really can lead to problems. Finally there is the compound project. This is a project that is made up of the other two. Here's where you can start having fun. You create a server project for each server type in your deployment path, a development server, a QA server, etc. You create the baseline project and populate it. Then, you use the compound project to put these two together.
Here's an example. In the figure below, you can see that I have a solution defined, SSC, and three projects, MyServer (a server project), SSC_Baseline (a database project, a baseline), and SSC_MyServer (a compound project).
You can see the References folder under the SSC_MyServer project. If you right click, the context menu gives you the choice to "Add Database Reference..." Selecting this will open the Add Database Reference window. There are a number of options here that I won't go into, but they include using .dbschema files instead of projects, taking control of project variables and suppress multiple reference errors (a good idea if you really go nuts linking projects together). By default the selection is for "Database projects in the current solution" Just add the two projects. You can navigate to the Properties for the SSC_MyServer project and take a look at the References window. It will look something like this:
What's this do for you? Well, let's pretend that MyServer is the development server. I want to create a role in the project to allow developers to create stored procedures as well as run their apps within the role defined for the project itself (stored in the baseline). I can add this special role to the SSC_MyServer project. Then, when I deploy that project, I'll get the SSC_Baseline as well as the SSC_MyServer special roles, making for a clean deployment.
Configurations
Defining all the projects gets you only part way. You also have to use the Configuration Manager to create a configuration for each server you wish to connect to. This requires you to define the connection string within each project for the configuration, but, it acts somewhat as a safety net. If you don't define a connection string within a server, deployments with the wrong configuration will fail. This will keep you from accidently deploying the development security out to production. The baseline project will have to have every single server connection for every configuration, but the other servers and projects will only need the connection for which they're targeted.
The Configuration Manager is very easy to use. You define a new Configuration by opening the Configuration Manager window from the default tool bar. Usually when you first create a project the default configuration is Debug. Once the window is open, select the Active solution configuration drop down and the New... menu item. Type a name and you're good to go. We usually name our configurations after the server they're targeting. A new one would look like this:
To deploy a database to a server, select the Configuration and then deploy, not the server or baseline project, but the compound project. It will deploy the baseline and itself, running like a fiber optic laser beam assembler. Done.
Conclusion
This is just a bare bones introduction to the new functionality available through Team System, but this is already licensed to your developers that have the Developers Edition of Team System and the next version of Visual Studio Team System will install it with the Developers Edition. If you or your developers are working with it now, time to get more acquainted because it will be a part of what your developers are capable of doing in the near future.
