Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

7. Monitoring and Maintenance > Configuring the BizTalk database jobs

Configuring the BizTalk database jobs

Configuring the BizTalk database jobs BizTalk Server database jobs are required to keep BizTalk healthy. The jobs are important for preventing the database from becoming too large and cripple BizTalk's performance. The following table lists the BizTalk Server and SQL Server Agent jobs with a description of their responsibilities:


By default, some of the jobs are not configured and are scheduled after installation and configuration of the BizTalk environment. These jobs are: Backup BizTalk Server (BizTalkMgmtDb)DTA Purge and Archive (BizTalkDTADb)MessageBox_Message_Cleanup_BizTalkMsgBoxDb Note There are other means of backing up the BizTalk Server databases, yet the Backup BizTalk Server job is the only one supported by Microsoft. In this recipe, the first two jobs will be configured and the last one can be left disabled, as this job is also started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. It is recommended that you disable the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job. If you run the BizTalk Server Best Practices Analyzer, you may see that two of the SQL Server Agent jobs that BizTalk relies on are not running successfully (as those are not configured). Getting ready For this recipe, you will need access to the SQL Server database through the SQL Management Studio and the appropriate rights to be able to operate it. How to do it... You need to perform the following steps to configure the Backup BizTalk Server (BizTalkMgmtDb) job: Open the SQL Management Studio.Connect to the SQL Server instance which contains the BizTalk Server databases.Navigate to SQL Server Agent.Expand SQL Server Agent and navigate to Jobs.Expand Jobs.Select the Backup BizTalk Server (BizTalkMgmtDb) job and right-click. Select Properties.In the Select a page pane on the left, choose Steps. You will see four steps, as shown in the following screenshot: Double-click on the first step named Set Compression Option.You will see the following procedure in the Command box: exec [dbo].[sp_SetBackupCompression] @bCompression = 0 /*0 - Do not use Compression, 1 - Use Compression */ Change @bCompression to 1 if you need compression. Click on either OK or Cancel.Double-click on the step named BackupFull.In the command box, you will find the following procedure: exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '<destination path>' /* location of backup files */ Change Frequency to the desired value, where d (daily) is the default name of your backup, and the location of the backup path (destination path). You can have something similar to the following: exec [dbo].[sp_BackupAllFull_Schedule] 'd', 'BTS_Test_Machine_X012', 'D:\MyBizTalkServer' Note Backups must never be on the same disks! After changing the procedure, click on OK.Double-click on the step named MarkandBackupLog.Here you will find the following procedure in the command box: exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '<destination path>' /* location of backup files */ Change the log mark name and destination path to your requirements. You could have something as follows: exec [dbo].[sp_MarkAll] 'BTS_Test_Machine_X012_Logs', 'C:\MyBizTalkServerDbLogs' After changing the procedure, click on OK.Finally you can double-click on the last step named as Clear Back Up History.Here you will find the following procedure in the command box: exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14 Here you can change the @DaysToKeep value to the desired value. This value represents the number of days.After changing the value, click on OK or Cancel, in case you will want to keep the default.Click on OK again to save job.Right-click on the job and choose Enable. You need to perform the following steps to configure the DTA Purge and Archive (BizTalkDTADb): Open the SQL Management Studio.Connect to the SQL Server instance that contains the BizTalk Server databases.Navigate to SQL Server Agent.Expand SQL Server Agent and navigate to Jobs.Expand Jobs.Select the DTA Purge and Archive (BizTalkDTADb) job and right-click on it. Select Properties.In the Select a page pane on the left, choose Steps. You will see one step in Job step list: Double-click on the step named Archive and Purge.You will see the following procedure in the command box: exec dtasp_BackupAndPurgeTrackingDatabase 0, --@nLiveHours tinyint, 1, --@nLiveDays tinyint = 0, 30, --@nHardDeleteDays tinyint = 0, null, --@nvcFolder nvarchar(1024) = null, null, --@nvcValidatingServer sysname = null, 0 --@fForceBackup int = 0 Change @nLiveHours.Eventually you have a procedure as follows: exec dtasp_BackupAndPurgeTrackingDatabase 1, 0, 1, '\\WIN-8BPNTQKTJ5M\MyBizTalkServer', null, 0 Click on OK after changing the procedure.Click on OK again to save the job.Right-click on the job and select Enable. How it works... Both jobs are essential for running BizTalk successfully. The backup job runs as scheduled by the SQL Server Agent service and create synchronized backups of all BizTalk Server databases by using full database backups and transaction log backups, in conjunction with a type of transaction known as a marked transaction. The latter are transactions that place a mark into the transaction log of all databases participating in the transaction. This transaction blocks new distributed transactions from starting, waits for the distributed transactions which are currently running to complete, and then executes to place the mark. The mark represents a transaction point which is consistent across all databases, you can use the mark with subsequent log backups to restore your databases to that point. The job consists of four steps, each responsible for completion of the job. The first step is Set Compression Option which is an option not present in previous BizTalk versions. This step enables you to set compression to on or off. Enabling compression will give you benefits, such as less space needed for the backup files, fewer I/O operations for the backup and restore, faster backup and restore, and less network usage to transfer the files to your disaster recovery site. The drawback is that it requires more CPU utilization and with today's hardware that can be a real problem. The subsequent step is the BackupFull that requires you to set a few parameters, such as frequency, name, and location of the backup. You can also provide values for three optional parameters to force a full backup after partial backups (ForceFullBackupAfterPartialSetFailure), time for backup to run (BackUpHour), and to use local time (UseLocalTime). The subsequent step is MarkAndBackUpLog. Here, you again have to provide values for two parameters. You should provide a name and backup path (location). You can also provide a value for timestamp (that is, local time) which is optional. The final step is Clear Backup History. Here, you provide value for the DaysToKeep parameter to determine after how many days the backup log has to be removed. Again, there is an optional parameter for using local time (UseLocalTime). This job step does not provide functionality for deleting backup files which have been accumulated over time (see the blog post at http://sandroaspbiztalkblog.wordpress.com/2011/01/27/biztalk-2010-installation-and-configuration-configure-biztalk-server-sql-jobs-part-11/ by Sandro Pereira to circumvent this issue). DTA Purge and Archive is a job you configure to keep BizTalk healthy and to bloat the archive database until a point when there is no disk space left, or overall performance can be crippled. It also enables archiving of tracked data. The job consists of one step, where six parameters have to be configured. You configure the parameters such as nLiveHours, nLiveDay, and nHardDeleteDays for setting the time-frame for deletion of the tracked data. Configure the nvcFolder parameter for determining the location of the backup file. With the nvcValidatingServer parameter, you can provide a value to optionally set up a secondary database server to validate the archives as they are created. The last parameter, fForceBackup, is used to determine whether or not to force a backup. There's more... For a complete overview of backing up and restoring BizTalk databases in context of the BizTalk Backup job see MSDN: Backing Up and Restoring the BizTalk Server Databases:http://msdn.microsoft.com/en-us/library/aa560972%28v=BTS.70%29.aspxBacking Up and Restoring BizTalk Server:http://msdn.microsoft.com/en-us/library/aa562140%28v=BTS.70%29.aspx The backup of BizTalk databases is one part of backup and recovery in case of disaster recovery. To have a full disaster recovery in place, the following resources can be of use: PowerShell cmdlet for BizTalk db restore:http://blogical.se/blogs/mikael/archive/2011/02/22/powershell-cmdlet-for-biztalk-db-restore.aspxBacking Up and Restoring BizTalk Server:http://msdn.microsoft.com/en-us/library/aa562140.aspx With regards to the DTA Purge and Archive job, refer to the document called Archiving and Purging the BizTalk Tracking Database ( http://msdn.microsoft.com/en-us/library/aa560754%28v=BTS.70%29.aspx) on MSDN. The BizTalk Database Server job is part of the overall strategy to maintain and troubleshoot the BizTalk Server databases. You can read more on it in the document called How to maintain and troubleshoot BizTalk Server databases at http://support.microsoft.com/kb/952555. See also Refer to the Leveraging the BizTalk monitoring job recipe discussed later in this chapter

  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint