Taoffi's blog

I recently went through another new issue with TFS:

I had the TFS database and Data warehouse both installed on a SQL Server 2008 instance.

After installing a new SQL Server 2008R2 instance, TFS just stopped working complaining about database access.

It took me a while to re-discover that the source of annoyance was – again - the msxmlsql.dll and msxmlsql.rll files located at:

\Program Files\Microsoft SQL Server\100\Shared

And

\Program Files\Microsoft SQL Server\100\Shared\Resources\1033

respectively.

For some reason (seems useless to spend time searching J) the versions to use are with TFS are:

msxmlsql.dll (1,311,256 bytes); 07/10/2008; file version: 2007.100.1600.22

msxmlsql.rll (52 248 bytes); 07/10/2008

The files that caused my problem:

msxmlsql.dll (1 308 000 bytes bytes); 04/03/2010; file version: 2009.100.1600.1

msxmlsql.rll (48 992 bytes); 04/03/2010

MOD est un pattern connu de gestion de projets. Il s'agit de réussir son entrée dans le mur !

Sa pratique est assez simple : elle consiste justement à éviter, au dernier moment, de rentrer dans le mur. Adopter MOD est facile. Le maîtriser et le réussir est une autre affaire !

Jean-François Champollion who made the first translation of the ancient hieroglyph (in 1822-1824, before ever going to Egypt) made his first (and last) journey to Egypt in 1828-1829. He spent 20 months and 20 days in this journey (of which the last 60 days in quarantine in Toulon when he was back homeJ).

