July 2007 - Posts
All of the different teams (TFS, Arch, Dev, DB Pro and Test) will be represented in a free public chat tomorrow. It will also include info on the Orcas release and the latest Beta 2 release... Two time slots - no waiting!
Join the chat on Wednesday, August 1st, 2007 from 10:00am - 11:00am Pacific Time. Add to Calendar | Additional Time Zones
Join the chat on Wednesday, August 1st, 2007 from 4:00pm - 5:00pm Pacific Time. Add to Calendar | Additional Time Zones
With the new beta of Team Foundation Server out I had to start upgrading some systems. The first is a demo/lab system that I use for training and explaining as well as some TFS extensibility development and customization work.
As this was a non-critical system it seemed like the best case. The upgrade started well but died early in the upgrade process with:
TFS220059: The installation process cannot continue. You are trying to install Team Foundation Server databases, but the specified data-tier server contains corrupted Team Foundation Server databases. Make sure that you have specified the correct data-tier server name. Otherwise, specify another server for the Team Foundation databases.
With some help from James Manning (http://blogs.msdn.com/jmanning/archive/2007/07/21/i-m-having-problems-with-tfs-install-upgrade-repair-uninstall-where-are-the-log-files.aspx) I located the log files and dug in... I didn't find the answer but some investigators behind the Microsoft curtain lent a hand. This sounds like something they were already aware of and have fixed, but not in the Beta 2 version. The problem seems to be how the upgrade wizard looks for the existing service account information and has a problem if you're not in a domain.
Bottom line, I've put that upgrade on hold for now. If you've got a TFS running in workgroup mode (i.e., not in Acitve Directory) and you try to upgrade to Beta 2, realize that this might plague you as well.
If I get any public fixes or other information I'll try to pass them along.
VS 2008 here I come!!!!! First the Database Professionals team gets SR1 out the door. Now DevDiv at Microsoft hits it out of the park with Visual Studio 2008 Team System.
There are many better posts than mine on the details so I'll point you to Soma's blog for the big announcement and Rob Caron's for some name changes.
The Visual Studio 2008 family of products will be another step forward. The big thing for me about Beta 2 is that there is a "Go Live" license. Using prerelease software in production isn't for everyone and I certainly don't recommend it. I'll be amongst the few actually using it in production. I'll let you know what I find, if anything, worth mentioning about using TFS 2008 Beta 2 in production.
Kudos to the Visual Studio Team and thank you. And to everyone else... have fun with the new release - I know I will.
I recently had a client who was using different test run configurations during their interactive unit testing to deploy different configuration files to use during testing. It is an interesting idea and one that I was surprised that I hadn’t heard of before. My challenge was to help them understand how to replicate this process using Team Build.
As defined in the Microsoft supported targets definition, the TestToolsTask supports the use of test lists and test run configurations. Unfortunately, the way it is used in Team Build, you have a single test run configuration regardless of how many test lists and VSMDI files you specify.
My client’s requirements were to support multiple configurations for a single test list; Team Build out-of-the-box supports multiple test lists with a single configuration. There are a couple of approaches to handle this but both will require the ability to specify multiple configurations. I proposed that the existing MetaDataFile item can be built upon with additional metadata to capture the test run configuration. This will allow any number of VSMDI files, each with one or more test lists AND a test run configuration. For my client they used the same VSMDI and test lists twice with different test run configurations. The following sample shows the concept that could be added to your TFSBuild.proj file:
<ItemGroup>
<MetaDataFile Include="$(SolutionRoot)\MySolution.vsmdi">
<TestList>BuildVerificationTests;DatabaseTests</TestList>
<TestRunConfig>$(SolutionRoot)\SqlTests.testrunconfig</TestList>
</MetaDataFile>
<MetaDataFile Include="$(SolutionRoot)\MySolution.vsmdi">
<TestList>DatabaseTests</TestList>
<TestRunConfig>$(SolutionRoot)\OracleTests.testrunconfig</TestList>
</MetaDataFile>
</ItemGroup>
As I said above, the existing TestToolsTask is capable of specifying test run configurations but is hard-coded in the Microsoft.TeamFoundation.Build.Targets file to only use the one specified in the property RunConfigFile. To use our newly defined TestRunConfig metadata in exchange for the RunConfigFile property, we can override the existing RunTestWithConfiguration target from the Team Build targets file; this target is called by the CoreTests target to actually perform the testing. You can copy the existing block from the Microsoft Team Build targets into your own TFSBuild.proj or your own custom targets file that you import into your TFSBuild.proj.
NOTE: You might be tempted to modify the original Microsoft Team Builds file. Please do NOT do this.If you modify the Microsoft.TeamFoundation.Build.Targets file, your changes may be wiped out during the next service pack upgrade. You’ll also impact all builds on that server including those that may want the default functionality. Instead, override the required targets in your own custom targets file or your TFSBuild.proj file!
The following snippet shows the code copied from the Team Build targets file as modified to support the updated MetaDataFile items shown above:
<Target Name="RunTestWithConfiguration" >
<TeamBuildMessage
Tag="Configuration"
Condition=" '$(IsDesktopBuild)'!='true' "
Value="$(Flavor)" />
<TeamBuildMessage
Tag="Platform"
Condition=" '$(IsDesktopBuild)'!='true' "
Value="$(Platform)" />
<!-- SearchPathRoot for not Any CPU -->
<CreateProperty
Condition=" '$(Platform)'!='Any CPU' "
Value="$(BinariesRoot)\$(Platform)\$(Flavor)\" >
<Output TaskParameter="Value" PropertyName="SearchPathRoot" />
</CreateProperty>
<!-- SearchPathRoot for Any CPU -->
<CreateProperty
Condition=" '$(Platform)'=='Any CPU' "
Value="$(BinariesRoot)\$(Flavor)\" >
<Output TaskParameter="Value" PropertyName="SearchPathRoot" />
</CreateProperty>
<!-- Test task for the end-to-end build -->
<TestToolsTask
Condition=" '$(IsDesktopBuild)'!='true' and '%(MetaDataFile.Identity)'!=''"
BuildFlavor="$(Flavor)"
Platform="$(Platform)"
PublishServer="$(TeamFoundationServerUrl)"
PublishBuild="$(BuildNumber)"
SearchPathRoot="$(SearchPathRoot)"
PathToResultsFilesRoot="$(TestResultsRoot)"
MetaDataFile="%(MetaDataFile.Identity)"
RunConfigFile="%(MetaDataFile.TestRunConfig)"
TestLists="%(MetaDataFile.TestList)"
TeamProject="$(TeamProject)"
ContinueOnError="true" />
</Target>
There are of course many variations on how you can achieve this, but hopefully this post will point you in the right direction. If your requirement does not require the use of test lists but you’d rather use a test container (i.e., a DLL containing all of the tests you’d like to run), the same approach can be applied to the TestToolsTask from the Team Foundation Power Tools.
I've been working with numerous different clients with Team Edition for DB Pros and this release has been anxiously awaited. To summarize the knowledgebase article, the following features have been added:
- Cross-database references: You can reference objects within different databases by using database project references or by referencing a database metafile (.dbmeta).
- Improved SQL Server file groups support: You can define files within file groups as database project properties instead of having to create files and file groups within the pre-deployment storage script.
- Variables: Variables can now be defined in the database properties and referenced from your deployment deployment scripts.
In addition, this puts us one step closer to even more great things from the DB Pro team. Congrats to the development team on this release and to all of us who will make use of it!
Important links:
Downloads for other languages are also available.
Although I spend more time on the road now than actually at my home base in Dallas, I still try to stay connected with the 'local' community. To that end I'd like to pass on some great community events for developers in the area. They are a bit of a drive from the metroplex but they could provide some fun road trips with your friends and peers:
Hope to see you there!
Part 3 - Database Testing during build
If you haven’t caught the first two parts of this series, check out the first part which describes why database builds are interesting and how to approach it for your team and the second part which details how to approach database builds using Team Build.
One place that teams of developers often collide is in the data tier. Team Edition for Database Professionals facilitates better team development and Team Build can enable developers to use a version controlled database definition. A great way to verify that updates haven’t broken existing functionality or collided with changes from another developer is through unit tests. This is true in the middle tier and also in the data tier. MSDN already provides basic instructions on how to create database unit tests. It also provides information on how to use data generation to prepare your database for reproducible test runs. I’m not going to cover those topics but instead I’d like to explain how to use Team Build along with the aforementioned to automate integration testing at the data tier.
Team Build already provides the ability to automatically run unit tests as part of the build process. The Team Build testing process is described in MSDN and will work with database unit tests as well as any other Team System supported test. So if the answer is simply create a test list that includes your database unit tests, why am I even blogging about it? Because I believe the integration process of automated build and unit test is insufficient if you can’t guarantee the data you’re testing against.
Performing Data Generation in Team Build
When you install Visual Studio Team Edition for Database Professionals on your build server you gain access to the custom MSBuild tasks that are used by Visual Studio. This includes several custom build and deploy tasks that we masked in my previous post by calling MSBuild directly against the database project file. Like those hidden build and deployment tasks there is also a custom task to execute a data generation plan. Unlike those tasks, I propose that you call this task directly in your team build or custom targets file.
You can gain access to the data generation task from Microsoft by referencing it in your custom targets or TFSBuild.proj file. Since it is installed into the GAC, you don’t need to specify the location, just the strongly named assembly. Note that directly accessing this task isn’t documented by Microsoft. By directly accessing it, you are putting your custom scripts at risk if Microsoft updates this in a service pack or other upgrade and doesn’t use a compatible binding. Assuming you’re not scared off by this, here is the UsingTask statement to reference the data generator task:
<UsingTask
TaskName="DataGeneratorTask"
AssemblyName="Microsoft.VisualStudio.TeamSystem.Data.Tasks, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
Once it is referenced, you can access it in your XML as follows:
<DataGeneratorTask
ConnectionString="MyConnectionString"
SourceFile="$(SolutionRoot)\DBProject\Data Generation Plans\MyGenerator.dgen"
PurgeTablesBeforePopulate="True" />
The connection string (i.e. “MyConnectionString”) references a standard SQL Server connection string. This is in contrast to the connection string information for the build process in the previous post that must handle the initial catalog separately. You can, of course, specify whether you want to purge the tables before running the plan by specifying the PurgeTablesBeforePopulate flag.
So when should you insert this magic snippet of XML? If you followed my recommendation from my previous post and performed the database build during AfterCompile target, you are ready to populate the database immediately after that point.
One final comment is that you should consider parameterizing your connection information either as MSBuild properties or items. Items will give you more flexibility as the same custom targets file will be able to build and deploy multiple databases during the same build if needed. Assuming an item named “Database” with metadata for ConnectionString, InitialCatalog, ProjectFile, and GenerationFile, the whole AfterCompile target will look something like the following:
<Target Name="AfterCompile">
<MSBuild
Projects="%(Database.ProjectFile)"
Properties="Configuration=Default;OutDir=$(SolutionRoot)\..\binaries\Default\;TargetDatabase=%(Database.InitialCatalog);TargetConnectionString=%(Database.ConnectionString)"
Targets="Build;Deploy" />
<DataGeneratorTask
Condition=" ‘%(Database.GenerationFile)’ != ‘’ “
ConnectionString="%(Database.ConnectionString);Initial Catalog=%(Database.InitialCatalog)"
SourceFile="%(Database.GenerationFile)"
PurgeTablesBeforePopulate="True" />
</Target>
This is just a starting point and with the magic that is MSBuild, you can really make the database buld process your own. Hopefully you’ve found this series of posts helpful. I’ll try to turn the next few around a little quicker.
Enjoy!
A quick interrupt in my 3-part DB Build blog. I just received a clarification from my local Developer Evangelist regarding the release of Visual Studio 2008. His point was that the February 27, 2008 date that has been publicized for Windows 2008/SQL 2008/VS 2008 is NOT the release date - it is the launch date.
According to Chris Koening, the release date is still this year. There is no exact date but they still expect Visual Studio 2008 to ship in 2007!
Part II – Build, Deploy and Database Connections
I actually wrote this post immediately after the previous one, but wanted to hold off until I QA’ed it. It is truly embarrassing to post part two of a series ten weeks after part one but at least it is better than when I first released it.
As I described in my previous post, using Microsoft Visual Studio 2005 Team Edition for Database Professionals can significantly evolve the approach for database team development; for the MSDN version of the story refer to http://msdn2.microsoft.com/en-us/library/aa833404(VS.80).aspx. For this post I’ll describe how to automate building and deploying an instance of your database based on the definition you have in version control. I’m going to assume that you’ve already figured out how to capture your database objects in a Visual Studio project and checked them into version control. I’ll also assume that you have a properly configured build machine and that you’re familiar with Team Build. If you need more information on any of these prerequisites, here are some good resources from Microsoft:
· How do I in Team Edition for Database Professionals (Note: This is the big one with references to the following and more!)
· Walkthrough: Creating an Isolated Database Development Environment
· Walkthrough: Establishing a Baseline for the Isolated Development Environment
· Walkthrough: Performing Iterative Database Development in an Isolated Environment
· How to: Create a Database Connection
There is other good documentation and blog postings so if you don’t find what you’re looking for in these links, your favorite search engine can help as well.
Database Builds within Visual Studio
Visual Studio 2005 Team Edition for Database Professionals includes extensions for MSBuild to enable building change or create scripts and deploying them to a target database. To do this interactively, you can access the project properties of your database project and set the connection string and target database as shown below:
If you’ve configured your database project appropriately, you can right-click on the project in Solution Explorer and select Build to create a script that can be used to create the target database or update the definition of the target database, depending upon your options in the project. Likewise, you can right-click and select Deploy to not only create the script but also to deploy it to the target database.
In itself, this manual build mechanism within Visual Studio can be very important. This, in part, enables you to easily create databases based on a point-in-time in version control. You can get a n old version of the database project and deploy it to your development box for troubleshooting, or get the latest version and deploy it to your local development database to incorporate changes from other team members.
This interactive mode of building and deploying databases is a great developer tool but it isn’t appropriate for automated builds. Luckily, Microsoft realized that this would be helpful so they’ve included custom targets and tasks to support automating database builds using MSBuild.
Additional Settings and Considerations
A couple of other quick notes on the build configuration screen. The build will, by default, break if you do something that will cause data loss. This can result from inserting a new column in the middle of the table, rename refactoring, reducing the width of a VARxxxx column or a few others that you may have discovered. If you’re early in the database design cycle and don’t care about the data, you can tell MSBuild to continue regardless of data loss by unchecking the “Block incremental deployment if data loss might occur” checkbox.
If that checkbox doesn’t fix it for you and/or if you simply want to always start with a fresh database build you can select “Always re-create database”. This will result in a faster database build as the system can easily generate a create script without having to inspect the existing database as required for an alter-based script. If you plan to use the resulting scripts later in a production environment, you’ll want to go with a variation of the alter scripts and leave this box unchecked.
Database Builds using MSBuild
These are already good documents on how to modify the database project files to enable MSBuild-based database builds (reference: How to: Prepare Database Build Scripts). I’m not going to duplicate the details but will provide a brief summary review as follows:
- This will enable us to simply copy the connection string information instead of trying to manually create it.
- This will be named something like MyDbProject.dbproj.user. Note that ‘user’ files are not checked into version control; this means that the interactive builds settings are per-user. This also means that the target database information is managed through Visual Studio 2005 and not currently through MSBuild.
- This file will be named similar to MyDbProject.dbproj. If you double-click on this file in Windows Explorer, Visual Studio will open the project as usual. This file is just a standard XML file so you can edit it using your favorite text editor. Alternatively, with your database project open in Visual Studio 2005, you can right-click on the project in Solution Explorer and “Unload Project”, then right-click and “Edit Project” and finally right-click again “Reload Project”.
With the connectivity information properly in your database project file, MSBuild is now able to build the database scripts and deploy to your chosen database. If you want to give it a shot, open a Visual Studio 2005 Command Prompt, move to the directory containing your project and run MSBuild (e.g., “MSBBUILD /t:build MyDbProject.dbproj”).
Now this approach is suitable if you want to create a command script to call MSBuild and always deploy to the same database. It is distinctly possible, however, that you’ll want to use the same database project file to build and deploy to one or more different databases. If so, read on…
Database Builds using Team Build
Team Build is the build engine included with Team Foundation Server. It leverages MSBuild but also includes additional functionality to integrate with Version Control, Work Item Tracking and Reporting. When you install Team Edition for Database Professionals and a SQL Server 2005 or SQL Express instance on the build server, it can also support database build and deployment. In fact, you can get it to work with only minor modifications the majority of which are described above. Microsoft has provided the details on MSDN at “How to: Deploy Changes using Team Foundation Build”. However, this will only work if you are always deploying to the same database server.
I propose that a more common usage in mature development shops will include the ability to deploy to different environments depending upon the specific team build. That is, you may want to build and deploy to an integration database server for one build type and then use a different build type to take the same database project and deploy it to a Test environment. Modifying the DBProj file as described on MSDN essentially hard-codes the target database server.
Implementing database builds in Team Edition for Database Professionals can be performed through the strategic use of an MSBuild task in your TFSBuild.proj. The official documentation proposes placing this in the AfterDropBuild target but I propose the AfterCompile target is a better place. AfterCompile will allow you to deploy a database for testing purposes before running the unit tests.
The key to specifying the specific database to deploy to within TFSBuild.proj is to add a property for the TargetDatabase. If you still use the ‘hard-coded’ connection string as described by MSDN, you can use different database names at that server. At this point, the overridden target will be similar to the following:
<Target Name="AfterBuildDrop">
<MSBuild
Projects="$(SolutionRoot)\MyDbProject\MyDbProject.dbproj"
Properties="Configuration=Default;OutDir=$(SolutionRoot)\..\binaries\Default\;TargetDatabase=MyDB_TestEnvironment"
Targets="Build;Deploy" />
</Target>
There is also a parameter for the target connection string which can be passed into the MSBuild task, but there is a catch. Unfortunately semicolon delimited strings have a special meaning in MSBuild and semicolon delimited strings are required as connection strings. Luckily you can handle this be quoting the semicolons using “%3b”. So you can completely avoid the hard-coded connection strings by updating your MSBuild task as shown below:
<MSBuild
Projects="$(SolutionRoot)\MyDbProject\MyDbProject.dbproj"
Properties="Configuration=Default;OutDir=$(SolutionRoot)\..\binaries\Default\;TargetDatabase=MyDB_TestEnvironment;TargetConnectionString=Data Source=MyTestDbServer%3bIntegrated Security=True%3bPooling=False%3b"
Targets="Build;Deploy" />
Note that this process results in a very long property string that should stay on a single line (regardless of how it was rendered in your browser).
By putting all of these pieces together you have the essentials to create your own automated build and deploy of a single source database project to multiple different database servers using the same database project file and different Team Build Types.
Assuming database builds are something you’ll see more than once, you may want to parameterize your database names and connection strings and expose their usage through a custom targets file. Consider using an ItemGroup to wrap your database connection information so that you can build multiple databases in a single Team Build Type.
But there is still more… Stay tuned if you want to learn how to automate testing and data generation in the data tier. And this time I promise it won’t take another 10 weeks until the next post J!
More Posts