Archiving and Purging the BizTalk Tracking Database in BizTalk Server 2004 SP2

Published: July 11, 2006

Summary: This white paper describes how you can configure BizTalk Server 2004 SP2 to take advantage of automated archiving and purging of the BizTalk Tracking database. It also describes factors you should consider during configuration to achieve and maintain high performance.

On This Page

How Automated Archiving and Purging Works
How to Configure the BTS_BACKUP_USERS Role for Archiving and Purging Data from the BizTalk Tracking Database
How to Configure the DTA Purge and Archive Job
How to Purge Data from the BizTalk Tracking Database
How to Manually Purge Data from the BizTalk Tracking Database
How to Enable Automatic Archive Validation
How to Copy Tracked Messages into the BizTalk Tracking Database
Improving the Performance of the Archiving and Purging Process

How Automated Archiving and Purging Works

As Microsoft BizTalk Server processes more and more data on your system, the BizTalk Tracking (BizTalkDTADb) database continues to grow in size. Unchecked growth decreases system performance and may generate errors in the Tracking Data Delivery Service (TDDS). In addition to general tracking data, tracked messages can also accumulate in the MessageBox database, causing poor disk performance.

While BizTalk Server 2004 included sample scripts for archiving tracked messages and purging the BizTalk Tracking database, BizTalk Server 2004 Service Pack 2 (SP2) includes archiving and purging functionality that automates both processes using the DTA Purge and Archive job. By archiving and purging data from the BizTalk Tracking database, you can maintain a healthy system, as well as keep your tracking data archived for future use. Because BizTalk Tracking database archives accumulate over time and consume disk space, it is a good idea to move the BizTalk Tracking database archives to secondary storage on a regular basis.

When you purge data from the BizTalk Tracking database, the DTA Purge and Archive job purges different types of tracking information such as message and service instance information, orchestration event information, and rules engine tracking data.

The age of a tracking data record is based on the time the tracking data was inserted into the BizTalk Tracking database. The DTA Purge and Archive job uses the time stamp to continuously verify whether the record is older than the live window of data. After every live window period, the BizTalk Tracking database is archived and a new archive file is created. At each SQL Server Agent job interval specified by the job schedule, all completed tracking data older than the live window period is purged.

BizTalk Server uses the concept of a soft purge and a hard purge. The soft purge is used to purge completed instances, while the hard purge is only used to purge incomplete instances.

Soft Purge

In the DTA Archive and Purge job, the sum of the LiveHours and LiveDays parameters is the live window of data you want to maintain in your BizTalk Server environment. All data associated with a completed instance older than this live window of data is purged.

For example, you can configure the DTA Purge and Archive job to run every 20 minutes, and set LiveHours=1 and LiveDays=0. The first time this SQL Server Agent job runs (T0), it takes a backup of the tracking database by creating an archive and an entry is saved in the database with this time stamp. A successful archive is necessary in order to purge tracking data. If the archive was successful, then all the data associated with the instances that completed over an hour ago is purged. Each time the job runs, completed data over one hour old is purged. On the third run (after one hour), a new archive is created that contains the data for all instances that were inserted into the tracking database in the last one hour segment.

Here is how you would configure the Archive and Purge step in the DTA Purge and Archive job to match the example above:

exec dtasp_BackupAndPurgeTrackingDatabase
1, --@nLiveHours 1,
0, --@nLiveDays
1, --@nHardDeleteDays
‘\\server\backup’, --@nvcFolder
null, --@nvcValidatingServer
0 --@fForceBackup Soft purge process

The time stamp of the last backup is stored in the BizTalk Tracking database and ensures that data is only purged if it is in the previous archive. For additional reliability, archives are overlapped by approximately 10 minutes. The following figure, based on the example above, shows the soft purge process. Note that the Archiving and Purging tasks do not necessarily happen at the same time.

Figure 1 - The soft purge process

soft purge process

Hard Purge

