November 11, 2019 Weekly Update on Microsoft Integration Platform & Azure iPaaS

November 11, 2019 Weekly Update on Microsoft Integration Platform & Azure iPaaS

Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?

Integration weekly update can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.

If you want to receive these updates weekly, then don’t forget to Subscribe!

 

Microsoft Announcements and Updates

 

Community Blog Posts

 

Video

 

Podcasts

 

How to get started with iPaaS design & development in Azure?

Feedback

Hope this would be helpful. Please feel free to reach out to me with your feedback and questions.

BizTalk Documenter: Unable to locate the help compiler executable while trying to generate the documentation

BizTalk Documenter: Unable to locate the help compiler executable while trying to generate the documentation

Without a doubt, BizTalk Documenter is my favorite documentation tool, and I do think that if each product had a tool like for the generation of technical documentation, it would be simpler to do, as the existing documentation significantly improved. I do miss this kind of tool for Azure Integration projects.

However,
each time I install this tool I always got the exact same problem:

Unable to locate the help compiler executable

BizTalk Documenter 2016 Unable to locate the help compiler executable

which I know well, and I know why since it was very well explained by Mitch Vanhelden in this blog post.

So, why this blog post?

Well, for two main reasons:

  • I’m
    always searching to find the link to the component I need to install;

    • Basically,
      this is just an easy personal reminder;
  • And
    second and most important, the link to the resource that Mitch point is
    obsolete and not working anymore;

Cause

Has Mitch explained in his blog post, the
reason for this is quite clear, the application can’t locate the help compiler
executable, either because:

  • it
    isn’t installed à most common situation
  • or
    it is also possible if you’re working on a 64-bit machine.

Solution

Make sure you have installed the HTML Help
Workshop compiler because this is the most common cause for this issue and if
not:

  • First, download and install this compiler that can be found here: HTML Help Workshop and Documentation;
  • And then install it, by executing the htmlhelp.exe file
  • On the HTML Help Workshop 1.3 screen, click Yes
install HTML Help Workshop compiler
  • On
    the HTML Help Workshop 1.3 Setup screen, click Yes
install HTML Help Workshop compiler
  • Specify
    the installation directory and then click OK
install HTML Help Workshop compiler
  • And
    finally, when the installation finish, click OK

After these steps, you should be able to
generate the BizTalk Server documentation form the tool. Otherwise, make sure
that the path to this help compiler is configured correctly in BizTalk
Documenter by:

  • Access
    to the installation path of BizTalk Documenter

    • By
      default: C:Program Files (x86)Microsoft ServicesBizTalk Documenter
  • Open
    Microsoft.Services.Tools.BiztalkDocumenter.exe.config file and validate
    and, if necessary, change the path for the HelpCompilerLocation key
    that needs to contain the correct path to the HTML Help Workshop compiler
    component.

The post BizTalk Documenter: Unable to locate the help compiler executable while trying to generate the documentation appeared first on SANDRO PEREIRA BIZTALK BLOG.

BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[ColumnName] of type StoredProcedure does not exist.

BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[ColumnName] of type StoredProcedure does not exist.

In the past, I wrote a blog post about a similar type of error: BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[StoredProcedureName] of type StoredProcedure does not exist. The difference for this new post is that instead of being the Stored Procedure name that is complaining it is now complaining about the Column name:

A message sent to adapter “WCF-Custom” on send port ” STAGING_SQL_WCF_SEND” with URI “mssql://SQL-SERVER-NAME//Database?” is suspended.
Error details: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[IdRecord] of type StoredProcedure does not exist

Server stack trace:
at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)
MessageId: {0193EE6F-8DFF-4861-87FB-FC1C82ECF3AB}
InstanceID: {59E3F39A-BF24-4583-BEA9-78CED5B621F7}

Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[ColumnName] of type StoredProcedure does not exist.

However, despite this error and despite the
fact that we were talking about the same server and same project of the previous
issue, one thing I was sure about: they were not related at all.

Cause

At a first glimpse, it seems that the stored
procedure does not include the column name: IdRecord, or that this field
was not passed to the stored procedure at all. So, this was my first point to validate,
however, quickly I realized that:

  • The
    stored procedure had that field;
  • And
    I was correctly passing that field to the stored procedure in the message;

