BizTalk Server 2004 Configuration Failures – Simple

I started this post to list suspect issues that may impact loading a BizTalk configuration (i.e., running configframework.exe in either a silent or interactive mode). I’m now splitting it into separate posts (simple, medium, difficult). I am not trying to offer specific solutions to all failures in this post, although I do provide some suggestions and pointers to solutions where I’ve seen these issues. This really is a collection of things you can confirm during the configuration steps.

The application of a BizTalk configuration file can be your delight or downfall, and differs for most every environment in which I’ve installed. Kudos to the big brains at MS for spending their resources on all that’s great about BizTalk; I’m confident they’ll come around to my wish list of easier/faster/better configuration options.

So, that said, let’s look at what makes a configuration fail, from the ‘easy’ perspective (note: these are from a multi-server deployment on a domain; some items will not apply to single-server deployments).

Simple (failures in this group typically roll back and do not require restoring a server to vanilla or running configframework with the /u switch):

• Unreadable configuration file (immediate; confirm you can open the XML file in IE before attempting to apply it).
• Missing configuration file or bad path.
• Non-existent user accounts (immediate; confirm domain and account names).
• Non-existent group accounts (immediate; confirm domain and group names).
• Bad passwords (the wizard checks these as you enter them).
• Non-existent SQL server -or- databases/MDFs on the SQL server.
• SQL Connectivity (this can be easy, medium and very hard, if connection is lost during the configuration step).

With these issues (for the most part), you either cannot complete the wizard, or configframework will fail on invocation with a pointer to your configuration file. You will realize and resolve most of them while the wizard is running (confirm/create accounts, check passwords, remove databases or MDFs, etc.). Note: BizTalk wants to run over TCP/IP and Kerberos connection to SQL, so save some time: ensure you can connect to the BizTalk server over port 1433 in SQL Enterprise Manager before you run the configuration wizard.

I am keen to receive any feedback and other posts. Please submit comments.

BizTalk Server 2004 Configuration Failures – Medium

I started this post to list suspect issues that may impact loading a BizTalk configuration (i.e., running configframework.exe in either a silent or interactive mode). I’m now splitting it into separate posts (simple, medium, difficult). I am not trying to offer specific solutions to all failures in this post, although I do provide some suggestions and pointers to solutions where I’ve seen these issues. This really is a collection of things you can confirm during the configuration steps.

The application of a BizTalk configuration file can be your delight or downfall, and differs for most every environment in which I’ve installed. Kudos to the big brains at MS for spending their resources on all that’s great about BizTalk; I’m confident they’ll come around to my wish list of easier/faster/better configuration options.

So, that said, let’s look at what makes a configuration fail, from the ‘medium’ perspective (note: these are from a multi-server deployment on a domain; some items will not apply to single-server deployments).

Medium (will likely require restoration to vanilla, and removal of the present configuration):

• IIS Application Pool identity failure (if installing BAS, ensure you log on with the same account that the BAS application pool runs under).
• Inadequate permissions on the SQL Server for the WSS (BAS) administration account (be sure this user has database creator and security administrator roles).
• Improper group membership (if configuring using domain accounts, forgetting to include the domain name for a particular service, causing the configuration to look for a local group that will not exist).

The result of these errors is a rollback, where BizTalk removes the configured components from the server, leaving behind the SQL databases and other artifacts. See the post titled “Restoring BizTalk to a Vanilla State“ to recover from these errors.

I am keen to receive any feedback and other posts. Please submit comments.

Microsoft SQL Maintenance Plans – Part One

I’m collecting a series of notes on MSSQL maintenance plans.  There may be some bits herein that can assist; please comment liberally and provide pointers if you think I’ve left something out.

My primary contact with these plans is through BizTalk Server 2004.  More detail on these jobs is available in the BizTalk Database Appendix (not yet published in this BLOG), my reference posts and in the BizTalk documentation on MSDN.

I mention these specifically because 1) I work with them the most and 2) I recently resolved issues with these jobs on a system;  my memory is still fresh.  Some maintenance notes:

