Data tracking and query performance issues in the Azure BizTalk Services Tracking database

View products that this article applies to.

Symptoms

Issue 1

When you manage the Tracking store and Tracking database in Microsoft Azure BizTalk Services, the tracked data (storage and SQL) is not archived by default. You have to manually clean up the data. For information about how to do this, see workaround 1.

Issue 2

When you try to query tracking data in the BizTalk Services Management portal, the query takes longer than expected, or you continually receive error messages and cannot retrieve the data.

The most common error message resembles the following:

An error occurred while retrieving tracking data. An error occurred while processing this request.

If you cannot obtain the tracking records on the Tracking portal, but the runtime environment is working fine, the tracking data has probably reached a limit where it's causing the query performance issues.

In this situation, you should purge the data by using workaround 1.

If you continue to receive an error on the portal after you clean up a reasonable volume of data, see workaround 2.

↑ Back to the top

Resolution

Workaround for Issue 1

To delete older records in the Tracking database, use one of the following methods:

Use the BizTalk PowerShell Clear-AzureBizTalkTrackingStore cmdlet:
1. Run the following command to register the BizTalk PowerShell cmdlet:
  
  import-module "C:\Program Files\Windows Azure BizTalk Services Tools\Microsoft.BizTalk.Services.Powershell.dll"
2. You may have to install an assembly in the global assembly cache. To do this, run the following cmdlet:
  
  gacutil /i Microsoft.WindowsAzure.StorageClient.dll
3. Update the SqlString.txt and AzureStore.txt files by using your SQL Azure and storage details.
  
  Sample AzureStore.txt:
```
DefaultEndpointsProtocol=https;AccountName=mystorageaccount;AccountKey=accountkey 
```
  Sample SQLString.txt:
```
Server=tcp:mydb.database.windows.net,1433;Database=mydb;User ID=user@mydb;Password=password;Trusted_Connection=False;Encrypt=True;Connection Timeout=120; 
```
4. Run the following command, which specifies the number of days that the tracking store data must be persisted:
  
  Clear-AzureBiztalkTrackingStore -Sql (Get-Content sqlString.txt) -AzureStore (Get- Content AzureStore.txt) -Ndays 2 true -NoPrompt
  
  For more information about the Clear-AzureBizTalkTrackingStore cmdlet, see Clear-AzureBizTalkTrackingStore.
Use the truncate stored procedure to clear SQL Tracking data only. You can run the following stored procedures to delete tracking records that are older than the specified date:

PurgeBridgeTrackRecords '<Date>'

PurgeB2BTrackRecords '<Date>'

Workaround for Issue 2

To work around this issue, check whether you are able to query the SQL database directly.

You can use SQL Server Management Studio (SSMS) to connect to SQL Azure. To obtain details of the SQL Azure version that's being used, you must go to the Azure portal and click BizTalk Services. On the dashboard tab, you can see the SQL Azure database that's being used for tracking. Click the database name on the database page, and then click Dashboard. On the left side of the screen, click Show connection string. This lets you see the database name and the user ID for the database.

After you connect to SQL Azure, run a simple query such as Select * from TrackRecordProperties2. If this query takes a long time to run or does not finish running, you must clean some additional data.

Your retention policy may be adding a burden to the Tracking database. Or, your business volume on even just a few days may contain a large volume of data. If the data-sizing guideline does not fit well with the specified retention period, consider backing up the data to some other SQL Server computer before you purge from the Tracking store.

Another option involves upgrading the SQL Azure server to a later edition or to a higher-performance level in order to provide more processing power for large tracking environments.

For more information, see Upgrade SQL Database Web or Business Databases to new service tiers.

↑ Back to the top