Slow performance and large MessageBox

This was a tricky one.  We’d seen performance dropping over a period of days.  Symptoms were as follows:

• Constant SQL churning.
• Large MessageBox Database size.
• Large MessageBox backups for system volume.
• Horrendously slow response times.

I decided at first it was the lack of the “Back up BizTalk Databases” job (this job archives completed messages in the MB database as it runs).  Once enabled, performance got worse as the large MessageBox created large backup files and lots of processing.  Of course, running out of space on the backup drive didn’t help, but that’s another discussion.

In digging (using SQL Query Analyzer), I found thousands of messages (140,000 in our case), all dehydrated, in the following tables:

• SELECT Count(*) AS BizTalkServerIsolatedHostQ FROM BizTalkServerIsolatedHostQ
• SELECT Count(*) AS DynamicStateInfo_BizTalkServerIsolatedHost FROM DynamicStateInfo_BizTalkServerIsolatedHost
• SELECT Count(*) AS Instances FROM Instances
• SELECT Count(*) AS MessageParts FROM MessageParts
• SELECT Count(*) AS MessageRefCountLogTotals FROM MessageRefCountLogTotals
• SELECT Count(*) AS Spool FROM Spool

So, I identified two potential causes:

• Long-running scope of STP orchestrations (I don’t think this caused it, but it’s something we should try to resolve, time allowing).
• BizTalk defect as described in MSKB 867449.  Hotfix released on 6/11/2004; but I don’t see it in any patches as of this writing.  This is an actual defect, relating to a BizTalk server that loses connection spamming the message tables once connection is re-established.  For the REALLY sleepy, read this article:
  http://support.microsoft.com/default.aspx?scid=kb;en-us;867449&Product=bizt2002

As it turns out, In this environment, the BizTalk and SQL servers are on Virtual Server .VHD files and undergo a nightly backup.  If the BizTalk server starts sooner than the SQL server, BizTalk frantically (and correctly) attempts to reconnect.  However, as the defect article describes, it pushes large numbers of messages into the MB.

The manual fix:

• Use HAT (Operations, Messages with no filter) and delete dehydrated messages.  The downside: HAT can only delete 2047 at a time .. and is S-L-O-W.
• Write something in WMI (I found some incomplete examples, but couldn’t use them).

I settled on backing up the MessageBox database and execute the following SQL statements in SQL Query Analyzer:

• DELETE FROM BizTalkServerIsolatedHostQ
• DELETE FROM DynamicStateInfo_BizTalkServerIsolatedHost
• DELETE FROM Instances
• DELETE FROM MessageRefCountLogTotals
• DELETE FROM MessageParts
• DELETE FROM Spool

This has the effect of orphaning all the dehydrated message parts.  After this, run all the maintenance jobs in SQL Enterprise Manager (Management, SQL Server Agent, Jobs) to tidy up the orphan references. Restart both servers so they’ll recover, and watch performance on each as they get to know each other again.

I’d appreciate comments as to the veracity of this solution; did I get all the tables, am I causing other damage, etc.

BizTalk 2004 Configuration Failures

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 may eventually split it into separate posts (WMI, security, permissions, etc.).  I am not trying to offer specific solutions to all failures in this post, although I do provide some suggestions and pointers to solutioins 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, easy to hard (note: these are from a multi-server deployment on a domain; some items will not apply to single-server deployments).

Easy (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.

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.

Hard (cannot remove present configuration, or configuration times out):

• SQL Connection loss during configuration (has the result of putting the server in a half-configured state); look to network or WMI issues.
• WMI recycle failure (I’ve seen some services refuse to stop and prevent WMI from recycling during configuration; the configuration times out and the process must be killed).

When configuration must be terminated, the server is in a non-configured state; cannot be unconfigured or configured.  See the post titled “When Good Configurations go Bad“ for the fix to this state, then restore the server to vanilla.  Confirm SQL connectivity and do test recycles of WMI-related services with the account under which you’re installing; some environments have a Group Policy (GPO) to prevent local administrators from managing services (ceding this responsibility to domain admins).

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

BizTalk Server 2004 accounts for multi-server installations

Found smatterings of documents on the web; even a few that mostly brought this topic together.  I built this one: it is fully tested in an AD environment.  I look forward to useful feedback.

Active Directory Recommendations
This document covers recommendations for creating Active Directory Organizational Units, Users and Groups for a BizTalk installation.  This appendix will be called out by the installation document at the point where account/group creation should occur.
 
The accounts created herein do not need any privileges on the domain beyond those of ordinary users.  The domain accounts may need to have elevated privileges within the trust boundary that includes:

• BizTalk
• Windows SharePoint Services (on the BizTalk server)
• BizTalk SQL Server
• External Database One
• External Database Two
• External Database N

For example, a domain account may need to be granted rights to perform certain actions on the systems hosting external databases.  In another case, an account may need to write a file to a file drop folder, requiring write access to the folder.

BizTalk Installation and Configuration Account
In the development environment, the BizTalk Setup program and the BizTalk Configuration Wizard require the use of an account with administrative rights on the BizTalk and SQL Server systems.  Rights can be revoked or the account disabled as soon as setup and configuration are complete.  The account must also belong to several BizTalk groups, covered in the sections below.
 
Note: You will not be able to configure SSO components if the account used for installation belongs to a different AD Forest than the server.  If lacking a BizTalk installer account, use a local administrator account for SSO configuration.  This methodology may create other issues during installation, such as the need to log onto resources using different credentials.
 
BizTalk Development Accounts
Individuals doing BizTalk development require access to adapters, receive and send handlers, and receive locations.  This access requires the domain developer group to be members of the BizTalk Server Administrators and SSO Affiliate Administrators groups.
 
Note: Active Directory has restrictions regarding the types of groups that can contain foreign domain users, and the types of groups that can be contained in other groups.  The groups and accounts created below are tested in a multi-server environment on a single domain.
 
BizTalk Deployment Accounts
Individuals deploying BizTalk applications will need to be administrators on the local systems and may require other permissions in the environment.  A BizTalk Deployment account is documented in the sections below for this purpose.
 
This access requires the domain deployment group to be members of the BizTalk Server Administrators and SSO Affiliate Administrators groups.
 
Note: You will not be able to configure SSO components if the account used for installation belongs to a different AD Forest than the server.  If lacking a BizTalk deployment account, use a local administrator account for SSO configuration.  This methodology may create other issues during installation, such as the need to log onto resources using different credentials.
 
BizTalk Support Accounts
Individuals supporting BizTalk applications will need to be administrators on the local systems.  A BizTalk Support account is documented in the sections below for this purpose. This access requires the domain support group to be members of the BizTalk Server Administrators.
 
SQL Server Service Accounts
The service running the SQL instance must belong to the same AD domain as the accounts installing, developing and deploying BizTalk components.

• Use “SQLAdmin” for administrative functions (interactive login).
• Use “SQLService” to manage the service (no interactive login).
• Use “SQLAccess” to access external databases.
• SQLAdmin must be a member of the local Administrators group on the SQL Server system.
• SQLService must be a member of the local Administrators group on the SQL Server system and needs to have the “Log on as a service” user right.
• SQLAccess needs appropriate rights on the remote database servers.

Username	First Name	Last Name	Full Name
SQLService	SQL	SQLService	SQL Service Account
SQLAdmin	Admin	SQLService	SQL Admin Account
SQLAccess	Access	SQLService	SQL Access Account

Set account passwords according to company standards.
 
Important: On the SQL Server, modify the startup parameters for the SQL Service and SQLServerAgent services to use the SQLService account and credentials.
 
Note: The Username fields are samples; you may need to change the names to avoid conflict with other AD accounts.
 
SharePoint Service Account
The SharePoint Service accounts must be created prior to installing Windows SharePoint Services (WSS).  WSS is a prerequisite to the BizTalk installation if deploying Business Activity Monitoring (recommended, even if not deployed until a later release).
 
Recommendations and notes on the Microsoft Windows SharePoint Services account:

• Use the SharePoint Admin Account (SPAdmin) for administrative functions, SharePoint Timer Service and all WSS access.
• SPAdmin is the site owner and will need an email alias.
• SPAdmin must be a member of the local administrators group on the local BizTalk server (WSS setup does this).
• SPAdmin must have the security administrator and database creator roles on the SQL Server (WSS setup does this).

Username	First Name	Last Name	Full Name
SPAdmin	Admin	SPService	SharePoint Admin Account

Set account passwords according to company standards and be able to retrieve these passwords during the configuration steps. Refer to the “Passwords” section in this document for issues surrounding generated passwords.

Note: The Username fields are samples; you may need to change the names to protect other AD accounts.

Important: Post-WSS installation on the BizTalk Server, confirm the startup parameters for the SharePoint Timer Service is using the SPAdmin account and credentials.

BizTalk Groups and Users
BizTalk Groups and Users must be created prior to running the BizTalk Configuration Wizard. In a single-system installation, BizTalk uses local groups and accounts, creating these during configuration. However, if separate BizTalk hosts are deployed or if BizTalk and SQL Server are installed on two different computers you must use domain user and group accounts.

Note: The Configuration Wizard cannot create domain accounts.

Recommendation: Create domain accounts and populate them via an ADSI script for user and group account creation for upline environments.

Recommendations and notes on BizTalk service and user accounts:

• Create an Organizational Unit (OU) for BizTalk. All accounts and groups will belong to this OU.
• Be descriptive with full names; the names in the lists below should enable the installer to select the proper groups/accounts/users during configuration.
• First name and last name are optional; included for consistency only.
• The differentiator “BTService” and “BTUser” refers to service accounts (automatons) and generic/shared human users.

BizTalk Service Accounts
The following accounts and groups have been tested in an AD environment. BizTalk configuration creates the SQL logins and database access when you reference these accounts in the BizTalk Configuration Wizard (domain names are omitted for brevity).

Username	First Name	Last Name	Full Name
BTService	BTS	BTService	BizTalk Service Account
BTServiceBAS	BAS	BTService	BizTalk Server BAS Application Pool Account
BTServiceBASWeb	BASWeb	BTService	BizTalk BAS Publishing Web Service Account (BAM Query)
BTServiceEDI	EDI	BTService	BizTalk Base EDI Service Account
BTServiceHost	Host	BTService	BizTalk Host Instance Account
BTServiceHostISO	HostIso	BTService	BizTalk Isolated Host Instance Account
SSOService	SSO	BTService	Enterprise Single Sign-On Service
BTServiceHWF	HWF	BTService	Human Workflow Services Account
BTServiceREU	REU	BTService	Rule Engine Update Service

Set usernames according to company and environmental standards (i.e., devBTService, alphaBTService). Set account passwords according to company standards and be able to retrieve it for the configuration steps. Refer to the “Passwords” section in this document for issues surrounding generated passwords.

The installer will notice the service accounts are quite granular, with a near one-to-one mapping to the services created by BizTalk. The granularity allows corporate IT security to track or restrict access as needed. The granularity is recommended, but it is up to the system designer and enterprise security personnel to determine if it is necessary in the enterprise environment.

Note: the service accounts in the group above are intended for automaton access only, not for interactive login by users. Set the following in the Active Directory OU for each account:

Service account security considerations:

• User cannot change password: check (enterprise security will batch change the passwords).
• Password never expires: check.
• “Allow logon to terminal server”: uncheck.
• “Remote control”: uncheck “enable remote control”.
• “Log on to”: none (disallow using the account on any other workstation).

BizTalk User Accounts
The following generic accounts have been tested in an AD environment. Project management may instead require the use of actual user accounts (domain names are omitted for brevity).

User Accounts

Username	First Name	Last Name	Full Name
BTUserAdmin	Admin	BTUser	BizTalk Administrative User Account
BTUserBASTechMgr	TechManager	BTUser	BizTalk BAS Tech Manager
BTUserBASTechPub	TechPublisher	BTUser	BizTalk BAS Tech Publisher
BTUserDeploy	Deploy	BTUser	BizTalk Deployment User Account
BTUserHostInstance	HostInstance	BTUser	BizTalk Host Instance Account
BTUserHostIsolated	IsolatedHost	BTUser	BizTalk Isolated Host Instance Account
BTUserInstall	Install	BTUser	BizTalk Installation User Account
BTUserSupport	Support	BTUser	BizTalk Support Access Account

Generic user account security considerations:

• User cannot change password: check (enterprise security will batch change the passwords).
• Password never expires: check.
• “Allow logon to terminal server”: check.
• “Remote control”: check “enable remote control”.
• “Log on to”: none (disallow using the account on any other workstation).

Note: any of these accounts can all be disabled if the roles they are to provide are assigned to actual users. In the early stages of release one and release two, the author is assuming the use of these accounts in the development, alpha test and beta test environments.

BizTalk Group Accounts
The following group accounts have been tested in an AD environment. Recommendations and notes on domain groups:

• Create the groups and add members prior to installing BizTalk.
• Domain groups are “Global” groups.
• Use <DomainName>\<UserName> when specifying domain account information in the Configuration Wizard.
• Groups and user/service accounts must belong to the domain in which the BizTalk server belongs (the Configuration Wizard checks this and will not display accounts or groups containing accounts from other domains).
• BizTalk Server requires domain accounts for all clustering scenarios.
• When installing BizTalk, the console user needs to be in the BizTalk Server Administrators group, SSO Administrators (only when configuring the master secret server); Windows administrator; SQL Server administrator; OLAP administrator. The BTUserInstall account should be used for installation and configuration; disable the account after configuration is complete.
• To allow HAT to attach orchestrations to the debugger, the developer needs to belong to the BizTalk Server Administrators group, as outlined above in “BizTalk Development Accounts”.

Group Name	Group Type	Members
BizTalk Application Users	Global	BTServiceHost; BTUserHostInstance
BizTalk BAS Administrators	Global	BTUserAdmin; SPAdmin; BTServiceBASWeb; BTUserInstall
BizTalk BAS Managers	Global	(none)
BizTalk BAS Users	Global	(none)
BizTalk BAS Web Services Group	Global	BTServiceBASWeb
BizTalk Development Users	Global	(local domain accounts of development users)
BizTalk Deployment Users	Global	(local domain accounts of deployment users)
BizTalk EDI Subsystem Users	Global	BTServiceEDI
BizTalk Host Users	Global	BTUserHostInstance
BizTalk Isolated Host Users	Global	BTServiceHostIso; BTUserHostInstance; BTServiceHWF
BizTalk Server Administrators	Global	BTUserAdmin; BTUserBASTechMgr; BTServiceBASWeb; BTUserInstall; BizTalk Development Users; BizTalk Deployment Users
BizTalk Support Users	Global	BTUserSupport (local domain accounts of support users)
SSO Administrators	Global	SSOService; BTUserInstall; Local Administrator
SSO Affiliate Administrators	Global	BizTalk Development Users; BizTalk Deployment Users; BTServiceHostIso; <console user>
WSS Administrators	Global	SPAdmin; BTUserInstall; BTUserDeploy; BizTalk Development Users; BizTalk Deployment users

Note: Do not enable BizTalk Development users in upline environments. Domain accounts are omitted for brevity.

Local Administrator Accounts
Confirm or add the following accounts and groups to the Local Administrators group on the SQL Server:

• Domain\BTUserInstall (disable when configuration is complete)
• Domain\BTUserDeploy (disable in production when deployment is complete)
• Domain\SPAdmin
• Domain\SQLAdmin
• Domain\SQLService
• Domain\BizTalk Development Users (omit in upline environments)
• Domain\BizTalk Deployment Users (omit in development environments)

Confirm or add the following accounts and groups to the Local Administrators group on the BizTalk Server:

• Domain\BTUserInstall (disable when configuration is complete)
• Domain\BTUserDeploy (disable in production when deployment is complete)
• Domain\BTUserSupport
• Domain\SPAdmin
• Domain\BizTalk Development Users (omit in upline environments)
• Domain\BizTalk Deployment Users (omit in development environments)

SQL Server Administrator Accounts
Setup programs accept input from the installer and assigns SQL Roles to users and groups:

• During the WSS setup, the SPAdmin account is granted Security Administrator and Database Creator rights on the SQL Server. This can be removed if the SPAdmin account is a member of the Local Administrators group.

Email Accounts
BizTalk and SharePoint services will send mail based on certain system events. Setup prompts for an email address during the configuration process. Create email aliases for these purposes and monitor them during setup and unit testing. In the production environment, this account should be accessible by a system administrator who is monitoring the system. These email accounts include:

• BAS Site Email
• WSS Administrator Email

Neither is of a critical nature until BAS is enabled.

Password Considerations for Development
For Development, and Test environments, passwords for the accounts can be set by a standard and be distributable. Installer standards vary; I use the template of initial capital letters abbreviating the service component followed by a lower-case abbreviation for the rest of the account (service or user). For service accounts, I use ‘Serv’, for user accounts, ‘User. Hence:

• WSS (SharePoint) Service and admin account (SPAdmin) passwords: ‘SPServ’.
• BizTalk Service account passwords: ‘BTServ’.
• BizTalk User account passwords: ‘BTUser’.

Some IT environments require passwords to contain non-alpha and/or numeric characters. I will typically substitute a dollar sign ($) for “s”, an ‘at’ sign (@) for “a”. The symbols are samples; develop a pattern that works for you for shared accounts with semi-public passwords.

Sample redistributable passwords in use in the development environment are:

• BT$erv99 BizTalk Service Accounts
• BTU$er99 BizTalk User Accounts
• SP$erv99 WSS Service Account (SPAdmin)
• SQL$erv99 SQL Service/Access/Admin Account

Note: These recommendations are for development and shared environments only. This document does not attempt to set or discourage the use of corporate password policies. See your administrator for password requirements.

Note: If corporate password policy includes generated passwords, be advised that some symbols and symbol combinations are special-use characters to XML. Inappropriate use of these characters will prevent the document from being opened during the configuration step. These symbols include “&”, “<”, “>, single- and double-quote, and may include others. Test the configuration XML file prior to executing file-based configuration. You can test thihs reliably for proper XML formatting by opening the document in Internet Explorer (or an XML editor) with the generated passwords embedded therein.

Deployment of secure passwords in upline environments (including the method to test the BizTalk configuration file) is referenced in the BizTalk Configuration document.

Runtime Security Considerations
This document assumes an insular environment, with BizTalk and other enterprise components functioning within a trusted boundary. As the development moves into production, designers should perform application threat modeling using STRIDE, DREAD and SD3 practices. Such modeling and practices is outside the scope of this document.

Please see the “BizTalk Development Infrastructure Recommendations” for more detail on the trust boundary.

BizTalk Configuration Wizard
This section is called out in the “BizTalk Configuration” document. It assumes you’ve created the accounts as described above, modifying names as necessary for a specific environment.

Windows Accounts Pane in the Configuration Wizard:

Name	Account
BizTalk Administrators Group	Domain\BizTalk Server Administrators
BAS Site Owner	Domain\SPAdmin
SSO Administrators	Domain\SSO Administrators
SSO Affiliate Administrators	Domain\SSO Affiliate Administrators
BizTalk Host Users Group	Domain\BizTalk Application Users
BizTalk Isolated Host Users	Domain\BizTalk Isolated Host Users
EDI Base EDI Users Group	Domain\EDI Subsystem Users
BizTalk BAS Web Services Group	Domain\BizTalk BAS Web Services Group
BizTalk BAS Users	Domain\BizTalk BAS Users
BizTalk BAS Managers	Domain\BizTalk BAS Managers
BizTalk BAS Administrators	Domain\BizTalk BAS Administrators

Note: the BizTalk Configuration Wizard checks for valid accounts and groups in this step.

Windows Services Configurations Pane:

Name	Log on as:
BizTalk Server BAS Publishing Web Service Account	Domain\BTServiceBASWeb
BizTalk BAS Management Web Service Account	Domain\BTServiceBASWeb
Human Workflow Services User Account	Domain\BTServiceHWF
BizTalk Base EDI service	Domain\BTServiceEDI
BizTalk Isolated Host Instance Account	Domain\BTServiceHostIso
BizTalk Host Instance Account	Domain\BTServiceHost
Rule Engine Update Service	Domain\BTServiceREU
Enterprise Single Sign-on Service	Domain\SSOService
BAM Query Web Service user	Domain\BTServiceBASWeb
BizTalk Server BAS Application Pool Account	Domain\BTServiceBAS

This pane requires the installer to know the passwords for each account, as the wizard checks each credential as it is entered. If deploying into the development, alpha or beta environments, these passwords are likely to be semi-public, and follow some pattern. See “Password Considerations”, above.

BizTalk Server 2004: When Good Configurations go Bad

Anyone who has configured BizTalk realizes the MS Team spent their budgets and their time on the important stuff: transactions, platform, security, etc.  They did NOT spend their dough on the configuration components that ship with BizTalk.  On a side note, some third-party folks have started coming to the rescue (TopXML is the one I looked at most recently), but for the time being, we must work with what we have.

Removing a configuration from a BizTalk Server requires only the execution of ‘configframework.exe /u’ at a command prompt.  However, there have been times (unfortunately, more than one), that I’ve had BizTalk configurations fail and entered into a twilight zone state; a state where I can neither remove a configuration or create a new configuration file.  It is quite frustrating: the Event Log is devoid of anything useful and even the configuration log (saved in your temp folder unless you use the /l switch to create your own) spouts garbage like "SQL Server is unavailable". 

The solution?  Uninstall BizTalk entirely and start over.  Annoying at best; maddening at the worst.

I got medieval on the server the other day and ran a registry trace utility to track what BizTalk configuration adds and takes away on a vanilla box.  Amazing, but it came down to four keys beneath HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration (default settings):

• BizTalkAdminNTGroup="BizTalk Server Administrators"
• BizTalkGroupName=BizTalk Group
• MgmtDBName=BizTalkMgmtDb
• MgmtDBServer=(servername)

Remove these four keys and you will be able to generate a configuration file.

Note: Leave “Default” and "SQLScriptPath" alone (if SQLScriptPath is removed or cleared out, BizTalk configuration will fail).

I tested this twice, no more, and it worked for me.  I hope it works for you too.

Removing a BizTalk Server 2004 Configuration

You can remove configuration settings from a BizTalk Server without uninstalling the BizTalk by opening a command prompt and executing:

configframework.exe /u

Confirm the configuration has been removed by launching BizTalk Administrator.  The application will notify you of the lack of configuration on startup.  This is useful if you are changing the role of an existing BizTalk server in an organization.

Note: this only removes the configuration settings from the server.  It does not remove SQL databases, SQL maintenance, OLAP cubes, users or groups.  By itself, this does not restore the server to a vanilla state.  If your intent is to do this, please see “Restoring a BizTalk Server to a Vanilla State”.

Restoring a BizTalk Server 2004 to a ‘Vanilla’ State

A ‘Vanilla’ BizTalk installation is an installation of the BizTalk binaries on a server.  Vanilla does not include configuration, management, rules or content databases.  This document assumes you have run ‘configframework /u’ to remove the BizTalk configuration (see the post titled “Removing a BizTalk Configuration“ for details).

Note: You must restore a BizTalk server to vanilla to apply a different configuration file.  The steps are outlined below.

Remove SQL Server Databases
The following list includes the default names for each of the SQL Server databases; if your installation used another naming convention, replace this list with your own:

• BAMPrimaryImport (BAM Primary Import database)
• BAMStarSchema: (Star Schema database)
• BizTalkDTADb (BizTalk Tracking database)
• BizTalkEDIDb (BizTalk EDI database)
• BizTalkHwsDb (HWS Administration database)
• BizTalkMgmtDb (Configuration database)
• BizTalkMsgBoxDb (BizTalk MessageBox database)
• BizTalkRuleEngineDb (Rule Engine database)
• TPM (Trading Partner Management database)
• BAMAnalysis (BAM Analysis database)
• BizTalkAnalysisDb (Tracking Analysis Server database)
• BizTalkEDIDb  (BizTalk Base EDI database)

Note: Enterprise Single-Sign is omitted from this list and is addressed below.

Remove MSSQL Analysis Services Cubes
The following list includes the default names for each of the Analysis Services cubes; if your installation used another naming convention, replace this list with your own:

• BAMAnalysis
• BizTalkAnalysisDB

Remove BizTalk SQL Server Maintenance Jobs
BizTalk Configuration installs a series of database maintenance operations on a server.  Remove the following jobs from the SQL Server:

• 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

Remove BizTalk SQL Server Logins
BizTalk Configuration associates several Windows users to SQL logins.  Remove the following logins (or their equivalents, if you gave them different names) from SQL Server:

• Domain\BizTalk Application Users
• Domain\BizTalk BAS Web Services Group
• Domain\BizTalk Isolated Host Users
• Domain\BizTalk Server Administrators
• Domain\BTServiceBASWeb
• Domain\BTServiceHWF
• Domain\BTServiceREU
• Domain\EDI Subsystem Users
• Domain\SSO Administrators

Determine Action for the Enterprise Single Sign-On Database
The Enterprise Single Sign-On database (default name SSODB) merits special attention. 

• If the current server is the only server in the BizTalk group, SSODB can be removed when restoring the server to a vanilla state. 
• If the current server is number 2-n of the BizTalk group, do not remove SSODB.
• If the current server is the first server in the group, it holds the master secret in SSODB.  You can remove SSODB and restore the master secret from a backup (see the “Backing Up the Master Secret” post).

Note: do not remove SSODB unless you have specific needs for its removal, or are relocating the master secret to another database in the enterprise.