Because the soft purge only purges data associated with completed instances, if you have many looping instances that run indefinitely, then your tracking database would grow and these instances would never be purged. The hard purge date allows all information older than the specified interval to be purged except for information indicating a service's existence. The hard purge setting should always be greater than your normal purge setting.

Archiving and purging includes the features described in the following table:

Feature

Description

Hard purge

Enables you to configure a time interval to purge information for incomplete instances older than a specified date.

Copying tracked messages to tracking database

Using the CopyTrackedMessageToDTA option, you can directly copy tracked messages from the MessageBox servers to your BizTalk Tracking database. This is required in order to purge data using the DTA Purge and Archive job.

Archive validation

Enables you to optionally set up a secondary database server to validate the archives as they are created.

Health and Activity Tracking (HAT) support for multiple BizTalk Tracking database versions

Enables you to use the HAT with multiple versions of the BizTalk Server 2004 tracking databases both before and after installing BizTalk Server 2004 SP2.

Reduction of tracking data

Substantially reduces the amount of tracking data stored without reducing any tracking information generated. This results in slower growth of the tracking database.

Faster HAT operations, significant optimization in database schemas

Enables you to use HAT tasks for finding messages and service instances on large databases; this feature has been significantly optimized.

Archive Validation

Archive validation is a feature that allows you to set up a secondary database server to validate the archives (backups) as they are created. Because the archiving process is a simple backup, it is possible the actual image stored on the disk can be corrupted because of a hardware issue. Using the archive validation feature, you can ensure the archive was successful and can be restored. This is an optional feature

The archive validation feature works in the following manner:

  1. After an archive is created, the validation server is notified that a new archive has been created.

  2. It will attempt to restore the archive.

    • If the restore is successful, it will communicate this back to the DTA database. Until a successful restore is completed, the purge job will not purge any more data.

    • If the restore is unsuccessful, this will be communicated back and then the purge job will create another archive and await validation of the new archive. This prevents the possibility of a bad archive causing you to lose tracking data.

How to Configure the BTS_BACKUP_USERS Role for Archiving and Purging Data from the BizTalk Tracking Database

The DTA Purge and Archive (BizTAlkDTADb) job normally runs using the credentials of the logged-on SQL Server Agent service account user. For added security, however, you can configure the DTA Purge and Archive (BizTalkDTADb) job to run using the credentials of an account which is a member of the BTS_BACKUP_USERS role. This helps to prevent elevation of privileges by running SQL Server Agent jobs under accounts with essential permissions.

Prerequisites

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

To configure the BTS_BACKUP_USERS role for archiving and purging data from the BizTalk Tracking database

  1. Click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager.

  2. Open the appropriate server by clicking it, double-click Databases, double-click BizTalkDTADb, and then click Roles.

  3. In the details pane, double-click BTS_BACKUP_USERS.

  4. In the Database Role Properties – BTS_BACKUP_USERS dialog box, click Add.

  5. In the Add Role Members dialog box, select an account with SQL Server Agent Service credentials, and then click OK.

    Dd879255.note(en-US,BTS.10).gif Note

    If the account you want to add to the role is not listed, you must first grant that account the appropriate SQL Server logon and database user rights. For more information, see SQL Server Books Online.

How to Configure the DTA Purge and Archive Job

Before you can archive or purge data from the BizTalk Tracking (BizTalkDTADb) database, you must configure the DTA Purge and Archive (BizTalkDTADb) job. This job is configured to call the stored procedure dtasp_BackupAndPurgeTrackingDatabase, which uses the six parameters you must configure in this job.

Prerequisites

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