Just to precaution, I double-check if that was
not related to security permission, and it was not also. So I went back to my some
conspiracy theories list that I described on my previous post:

  • You
    should regenerate schemas: no, never
  • or
    that may be a mismatch in the namespaces: I believe it could be a
    possibility, but no, since I was using the same scheme and it was working fine
  • that
    the “?” character that you normally find in the URI is causing this problem: also
    impossible in my case
    .
  • And
    my favorite is that you should give “sysadmin” rights to the service account
    that is running the host instance: never.

But the last one put me thinking: the
operation is not properly set
.

Because the last change I did was redesign the
solution that was performing composite operations with the SQL Adapter, in my
case I was sending multiple-rows to update in the same message.

And now I was doing a single operation.

And by doing that the WCF-SQL Adapter was throwing
this strange behavior.

Solution

The solution was quite simple. We just have to
change the Action CompositeOperation with the correct operation action mapping
as show bellow as an example:

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Operation Name="SQLStatusUpdateOp" Action="TypedProcedure/dbo/sp_BTS_updateTransaction" />
</BtsActionMapping>

After doing this change, everything started
working perfectly again.

The post BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[ColumnName] of type StoredProcedure does not exist. appeared first on SANDRO PEREIRA BIZTALK BLOG.

November 04, 2019 Weekly Update on Microsoft Integration Platform & Azure iPaaS

November 04, 2019 Weekly Update on Microsoft Integration Platform & Azure iPaaS

Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?

Integration weekly update can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.

If you want to receive these updates weekly, then don’t forget to Subscribe!

 

Microsoft Announcements and Updates

Community Blog Posts

 

Video

 

Podcasts

 

How to get started with iPaaS design & development in Azure?

Feedback

Hope this would be helpful. Please feel free to reach out to me with your feedback and questions.

BizTalk Server: The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’.

BizTalk Server: The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’.

Recently a
friend of mine that is working with me in a project send me an email reporting
a quite curious issue that I found while accessing the BizTalk Server
Administration console in our development environment:

This operation failed while accessing at least one of the Message Bix databases. Some results might be omitted. (Microsoft.Biztalk.Administration.SnapIn)

Additional information:

The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’. (Microsoft SQL Server, Error: 9002)

The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’

Immediately I point out to the team that this issue
was related to lack of disk space.

Cause

The official cause of
this issue is that when the transaction log becomes full, SQL Server Database
Engine issues a 9002 error. The log can fill when the database is online, or in
recovery. If the log fills while the database is online, the database remains
online but can only be read, not updated.

And for us to know the exact cause for what is
preventing log truncation, we can use the log_reuse_wait and log_reuse_wait_desc
columns of the sys.database catalog view.

In our case, it was indeed a problem with disk
space and what happened was that the disk to where we were doing backup went out
of disk space, because we cannot do the backups the transaction log grow until
the point that disk (that contain the log file) also went out of disk space.

Solution

When you know the issue, the solution is quite easy. In this case, freeing disk space from both hard drives immediately solves the problem. Nevertheless, because the log file got quite big you should think of stopping your BizTalk Server environment and do maintenance in your databases in special reduce the size of the transaction log.

For that you should:

  • Perform
    a full back of your databases;
  • Stop
    all BizTalk Server services (host instances and enterprise Single Sign-on)
  • And
    run the following SQL Script
ALTER DATABASE BizTalkMsgBoxDb
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (BizTalkMsgBoxDb_log, 2048);
GO

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
GO

  • Do
    about the same steps for other databases whose transaction logs are also quite large.

The post BizTalk Server: The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’. appeared first on SANDRO PEREIRA BIZTALK BLOG.

October 28, 2019 Weekly Update on Microsoft Integration Platform & Azure iPaaS

October 28, 2019 Weekly Update on Microsoft Integration Platform & Azure iPaaS

Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?

Integration weekly update can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.

If you want to receive these updates weekly, then don’t forget to Subscribe!

Microsoft Announcements and Updates

 

Community Blog Posts

 

Video

 

Podcasts

 

How to get started with iPaaS design & development in Azure?

Feedback

Hope this would be helpful. Please feel free to reach out to me with your feedback and questions.

SQL Server Monitoring in BizTalk360

SQL Server Monitoring in BizTalk360

From the upcoming BizTalk360 version 9.0 phase 3 on, we extend our support to monitor SQL Server clusters. By configuring the SQL Server Network Name for monitoring, BizTalk360 will start monitoring the current active node and in case of failover scenario, BizTalk360 will automatically take the active server for monitoring.

 It’s important to make sure the physical infrastructure of your SQL servers like disks, CPU, memory, etc., are healthy for proper functioning. BizTalk360 made this simpler by having the SQL Server Monitoring capability.