An amazing book "Lettres écrites d'Égypte et de Nubie en 1828 et 1829 by Jean-François Champollion" (there should be an English translation… don't really know) contains a collection of letters - written to his friends, family and some officials- that recount this journey in a very smooth and pedagogic way. In an annex, the book contains a very dense description of the History of ancient Egypt and neighboring civilizations.

Champollion was evidently passionate about ancient Egypt. During this journey, he seems to be revisiting places and events that he knew quite well. And he recounts all of this in a fascinating way.

One thing that may seem a little strange for us, is the time spent in travelling. For instance: it took him several months to travel from Paris to Luxor (in the south of Egypt)… which really seems too long now (probably unacceptable!).

In the same time, he could compose (with his small team), in less than 2 months, more than 600 entire exact colorful drawing copies of all the monuments he visited (including endless hieroglyphic texts!). All in quite harsh travelling conditions between the Nile cataracts.

Near the end of his journey, he heard that Jerusalem's Archbishop decided to honor him with a distinguished prize (Croix de chevalier du saint-sépulcure). He recounts that getting hold of the prize was too expensive for him (200 Louis)!

The book is also available in an audio version (in French) on the great Free Litérature audio web site.

(Not sure you can write code while hearing such a bookJ)

It is quite useful and handy to synchronize SQL replicated databases through the Windows Synchronization Manager. You simply click the Sync Manager / Click Microsoft SQL Server / Right-click the desired subscription to synchronize and click Sync. Easy and great!

Annoying problems appear in some particular cases. I encountered one of these cases when I had an orphaned subscription (i.e. a subscription that doesn't have any more a declared publication). This may happen when, for instance, the publisher server doesn't exist anymore… or when the publication had been deleted in some circumstances.

The annoyance is that in this case, Windows Sync Manager continues to display the orphaned subscription and you have no means to delete it as this requires contacting the publisher which may not exist anymore and/or deleting the subscription from a publication which may not exist either!.

At this phase, you indeed have little choices… the most reasonable would be to leave this orphaned subscription displayed and simply ignore it (though a little frustratingJ)… That was what I did until I found some time to dig through (little information, if any, is available on this):

I ended up by finding that Windows Synchronization Manager reads the list of its displayed subscriptions in the registry key:

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCE\Replication\Subscriptions

(MSSQL10.INSTANCE is the SQL version and instance of the subscriber server… which, in the figure below, is MSSQL10.SQL2008).

The subscriptions node contains one key per subscription with required synchronization settings.

I simply deleted the key related to the orphaned subscription. To avoid Windows mysteries, I also restarted the machineJ. That now works as I would like. (Craftsmanship again!)

Browsers add-ons and plug-ins are nice features that can sometimes become annoying. Flash Player, for instance, is nice but extensively used in boring commercial ads.

Fortunately you can either disable the annoying add-on or allow it to run only on specific sites.

To do this in Internet Explorer:

• First click Internet options / Programs / Manage add-ons.
• Select the add-on and click More Information.
• By default, the add-on is allowed to run on all sites. This is displayed as * (asterisk). Click Remove all sites.
• Click OK to confirm your choice and close the Internet options dialog. You are done.
• Now, the add-on will no more be executed on any web site.
• In fact, each time a web site needs to run the add-on, IE will ask you if you want to allow it to run on this specific site.
• The list of allowed web sites is then maintained by Internet explorer.

After some weeks / months / years… this list may become quite long.

Now, what to do to remove just ONE site or TWO of this allowed list?

Internet Explorer allows you to REMOVE ALL SITES… There is no button to remove just ONE site!

So, you remove all sites and start a new history again… More annoying than letting the add-on run on all sites… isn't it?

WHERE does IE keep this list? Mystery!

After a lot of search, I didn't find any information. Until….

Yes… it is in the registry (Please be extremely cautious when modifying this vital thing: The registry)

HKey_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Ext\Stats\[add-on class ID]\iexplore\AllowedDomains

Example for Flash Player add-on:

When all sites are allowed:

When only some are:

Now, if we want to delete just ONE web site, simply delete its key.

Again: Please be extremely cautious when modifying this vital thing: The registry

As I already said (I should not be the only one to sayJ): TFS is a precious tool for project management. The problem with TFS is the tedious processes to install and troubleshoot specific issues.

This has greatly been changed in TFS 2010 to become much less tedious (thanks to the TFS team and to Brian Harry for all the useful information) but IT IS, still, quite tedious!

For instance: One of the usual tasks we do from time in the real world is to move our stuff from somewhere to somewhere else!

Don't expect this to be as easy as you may think in TFSJ

The context

Let's say we have a whole TFS installation somewhere (configuration / collections databases… etc.) and we want (or need) to move everything to another machine.

At a first glance, that should be straightforward:

• Install TFS (and SQL server) on the new machine;
• Attach (or restore) TFS databases to the new machine;
• Tell the new TFS where to find the databases;
• Done!

Yes, it is a good path… but somewhat tortuous!

The recipe

On the new machine:

SQL configuration and settings

• Install SQL Server (2008 or 2008R2). Note the following elementary observations:
  • During installation, you should select a CI AS (case insensitive / accent sensitive) collation (either Windows or SQL);
  • Prefer a 'default instance' installation
• Attach (or restore) all TFS databases (configuration database, collections and datawarehouse databases.
• Install latest SQL server updates and Service Packs.

TFS configuration and settings

• Install TFS
• DON'T click Configure at the end of the install process
• Launch TFS admin console
  • Click Application Tier node
  • Click Configure Installed Features
  • Select the 'Application-Tier only' Wizard
  • Click List Available Databases.
    • This will display detected TFS configuration databases on the SQL server. If none is detected, then you have to go back to SQL configuration and see what is missing
  • Select the configuration database
  • Select the account service
  • Check and correct errors and warnings. You may need to go back to SQL configuration steps.
  • Click Configure

Problems encountered?

If you encounter problems (this is often the case with TFSJ), you may need to uninstall and restart the Application-Tier configuration:

• Open a command prompt (Run As Administrator)
• Go to TFS tools directory (%program files%\Microsoft Team Foundation Server 2010\Tools)
• Uninstall the Application Tier: Type the following command:

tfsconfig setup /uninstall:ApplicationTier

• Restart the above TFS configuration steps at Launch TFS admin console

Security configuration issues

Original SQL server configuration, TFS and various service accounts may not match the new machine's configuration. This may generate some errors that can be corrected using SQL server management studio and, in case, TF admin console (Administer security option). It is sometimes necessary to go through TFSSecurity command line utility.

Please note: all this should be considered as "useful hints" to solve TFS migration from machine to machine. You should be armed with patience and try find other simple and reliable paths for your specific problems.

Anyway, Hope this may help!

In everyday work, we come through new tools or new versions of tools we knew before.

The cycle of playing with and manipulating these tools’ objects to attain a reasonable level of mastering… is often daunting!

To minimize the learning cycle of tools, some helpful features have been introduced: like documentation, tooltips… etc.

Documentation is of course important, but, for me, tooltips are much more helpful. They just appear when I need them in the context of using the object or the property.

The thing is: when you manipulate an object for the first time, you really know few things about its composition and, even less, about its role in the global mechanics of the tool in question. You just know it is there, and it should be there for some reason and that you should spend some time to understand: its role, its structure, and finally how can it be useful for you (if at all it could be!)

One way to shorten this learning cycle is to let others show you how to use the tool and its object. Quite useful, but, on one hand, this occults some side of self-experience (important)… and also negatively interferes in your critical view of the tool (which is often useful for the tool’s enhancement itself)

What we do to know about (most) simple toys seems more rational: You just set something on or off (left/right or up/down…)… then put the toy on work and see... after some cycles you end up by figuring out what is the ‘best’ position for your needs (or mood!).

Another representation, which may also give an interesting slant related to this subject, is the DeepZoom technology used in maps applications where you can first see the whole world map, and then, zooming-in on the map you get more details about a given country, city, streets, buildings… etc.

A good path for reducing the time and effort needed to learn a subject or a tool would be:

• To be able to see (explore) the global image of the subject or tool’s structure in action;
• Be able to zoom-in on its objects and see their properties (progressively detailed according to your zoom level);
• Be able to change the value of a give object’ property… and perceive the impact of the change on the global behavior.

This proposed path cannot of course be applied in all situations or contexts, but can be useful in many (most) cases.

We may, for instance, need to create a simulation context inside which we can ‘run’ the specific tool or object. An approach which may also be useful for product tests and benchmarking.

Some interesting works have been done on some aspects (like The Property Grid project)… More is to be done on the visualization of objects and properties by zoom level. Will try to write a sample on this in a future post.

In a previous post, I started exploring the TFS configuration database. Let's continue here by having a look at some more configuration objects and relationships.

TFS Config security objects

The relationship diagram above suggests that we can query user logins, group-membership for specific projects by a query like the following:

The above query may give us results similar to the following

Where to find change-sets information?

TFS source control maintains change-sets for projects in the corresponding collection database. Each change-set is of course composed of the modified project files at the time of the Check-in.

Change-set information is stored in the tbl_ChangeSet table of the project collection database. That is: if you have a project collection 'Default Collection' stored in a database named tfs_default_collection, you will find change-sets information in the table tfs_default_collection.dbo.tbl_ChangeSet.

Files information is stored in tbl_files table, and file-versions information in tbl_Version table.

So, to obtain the list of files of a change-set number 861, for instance, you can execute a query similar to the following:

Which may produce results like the following:

How many bytes changed?

As you may have noticed from the figure above, change-set file information contains a FileLength column. This suggests that we can obtain the number of changed bytes of a given file between two change-sets… through the following query results we can know that the file grew 696-551 = 145 bytes:

Who changed what?

The change-set table provides an CommitterId column through which we can know who made a given check-in. As this column contains an ID (integer refering tbl_identity.IdentityId), this is not straight forward information. We have to join other tables to obtain something useful.

In fact, the committer user name is stored in the Constants table. So, we have to join the Change-set's CommitterId column to tbl_identity.IdentityId, and then join tbl_identity.TeamFoundationId to Constants.TeamFoundationId… we can then get the user name in the Constants.DisplayPart column (or Constants.String column)

This can be presented by the following change in our initial view diagram:

Changed bytes tracking generalization

It would, ideally, be beneficial to integrate such information into the TFS analytical database.

We still can do some turnaround by directly querying the database tables.

The idea is to list all change-set files in the order of their respective check-in date. To handle the probability of identical dates / files, we can use a row number to distinguish changes. This may be done like in the following view:

We can then refer the view to itself (current version / previous version) obtain row-relative quantitative changes:

TFS… introduction to this series

Microsoft TFS (Team Foundation Server… recently renamed to ALM: Application Lifecycle Management) is a great project management platform.

TFS is built on meta-model abstractions (that were first seen, in Microsoft's products, in SharePoint).

TFS, like SharePoint, is composed of several software components which operate around several databases: the configuration database and one or more content databases.

Configuration and content databases' elements (tables, views, functions, stored procedures… etc.) represent the meta-models (schemas and business logic for each related feature) which govern and control much of the software components' behaviors.

TFS is often presented as a software project management solution. In my view, this seems a little reductive of its value. Because TFS features truly span a much larger project management scope.

Many useful literatures have been written about TFS: its features, usage, guidance, troubleshooting… etc. Few have enlightened its meta-model structures and, as of my knowledge, none has put some clear light about its (undocumented) databases' structures. That is what I will try to do in this series, more to value TFS abstractions than to nude its internal mechanics. And also to enforce, again, the meta-model approach as a rational basis for software solutions.

TFS logical architecture

For its contents, TFS maintains a database per project-collection.

Each collection may contain one or more projects. And each project can be managed according to a selected methodology (Agile / Scrum / CMMI… etc.). In fact, TFS applies and operates the selected methodology through the provided methodology project management template. Ultimately, this means you can build your own methodology and use it to manage your project with TFS. All what you need is to provide the desired methodology template.

Managing a project with TFS consists of recording and following-up specific Work items (defined by the methodology template)… those can be items like: Tasks, Bugs, Change requests, User story… etc.

As TFS was initially brewed to manage software projects, a common item in each project is the Source control repository which helps developers to maintain their source code versions and branches in a very efficient architecture composed of 'change sets'.

To discover and figure out its components' data and their required behaviors, TFS uses a configuration database. A first look at the configuration database will be the subject of this first article in the series.

The configuration database

Like SharePoint, TFS databases are delivered without documentation or relational diagrams. They simply don't contain any explicit Foreign-keys or relationships. Whatever the reason behind this choice, that seems a little surprising of a product whose main aim is projects' transparency and documentationJ.

That said, to discover, understand and correctly interpret the role of each TFS database item and their inter-relationships you should do a deep dive and be armed with some patience and empiric methods!

Hosts and processes

Let's have a look at three important tables:

• tbl_ServiceHost contains the list of services' host information. This includes the item's name, the database connection string (when relevant), the Virtual directory and Resource directory…
• tbl_ServiceHostProcess contains information about current processes. Machine name, Process name, Identity under which the process is running, start time…
• tbl_ServiceHostInstance contains the list of processes' instances per host (Process Id / Host Id / Start time).

In my case, the table tbl_ServiceHost contained 3 entries for:

• Team Foundation: root item (parentless… see diagram ParentHostId column). Connected to the configuration database.
• Two child entries each for one of my TFS Project collections:
  • Default collection.
  • Training collection.

The above relational schema allows us to query the tables and obtain results like the following about current running services:

Sample results

Ressources and services

Again, let's follow up the following tables:

• tbl_CatalogResourceType stores a list of resource types. Like: 'Machine', 'Infrastructure Root', 'Project Server', 'Team Foundation Project Collection Database'… etc. Here is some sample entries:

• tbl_CatalogResource stores resources (each referring to one of the types in the above tbl_CatalogResourceType table. Sample entries:

• tbl_ServiceDefinition stores a list of predefined services (classes) and the web access page for each. Sample entries (among other things, you may note curly braces which are place holders for some url parameters in the RelativePath column):

• tbl_CatalogServiceReference stores per-resource services' entries. (Resource Identifier / Service Identifier…).

This allows us to query the services in a way similar to the following:

Sample results:

More about TFS meta-models in following posts!