To configure the DTA purge and archive job

  1. On the SQL server that hosts the BizTalk Tracking (BizTalkDTADb) database, click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager.

  2. Open the appropriate server by clicking it, double-click Management, double-click SQL Server Agent, and then click Jobs.

  3. In the details pane, right-click DTA Purge and Archive (BizTalkDTADb), and then click Properties.

  4. In the DTA Purge and Archive (BizTalkDTADb) Properties dialog box, click the Steps tab, click Archive and Purge, and then click Edit.

  5. On the General tab, in the Command box, edit the following parameters as appropriate, and then click OK.

    • @nLiveHours tinyint — Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. Default is 0 hours.

    • @nLiveDays tinyint — Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. Default interval is 1 day.

      Dd879255.note(en-US,BTS.10).gif Note

      For the purposes of the BizTalk Tracking (BizTalkDTADb) database, the sum of LiveHours plus LiveDays is the live window of data you want to maintain in your BizTalk Server environment. All data associated with a completed instance older than the live window of data is deleted.

    • @nHardDeleteDays tinyint — All data (even if incomplete) older than this will be deleted. The time interval specified for HardDeleteDays should be greater than the live window of data. The live window of data is the interval of time for which you want to maintain tracking data in the BizTalk Tracking (BizTalkDTADb) database. Anything older than this interval is eligible to be archived at the next archive and then purged. Default is 30 days.

    • @nvcFolder nvarchar(1024) — Folder in which to put the backup files.

    • @nvcValidatingServer sysname — Server on which validation will be done. NULL value indicates no validation is being done. Default is NULL.

    • @fForceBackup int — Default is 0. This is reserved for future use.

      The edited command should look similar to this:

      exec dtasp_BackupAndPurgeTrackingDatabase 12, 1, 3, '\\MyBizTalkServer\backup', null, 0
      
  6. In the details pane, right-click the DTA Purge and Archive (BizTalkDTADb) job, and then click Enable Job.

    In the Enabled column, the status changes to Yes.

How to Purge Data from the BizTalk Tracking Database

When you purge data from the BizTalk Tracking (BizTalkDTADb) database, the DTA Purge and Archive job purges different types of tracking information such as message and service instance information, orchestration event information, and rules engine tracking data from the BizTalk Tracking (BizTalkDTADb) database.

Dd879255.important(en-US,BTS.10).gif Important

The BizTalk Tracking (BizTalkDTADb) database is not archived using this procedure.

Prerequisites

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

To purge data from the BizTalk Tracking database

  1. Click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager.

  2. Open the appropriate server by clicking it, double-click Management, double-click SQL Server Agent, and then click Jobs.

  3. In the details pane, right-click DTA Purge and Archive (BizTalkDTADb), and then click Properties.

  4. In the DTA Purge and Archive (BizTalkDTADb) Properties dialog box, click the Steps tab, click Archive and Purge, and then click Edit.

  5. On the General tab, in the Command box, change exec dtasp_BackupAndPurgeTrackingDatabase to exec dtasp_PurgeTrackingDatabase.

    Dd879255.caution1(en-US,BTS.10).gif Caution

    The exec dtasp_PurgeTrackingDatabase stored procedure does not archive the BizTalk Tracking (BizTalkDTADb) database. Before using this option, be certain that you no longer require archived tracking data.

  6. In the Command box, edit the following parameters as appropriate, and then click OK.

    • @nHours tinyint — Any completed instance older than (live hours) + (live days) will be deleted along with all associated data.

    • @nDays tinyint — Any completed instance older than (live hours) + (live days) will be deleted along with all associated data. Default interval is 1 day.

    • @nHardDays tinyint — All data older than this day will be deleted, even if the data is incomplete. The time interval specified for HardDeleteDays should be greater than the live window of data. The live window of data is the interval of time for which you want to maintain tracking data in the BizTalk Tracking (BizTalkDTADb) database. Anything older than this interval is eligible to be archived at the next archive and then purged.

    • @dtLastBackup — Set this to GetUTCDate() to purge data from the BizTalk Tracking (BizTalkDTADb) database. When set to NULL, data is not purged from the database.

  7. In the details pane, right-click the DTA Purge and Archive (BizTalkDTADb) job, and then click Enable Job.

  8. In the Enabled column, the status changes to Yes.

How to Manually Purge Data from the BizTalk Tracking Database