BizTalk360 will alert to the user when the configured disk space, CPU, memory usage exceeds the set percentage.

Challenge

In existing BizTalk SQL Monitoring, we came across a few challenges on monitoring the clustered SQL Server

  1. The user could not find the active node while monitoring the clustered SQL Server.
  2. When the Failover happens, the disk monitoring could not identify the disks of the failover node and an error message was displayed as below.

sql-server-status

Scenario 1 (Active/Active): As the same as the previous version of BizTalk360, you can monitor the Clustered SQL Server (Active/Active) or a non-clustered SQL Server, by simply adding the SQL Server physical node in the “Add SQL Server For Monitoring”.

Scenario 2 (Active/Passive):  In BizTalk360, you can monitor the Clustered SQL Server (Active/Passive), by adding the common “SQL Server Network Name” for the physical node into the “Add SQL Server For Monitoring”. It will automatically pick up the active server node for monitoring and then it will start monitoring the active node. If a Failover happens, then it will automatically switch to the failover node (Active Node).

Let’s take a quick look at how you can monitor Non-Clustered/Clustered SQL Server (Active/Passive) using BizTalk360.

Non-Clustered SQL Server

The BizTalk server database will be pointed to one SQL server Instance as below.

Non-SQL-clustered-server

Failover Clustered SQL Server

Windows Failover Clustering is a high-availability option which is designed to increase the uptime of SQL Server instances. A SQL Server cluster includes two or more physical servers, called nodes. One is identified as the active node, on which a SQL Server instance is running the production workload, and the other is a passive node, on which SQL Server is installed but not processing the workload. If the SQL Server instance on the active node fails, the passive node becomes the active node and begins to run the SQL Server production workload with some minimal failover downtime.

http://www.biztalkgurus.com/wp-content/uploads/2019/10/Failover-Clustered-SQL-Server.png

The SQL Server Network Name is used to identify a failover cluster (active node) on the network. This was known as the virtual SQL Server name.

sql-server-network

The virtual SQL Server will automatically connect to the currently active node. You can find the SQL Server Network Name as below in the Failover Manager.

connecting-active-node

Prerequisites or Permissions required to monitor the Non-Clustered/Clustered SQL Server in BizTalk360

  1. MSDTC should be enabled in the BizTalk360 server and SQL Server. In the case of Clustered SQL Server, MSDTC should be enabled in all the SQL Servers which belong to the cluster. Follow the link to enable the MDTC.
  2. If BizTalk360 is installed using the service account, then the service account should get added to the Administrator group in the SQL Server node. In the case of a Clustered SQL Server, the service account should get added to both physical SQL Server Nodes(active & passive node).

How the (Non-Clustered) SQL Server  is monitored in BizTalk360

Let’s have a detailed look at how you can monitor a non-clustered SQL server using BizTalk360.

By adding the Physical SQL server name for Monitoring, you can monitor the disks, CPU, memory, etc. You can add the SQL Server name under BizTalk360->Settings ->Monitoring and Notification ->Add SQL Server for monitoring.

monitoring-sql-server

Note: For non-clustered SQL servers you must add the physical SQL Server name for monitoring.

Once the above configuration is done, the added SQL servers will get available for monitoring as below.

monitoring

The configured server will be listed for monitoring under BizTalk360->Monitoring ->Manage Mapping->SQL Servers. By clicking on the Server name you can configure Disks, System Resources (CPU, memory), Event Logs and NT Services of the specific SQL server for monitoring.

Disk Monitoring

A click on the Disks tab, will automatically pick up all the disks in the configured SQL server and show a graphical view of the disk properties with total and available disk space. To monitor the disk, you can simply set the appropriate warning and error threshold level for each disk in percentage (%), as shown below. For more detailed information, follow the article link.

disk-monitoring

Note: The system reserved and the unpartitioned disk will not be available for monitoring.

System Resource Monitoring

Click on the System resource tab to monitor the CPU usage and Memory consumption. Next, configure the appropriate warning and error thresholds for CPU and memory. The system will start alerting if the system condition hits the configured warning/error level for the persisted duration, which you can see below. For more detailed information about the CPU and Memory, follow the article link.

system-resource-monitoring

By default, warning and error thresholds for CPU and memory will be in 30% and 10%, but you are free to reconfigure these thresholds depending on your own requirements

Event Log Monitoring

You can monitor for various event log entry conditions using BizTalk360. You can have multiple combinations of event log monitoring, for example, certain sources, certain event ids, certain text in the message, with configured threshold conditions as below.