• When creating a maintenance plan, group databases with transactional logging (full recovery mode) together. Mixing with other transaction types (simple) will generate errors.  You’ll get ‘(SQLSTATE 42000)’ and ‘(ERROR 22029)’ error messages in the job history and error number 208 in the Windows Event Log.  Look at each database and ensure the modes are the same for the databases selected for this operation.
• The above error will also arise if you attempt an operation that requires the database to be in single-user mode (rebuilding an index, for example).  To look for this issue, click “Management“ and right-click “Database Maintenance Plans” , selecting “Maintenance Plan History“.  Sort for the failed job and click “Properties”.   Troubleshooting Database Maintenance Plan Failures on MSDN provides some hints on the above issues.
• If you see the ever-present (for me, anyway) “The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060)“ error, on a BizTalk server the cause is a missing adm_OtherBackupDatabases table in the BizTalkMgmtDb database.  Create this table per the Microsoft SQL Maintenance Jobs for BizTalk Server article on this blog.

More to follow.

MSMQ and MSMQT Coexistence?

This document references other documents in the installation/configuration document set.

While I see it ideal to have single queuing support on a system, there may be a time where your situation requires both protocols.  Maybe your BizTalk implementation needs to access MSMQ elsewhere in the enterprise for a different purpose.  Whatever the situation, I’ve tested these steps to add MSMQ to a system:

• Add two static IP addresses to the system; one for the existing MSMQT and the other for MSMQ. If you already have one protocol installed, make it static and add the second.
• Register the IP addresses in DNS with different names.
• Install MSMQ (message queuing) from ‘Add/Remove Windows Components’.
• In the registry, create a new key (string) under “HKLM\SOFTWARE\Microsoft\MSMQ\Parameters“ called “BindInterfaceIP“ and set the value to the IP address for MSMQ.
• Configure BizTalk and at the Message Queuing screen, set the IP and name for MSMQT.
• Enable the MSMQT adapter per post-configuration tasks.

Once enabled, give each queue a unique address and you should be good to go.

Testing Distributed Transaction Coordinator on a Remote MSSQL Server using SQL Query Analyzer

When using transactions in SQL or BizTalk, the Distributed Transaction Coordinator (DTC) must be configured to allow network clients (typically referred to “Network DTC Access”)  This step is part of BizTalk installation on this blog (found in My Reference Posts), but I’ve broken it out here.

On the SQL Server that will allow remote transaction access:

• Navigate to Administrative Tools, Component Services.
• Click Component Services.
• Expand Computers.
• Select the local computer, right-click for Properties.
• Select the MSDTC tab and click the Security Configuration button.
• Check the “Network DTC Access” checkbox.
• Check the “Network Clients” checkbox.
• Click “OK” until the Component Services window is closed (DTC will stop and restart).

Once complete, perform this simple test to ensure you can execute transactions on a remote MSSQL client (requires SQL Client Tools):

• Open SQL Enterprise Manager and connect to the server to test.  Local or SA crendentials are suitable for this test.
• In the left pane, select the desired server.
• Open SQL Query Analyzer.
• Paste the following code in the query pane, modifying the [‘servername’] attribute with the server name you’re testing (square brackets are required).
• Execute the query.

If DTC is properly configured, the query should execute and return a single field.

begin tran

create table #temp (columname varchar(20))
insert #temp select top 1 name from [xprtm-michaelco].master.dbo.sysobjects
select * from #temp
drop table #temp

commit tran

I’ve tested this in multiple environments where DTC operations have failed, but I have yet to see it fail in a previously-working environment under the following conditions:

• A previously-working DTC environment, removing “Network Clients” or “Network DTC access”.
• A previously-working DTC environment, stopping the DTC service on the SQL Box.

I’d appreciate any insight or observations from experts.

Microsoft SQL Maintenance Jobs for BizTalk Server 2004