The DTA Archive and Purge SQL Server Agent job reduces the need to manually purge data from the BizTalk Tracking (BizTalkDTADb) database due to continuous purging of the database and compaction of stored tracking data. You might need to manually purge data if your BizTalk Tracking (BizTalkDTADb) database has grown so much that sustained performance degradation is occurring and the DTA Archive and Purge job is unable to keep up with the database growth.

Dd879255.caution1(en-US,BTS.10).gif Caution

Performing this procedure deletes all tracking data for completed instances from the BizTalk Tracking (BizTalkDTADb) database regardless of completion time. Before performing this procedure, you should archive the BizTalk Tracking (BizTalkDTADb) database separately from the other BizTalk Server databases.

Prerequisites

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

To manually purge data from the BizTalk Tracking database

  1. Back up your BizTalk Server databases.

  2. Archive the BizTalk Tracking (BizTalkDTADb) database.

  3. Open Services. Click Start, click Run, and then type services.msc.

  4. Right-click each of the following services, and then click Stop:

    • BizTalk Base EDI Service

    • Enterprise Single Sign-On Service

    • Rule Engine Update Service

  5. Click Start, click Programs, click Microsoft BizTalk Server 2004, and then click BizTalk Server Administration.

  6. In the BizTalk Server Administration Console, double-click the Microsoft BizTalk Server 2004 (Local) node and expand the Hosts node.

  7. For each in-process Host, go to the list of host instances on the right side of the results pane, right-click each running host instance, and then click Stop.

  8. Click Start, click Run, type cmd, and then click OK.

  9. On all computers in the BizTalk group where the BizTalk Server runtime is installed, at the command prompt, type:

    net stop iisadmin /y

    This stops the IIS Admin Service and all dependent services, one by one. Write down the list of services as each one is stopped. You will need to use this list of services later when you restart IIS.

    Following is an example of the output you will see after issuing this command (the dependent services listed on your computer may vary):

    The following services are dependent on the IIS Admin Service service. 
    Stopping the IIS Admin Service service will also stop these services.
    World Wide Web Publishing Service
    HTTP SSL
    
  10. Click Start, click Run, type isqlw.exe, and then click OK.

  11. In the Connect to SQL Server dialog box, specify the name of the SQL server where the BizTalk Tracking (BizTalkDTADb) database resides and the appropriate authentication type to connect to the appropriate SQL server.

  12. In SQL Query Analyzer, click the BizTalkDTADb database, click Stored Procedures, right-click dbo.dtasp_PurgeAllCompletedTrackingData, and then click Open.

  13. In the Execute Procedure dialog box, click Execute.

    This stored procedure deletes all tracking data associated with completed instances regardless of their completion time.

  14. Open Services. Click Start, click Run, and then type services.msc.

  15. Right-click each of the following services, and then click Start:

    • BizTalk Base EDI Service

    • Enterprise Single Sign-On Service

    • Rule Engine Update Service

  16. Click Start, click Programs, click Microsoft BizTalk Server 2004, and then click BizTalk Server Administration.

  17. For each in-process Host, go to the list of host instances on the right side of the results pane, right-click each stopped host instance, and then click Start.

  18. Click Start, click Run, type cmd, and then click OK.

  19. At the command prompt, restart each of the IIS services that you stopped in step 9. Type:

    net start <IISserviceName>

    Where <IISserviceName> is the name of the IIS service you want to restart. You must repeat this command for each of the IIS services.

How to Enable Automatic Archive Validation

Automatic archive validation enables you to validate the archives as they are created. Before you can enable automatic archive validation, you must set up a secondary database server, also called a validation server. Because the archiving process is a simple backup, it is possible that the actual image stored on the disk can be corrupted due to a hardware issue.

Using the archive validation feature, you can ensure the archive (backup) was successful and can be restored. After an archive is created, the validation server is notified that a new archive has been created. The validation server attempts to restore the archive. A validation server must be another instance of SQL Server different from the one in which the job is running.

