Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

BizTalk Server 2009 Operations : Maintaining the BizTalk Group (part 1) - SQL Agent Job Configuration

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
11/25/2012 11:13:08 AM
A BizTalk Group consists of the set of databases hosted in SQL Server. SQL Server provides high availability for BizTalk Server 2009 applications through Windows cluster installations hosting the BizTalk configuration and operation databases. For BizTalk applications, the servers with BizTalk installed provide the runtime environment with SQL Server as the persistent store. Therefore, BizTalk Server 2009 backup, restore, and disaster recovery procedures are heavily focused on procedures related to SQL Server.

A BizTalk Server solution's availability and performance are highly dependent on maintaining the SQL Server-based back end where the BizTalk Group is located. Every message that is received by a BizTalk host instance enters the SQL Server Messagebox database, tracked to some degree, retrieved from SQL Server by the host instance where the subscribing orchestration is executed with the results put back into SQL Server for transmission on a host instance send port. As you can see, overall BizTalk performance is highly dependent on the underlying SQL Server performance.

Changes to the BizTalk Group SQL Server schema are not supported. Do not modify the SQL Server schema for any tables or stored procedures that are part of the BizTalk Group.


Luckily, the BizTalk product team provides several SQL Agent jobs to assist with keeping the BizTalk Group running in top form. SQL Server Agent makes message bodies available to BizTalk and WMI and enables you to run jobs to clean up the Messagebox databases.

The SQL Agent jobs are created when the BizTalk Group is configured using Configuration.exe. The user who runs Configuration.exe is also designated as the SQL Agent job owner. The account designated as the job owner is the security context that the SQL Agent job executes under. If any SQL Agent jobs are failing, consider changing the job owner to a different account that has the required privileges to folders or UNC shares, and so on, so that the job can complete successfully.

Table 1 describes the SQL Agent jobs. Do not alter the schedule for any jobs except possibly for the Backup BizTalk Server SQL Agent job. Details configuring the Backup BizTalk Server SQL Agent job are covered in the subsection titled "Configuring the Backup BizTalk Server SQL Agent Job."

Table 1. BizTalk Group SQL Agent Jobs
SQL Agent JobRemarks
Backup BizTalk Server (BizTalkMgmtDb)This SQL Agent job performs backup operations on BizTalk databases that participate in Distributed Transaction Coordinator (DTC) transactions. Not all BizTalk databases are part of this job. Also, additional databases can be added to this job. This job is disabled by default and must be configured in order to run. Scheduled to perform full backup daily and log backup every 15 minutes by default.
CleanupBTFExpiredEntriesJob_<BizTalkMgmtDb>This SQL Agent job cleans up expired BizTalk Framework (BTF) entries in the BizTalk Management Database. Scheduled to run every 12 hours and enabled by default.
DTA Purge and Archive (BizTalkMsgBoxDb)This SQL Agent job automatically archives data in the BizTalk Tracking (BizTalkDTADb) database and purges obsolete data. This job is disabled by default and must be configured in order to run. Scheduled to run every minute by default.
MessageBox_DeadProcesses_Cleanup_<BizTalkMsgBoxDb>This SQL Agent job detects when a running BizTalk host instance has stopped. This SQL Agent job releases all in-progress work for that host instance so that it can be picked up by another host instance. Scheduled to run every minute and enabled by default.
MessageBox_Message_ Cleanup_<BizTalkMsgBoxDb>This SQL Agent job removes messages that are no longer referenced by any subscribers in the Messagebox database tables. This job is disabled by default, but it should not be manually run. This job is started when needed by the MessageBox_Message_ManageRefCountLog_<BiztalkMsgBoxDb> job.
MessageBox_Message_ManageRefCountLog_<BizTalkMsgBoxDb>This SQL Agent job manages the reference count logs for messages. It determines when a message is no longer referenced by a subscriber. Scheduled to run every minute and enabled by default.
MessageBox_Parts_Cleanup_<BizTalkMsgBoxDb>This SQL Agent job removes message parts that are no longerreferenced by any messages in the Messagebox database tables. Scheduled to run every minute and enabled by default.
MessageBox_UpdateStats_<BizTalkMsgBoxDb>This SQL Agent job manually updates database statistics forthe BizTalk Messagebox database. Scheduled to run every 5 minutes and enabled by default.
Operations_OperateOnInstances_OnMaster_<BizTalkMsgBoxDb>This SQL Agent job is required for multiple Messagebox database deployments. It performs operational tasks on Messagebox databases. Scheduled to run every minute and enabled by default.
PurgeSubscriptionsJob_<BizTalkMsgBoxDb>This SQL Agent job purges unused subscription predicates from the Messagebox database. Scheduled to run every minute and enabled by default.
Rules_Database_Cleanup <BizTalkRuleEngineDb>New in BizTalk Server 2006, this SQL Agent job purges old audit data from the Rule Engine database every 90 days. It also purges old history data from the Rule Engine database every 3 days. Scheduled to run every hour and enabled by default.
TrackedMessages_Copy_<BizTalkMsgBoxDb>This SQL Agent job copies message bodies of tracked messages from the Messagebox database to the Tracking database. Scheduled to run every minute and enabled by default.

