# Sunday, 15 July 2007
« My jobs aint a job - it's a damn good ti... | Main | Training - Expense or Investment »

 Microsoft BizTalk Server 2006 introduced the concept of applications to group artifacts used in a BizTalk Server solution in a logical way. This was done to make deployment, management as well as troubleshooting easier and quicker.

Applications are managed through the BizTalk Administration Console, and I think the concept has been of great help in my daily work with BizTalk. One thing, however, has annoyed me very much: Applications in the Tree View in the Administration Console are not sorted alphabetically, but instead they are listed in the same order as they have been created.  BizTalk Administration Console with Unsorted Applications

As BizTalk installations grow and more and more applications are created it takes more time to find the application you are looking for, especially if the application names are somewhat similar. At Vertica we have BizTalk installations with 25+ applications, and eventually we decided to do something about it.

My first thought was that there would be a stored procedure somewhere that was used to select the application names for the Console. If I could find this I could just add an order by clause to the select statement, and hopefully the issue would be solved. To find this procedure I fired up SQL Server Profiler. Unfortunately I came to the conclusion with my colleague Troels, that is was some dynamically created select statement that was used, and this idea was of no use.

One thing we noticed though, was that all applications were entered in the bts_application table in the BizTalkMgmtDb database. This table has two indexed; a clustered on the primary key nID, and a unique index on nvcName. As the clustered index also determines how the data is stored physically and since the nvcName column hold the names shown in the Administration Console, we figured that if we just switched the clustered index from the nID column to the nvcName the problem might be solved. 

This operation was quickly performed in the SQL Server Management Studio, and after a quick restart of the Administration Console the applications now appeared in alphabetically order. Since this table only holds one row for each application, it is hard to imagine that someone would ever get more than 100 rows (and that I would think is also very high), thus there should be no considerable performance issues with changing the clustered index.

Clustered Index On nID
Before Change: Clustered Index On nID
Clustered Index On nvcName
After Change: Clustered Index On nvcName

Please note that you should never change procedures or tables of a Microsoft server product in a production environment as you will no longer be eligible for support. But this trick might help you maintain the overview of you BizTalk development installation. In the meantime we can hope the next service pack will sort the applications.

BizTalk Administration Console with Sorted Applications