If the restore is successful, the validation server communicates this information back to the BizTalk Tracking (BizTalkDTADb) database. Until a successful restore is completed, the purge job will not purge any more data.

If the restore is not successful, the validation server communicates this information back to the BizTalk Tracking database. The purge job creates another archive and awaits validation of the new archive. This prevents the possibility of a corrupted archive causing you to lose tracking data.

Prerequisites

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

To enable automatic archive validation

  1. On the validation server, click Start, click Run, type isqlw.exe, and then click OK.

  2. Click File, and then click Connect.

  3. In the Connect to SQL Server dialog box, click the server from the list, or click the ellipsis () button to browse to the SQL server where you can validate the archive by performing a test of the restore process, and then click OK.

    Dd879255.note(en-US,BTS.10).gif Note

    This server should not be another BizTalk Server database server as it reduces system performance when validating the archive.

  4. Click File, click Open, and then browse to the following SQL script:

    %SystemRoot%\Program Files\Microsoft BizTalk Server 2004\Schema\BTS_Tracking_ValidateArchive.sql
  5. Click Query, and then click Execute.

    Dd879255.note(en-US,BTS.10).gif Note

    The BTS_Tracking_ValidateArchive.sql script only works if the folder where you are archiving your BizTalk Tracking (BizTalkDTSDb) database is a network share.

    The BTS_Tracking_ValidateArchive.sql script creates a SQL Server Agent job called ValidateArchive.

  6. Click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager.

    The archiving and purging process potentially accesses\updates databases in different SQL servers, so you must set up linked servers between the related SQL Server instances. You must set up a linked server between:

    • Each of your BizTalk MessageBox (BizTalkMsgBoxDb) databases and the BizTalk Tracking (BizTalkDTADb) database.

    • The BizTalk Tracking (BizTalkDTADb) database and the validating server for archive validation.

    Dd879255.note(en-US,BTS.10).gif Note

    The account used for running the job should have Database Operator (DBO) privileges on both the databases.

  7. Open the appropriate server by clicking it, double-click Security, right-click Linked Servers, and then click New Linked Server.

  8. In the Linked Server Properties - New Linked Server dialog box, in Linked server, enter the name of the server you want to link to.

    For example, the server hosting the BizTalk MessageBox (BizTalkMsgBoxDb) database, BizTalk Tracking (BizTalkDTADb) database, or the validation server.

  9. Under Server type, click SQL Server, and then click OK.

  10. In SQL Server Enterprise Manager, open the appropriate server by clicking it, double-click Management, double-click SQL Server Agent, and then click Jobs.

  11. In the details pane, right-click ValidateArchive, and then click Properties.

  12. In the ValidateArchive Properties dialog box, click the Steps tab, click validate, and then click Edit.

  13. In the Edit Job Step dialog box, on the General tab, in the Command box, in the command, exec dtasp_ValidateArchive null, null, replace null, null with the name of the server hosting the BizTalk Tracking database, surrounded by single quotes, followed by the name of the BizTalk Tracking database, surrounded by quotes, and then click OK. For example:

    exec dtasp_ValidateArchive ' <TrackingServerName> ', ' <TrackingDatabaseName> '

    Dd879255.note(en-US,BTS.10).gif Note

    The ValidateArchive job does not have a schedule and you should not configure a schedule for it. Instead, the DTA Purge and Archive (BizTalkDTADb) job starts this job automatically when an archive is created.

How to Copy Tracked Messages into the BizTalk Tracking Database

The archiving and purging process potentially accesses and/or updates databases in different SQL servers, so you must set up linked servers between the involved SQL Server instances. You can directly copy tracked messages from the BizTalk MessageBox (BizTalkMsgBoxDb) database server to your BizTalk Tracking (BizTalkDTADb) database using a linked server. You must set up linked servers between:

  • Each of your BizTalk MessageBox (BizTalkMsgBoxDb) databases and the BizTalk Tracking (BizTalkDTADb) database.

  • The BizTalk Tracking (BizTalkDTADb) database and the validating server for archive validation.

