BizTalk360 with SQL Server Express Edition

Introduction

Most of our customers prefer to install BizTalk360 in a standalone server. Importantly, this approach does not overload the BizTalk environment or the BizTalk360 instance. Quite often, the BizTalk360 database is configured on the same SQL Server instance as where BizTalk databases reside. At the same time, few customers prefer to configure the BizTalk360 database in a separate SQL Server Instance, considering the 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 using SQL Server Express Edition.

Customer Scenario

BizTalk360 is installed on a standalone server with all components, which are:

• The database
• The web portal
• The monitoring
• The analytics service

The customer has installed SQL Server 2017 Express edition.

SQL Server Express Edition

Let us see features and limitations of SQL Server Express edition.

Features

• 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

Limitations

• 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 of 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
• Analytics
• Data Purging

Event Log

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

Analytics

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 can be enabled at port level and at pipeline level. BizTalk360 uses its Analytics service to determine Message Patterns, 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.

Data Purging

BizTalk360 comes out of the box with the ability to set purging policies. The background Monitoring service has the capability to purge older data automatically after a specified period.  Data purging is configurable for different activities 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 implications 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.

Conclusion

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

1. Enable important Event Log source to data collection.
2. Another key point is configure single BizTalk environment in a BizTalk360 Instance.
3. Periodically check the size of database and optimize the collected data.