Today the Visual Studio ALM Rangers published more solid guidance. This time it was around a favorite topic of mine – Visual Studio database projects.

This guidance focuses on 5 areas:

• Solution and Project Management
• Source Code Control and Configuration Management
• Integrating External Changes with the Project System
• Build and Deployment Automation with Visual Studio Database Projects
• Database Testing and Deployment Verification

This release includes common guidance, usage scenarios, hands on labs, and lessons learned from real world engagements and the community discussions.

Microsoft has yet to provide us with some kind of utility to handle the importing, managing, versioning, and deploying of data along with our schema changes inside Visual Studio Team System 2008 database projects. For most of the teams I work with, their needs are simple: they just want the ability to store data (INSERT statements are fine) in scripts within their database projects. Ideally the project would be smart enough to know which version of data goes with which version of schema, but for now they’re able to live with handling that manually.

Here’s one solution, albeit a manual one:

1. Create a database project.
2. Import database schema.
3. Launch SQL Server Management Studio (2008 version).
4. Right-click on the database and select Tasks > Generate Scripts.
5. Select the database and under Script Options deselect everything except for “Script Data”.

6. Click Next and select just the Tables you want (ideally just the smaller, static/lookup tables).

7. Click Next and specify the file to generate – something like LookupTableData.sql and let it rip.
8. You can now take that script and add it to your database project in a folder for data-related scripts.

Ideally you would link in the INSERT script(s) to your Post-Deployment script to automatically populate the data tables upon deployment. You can also use the option in the Generate Scripts dialog to give you one file per table, to maximize your versioning options. If you are already using Data Generation Plans, be careful to not overlap what they are already doing. For more information, be sure to read Barclay Hill’s Part 1 and Part 2 of a posting on how to manage data motion during your deployments.

I’ve been doing a lot of work with the Database, er Development edition of VSTS 2008. Of course I’m running the GDR-R2 version which really changed the architecture of the database projects, as well as the process of building and deploying.

Prior to the GDR, if you deployed a database project it would automatically create a Data Connection in the Server Explorer window. I liked this, because I would almost always follow-up a first time deployment with some data generation or unit testing, and it just made it easier to select the pre-defined connection from the dropdowns. It seems that the GDR erased this timesaver.

For example, I just deployed a GDR-R2 database project according to these settings:

And when I go to the Server Explorer window, I don’t see my VSTS\dev.AdventureWorks2009.dbo connection like I would have expected:

Well it seems that this change was by design and it is configurable! According to Duke Kamstra, there’s a property in the database project (.dbproj) file that lets you control this behavior:

<DeployToDatabaseAddToServerExplorer>False</DeployToDatabaseAddToServerExplorer>

If you set the property to True, the connection will get added to the list which Server Explorer displays, and the behavior I enjoyed prior to GDR will return. For added coolness, if you always want this behavior you could modify the template(s) that are instantiate dbproj file(s) from: C:\Program Files\Microsoft Visual Studio [9.0 | 10.0]\VSTSDB\Extensions\SqlServer\ProjectItems\*\*.dbproj

Duke also tells me that the same property exists in Visual Studio 2010.

Microsoft recently announced Microsoft Office PerformancePoint Server 2007. I believe this product was a result of the acquisition of Proclarity (Boise, ID) and a combination SQL Server 2005 BI, and Microsoft Excel.

Read the announcement from InfoWorld (Yahoo).