Dd879255.note(en-US,BTS.10).gif Note

In SQL Server Agent, verify that the copy job runs without errors. Otherwise, errors might prevent data from being moved to the tracking database.

Important If you add a new MessageBox database, you will need to perform this procedure again for the new MessageBox database.

Prerequisites

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

To copy tracked messages into the BizTalk Tracking database

  1. Click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager.

  2. Open the appropriate server by clicking it, double-click Management, double-click SQL Server Agent, and then click Jobs.

  3. In the details pane, right-click CopyTrackedMessages_ <msgboxname> , and then click Properties.

  4. In the CopyTrackedMessages_ <msgboxname> Properties dialog box, click the Steps tab, click Purge, and then click Edit.

  5. On the Steps tab, in the Command box, edit the tracking server and database names parameters as appropriate, and then click OK.

  6. In the details pane, right-click the CopyTrackedMessages_ <msgboxname> job, and then click Enable Job.

    In the Enabled column, the status changes to Yes. The messages will be copied to the BizTalk Tracking (BizTalkDTADb) database.

Dd879255.important(en-US,BTS.10).gif Important

If you add a new MessageBox, you will need to perform this procedure again for the new MessageBox.

Improving the Performance of the Archiving and Purging Process

The amount of data stored in the BizTalk Server databases can grow very quickly depending on how you have designed your BizTalk Server scenario, depending on the number and size of messages processed by your BizTalk Server scenario, and depending on how you have configured tracking. By maintaining your database size at a healthy level, processing is more efficient and the amount of data in your system is normalized at any given time. This provides efficient and consistent performance. By automating this process you free yourself of the burden of manually maintaining your databases.

Configuring a Healthy Environment

Your strategy in maintaining a healthy BizTalk Server environment is heavily dependent on your particular scenario and the hardware it is running on. The key things to monitor are the rate of growth and the size of your BizTalk Tracking (BizTalkDTADb) database. A few tables of the tracking database account for the bulk of the database size and hence the performance impact at run time.

The same scenario can be configured to produce a vastly different amount of tracking data based on how many tracking points are present, how many different messages are used, the size of the messages, and the level of message body tracking used. Following are some important factors to monitor to maintain a healthy environment:

  • Number of tracking points - such as pipelines, orchestrations, and ports

  • Number of message properties tracked

  • Number of messages per incoming message

  • Message size

  • Traffic rate (average and peak)

  • Message body tracking configuration

When considering automatic archiving and purging of data consider how much live data you need to keep in your tracking database. You need to tune the DTA Purge and Archive job parameters as appropriate for your environment so that the targeted amount of live data can be supported by the purging performance without degradation.

The DTA Purge and Archive job can purge a certain amount of data within a given time interval. The capacity of the job depends on the scenarios running, the current database size, and the hardware. In order to have a stable environment, you must have a balance between the incoming tracking data generation and purging. In your test environment, you can find the balance by varying the live window of data and the frequency of the purging job. In a balanced state, your system will deliver sustainable throughput. Your goal is to have enough of a buffer before the BizTalk Tracking database table size causes sustained, significant performance issues.

Performance Limitations

Purging performance will not scale for all scenarios. For any scenario, it is possible to generate increasing amounts of tracking data. When tracking data is purged at a consistently slower rate, there is a buildup of the tracking database size, which worsens the purging performance further.

Under unsustainable load conditions, the copying of message bodies may also become slower and there may become a backlog in the MessageBox database. Under extreme conditions, the daily message body copying and tracking may lead to archives where the message body is not available even though it contains related instance information. Typically, periods of high loads alternate with periods of low load and enable the job to catch up during periods of low load.

Archiving and purging the BizTalk Tracking database should considerably reduce the possibility of unsustainable load conditions because of the continuous pruning of the database and the compaction of stored tracking data. These processes greatly reduce the need for manual intervention.

Show: