Published By: Bill Chesnut
Over the years working with BizTalk Server I have discovered a couple of things that have been left out of the base BizTalk Server Product, the 1st and foremost is that the Backup BizTalk Server SQL Agent job does not delete the backup files, it has a step to delete the backup history from the database, but not the files from the file system. The other 2 things that can cause issues are related to BAM, when a BAM activity is deployed into BizTalk, it creates a SSIS package that does the Archiving and Deleting of the BAM data, but if you don’t remember to schedule this SSIS package it never happens, the last BAM related one is Scheduled Aggregations. If you are using SQL Server Standard Edition, you cannot enable Real Time Aggregation in your BAM Activity and you must schedule the created Scheduled Aggregation SSIS package to run.
Now that we have discussed these 3 missing components to the base BizTalk Server product, lets look at how to solve these issues.
SSIS Package to Delete BizTalk Server Database Backups
In the past I have created a replace stored procedure that will delete the history and files created by the BizTalk Server Backup SQL agent job, the issue with this SQL Stored Procedure was that it required SQL Command Exec to be enabled on the SQL Server and this is considered a security risk by many SQL DBAs.
So to please the SQL DBAs I decided that an SSIS package could do the same thing, without needing SQL Command Exec to be enabled, this SSIS package uses that same SQL query as the Clear Backup History step of the default Backup BizTalk Server SQL Agent Job. Below are the instructions to install the SSIS package:
- Download the DeleteBackupHistoryAndFiles SSIS package for your version of BizTalk and SQL from here
- Import SSIS Package into SQL Integration Services
- Select the SSIS Package from your download location
- Change the Protection Level to “Rely on server storage and roles for access control”, Click OK
- Open the Backup BizTalk Server SQL Agent Job, Click Steps, Click Clear Backup History, Configure as Shown (replace Server name with your SQL Server Name), Click Data Sources
- In the Data Sources update the name of the SQL Server, Click Set values
- In the Set values, enter the following 2 values (UseLocalTime should set to the value in Backup Full, DaysToKeep is up to you), Click OK
- \Package.Variables[User::UseLocalTime].Properties[Value]
- \Package.Variables[User::DaysToKeep].Properties[Value]
- The Backup BizTalk Server is now updated, test to make sure it is working
- Trouble Shooting
- make sure the account the SQL Agent is running under has permissions to delete the backup files
- Make sure the Backup BizTalk Server is creating full backups, the job will not delete any files newer than the last full backup
SSIS Package to run All BAM Archive Packages
This package was created to ease the efforts on the BizTalk Administrators, the recommend procedure in BizTalk Server is that every time a BAM activity is deployed the BizTalk Administrators need to create a SQL Agent job to run the BAM_DM_ SSIS Package, this can easily be forgotten and can result in a large performance impact on BizTalk Server, the worse case and the reason I wrote this SSIS package was 20,000,000 rows for a Single BAM Activity.
This package once install will run the BAM_DM_ SSIS Package for each BAM Activity that is currently deployed in the BizTalk Server, by reading the bam_Metadata_Activities table in the BAMPrimaryImport database.
- Download the Run All BAM SSIS Packages from here
- Import SSIS Package into SQL Integration Services
- Select the SSIS Package from your download location
- Change the Protection Level to “Rely on server storage and roles for access control”, Click OK
- Create a new job in the SQL Agent Jobs, Set the Job Name and Click Steps
- In Steps, Click New, configure as Shown (replace Server name with your SQL Server Name), Click Data Sources
- Update Data Sources with your SQL Server Name, Click OK, Click Schedules, Click New
- Enter Your Schedule Details (My recommendation is once a week on Sunday Morning), Click OK, Click OK
- The Run All BAM Archive Packages Job is now created, test to make sure it is working
- Trouble Shooting
- Make sure the account the SQL Agent is running under has permissions to read the bam_Metadata_Activities table.
- Make sure the account the SQL Agent is running under has permissions to run SSIS Packages
SSIS Package to run All Schedule Aggregation Packages
This package was again create to ease the workload on BizTalk Administrators, very similar to the package above, if you have SQL Standard Edition in your BizTalk Server Environment you cannot use Real Time Aggregation, you must use Scheduled Aggregations to update SQL Analysis Services for your BAM Views. This again requires your BizTalk Administrator to create a SQL Agent Job to run the BAM_AN_ SSIS Package, this can be easily forgotten and your views will not have the most current data.
This package once install will run the BAM_AN_ SSIS Package for each BAM View that is currently deployed in the BizTalk Server, by reading the bam_Metadata_AnalysisTasks table in the BAMPrimaryImport database.
- Download the RunAllScheduledAggregations SSIS package for your version of BizTalk from here
- Import SSIS Package into SQL Integration Services
- Select the SSIS Package from your download location
- Change the Protection Level to “Rely on server storage and roles for access control”, Click OK
- Create a new job in the SQL Agent Jobs, Set the Job Name and Click Steps, Click New
- On Steps, configure as Shown (replacing you SQL Server Name), Click Data Sources
- Update Data Sources with your SQL Server Name, Click OK, Click Schedules, Click New
- Enter Your Schedule Details (This depends on your data, I will show every 5 minutes during business hours), Click OK, Click OK
- The Run All BAM Scheduled Aggregations Job is now created, test to make sure it is working
- Trouble Shooting
- Make sure the account the SQL Agent is running under has permissions to read the bam_Metadata_AnalysisTasks table.
- Make sure the account the SQL Agent is running under has permissions to run SSIS Packages
I hope this post helps reduce the load on your BizTalk Administrators and keeps your BizTalk Server running efficiently.