SQL Server

SQL Server availability monitoring in BizTalk360 alerts you when the SQL Servers behind your BizTalk environment become unavailable, before message processing stops. BizTalk Server depends entirely on its SQL databases (MessageBox, Management, Tracking), so a SQL outage halts the whole integration platform.

Prerequisite

To monitor SQL Server availability, configure the SQL Server under Environment Settings. Once the configuration is successful, the SQL tab under Server Availability lists all discovered SQL Servers, grouped by type: Standalone, Windows Cluster, SQL Cluster, and Always On Availability Group.

BizTalk360 supports below SQL Server deployment types:

• Standalone SQL Server: A single SQL Server instance. BizTalk360 checks whether the server is reachable and alerts when it is not.

• Windows cluster: A Windows Server Failover Cluster. BizTalk360 monitors each node's availability and tracks which node is active.

• SQL cluster (Failover Cluster Instance): A SQL Server instance running on a Windows cluster. BizTalk360 monitors node availability and the instance's active node, so you know when a failover has occurred.

• Always On availability group: A group of databases replicated across SQL Server replicas. BizTalk360 monitors the nodes behind each replica and tracks which replica is primary.

Note: Ensure that either Ping or Telnet is enabled and accessible on the respective server to perform availability monitoring.

Protocol Type sets how BizTalk360 checks whether a server is reachable:

• ICMP (Ping): Sends a ping to the server. Use this when ICMP is allowed on your network.

• Telnet: Opens a connection to the server's port. Use this when ping is blocked by a firewall.

Changes to Alert when configuration in 12.0

1. After migrating to version 12.0, the Alert When option has been moved to the individual Cluster or Availability Group level ,and this is no longer applicable to Standalone servers.

2. When you migrate to v12.0, Alert When is set to All Nodes Down for every Windows cluster, SQL cluster and Always On availability group. Review this setting after migration and change it to match your monitoring requirements. For example, you can choose to be alerted when any one node in a cluster goes down, or when a node goes down in the primary or secondary replica of an availability group.

Configure Standalone Server Availability Monitoring

Monitor a standalone SQL Server and receive an alert when it becomes unavailable.

1. Navigate to Monitoring → Server Availability → SQL.

2. Locate the required server under Standalone and click the server name. The monitoring configuration blade opens.

3. Enable the Monitoring toggle.

4. Map an automated action, if required, to run a recovery task as soon as a failure is detected and keep the business running.

5. Save the configuration. The BizTalk360 monitoring service now checks the server on each monitoring cycle and sets its status.

6. Click the eye icon to view the monitoring summary for the server.

Configure Windows Cluster Node Monitoring

You can monitor the availability of each node in a Windows cluster and track changes to the active node.

1. Navigate to Monitoring → Server Availability → SQL.

2. Locate the required cluster under Windows Cluster and click the cluster name. The monitoring configuration blade opens.

3. Enable the Monitoring toggle.

4. Under Node Availability, configure the Alert When option:

   • All Nodes Down: Triggers an alert only when all nodes in the cluster are unavailable.

   • Any Node Down: Triggers an alert when any single node in the cluster is unavailable.

5. Under the cluster configuration, review the Current Active Node and select the required Preferred Node.

6. Enable Alert when the preferred node is not active to receive an alert when the preferred node is no longer active.

7. Enable Alert when the active node changes to receive an alert whenever the active node changes.

8. Configure the required alert actions for these conditions, if applicable.

9. Map automated action to trigger any action once failure is detected to continue the business .

10. On successful configuration the monitoring status will be determined by monitoring service , You can now see the available nodes and their current status .

11. Click on the eye icon to view the monitoring summary for the cluster.

    

Configure SQL cluster monitoring

You can monitor the nodes of a SQL Server Failover Cluster Instance and track which node is running the SQL instance.

1. Navigate to Monitoring → Server Availability → SQL.

2. Locate the required cluster under SQL Cluster and click the cluster name. The monitoring configuration blade opens.

3. Enable the Monitoring toggle.

4. Under Node Availability, set the Alert When option:

   • All Nodes Down: Triggers an alert only when every node in the cluster is unavailable.

   • Any Node Down: Triggers an alert when any single node in the cluster is unavailable.

5. Under Instance Monitoring, review the Current Active Node for the SQL instance and select the required Preferred Node.

6. Enable Alert when the preferred node is not active to be alerted when the instance is running on a node other than the preferred one.

7. Enable Alert when the active node changes to be alerted whenever the SQL instance fails over to another node.

8. Under Automated Action, select a Task to Trigger on Violation, if required.

9. Save the configuration. The grid shows the cluster's Alert When condition and whether instance monitoring and an automated action are configured.

10. Click the eye icon to view the monitoring summary for the cluster.

Configure Always On availability group monitoring

You can monitor the nodes behind each replica of an Always On availability group and track which replica is primary.

1. Navigate to Monitoring → Server Availability → SQL.

2. Locate the required availability group under Always On Availability and click its name. The Replica & Node Availability Monitor blade opens.

3. Enable the Enable monitoring toggle.

4. Under Node Availability, select one or more Alert When conditions:

   • All nodes down: Triggers an alert only when every node across all replicas is unavailable.

   • Any one node down in primary replica: Triggers an alert when any node hosting the primary replica is unavailable.

   • Any node down in secondary replica: Triggers an alert when any node hosting a secondary replica is unavailable.

5. Under Primary Replica Monitoring, expand the availability group and review the Current Primary Replica.

6. Select the required Preferred Replica from the list of replicas in the group.

7. Enable Alert when the preferred replica is not primary and choose the alert severity, for example Throw Error.

8. Enable Alert when the primary replica changes and choose the alert severity.

9. Under Automated Action, select a Task to Trigger on Violation. Any automated task configured in the environment can be mapped here, and it runs automatically when a violation reaches the specified threshold. Click the refresh icon to reload the task list.

10. Save the configuration. The grid now shows the group's Alert When condition and whether replica monitoring and an automated action are configured.

11. Expand the availability group in the grid to see each replica's role, failover mode and node status.

12. Click the eye icon to view the monitoring.

Use case: detecting an unplanned SQL failover

A retail company runs its BizTalk databases on a two-node SQL Server Failover Cluster Instance, SQLFCI2, with nodes SQLNODE1 and SQLNODE2. SQLNODE1 is sized for production load and is the preferred node. SQLNODE2 is a smaller standby.

Configuration. The BizTalk administrator configures cluster monitoring for SQLFCI2 as follows:

What happens during an outage.

1. When SQLNODE1 loses its network connection. The cluster fails over and SQLNODE2 becomes the active node.

2. On its next cycle, the BizTalk360 monitoring service detects three conditions: a node is down, the active node changed, and the preferred node is not active.

3. Team receives alert describing all three conditions, through the configured notification channels.

4. The automated action restarts the BizTalk host instances so they reconnect to the database on SQLNODE2, and processing continues.

Without these monitoring insights, the failover would have been invisible. BizTalk would have kept running on the undersized standby node, with no redundancy left, until a second failure caused a complete outage.

Dashboard and Notifications

Once the SQL servers are enabled for monitoring, the health status of it will be shown in dashboard in a graphical tree view.  If the server enabled is not available, it will be listed in the errors and warnings along with the associated alarms. You can also navigate to the respective SQL server on clicking them.

In order to receive email notifications from BizTalk360 on the threshold violation alerts (and auto-correction alerts), you need to configure the SMTP settings under BizTalk360 Settings. Follow the steps in this article to be able to configure the SMTP settings in BizTalk360. You will receive an email notification with the exact details of the status of the SQL server availability.