This article covers some observations and challenges with the MSSQL maintenance jobs installed with BizTalk Server 2004.  For error messages I’ve seen associated with other MSSQL maintenance jobs, please see my blog post: Microsoft SQL Maintenance Jobs for BizTalk Server 2004.  I will update this article as I continue to resolve issues and receive feedback.

To refresh; BizTalk Server 2004 creates 8 maintenance tasks to keep the MessageBox and other BizTalk databases running smoothly.  The jobs include:

• Backup BizTalk Server
• CleanupBTFExpiredEntriesJob_BizTalkMgmtDb
• MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb
• MessageBox_Message_Cleanup_BizTalkMsgBoxDb
• MessageBox_Parts_Cleanup_BizTalkMsgBoxDb
• PurgeSubscriptionsJob_BizTalkMsgBoxDb
• TrackedMessages_Copy_BizTalkMsgBoxDb
• TrackingSpool_Cleanup_BizTalkMsgBoxDb

More detail on these jobs is available in the BizTalk Database Appendix (in progress; contact me for an advance copy) and in the BizTalk documentation on MSDN.  These jobs are set up to run at default intervals ranging from once a minute to once a week and, for the most part, require little tweaking.

You must edit and enable the first and last jobs on the list in order for them to function.  For more detail, see the BizTalk Database Appendix (in progress; contact me for an advance copy).  If you see the jobs failing, view the job history; some interesting error messages can arise:

The following error occurs if you don’t install all the features of BizTalk Server 2004 (HWS, BRE, etc.):

“Warning: The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. [SQLSTATE 01000] (Message 1708)  Invalid object name ‘adm_OtherBackupDatabases’. [SQLSTATE 42S02] (Error 208)  Warning: The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. [SQLSTATE 01000] (Error 1708).  The step failed.“

To resolve this issue, create the ‘adm_OtherBackupDatabases’ table in the BizTalkMgmtDb (or it’s equivalent in your installation) in SQL Query Analyzer with the following script:

CREATE TABLE [dbo].[adm_OtherBackupDatabases] (
[DefaultDatabaseName] [nvarchar] (128) NOT NULL ,
[DatabaseName] [nvarchar] (128) NOT NULL ,
[ServerName] [nvarchar] (80) NOT NULL ,
[BTSServerName] [nvarchar] (80) NOT NULL
)
GO

For more detail, please see “The Backup BizTalk Server SQL Job Fails ..“ article in MSDN.

Jeff Lynch has a useful article on these jobs in BizTalk Server 2004 – Database Growth.  It’s well worth a look.

Enterprise Single Sign-on (ENTSSO) – Back up the Master Secret

This is part of a series of posts devoted to responding to Enterprise Single Sign-on issues.

Event Type: Error
Event Source: ENTSSO
Event Category: Enterprise Single Sign-On
Event ID: 10589
Date:  26-4-2004
Time:  13:03:37
User:  N/A
Computer:
Description:
The master secret has not been backed up. If you lose the master secret all the information stored in the SSO system will be lost permanently and your systems may fail to work correctly. Please use the SSO admin tools to back up your master secret.

Scary, I’ll agree.  Simple fix, however.  This step is part of BizTalk installation on this blog (not published yet; write me for an advance copy), but I’ve broken it out here:

• On the Start menu, click Run.
• In the Run dialog box, type “cmd”, and then click OK.
• At the command line, go to the Enterprise Single Sign-On installation directory.
• The default installation directory is <drive>:\Program Files\Common Files\Enterprise Single Sign-On.
• Type “ssoconfig -backupsecret <backup file>”, where <backup file> is the path and name of the file where the master secret will be backed up. For example, “A:\ssobackup.bak” to back up to a floppy disk, or “C:\mastersecret_servername.bak” to be moved to VSS or some other secure repository.
• Provide a password to protect this file. You will be prompted to confirm the password and to provide a password hint to help you remember this password.
• Store the backup file in a secure location.

The backup file can be used to restore a Master Secret Server or promote a group member to master secret server, as needed.

BizTalk Server 2004 Technical Guides and Resources