Note that you can also set up Event Log monitoring in the Data Monitoring section. Setting it up there helps in correlating event log entries across multiple servers. For more detailed information about the Event Log monitoring, follow the article link.

event-log-monitoring

NT Service Monitoring

You may want to keep an eye on certain NT services that are running in SQL Server. BizTalk360 gives you the opportunity to automatically monitor the NT services by just setting the expected state. BizTalk360 will alert you when the configured expected state is not equal to the current state. For more detailed information about the NT Services monitoring, follow the article link.

NT-Service-Monitoring

How a SQL Clustered node is monitored in BizTalk360

Now, let us have a quick look at how we can use BizTalk360 to monitor a clustered SQL server.

Configuring a Cluster SQL Server for monitoring

If you want to monitor a Clustered SQL Server, all you need to do is, specify the name of the Virtual SQL Server (SQL Server Network Name). When you connect to SQL Server using this SQL Server Network Name, BizTalk360 will automatically connect to the currently active node.

Configuring-Cluster-SQL-Server

Note: It is is enough to add just the SQL Server Network Name for monitoring the SQL Cluster,  it is not required to add the actual physical SQL cluster nodes.

Once the above configuration is done, then the added clustered SQL Server Name will get listed for monitoring as shown below.

It will automatically pick the active node and the state of the (clustered) server.

Disk Monitoring

In clustered SQL Server monitoring, it will show the currently active monitoring Node, and also it will automatically pick up all the disks in the currently active node. 

By Default, warning and error thresholds for each disk will be in 20% and 10%. You can also change the warning and error percentage as per your need and save it. Then, the currently active node will get monitored by the BizTalk360. If the disk space exceeds the set percentage, BizTalk360 will send the alert to the respective user.

If the “Failover” happens for the monitored disk, then the configured disk will automatically switch to the active server(Failover server).

Consider a scenario if Node 1 has disk A and disk B which are configured for monitoring (error and warning condition as 50 %). Let say, Node 1 went down, then BizTalk360 will automatically pick up the currently active node 2 for monitoring. But what happens if node 2 has Disk B and Disk C?

In the above scenario, the default monitoring configuration i.e error and warning condition 20% is set to the Disk C. Disk A, which is available in node 1 but not in node 2 will be moved to the orphaned state.
state

As in the above screenshot, the “STR-Disk1 (F:)” disk is newly added after failover with the default warning and error conditions. The other two disks are “STR-Disk2 (E:)”, which is a shared volume, and the (C:) drive is a common volume, which is also configured with the existing warning and error percentages.

System Resource Monitoring

As the same as disk monitoring, you can set up appropriate warning and error thresholds for CPU and memory as per your business needs. For a Clustered SQL Server, it will show the current active monitoring Node. It will automatically pick up the CPU usage and the memory for the currently active node.

System-Resource-Monitoring

In the case of a Failover, the CPU and the memory will automatically get updated for the active node (failover node) with the existing warning and error conditions.

Event Log Monitoring

In a Clustered SQL Server, BizTalk360 monitors the Event Log based on the active node. You can have multiple combinations of event log monitoring, for example, certain sources, certain event ids, certain text in the message, with configured threshold conditions.

Once after Failover, then BizTalk360 automatically starts monitoring the existing event log conditions based on the currently active node (Failover node).

NT Service Monitoring

In a Clustered SQL Server, current monitoring will be based on the active server. It will pick up the NT services from the active server for monitoring as you can below.

Once after Failover, the configured NT service will automatically switch to the failover node and start monitoring.

Conclusion

Our objective with the monitoring aspect of BizTalk360 is to make it simple to configure, manage and use it on a day to day basis. The above SQL Server Monitoring will do that, and it will also do the automatic way of monitoring when a failover happens. There is plenty to consider when planning on clustering SQL Server, but we trust that we came up with the best solution. For any suggestion or feedback, post it in the feedback portal.

The post SQL Server Monitoring in BizTalk360 appeared first on BizTalk360.

October 21, 2019 Weekly Update on Microsoft Integration Platform & Azure iPaaS

October 21, 2019 Weekly Update on Microsoft Integration Platform & Azure iPaaS

Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform and Azure iPaaS?

Integration weekly update can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.

If you want to receive these updates weekly, then don’t forget to Subscribe!

 

Microsoft Announcements and Updates

 

Community Blog Posts

 

Video

 

Podcasts

 

How to get started with iPaaS design & development in Azure?

Feedback

