Manage SQL Server

Ensuring the optimal performance of your SQL server involves maintaining the health of its physical infrastructure, including disks, CPU, memory, and other components. To monitor the SQL Server, you need to add SQL Servers to the system. Similarly, for monitoring SQL jobs, you need to add SQL instances to the system.

Adding SQL servers

Add SQL Server to manage and monitor the server Disk, System resources, Event logs, and NT Services running in the server. To add SQL servers to the BizTalk360 portal, you need to follow the below steps:

1. Log in to BizTalk360 and Select the Particular environment.

2. Navigate to Environment Settings->General->SQL Server and click Add SQL Server.

3. Select the server type as Standalone, Cluster, or Always On Group.

   • Standalone - Provided SQL Server name. Though it's reachable or not, you can add the server, but the monitoring will work seamlessly only if it's reachable.

   

   • Cluster - For a cluster SQL Server, you need to add the SQL Server Network Name instead of the SQL Server name. Then select the instance type as Default or Named, if it's named then you need to provide the name of the instance. Once the clustered server is added, BizTalk360 can determine the Active node.

   

   • Always On Group - Provide the Availability group name and the SQL instance configured for that group, it will list all the active nodes and It will show the Primary and secondary node indications for the configured server.

   Always On Availability Group - In order for BizTalk360 to fetch SQL Always On Availability Group nodes, the VIEW STATE permission is required on the master database for the BizTalk360 service account.

   By default, most SQL users will already have this access. However, in environments with restricted security configurations, the VIEW STATE permission might be explicitly revoked or not granted.

   To grant the required permission, execute the following SQL script on the master database:

   use master

   GRANT VIEW SERVER STATE TO [KOVLTP366\BizTalk360] -- Grant access for the Login

   SELECT * FROM fn_my_permissions(NULL, 'SERVER') where permission_name = 'VIEW SERVER STATE';

   

4. Click the Test Server availability to ensure all the configured servers are running. you can check for individual servers or for all the server types in one go.

5. Click Add to add the SQL server, the server connectivity is checked with either Telnet or Ping. Once the SQL Server is added, you can administrate and monitor the resources from BizTalk360 if that is reachable. The reachability of the server is indicated with a color patch of green and red.

6. Remove Server - If the SQL Server is removed, then all the monitoring configurations for that server will be removed.

Adding SQL instances:

The SQL instances must be added to monitor and administrate the SQL jobs. By default, all the instances associated with the BizTalk environment will be added automatically in case if you want to add additional instances, follow below steps.

• Log in to BizTalk360 and Select the Particular environment.

• Navigate to Environment Settings->General->SQL Server and click Add SQL Instances

• Enter the SQL instance name and click Add button.

Once the SQL Server instance is added, you can start administrating and monitor the SQL jobs from BizTalk360.

Note: The instances from the SQL server should belongs the same domain.

Minimum Permissions Required to Access a Remote SQL Server

To allow BizTalk360 to access and manage a remote SQL Server, the BizTalk360 service account must be granted specific user group memberships and WMI permissions on the remote machine.

Required Group Memberships

Ensure the BizTalk360 service account is added to the following local groups on the remote SQL Server:

• Distributed COM Users

• Performance Log Users

• Performance Monitor Users

Configuring WMI Namespace Permissions

Additionally, configure WMI namespace permissions

• Open WMI Management ,In the WMI Control window, right-click WMI Control (Local) → select Properties.

• Go to the Security tab → expand Root → select CIMV2 → click Security.

• Click Add, select the BizTalk360 service account, and grant the following permissions:

  • Enable Account

  • Remote Enable

To ensure seamless connectivity, verify that both WMI and PerfMon can successfully connect to the remote machine. This is crucial for BizTalk360 to retrieve data without interruption.

Licensing

The number of SQL servers and SQL instances you can add is based on the licensing tier.