Microsoft has made available a variety of technical guides for deployment, migration, security and performance/capacity planning.  They are aimed primarily at BizTalk Administrators and developers, and cover a wide range of the develop-deploy life cycle. The guides are available on MSDN at:
http://msdn.microsoft.com/BPI/TechGuides/default.aspx

In various travels, I’ve come across a series of other BizTalk resources:

Main Public Website
http://www.microsoft.com/biztalk/

Product Documentation
http://www.microsoft.com/biztalk/techinfo/productdoc/2004/default.asp

BizTalk Server 2004 Installation Guide
http://www.microsoft.com/downloads/details.aspx?FamilyId=32FF7D19-9E25-44CE-8A8C-03E3EB5D726F&displaylang=en

MSDN Product Documentation
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/def/htm/ebiz_def_portal_page.asp

Adapters – General
http://www.microsoft.com/biztalk/evaluation/adapter/default.asp

Security Recommendations for BizTalk Server Components
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/deploying/htm/ebiz_depl_secure_wtti.asp

Case Studies
http://www.microsoft.com/biztalk/evaluation/casestudies/default_2004.asp

White Papers
http://www.microsoft.com/biztalk/techinfo/whitepapers/2004/default.asp

Microsoft BizTalk Server Newsgroups
http://communities.microsoft.com/newsgroups/default.asp?icp=Biztalk&slcid=us

Scott Woodgate – Microsoft Product Manager
http://blogs.msdn.com/scottwoo

QuickStart Guide to Learning BizTalk Server 2004
http://blogs.msdn.com/luke/articles/380519.aspx

BizTalk Server 2004 Tutorials
http://www.microsoft.com/downloads/details.aspx?familyid=9C64562C-3FA7-49BA-885E-82213D00776E&displaylang=en

Modifying File Names in BizTalk Send Adapters

There are times you’ll want to control the name of a message as it’s saved to disk (default is GUID).  The question arose if promoted properties were allowed; a link to this is presented below the bullets.  Thanks to Todd Uhl for providing this.

• %datetime_bts2000%: UTC date time in format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (e.g. 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds)
• %datetime%: UTC date time in format YYYY-MM-DDThhmmss (e.g. 1997-07-12T103508)
• %datetime.tz%: Local date time + time zone from GMT in format YYYY-MM-DDThhmmssTZD, (e.g. 1997-07-12T103508+800)
• %time%: UTC time in format hhmmss
• %time.tz%: Local time + time zone from GMT in format hhmmssTZD (e.g. 124525+530)%DestinationParty%: Name of the destination party. The value comes from message the context property BTS.DestinationParty.
• %DestinationPartyID%: Identifier of the destination party (GUID). The value comes from the message context property BTS.DestinationPartyID.
• %DestinationPartyQualifier%: Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier
• %SourceFileName%: Name of the file from where the File adapter read the message. The file name includes extension and excludes the file path, for example, foo.xml. When substituting this property, the File adapter extracts the file name from the absolute file path stored in the FILE.ReceivedFileName context property. If the context property does not have a value, for example, if message was received on an adapter other than File adapter, then the macro will not be substituted and will remain in the file name as is (for example, C:\Drop\%SourceFileName%).
• %SourceParty%: Name of the source party from which the File adapter received the message.
• %SourcePartyID%: Identifier of the source party (GUID). The value comes from the message context property BTS.SourcePartyID
• %SourcePartyQualifier%: Qualifier of the source party from which the File adapter received the message.

The following link points to a blog presenting two ways to use a promoted property as a file name: http://geekswithblogs.net/sthomas/archive/2004/11/13/14949.aspx

Promote a group member to master secret server

If your MSS should fail, here are the steps to promote a member server to the MSS:

• Change the master secret server name from the original to the new using ssomanage: “ssomanage -updatedb”.
• Stop the ENTSSO service on the new master secret server.
• Start the ENTSSO service on the new master secret server. It will recognize that it is the master secret server and that it has no secrets.
• On the new master secret server, restore the backed up master secret file using ssoconfig: “ssoconfig -restoresecret BackupFile”

The new server is now the master of the group.