Hope this would be helpful. Please feel free to reach out to me with your feedback and questions.

Microsoft Integration and Azure Stencils Pack for Visio: New major version available (v5.0.0)

Microsoft Integration and Azure Stencils Pack for Visio: New major version available (v5.0.0)

It was only 3 days ago that I released the latest version of this package, but someone (aka Wagner Silveira) alerted me to the existence of new shiny icons in the Azure Portal… so I decided it would be a good time to launch a new major release and here it is! I hope you guys enjoy.

Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack it’s a Visio package that contains fully resizable Visio shapes (symbols/icons) that will help you to visually represent On-premise, Cloud or Hybrid Integration and Enterprise architectures scenarios (BizTalk Server, API Management, Logic Apps, Service Bus, Event Hub…), solutions diagrams and features or systems that use Microsoft Azure and related cloud and on-premises technologies in Visio 2016/2013:

  • BizTalk Server
  • Microsoft Azure
    • Integration
      • Integration Service Environments (ISE)
      • Logic Apps and Azure App Service in general (API Apps, Web Apps, and Mobile Apps)
      • Azure API Management
      • Messaging: Event Hubs, Event Grid, Service Bus, …
    • Azure IoT and Docker
    • AI, Machine Learning, Stream Analytics, Data Factory, Data Pipelines
    • SQL Server, DocumentDB, CosmosDB, MySQL, …
    • and so on
  • Microsoft Power Platform
    • Microsoft Flow
    • PowerApps
    • Power BI
  • Office365, SharePoint,…
  • DevOps and PowerShell
  • Security and Governance
  • And much more…
  • … and now non-related Microsoft technologies like:
    • SAP Stencils
Microsoft Integration (Azure and much more) Stencils Pack

The Microsoft Integration Stencils Pack is composed of 27 files:

  • Microsoft Integration Stencils
  • MIS Additional or Support Stencils
  • MIS AI and Machine Learning Stencils
  • MIS Apps and Systems Logo Stencils  
  • MIS Azure Additional or Support Stencils
  • MIS Azure Mono Color
  • MIS Azure Old Versions
  • MIS Azure Others Stencils
  • MIS Azure Stencils
  • MIS Buildings Stencils
  • MIS Databases and Analytics Stencils
  • MIS Deprecated Stencils
  • MIS Developer Stencils
  • MIS Devices Stencils
  • MIS Files Stencils
  • MIS Generic Stencils
  • MIS Infrastructure Stencils
  • MIS Integration Fun
  • MIS Integration Patterns Stencils
  • MIS IoT Devices Stencils
  • MIS Office365
  • MIS Power BI Stencils
  • MIS PowerApps and Flows Stencils
  • MIS SAP Stencils
  • MIS Security and Governance
  • MIS Servers (HEX) Stencils
  • MIS Users and Roles Stencils

That you can use and resize without losing quality, in particular, the new shapes.

What’s new in this version?

I still have many things to do in this project in terms of organization and cleaning some resources but I will leave that for another occasion. The main goal of this release was to provide the new icons present in Azure Portal. In this version the changes and additions are:

  • New shapes: I think almost all the new shapes layout present in Azure Portal are now added in this package.
  • New categories: MIS Azure Mono Color, MIS Azure Old Versions, MIS Azure Others, MIS Integration Fun;
  • Categories Renaming: MIS Databases and Analytics and MIS AI and Machine Learning
  • SVG Files: The SVG files, from all these new resources, are now available on GitHub

Download

You can download Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio from:
Microsoft Integration Azure Stencils Pack VisioMicrosoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack for Visio
GitHub

or from :

You can download Microsoft Integration Stencils Pack for Visio 2016/2013 from:

Microsoft Integration Stencils Pack for Visio 2016/2013 (10,1 MB)
Microsoft | TechNet Gallery

The post Microsoft Integration and Azure Stencils Pack for Visio: New major version available (v5.0.0) appeared first on SANDRO PEREIRA BIZTALK BLOG.

Database Size Monitoring

Database Size Monitoring

In any BizTalk environment, it is important to keep track of database growth and execute the data purging policies whenever necessary. When the database size grows, the SQL server needs more memory and CPU to read data from the tables, which slows down the database operation. Besides that, there is also the risk that, due to the growing database(s), you are running out of disk space. Database Administrators spend a lot of their time dealing with the problem of database processes consuming too much disk space. So, it’s very important to monitor the database size to ensure the database is not seizing the memory and CPU for smooth BizTalk server operation.

