Most of users prefer to install BizTalk360 in a standalone sever. Importantly, this approach does not overload the BizTalk environment or the BizTalk360 instance. Quite often, BizTalk360 database is configured in the same SQL Server instance as where BizTalk databases resides. In this case, configuring database in a separate SQL Server Instance for better performance and disk usage. Considering this fact, customers are acquiring a dedicated SQL Server for the BizTalk360 database. Meanwhile, few customers are using SQL Server Express edition, when they don’t prefer to buy a separate license for SQL Server. This article covers things that need to be considered when BizTalk360 with SQL Server Express Edition.
BizTalk360 is installed on a standalone server with all components, which are:
- The database
- The web portal
- The monitoring
- The analytics services
The customer has installed SQL Server 2017 Express edition
SQL Server Express Edition
Let us see features and limitations of SQL Server Express edition.
- There is no requirement of license for using it, as it is free for distribution
- It is an easy to use version, designed for building simple data driven applications
- Applications develop faster through the deep integration with Visual Web Developer, Visual Studio, and so on
- Easy to create and share reports that answer complex questions through basic reporting services
- Provides backup and restore functionality and compatibility with all its versions
- SQL Server Express can be installed on a server with many CPUs. But, it can use only one CPU at a time and 4GB database size limit was there for SQL2005/2008 but after 2008R2 it has been increased to up to 10GB for each database. For this limit, only the size of the Data file is considered. The size of the Log file is not relevant
- Can use a maximum of 1GB memory for temporarily storing the data while it is being transferred from one place to the other
- The performance analysis tool, Profiles, is not included with the SQL Server Express edition
- The functionality to create and attach a schedule to a job (Job Scheduler) is not available with the express edition
- You cannot import or export the data with the SQL server express as this feature is also not available
BizTalk360 as Lightweight
Under this circumstance, user must take care few considerations in BizTalk360 to keep the size of database within the value supported by SQL Express Edition. For better performance of data processing, make sure you keep the database as light as possible.
The following configuration will help to maintain BizTalk360 database as lightweight:
- Event Log
- Data Purging
In a BizTalk environment it’s important to monitor the Event Log to avoid any activities blocked at the back-end. A BizTalk Administrator’s main job is to keep BizTalk System Resources, Disk Usage and Database processes 100% healthy!
BizTalk360 collects Event Log data based on configured Event Logs and Event Log Sources. With this collected data, the BizTalk360 Monitoring service will send notifications based on configuration (Threshold and Data Monitoring). From the Operations section, Event Log data access functionality is also available with all kind of filtering capabilities. In BizTalk Reporting Analytics, the Event Log widget is populating the report with “Top 10” Event Log sources.
Event Log data collection will need more space based on the configured number of sources and the number of physical BizTalk Servers and SQL Servers in an environment. We can see the possibility to reduce the amount of event log data collection by:
- Configure just the important Event Log type and sources you want to monitor
- Enable Event Log data collection only at the physical server level (BizTalk/SQL) for which Event Log monitoring is important
In a BizTalk production environment most of the users are using the BizTalk360 Analytics capabilities. The ‘Analytics’ section has the Performance Analyser functionality, the Messaging Patterns and the Throttling Analyser. The BizTalk360 services are also collecting data to generate the graphical widgets which are shown in multiple dashboards. Both Throttling and Performance data are collected every 15 secs. In a complex BizTalk environment data collection is at considerable size.
BizTalk Artifacts tracking is enabled at port level and at pipeline level. BizTalk360 uses its Analytics service to determine Message Patterns are by querying tracking data which is collected from the BizTalkDTADb. Similarly, tracking data is used to visualize messaging performance by port and by pipeline.
The Analytics service is responsible for collecting the Throttling Performance Counter data from all the BizTalk servers in the environment. The BizTalk360 Throttling Analyser helps to simplify the complexity in understanding BizTalk throttling mechanism and provide a simple dashboard view.
Users can observe the ways to optimize the data collection;
- Disable unnecessary tracking (Port and Pipeline). When possible, try to limit to Event tracking and prevent using Message body and context tracking, it will also boost performance of the BizTalk Tracking database
- Configure requirement performance counters in an environment to boost the data collection
To be able to get a good overview of all the Tracking settings within your BizTalk environment and to configure these settings in one consolidated screen, you can use the Advanced Tracking Manager.
BizTalk360 comes out of the box with the ability to set purging duration. The background Monitoring service has the capability to purge older data automatically after a specified period. Data purging is configurable for different activities in which can be found under BizTalk360 Settings > Data Purging. Through this feature, you can control the size of data of the BizTalk360 database.
Compromising Performance and Storage?
We have seen about the performance and the size of data with several features in BizTalk360. Using SQL Express edition with 4 CPU Cores, 1410 MB of buffer pool size and storage of 10 GB in a BizTalk Production environment is critical. A BizTalk/Database administrator must monitor the size of the database by setting purging policies to a minimum period for data persistence.
Consider using SQL Express edition with BizTalk360 based on case by case as we discussed.
- Using SQL Express Edition in a critical production environment is not suggested as it compromises performance and data storage. For those BizTalk environments with more than one BizTalk servers, we advise to use a higher edition of SQL Server
- In Non-Production environments install different BizTalk360 instances by considering
- Enable important Event Log source to data collection.
- Another key point is configure single BizTalk environment in a BizTalk360 Instance.
- Periodically check the size of database and optimize the collected data.
Import/Export is an important capability to manage configurations in complex environments. It facilitates that configuration can be exported and imported across multiple environments like Production, Staging, QA. BizTalk360 helped its customers by providing alarm import/export confirmation for many releases now. This feature can also be used as a backup mechanism of BizTalk360 configurations, in case you want to setup a new instance of BizTalk360.
In feedback portal, many customers requested us to extend the existing functionality to other sections of the product, like User Access Policies, Secure SQL Queries, Saved queries configuration, Knowledge Base, Dashboards & Widgets and BizTalk Reporting configurations.
Real Time Scenarios
Let us see scenarios where import/export configurations can be used quite frequently.
When your organization has deployed BizTalk360 on one of the Development/QA/UAT environment(s) and made lots of monitoring, user, SQL queries configurations, at some point in time, you would want to move all these configurations to the Production environment. The BizTalk360 administrator must do the usual practice to set up the configurations manually in production environment. If there are just a handful of alarm configurations/user configuration, this is a feasible option for the BizTalk360 administrator. However, if there are many configurations (100, 200, or even 500), it becomes a painstaking process for BizTalk360 administrator to manually create each alarm and its associated monitoring configurations in the production environment. Besides that, it’s just an awful amount of work, creating the configuration is also error prone.
In downward scenario, when an issue happened in Production, for example in the Data Monitoring capability, when you try to action on Suspended Service Instances. By simply exporting the configuration(s) from Production and import the configuration into the Staging environment, you might just be able to replicate the issue.
Prior to 8.8, the Import/Export feature is available for Alarm configuration. From version 8.8 onward it is extended to other sections in BizTalk360
- User Access Policies
- Saved Queries
- Message Box and Tracking queries
- BAM, ESB and EDI queries
- Advanced Event Viewer queries
- Secure SQL Queries
The Import/Export configuration feature is available under the Settings section of BizTalk360 web portal. Alarm configuration Export/Import is removed from the Manage Alarms screen in the Monitoring section, so all the full Export/Import configurations feature is incorporated in a single place in the Settings section (Settings -> Import and Export).
From this version onward, user can pick the configuration they want to export from an environment. For instance, a user can choose desired Alarms and User Access Policy configurations for export. Exporting configurations is a straight forward process; selected configurations are exported in single ZIP File.
Exporting alarm configuration has the same functionality as like previous versions, with additional improvement, to select the desired alarms. To know more detail about alarm configuration, check the Blog written by our founder Saravana Kumar.
User Access Policies
Both Super and Non-Super User Access Policies configuration are available to export . The user can select user access policy configurations they want to export to the other environment.
The user can select the Saved queries configuration to export the data in following sections:
- Message Box Queries
- Advanced Event Viewer
- Graphical Flow (Tracking)
- ESB Exception Data
- Business Activity Monitoring
- Electronic Data Interchange
While selecting the export configuration, the user can choose saved queries sections will be exported. During export, all the queries under the that section will be available in JSON.
Secure SQL Queries
In Secure SQL query configuration, the user can select the queries they want to export.
A user can choose the environment in which they want to import the configurations. First step to import configurations, is to select the exported ZIP File. This will list the configurations which are exported, after which the user can select the configurations which need to be imported.
User Access Policies
While importing the User Access Policy configuration, there will be three options available. For Super user’s configurations, only the Create option is available, since Super user access all the environments and features based on license policies.
All the three options are available for Non-Super users:
- Create – the create option will be available for both Super and Non-Super user policy configurations.
- Overwrite – this option will replace the existing User Access Policy configuration of the non-super user
- Merge – this option will merge already existing User Access Policies in the destination environment with the policies, which are exported. If, according to the Exported policies, a Non-Super user has permission to edit queries in the Secure SQL Query feature, that means that the same User Access Policy in the destination environment, will permit this feature.
Similar to exporting configuration, a user selects the sections that need to be imported. This will import all the queries for the selected sections.
Secure SQL Queries
While importing Secure SQL Query configuration, the user can edit the query name, database and SQL Instance, according to the destination environment. If the Secure SQL Query configuration is already available, then the Override option is applied, otherwise it will create a new query.
The user confirms the import operation by clicking the Import button. Once the import process is completed it will produce the Imported Result Summary. This Summary might look like shown below.
Next set of Configuration
For the upcoming release, version 8.9, the following Import/Export configurations sections are under development pipeline:
- Knowledge Base
- BizTalk Reporting
- Custom Widgets
The Import/Export configuration feature is useful with respect to back-up of, amongst others, user and alarm configurations and increase productivity by sharing the configuration between multiple environments. In future releases of BizTalk360, we will bring more export/import features, to make the experience as rich as possible.
If you have any suggestions what kind of date you would like to transfer between BizTalk environments, feel free to let us know by entering a request on our Feedback portal.
BizTalk server state based artifacts such as receive location, send port, orchestration may go down due to various reasons. This could stop a business critical interfaces from processing messages. Monitoring the artifact states is a tedious task for both operational and administrative users. The operational users must monitor the state of artifacts constantly if any violation happens to the artifact state the operational user will intimate the administrative users immediately. In turn, administrative users should take corrective actions to bring the artifact to the expected state to avoid/minimize the business impact. This process involves the operational and administrative users be available throughout the clock to enable the interfaces again. BizTalk360 overcomes this challenge with its auto healing capability.
I am writing series of articles on “Automating BizTalk Administration Tasks Using BizTalk360”. First Blog was focused on service instance data monitoring. In this article, I will be explaining how you can leverage auto healing capability in BizTalk360.
How it works
With the auto healing functionality, Administrators can set up monitoring on any “State-based” artifact and let the monitoring service to automatically heal the artifact any time when there is a mismatch between the “Expected State” and “Current State”. For instance, Administrators can set up monitoring on the receive location(s) of an application and additionally set up the auto correct functionality for the Expected State of the artifact (which should be “Started”). Whenever the receive location goes down/gets disabled, there will be a state mismatch and the auto correct will try to bring the artifact back to the expected state. If the operation is successful, the artifact will come back to the Expected State within the next monitoring service cycle (60 seconds).
State Based Monitoring
Artefacts State Based monitoring is one of core feature in BizTalk360. State Based monitoring is included for Application Artefacts
- Send Ports
- Receive Location
- Host Instances
- NT Services
- SQL Jobs
- Logic Apps
To avoid this manual intervention, administrators can set up the “Max Retries” count in the auto healing. This would allow the auto healing (BizTalk360 monitoring service) to continuously try and bring back the receive location to “Enabled State”. If the operation was successful within the Max Retry count, the artifact would be automatically healed to ensure business continuity. If the operation was not successful within the Max Retry count, the artifact would move into a Critical state.
Artefact (Receive Location) is configured for monitoring and auto healing when the receive location violates the threshold condition means monitoring service will first trigger the down alert.
Monitoring service will auto correct the state of receive location to expected state and trigger an auto correct email.
Custom BizTalk Adapters
Custom BizTalk adapters are used in Receive Location/Send Port; In this case, we must install Custom adapter components in BizTalk360 servers.
If you have installed BizTalk360 Monitoring service in multiple servers for BizTalk360 HA then install Custom Adapters pack in multiple servers.
We can few cases where custom adapters pack needs to be installed;
1. BizTalk Scheduler Adapter
When you are using BizTalk Scheduler in BizTalk Artefacts and not installed in BizTalk360 Box. Then you will get the error as like below
“ReceiveLocations: Exception raised while trying to set receive locations to the expected state. Ex: Microsoft.BizTalk.ExplorerOM.BtsException: Could not validate TransportTypeData, Address or Public Address properties for Receive Location ‘Receive_INV_Scheduler’ “
To address this issue we need to GAC Microsoft.BizTalk.Scheduler.dll in BizTalk360 Box(es).
2. nSoftware Adapter
nSoftware Adapters are used in BizTalk Artefacts (e.g.: FTP(s)/SFTP Adapters). Exception raised while trying to set receive locations to the expected state by auto correct feature.
“Ex: Microsoft.BizTalk.ExplorerOM.BtsException: Failed to create ‘nsoftware.SFTP v4’ Transport Component at Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer.SaveChangesWithTransaction(Object transactionObj)”
Overcome this challenge we need to install the nSoftware adapters where BizTalk360 Monitoring service is running.
3. Host Integration Adapter Pack
BizTalk Artefacts are using MQSC (HIS) adapters in your BizTalk Environment means you have to install Host Integration Pack Adapters.
The following are adapters in Host Integration Adapter Pack
- Host Applications
- WebSphere MQ
- Host Files
Taking automatic actions on BizTalk Artefacts, SQL Jobs, Host Instances and NT Services we need adequate permissions to Service Account user with respect to Windows, SQL, and Azure.
|Feature||Minimum Required Permission|
|BizTalk Artefacts – To change the status of BizTalk Artefacts||BizTalk Operators Group|
|Host Instances – Operations (WMI)||BizTalk Administrator Group|
|SQL Jobs – Change state of Jobs||SQLAgentOperatorRole|
|Logic Apps – Enable/Disable||Owner (Subscription User)|
You can see more information about BizTalk Server Security.
BizTalk users can leverage this most powerful feature “Auto Healing” in BizTalk360 to maintain/monitoring the states of various Artefacts. It will minimize the downtime of BizTalk Artefacts.
On a day to day basis, a BizTalk administrator must perform few monotonous activities such as terminating instances, enabling receive locations, ensuring the status of SQL jobs etc. BizTalk360 has few powerful features which help you to automate such monotonous tasks. These features are hidden gems and are overlooked by many BizTalk360’s users, despite the availability of a good documentation. That prompted me to start my new blog series called “Automating BizTalk administration tasks using BizTalk360”. In this blog series, I will be explaining these automation capabilities which BizTalk360 brings to its users.
To start off with in this first blog I am focusing on “Data Monitoring Actions”.
What is Data Monitoring in BizTalk360?
As we are aware, BizTalk collects a diverse set of data into message box database, tracking database, BAM primary import and ESB databases. BizTalk360 brings all these data into a single console and on top of that provides a powerful capability to set alerts based on various thresholds. This feature is called data monitoring. Below is a screenshot that shows all different data sets which can be used in data monitoring feature.
Below table briefly explains various types of data items which could be monitored.
Data monitoring category
|Process Monitoring||With process monitoring you will be able to monitor the number of messages being processed by receive ports, send ports. This is popularly also called as “non-event monitoring”|
Ex: if you want to alert when less than 50 messages received in an hourly window during business hours, then process monitoring is the best fit.
Refer the assist article Process Monitoring for more information.
|Message Box Data monitoring||With this you will be able to set alerts on the number of suspended, running, dehydrated messaging instance.|
Refer the assist article Message Box Data Monitoring for more information.
|Tracking Data Monitoring||With this you can set alerts on tracked messaging events and tracked service instances.|
Refer the assist article Tracking Data Monitoring for more information.
|BAM Data Monitoring||With this you can set alerts on the data stored in BAM tables.|
Refer the assist article BAM Data Monitoring for more information.
|EDI Data Monitoring||With this you can set alerts on the EDI and AS2 reporting data stored in BAM tables.|
Refer the assist article EDI Data Monitoring for more information.
|ESB Data Monitoring||With this you can set alerts on the ESB data and exceptions stored in BAM and ESB tables.|
Refer the assist article ESB Data Monitoring for more information
|Logic Apps Metrics Monitoring||With this you can set alerts on metrics emitted by Logic apps.|
Refer the assist article Logic Apps Metrics Monitoring for more information
Message Box Data Monitoring Actions
In Message Box Data Monitoring, the user can configure the queries to monitor service instances and messages. Monitoring service will send the notification to the users whenever the service instances/Messages count violates the threshold condition.
Message Box Data Schedule can be configured in Data Monitoring > Message Box Data. It can be scheduled at the different frequencies (Daily, Weekly, and Monthly) based on the volume and priority to take the action on service instances/messages.
BizTalk360 provides Highly advanced query builders for selecting the precise and expected suspended instances based data-result. While querying the suspended/All In-Progress Service Instances you can apply the filters like Error Code, Application, Service Class, Service Name etc.
A Message-Context based query is been provided by BizTalk360 for higher business-friendly scenarios. In Message payload, context/promoted properties can be selected to know the transactional message. In Data monitoring schedule the user can choose which context promoted properties to be in an email alert.
Action on Service Instances
The operational user must closely watch the suspended service instances to act on. It is a tedious process to look after all the time. Message Box data monitoring feature will take automatic action on service instances when the set actions are configured in our schedule. The monitoring service will Terminate/Resume the service instances based on either error or warning condition which doesn’t require any manual intervention.
Archiving & Downloading the Message Instances
Message content & context is required for auditing or other purposes of reconciliation. If you have not enabled the tracking option, it is not possible to get hold of the data again. Keeping this in mind, we have implemented archiving the message context when setting the action is taken on instances. In BizTalk360 Settings>System Settings, Archive and Download location of message instances must be configured to archive and download the message instances. Automatic actions with desired backup steps are been taken to make sure all the data are preserved before taking any action.
Note: In order take action on suspended service instances the monitoring service account has to be created as superuser in BizTalk360.
In Data Monitoring dashboard, every monitoring cycle status is shown. When the user clicks on the status tab, it will bring the details about the Query result, Task Action and Exception summary.
In Task Action tab, you can download each instance separately or by using “Click here” button you can download all the instances to the server location. Service Instances messages are download in server location as Zip file with activity ID for the monitoring run cycle.
Data Monitoring, an Auto-Monitoring feature of BizTalk Administration which can take corrective actions with all backup steps in the event of any threshold violations. With just a one-time setting we have our BizTalk360 to make sure all your routine tasks are addressed without a manual intervention. Also, BizTalk360 offers much more monitoring features which will enable all administrators to be pro-actively monitoring the BizTalk environment(s). Next article will see the Auto correction on BizTalk Artifacts and Logic Apps.