Microsoft strongly recommends monitoring the SQL Agent service and the individual jobs using a monitoring tool such as MOM or similar enterprise monitoring product. If any of these jobs starts failing, it is a strong indication that there are performance issues with the application. It is also a good idea to monitor how long it takes for the SQL Agent jobs to run, perhaps on a weekly basis. If the jobs are taking longer and longer to run over time, it is another indication that there may be performance issues.

For completeness, the BizTalk Server 2004 SQL Agent job TrackingSpool_Cleanup_<BizTalkMsgBox was removed in BizTalk Server 2006.

The next step is to configure the necessary SQL Agent jobs to perform backups and to maintain the BizTalk Group.

1. SQL Agent Job Configuration

The following jobs from Table 9-1 require configuration before they can be enabled and can run successfully:

  • Backup BizTalk Server (BizTalkMgmtDb)

  • DTA Purge and Archive (BizTalkMsgBoxDb)

NOTE

The SQL Agent job MessageBox_Message_Cleanup_<BiztalkMsgBoxDb> is disabled by default. It is not supported to enable this job or run it manually. This SQL Agent job is managed by the MessageBox_Message_ManageRefCountLog_<BiztalkMsgBoxDb> SQL Agent job.

The following two subsections cover how to configure these SQL Agent jobs.

1.1. Configuring the Backup BizTalk Server SQL Agent Job

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 2009 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.

NOTE

Not all BizTalk databases are backed up as part of the Backup BizTalk Server job. Backing up these databases is covered in the next subsection.

The following databases are backed up as part of the Backup BizTalk Server SQL Agent job:

  • BizTalk Configuration (BizTalkMgmtDb)

  • BizTalk Messagebox (BizTalkMsgBoxDb)

  • BizTalk Tracking (BizTalkDTADb)

  • Rule Engine (BizTalkRuleEngineDb)

  • BAM Primary Import (BAMPrimaryImport)

  • Trading Partner Management (TPM)

These databases must be backed up by the Backup BizTalk Server SQL Agent job and cannot be backed up using the normal SQL Server backup procedures. The reason is because BizTalk uses SQL Server log marks to keep the set of databases consistent as part of DTC transactions. The Backup BizTalk Server job creates a log mark and then backs up the database log for each database that is part of the Backup BizTalk Server SQL Agent job. This log mark is used when restoring the last log file for each database so that transactional consistency is maintained. Here are the steps to configure the Backup BizTalk Server SQL Agent job:

  1. In SQL Server 2005/2008 Management Studio, navigate to the SQL Agent jobs list.

  2. Right-click Backup BizTalk Server (BizTalkMqmtDb), and select Properties.

  3. In the Job Properties dialog box under "Select a page," click Steps to view the job steps.

  4. In the "Job step" list, click BackupFull, and then click Edit.

  5. On the General page, in the Command box, replace '<destination path>' with the full path (the path must include the single quotes) to the computer and folder where you want to back up the BizTalk Server databases. Also add a new parameter by typing a comma and then a number one (,1) at the end of the parameter list for the stored procedure sp_BackupAllFull. Adding this parameter enables an automatic full backup after a backup failure. Click OK when finished.

    NOTE

    The default frequency for the BackupFull job is d for daily. Other values are hourly (h/H), weekly (w/W), monthly (m/M), and yearly (y/Y). The first time the job is run during a new period, a full backup is performed. Also, the default name is BTS, which will be part of the backup file name. Change this to reflect a better name for the application such as OrdSys for an application named Order System.

  6. In the Job step list, click MarkAndBackupLog, and then click Edit.

  7. On the General page, in the Command box, replace '<destination path>' with the full path (including single quotes) to the computer and folder where you want to store the BizTalk Server database logs, and then click OK. The <destination path> may be local or a UNC path to another server.

    NOTE

    We recommend a UNC share to store the backup files on a different file system than where the databases reside for production environments. For a dev or test environment, if you are not concerned with maintaining offsite backup sets or multiple backup sets, you can consider using a local path instead of a UNC path.

    Also, for the job step MarkAndBackupLog, Log Mark Name is part of the naming convention for backup files:

    <Server Name>_<Database Name>_Log_< Log Mark Name >_<Timestamp>
    Replace "BTS' with a more appropriate name for the solution.

  8. In the Job step list, click Clear Backup History, and then click Edit.

  9. On the General page, in the Command box, change DaysToKeep=<number> to the number of days (default is 14) you want to keep the backup history, and then click OK twice to close the Job Properties dialog box.

NOTE

The DaysToKeep setting is not related to how many sets of backup files are maintained. Backup file sets must be handled manually by copying to another system for long-term archival.

Change the backup schedule for MarkAndBackupLogSched if desired, and then right-click the Backup BizTalk Server SQL Agent job and select Enable. The default schedule is to perform a log backup every 15 minutes.

Once the Backup BizTalk Server job is configured and enabled, right-click, and select Start Job to test. Click F5 to refresh the status on the Jobs node. If the result is not successful, check the following:

  • Verify that the destination folder exists and is reachable if a UNC share.

  • Check that the job owner has permissions on the destination folder.

  • Ensure that linked servers are configured properly if BizTalk databases are present in multiple SQL Server database instances.

For SQL Server 2005 and 2008, there are additional security settings for linked servers. When configuring linked servers in SQL Server 2005 or 2008 as part of the Backup BizTalk Server SQL Agent job, click the Security tab, and select the "Be made using the login's current security context" option. Next click Server Options, set RPC Out to True, and then click OK.


Be aware that the file name includes the date/time from when the backup file was created. This date/time is GMT time, not local time. If you look at the Date Modified field in Windows Explorer, you will see the local time.

Also, the Backup BizTalk Server SQL Agent job does not manage disk space, meaning it will continue to copy files into the same directory until the drive runs out of space. This allows the administrator to decide how many backup sets to keep on disk as well as how many to archive to an offsite location, deleting the files from disk after archival.

The next subsection covers how to configure the DTA Purge and Archive SQL Agent job.

1.2. Configuring the DTA Purge and Archive SQL Agent Job

With companies having to comply with IRS, general accounting, and legislative requirements for business reporting, BizTalk Server provides extensive tracking capabilities to help with complying with these mandates. This data must be kept for various periods of time to meet reporting requirements. BizTalk Server 2009 has the DTA Purge and Archive job to help automate the backup of tracking data including the ability to perform on-the-fly validation of tracking data backups using another instance of SQL Server to ensure that a complete record of activity is maintained and available.

In addition to providing data archival, the DTA Purge and Archive SQL Agent job performs data pruning to help keep the system running smoothly. As with any database system, unchecked growth in table size will eventually push the limits of the hardware. In general, there are two solutions to this problem: buy more disks or a faster disk or have a purge and archival policy to "prune" the databases where it makes sense. While all database-based systems benefit from more and faster disks, BizTalk has a process to keep the BizTalk Tracking and Messagebox databases performing optimally by automating purging and archival tasks through the DTA Purge and Archive SQL Agent job.

The DTA Purge and Archive job purges various tracking information such as service instance and message information, orchestration event information, and rule engine tracking data. The purge process is based on the age of the tracking data, which is maintained by having a time stamp added when tracking information is inserted into the database. The DTA Purge and Archive job has a soft purge and hard purge process. The soft purge processes completed instances, while the hard purge processes incomplete instances. Note that both soft purge and hard purge process just the tracking data, not the actual running instances, so they have no effect on actual data processing data. The purge process helps to optimize tracking processes and HAT operations when looking at historical data. Here are the steps to configure the DTA Purge and Archive SQL Agent job:

  1. Depending on where you are, navigate to the Management node and view the SQL Agent jobs.

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

  3. In the Job Properties dialog box, click the Steps tab, click Archive and Purge, and then click Edit.

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

NOTE

For the soft purge, the sum of LiveHours and LiveDays is the live window of data that will be maintained for the BizTalk Tracking database. All tracking data associated with completed instances older than the live window will be deleted and archived.

  • @nLiveHours tinyint: Default is 0 hours.

  • @nLiveDays tinyint: Default is 1 day.

  • @nHardDeleteDays tinyint: Default is 30 days.

  • @nvcFolder nvarchar(1024): Specify the folder or UNC share to put the tracking data backup files.

  • @nvcValidatingServer: SQL Server instance where validation is performed. Default is null.

  • @fForceBackup int: Default is 0. This is not currently implemented.

