# Saturday, 30 August 2008
Ever had the need to export either all BizTalk applications or all binding files from a BizTalk Server installation? You can use this little SQL script to generate a BTSTAST script automatic this process for you.

Simply open SQL Server Management Studio, connect to your database server and open a new query window. Paste the script, hit CTRL+T to have the result output as text and F5 to run it.

Remove the lines with dashes (-) and (XX row(s) affected) and copy the remaining lines to a .bat file of your choice. Run the bat script, sit back and watch the export process :o)

DECLARE @MSIPATH VARCHAR(50);
DECLARE @BINDINGPATH VARCHAR(50);

-- Set paths for exported files
SET @MSIPATH = 'C:\Backup\' + CONVERT(VARCHAR(8), GETDATE(), 112) + '\MSI\';
SET @BINDINGPATH = 'C:\Backup\' + CONVERT(VARCHAR(8), GETDATE(), 112) + '\Bindings\';

-- Generate script for exporting MSI packages
SELECT
    'BTSTASK ExportApp -ApplicationName:"' + nvcName + '" -Package:"' + @MSIPATH + nvcName + '.msi"'
FROM
    [BizTalkMgmtDb].[dbo].[bts_application]
WHERE
    isDefault <> 1
AND
    isSystem <> 1;

-- Generate script for exporting binding files
SELECT
    'BTSTASK ExportBindings -ApplicationName:"' + nvcName + '" -Destination:"' + @BINDINGPATH + nvcName + '.xml"'
FROM
    [BizTalkMgmtDb].[dbo].[bts_application]
WHERE
    isDefault <> 1
AND
    isSystem <> 1;

SELECT 'PAUSE';


Saturday, 30 August 2008 10:07:10 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |   |  Trackback
# Tuesday, 08 April 2008

BizTalk-MsgBoxViewerRecently we had some BizTalk issues that kept puzzling us, and in the end we turned to Microsoft Support and opened a Service Request. As always we were asked to collect different kinds of information for the supporter to help us solve our issue. However this time the supporter asked for something we had not previously been asked for: a report from the MsgBoxViewer tool.

To be honest I did not know the MsgBoxView tool when asked for the report, but I quickly started appreciating the tool after downloading it.

The tool is created by the Jean-Pierre Auconie, who is Tech Lead in the European MS BizTalk Support team. In his job as a BizTalk Support team member Jean-Pierre found himself mailing the same SQL scripts to clients over and over to collect data from the BizTalk databases, to help solve their issues.

He found the approach laborious, and eventually decided to include everything in a tool; the MsgBoxViewer was born. The name is a bit misleading though, as the tool does a lot more than just looking in the MessageBox database. Pretty much anything you would ever need to know about your BizTalk installation is included. You get several comprehensive reports including a summary report and your installation is validated against best practices and recommendation providing warnings and of different severity. I.e. very useful to get a quick overview when taking over a BizTalk installation you have not performed yourself.

If the MsgBoxViewer is not already in your BizTalk toolbox I strongly recommend you to go get it right away. It can really be a big help in your work with BizTalk Server – especially when something is not quite behaving as expected. Needless to say that as soon as we provided the report to the supporter it was only a matter of hours before our issue was solved.

Tuesday, 08 April 2008 20:09:02 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |   |  Trackback
# Wednesday, 03 October 2007

For some time I have been wondering what assemblies exactly are included when a MSI package is exported in BizTalk. When moving assemblies for one environment to another I had noticed, that I didn’t always get the latest version of the assembly included in the package.

For BizTalk to function properly the assemblies must be deployed to the Global Assembly Cache (GAC). This copy of the assembly is the one actually being used during execution, but apparently it wasn’t the one being included in the MSI package. However, In the BizTalk Administration Console for an application’s resources there are both a Source Location as well as a Destination Location pointing to a copy of the assemblies as well. Thus my first thought was that perhaps, it was one of these that were included in the MSI package.

To test this I tried to delete first one of them, and then the other. Either way I was still able to generate the MSI package, which meant that it were neither of these that were being used.

Increasingly puzzled I ended up turning to the good old newsgroups for some help. It took BizTalk MVP Tomas Restrepo less than 1½ hour to point me to an explanation, and apparently I was not the only one a bit puzzled by this. The explanation was another blog post by Richard Seroter titled Preventing Stale Artifacts in BizTalk Application Packages.

In here he explains what is actually going on when assemblies are deployed and how the MSI packages are generated. Interestingly and, at least to me, also quite surprising as first are, that the assemblies are added to a CAB file and stored in a table in the BizTalkMgmtDb database. For the full explanation I encourage you to read the post.

One obvious advantage of storing this information in the database is of course that you can be sure that in a multi-server environment regardless of which server you use to generate your MSI package will always be exactly the same. However, whether this is the only reason for the implementation I do not know.

Wednesday, 03 October 2007 19:23:22 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |   |  Trackback
# Sunday, 15 July 2007

 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

Sunday, 15 July 2007 16:52:17 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |   |  Trackback