The ability of Database Size Monitoring is a feature request we picked up from our feedback portal. This feature will be available for production use from BizTalk360 version 9.0 phase 3 on.

What can be monitored with Database Size Monitoring?

With Database Size Monitoring, you can monitor the data file and log file sizes of below BizTalk Server and BizTalk360 databases, by simply configuring the error and warning threshold conditions for the data and log file sizes.

  • BizTalkDTADb
  • BizTalkMgmtDb
  • BizTalkMsgBoxDb
  • BizTalkRuleEngineDb
  • BAM databases (BAMPrimaryImport, BAMAlertsApplication, BAMArchive, BAMStarSchema)
  • SSODB
  • BizTalk360

For example, if the size of the BizTalkDTADb is greater than a configured threshold value, then you will get notified.

Scenario

OutOfMemoryException in BizTalkDTADb

BizTalkDTADb contains the health monitoring data tracked by the BizTalk Server tracking engine. The size of the database can grow relatively quickly depending on the load of your server, which in turn results in OutOfMemory Exceptions.

This scenario can be overcome by proactively knowing the size of the database and acting against the exponential growth of the database by configuring the threshold limit in database size monitoring.

Monitor BizTalk360 database Growth

There might be a situation where the BizTalk360 database grows due to the amount and size of event log data and performance counter data. The BizTalk360 purging policy will take care of the data growth, but it is also important to monitor the size regularly to ensure BizTalk360 is working seamlessly.

Configuring Database for monitoring size

We wanted to simplify the process of configuring database size monitoring. To achieve this, we list all the BizTalk and BizTalk360 related databases. You can start monitoring by specifying the threshold details from the SQL instance.

To configure a database for monitoring, navigate to Monitoring->BizTalk environment-> Database Size, which lists all the BizTalk and bizTalk360 databases under the respective SQL Instances. Click on the config gear icon, as shown in the above image, to configure the threshold values to monitor the size of the database. Based on the threshold configuration, the user will get notified if there is any threshold violation occurring.

The available Threshold types, logical condition, and unit prefixes are:

Threshold Type

Logical Condition

Unit Prefixes

Data File Size

Data File Used Space

Data File Available Space

Log File Size

Log File Used Space

Log File Available Space

Equals

Not-Equals

Greater-than

Greater-than-or-Equal

Less-than

Less-than-or-Equals

Bytes

Kilo-Bytes (KB)

Mega-Bytes (MB)

GigaBytes (GB)

Following are the threshold types on which you can monitor a database:

Threshold Type

Explanation

Data File Size

This threshold type represents the reserved space for the data file.

For e.g.:

Warn Me if Data File Size is Greater-than 200 GB

Error if Data File Size is Greater-than 250 GB

Data File Used Space

This threshold type represents the size used by the data file.

For e.g.:

Warn Me if Data file Used Space not-equals 250 GB

Error if Data file Used Space not-equals 250 GB

Data File Available Space

This threshold type represents the available space for the data file

For e.g.:

Warn Me if Data file Available space less-than-or-equals 99 GB

Error if Data file Available space less-than-or-equals 99 GB

Log File Size

This threshold type represents the size reserved for the transaction log file.

For e.g.:

Warn Me if Log File Size is Greater-than 200 GB

Error if Log File Size is Greater-than 200GB

Log File Used Space

This threshold type represents the space used by the log file.

For e.g.:

Warn Me if Log File Used Space not-equals 250 GB

Error if Log File Used Space not-equals 250 GB

Log File Available Space

This threshold type represents the available space for the log file.

For e.g.:

Warn Me if Log File Available Space Greater-than 250 GB

Error if Log File Available Space Greater-than 250 GB

Once the threshold values are configured and saved, the monitoring conditions will be evaluated, and the status of the database size will be displayed in the grid.

The Monitoring Dashboard and BizTalk Group Dashboard display the overall status of the alarm including the Database size shown in the picture below.

The user will get notified through mail or notification channel when any threshold violation happens.

Quick Alarm

You can also configure database size for monitoring simply by configuring Quick alarm. In this case, an alarm will be created and all the BizTalk and BizTalk360 databases will get configured for database size monitoring with default threshold conditions.

Conclusion

Considering the feedback provided by our customers, via the feedback portal, BizTalk360 will continue to provide more useful features every single release. In the upcoming release v9.0 phase 3, the ability to monitor Database Sizes is only one of the features we bring. If you have any suggestions for a =n upcoming version, please write to us at support@biztalk360.com.

The post Database Size Monitoring appeared first on BizTalk360.