Here is an example command that specifies that soft purge occurs every 12 hours and hard purge occurs every 7 days:

exec dtasp_BackupAndPurgeTrackingDatabase 12, 0, 7, '\\BizTalkBackupServer\data', null, 0

					  

In the preceding example, we left the validation server value as null; however, we recommend that you set up a validation server for the tracking data to ensure that the backup files of the tracking data for reporting and compliance purposes are valid. Also, the data can be queried on the validation server, offloading potentially long-running queries from the production BizTalk databases. To configure a validation server for the DTA Purge and Archive SQL Agent job, you must have a separate instance of SQL Server available. Having a validation server requires that the @nvcFolder variable in the DTA Purge and Archive job points to a UNC share reachable by the validation server. The SQL Server instance where the BizTalk databases are configured cannot also act as the validating server. On the server designated as the validation server, perform these steps:

  1. In the SQL Management Studio in SQL Server 2005/2008, open a file to execute a SQL file. Connect to the SQL instance that is the validation server.

  2. Select File => Open, and then browse to this SQL script on the server/drive where BizTalk Server 2009 is installed: \Program Files\Microsoft BizTalk Server 2009\Schema\BTS_Tracking_ValidateArchive.sql.

  3. Execute the query to create a SQL Agent job called ValidateArchive on the validating server.

  4. Open the SQL Management Studio for SQL Server 2005/2008 to set up the required linked servers. Linked servers must be created between the following:

    • Each of the BizTalk Messagebox (BizTalkMsgBoxDB) SQL Server instances and the BizTalk Tracking (BizTalkDTADb) SQL Server instances. The SQL instance hosting the DTA database requires a linked server to each SQL instance hosting a BizTalk Messagebox, and vice versa.

    • The BizTalk Tracking (BizTalkDTADb) SQL Server instance and the validating server SQL Server instance. Create a linked server on each SQL instance to the other SQL instance so that the SQL instance hosting the DTA database has a linked server to the validating server, and vice versa.

Next, we turn our attention to monitoring best practices for the SQL Agent jobs.

1.3. Monitoring the BizTalk Group SQL Agent Jobs

Because the SQL Agent jobs are critical to maintaining BizTalk performance, the jobs must be monitored so that operations personnel can be alerted if a job fails. The Microsoft SQL Server Management Pack contains MOM rules for monitoring SQL databases, SQL Server Agent jobs, and so on, for comprehensive monitoring of SQL Server items. The BizTalk Server 2009 Management Pack for Microsoft Operations Manager 2005 includes two rules, disabled by default, for monitoring the health of two of the most important BizTalk SQL Server Agent jobs. The rule names as defined in the Management Pack are as follows:

  • Critical Error: A BizTalk SQL Server Agent job failed—Backup BizTalk Server

  • Critical Error: A BizTalk SQL Server Agent job failed—Tracked Message Copy

To monitor all BizTalk Server SQL Server Agent jobs from within the BizTalk Server 2009 Management Pack, enable these rules and create additional rules for other jobs that you want to monitor. To enable these rules, perform the following steps in the MOM Administrator Console:

  1. Create a copy of the two rules just listed in the BizTalk Server Core Rule group, and rename each rule appropriately.

  2. In the criteria section for the MOM rule, change the wildcard comparison for Parameter 1 because the job names are specific to the BizTalk Group configuration.

You need to add the SQL Server computers into the BizTalk Server 2009 Computer Group in MOM. This is because the MOM rule needs to be evaluated on the SQL Server computer and the SQL Server computer will not be recognized as a BizTalk Server computer unless BizTalk and SQL Server happen to be installed on the same machine.

Other -----------------
- SQL Server 2008 R2 : Managing Workloads with the Resource Governor - Modifying Your Resource Governor Configuration
- SQL Server 2008 R2 : Managing Workloads with the Resource Governor - Monitoring Resource Usage
- Windows Server 2008 Server Core : Decompressing Files with the Expand Utility, Performing Advanced File Comparison with the FC Utility
- Windows Server 2008 Server Core : Modifying Files with the Edlin Utility, Repairing System Databases with the ESEnTUtl Utility
- Microsoft Dynamics CRM 4.0 : Silverlight - Tools and Resources
- Microsoft Dynamics CRM 4.0 : Infrastructure Design Considerations - Windows SharePoint Integration
- Connecting Dynamics GP to Microsoft Office 2010 : Improving performance by globally turning off Outlook integration
- Connecting Dynamics GP to Microsoft Office 2010 : Skipping the exports by using Prebuilt Excel Reports
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Reading Excel files
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Creating Excel files
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer