BizTalk: BizTalk SQL Database: Configuration Errors and Resolution
This article is intended to be a knowledge base of the BizTalk SQL database configuration errors, causes, and resolutions documented in all stages/components of BizTalk.
Join the DZone community and get the full member experience.
Join For FreeIntroduction
This article is intended to be a knowledge base of BizTalk SQL database configuration errors, causes, and resolutions documented in all stages/components of BizTalk. If the database is not configured in the manner that Microsoft requires, then we would see these types of errors. Let’s check out the causes and solutions for these errors.
Error
The above screenshot and error are self-explanatory. The error says “SIMPLE (for BizTalk Dbs - Not compatible with BizTalk BackupJob)”, which means BizTalk Dbs not compatible with BizTalk SQL jobs, and we need to make it compatible. Microsoft says the recovery model should be FULL for each of the databases in the BizTalk server.
Cause
This is because the recovery model is SIMPLE, and it should be FULL. We need to check which of these databases are currently configured in SIMPLE recovery mode. That can be exposed by executing the following stored procedure against each BizTalk database. The information we want is in the ‘status’ column, Check the result of each stored procedure.
EXEC sp_helpdb 'BizTalkMgmtDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkMsgBoxDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkDTADb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BAMPrimaryImport'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkRuleEngineDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'SSODB'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
EXEC sp_helpdb 'BAMArchive'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
Resolution
We need to change the recovery model to FULL, and this will be made compatible for our BizTalk database as per Microsoft. Run the below scripts to change the recovery mode from SIMPLE to FULL for each of the databases identified in the previous step;
ALTER DATABASE BizTalkMgmtDb
SET recovery FULL
ALTER DATABASE BizTalkMsgBoxDb
SET recovery FULL
ALTER DATABASE BizTalkDTADb
SET recovery FULL
ALTER DATABASE BAMPrimaryImport
SET recovery FULL
ALTER DATABASE BizTalkRuleEngineDb
SET recovery FULL
ALTER DATABASE SSODB
SET recovery FULL
ALTER DATABASE BAMArchive
SET recovery FULL
Verification
Again, execute the following stored procedure against each BizTalk database. This time, the information will be different and can be checked in the ‘status’ column.
EXEC sp_helpdb 'BizTalkMgmtDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkMsgBoxDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkDTADb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BAMPrimaryImport'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkRuleEngineDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'SSODB'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
EXEC sp_helpdb 'BAMArchive'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
Errors
We have two different errors in this category:
- Backup BizTalk Server SQL job is disabled or not running
- Monitor BizTalk Server SQL job is also disabled or not running.
BizTalk Server SQL Agent jobs are very critical jobs that maintain the BizTalk databases and performance, and it also controls the growth of the database when running and configured properly. Both of the above jobs are important and must be configured in order to be able to perform their roles.
Causes
The first error clearly says either this job is disabled and no history is found or maybe it's not running properly. This job has 4 steps, and we have to configure this properly to execute all steps after each. We’ll check in the resolution part how to configure this job properly.
The second error says, “This job failed probably because of some MsgBox DB integrity issues — please check the integrity issues reported by this application.” That means there are some issues in MsgBox DB that cause this issue. If we do more analysis, then we should have some error integrity issue in MsgBox DB that causes this error. We have to fix this to run Monitor BizTalk Server job properly.
Resolution
The Backup BizTalk Server SQL Agent job is a critical job that must be configured in order to be able to successfully back up the BizTalk Server databases that participate in Distributed Transaction Coordinator transactions. Databases that participate in DTC transactions such as with BizTalk must be backed up and restored as a set to ensure consistency.
Refer to:
- https://social.msdn.microsoft.com/Forums/en-US/d1fbdcc8-6b63-439f-942b-f4d3438432ef/backup-biztalk-server-job
- https://docs.microsoft.com/en-us/biztalk/core/how-to-configure-the-backup-biztalk-server-job
We’ll connect to BizTalk database server and expend SQL Server Agent. All BizTalk server related SQL jobs will be listed there, and we could verify if the ‘Backup BizTalk Server’ job is enabled and running properly. As per the below screenshot, this job is not enabled, and we have to configure it below to enable.
We’ll go to the property of this job and check the schedule, this job runs every 15 min.
Now we have to check all the steps under this job, we have 4 steps here and on success go to next step run.
- Set Compression Option
- BackupFull
- MarkandBackuplog
- Clear Backup History
Let’s start with step 1 ‘Set Compression Option’, default command on this step would be, where we can set the parameter @bCompression value to 0 or 1. It depends if we want to use compression or not.
0 – Don’t use Compression
1 – Use Compression.
exec [dbo].[sp_SetBackupCompression] @bCompression = 0 /*0 - Do not use Compression, 1 - Use Compression */
We’ll modify our command and set the value to 1, and now update command will be,
exec [dbo].[sp_SetBackupCompression] @bCompression = 1 /*0 - Do not use Compression, 1 - Use Compression */
Click OK to save this change.
Move to next step i.e. step 2 ‘BackupFull’, in this step we’ll configure the frequency, name of server and destination path of backup files.
The default command value of this step is:
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '' /* location of backup files */
Where ‘d’ indicates daily frequency, and we have to set the destination path to save the backup files.
We’ll modify our command and the updated command will be:
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'F:\Backup\BizTalk Database\Full' /* location of backup files */
Click OK to save this change.
Move to step 3 ‘MarkAndBackupLog.’ In this step, we’ll configure the destination path of log files.
The default command value of this step is:
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '' /* location of backup files */
and we have to set the destination path to save the log files.
We’ll modify our command and the updated command will be:
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'F:\Backup\BizTalk Database\Logs' /* location of backup files */
Save the Log files on the given destination.
Click OK to save this change.
Move to step 4 ‘Clear Backup History.’ In this step, we’ll delete the backup history. This step requires the parameter, @DaysToKeep, which takes the value for how many days of data you want to keep in the history table.
The default command value of this step is:
exec [dbo].[sp_DeleteBackupHistory] @
@DaysToKeep: Default value is 14 days. Determines how long the backup history is kept in the Adm_BackupHistory table. Periodically clearing the backup history helps maintain the Adm_BackupHistory table to an appropriate size. And we have one more optional parameter @UseLocalTime: Tells the procedure to use local time. The default value is 0. It uses current UTC time – GETUTCDATE() – 2007-05-04 01:34:11.933. If set to 1, then it uses local time – GETDATE() – 2007-05-03 18:34:11.933
We’ll modify our command and now updated command will be:
exec [dbo].[sp_DeleteBackupHistory] @ , @UseLocalTime
Click OK to save this change.
Now everything is done, and all the steps configured properly. The final step is to enable this job and check if this is enabled without any issue.
Right-click the Backup BizTalk Server job and select Enable. The status should change to Success. And we can see this in the below screenshot.
Verification
Once all the steps are configured and the job is enabled, we can check the job execution result in job history. Simply go to properties of job and click on ‘view Job History.’ It’ll open a new window where we can view the all step results and execution time.
Error
When we run BHM or MsgBoxViewer, we would find these types of errors: “DTA Orphaned Instances (Incompleted Instances in DTA but not in Msgbox)” or “Total Integrity issues Found in MsgBox Db(s).” These errors come if we terminate an orphaned tracking instance not in the proper manner. So, ideally, we should use Terminal tool to clear such types of instances.
Cause
When we 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.
But if an exception is caught and handled in an orchestration without the tracking turned on, an orphaned tracking instance with a Started state and exception information may be inserted into the BizTalk Tracking (BizTalkDTADb) database. This record will remain after purging the database.
Sometimes this can happen due to a few very common reasons. For example, an orchestration might throw an exception or be terminated by an administrator. We can detect these using the following query:
SELECT COUNT(*) FROM [BizTalkDTAdb].[dbo].[dta_ServiceInstances]
WHERE dtEndTime IS NULL AND [uidServiceInstanceId] NOT IN
(SELECT [uidInstanceID] FROM [BizTalkMsgBoxDb].[dbo].[Instances] WITH (NOLOCK)
UNION
SELECT [StreamID] FROM [BizTalkMsgBoxDb].[dbo].[TrackingData] with (NOLOCK))
Resolution
The recommended way to remove these instances is through the Terminator tool, and these can also be detected by the MsgBoxViewer, a great tool that we’d recommend for all BizTalk administrators. Also, we can use the below query to clear these data:
BEGIN TRAN
USE [biztalkDTADb]
UPDATE [dbo].[dta_ServiceInstances] SET [dtEndTime] = GetUTCDate() WHERE dtEndTime is NULL
AND [uidServiceInstanceId] NOT IN
(SELECT [uidInstanceID] FROM BizTalkMsgBoxDb.[dbo].[Instances] WITH (NOLOCK)
UNION
SELECT [StreamID] FROM BizTalkMsgBoxDb.[dbo].[TrackingData] WITH (NOLOCK))
-- If count match with above result then uncomment below query and run
-- Commit tran
-- If it does NOT match then uncomment and run below query
-- Rollback tran
Error
The error itself says this is something like a bug “KB3062831 not installed on <BizTalkServer>: Master secret key cannot be restored and/or memory leaks can occur in SSO”. We noticed high memory usage by Enterprise Single Sign On service and after running BHM report found the cause of the problem,
Cause
After applying the following hotfix from KB3000847, we may find the memory usage of SSO service keeps increasing and will not be released before restarting it. The issue was already addressed on the MSDN blog.
Resolution
Install the following hotfixes on the BizTalk server and restart the server after installation.
Opinions expressed by DZone contributors are their own.
Comments