You can use SQL Monitor to monitor servers on a different network from your base monitor, for example those hosted on Azure or Amazon EC2.
Certain firewall requirements must be met before you can do this. If you want to monitor clusters, you also need to set up DNS.
The SQL Monitor base monitor needs to be able to connect to the following ports on each machine you want to monitor:
- TCP port 135, used by the Remote Procedure Call (RPC) service.
- TCP port 445, used by the Server Message Block (SMB) service that allows remote file access.
The TCP port for each SQL Server instance on the machine. The default TCP port for the default instance is 1433.
Named SQL Server instances use dynamic ports by default, so you need to configure a specific TCP port for each named instance. For more information, see Configure a Server to Listen on a Specific TCP Port
note: in the TCP/IP Protocol configuration in SQL Server Configuration Manager, it is important to set the TCP Port and clear the TCP Dynamic Ports for each IP address
- Option 1: When not connecting to machines hosted in the cloud or using VPN, dynamic ports can be used: How to configure RPC dynamic port allocation to work with firewalls
- Option 2: When connecting to machines hosted in the cloud, similarly to SQL Server, WMI needs to be configured to use a static port:
- Step 1: Run "dcomcnfg" from command prompt.
- Step 2: Follow steps in this MSDN article: Setting Up a Fixed Port for WMI
- Step 3: Ensure either the machine who you want monitor is using a static IP address and add a lookup for that into %windir%\system32\drivers\etc\hosts - e.g. 126.96.36.199 myazurevm OR that the dns search suffix is added to the comma-separated search list located at HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters (you may need to run ipconfig /registerdns to re-register the dns search list for the first time after setting this registry value)
Further requirements for machines hosted in the cloud
If you want to monitor machines hosted in the cloud, you need to either:
- set up a VPN connection between the network that hosts the base monitor and the network that hosts the machines you want to monitor
set up corresponding inbound rules for the firewall ports listed above, using your cloud provider's management tool
For instructions on how to do this if you're using Azure or Amazon EC2, see the table below.
|Azure - classic deployment model
- In the Azure classic portal, click Virtual Machines and select the machine you want to monitor.
- Click Endpoints. Then, at the bottom of the page, click Add.
- Specify the details required to open the ports listed above under Firewall requirements.
- For each endpoint you add, at the bottom of the page, click Manage ACL. In the Remote subnet box, enter the public IP address of the base monitor.
|Azure - Resource Manager deployment model
- In the Resource Manager portal, click Virtual machines and select the machine you want to monitor.
- On the Settings panel, click Network security group.
- On the Network security group panel, choose the security group associated with the machine you want to monitor, and click Add an inbound rule.
- On the Add inbound security rule panel, specify the details required to open the ports listed above under Firewall requirements. Under Source, select CIDR block, and under Source IP address range, enter the public IP address of the base monitor, followed by /32.
- On the EC2 dashboard, under Network & security, click Security Groups.
- Select a security group associated with one or more of the machines you want to monitor, and click the Inbound tab.
- Click Edit, and add a rule to open each of the ports listed above under Firewall requirements. Under Source, make sure you select Custom IP and enter the public IP address of the base monitor.
- If using Elastic IPs on the machines you want to monitor, you will need to add host entries on the machine which is running the Base Monitor for each SQL instance IP.
DNS (required for monitoring clusters)
If you want to monitor clusters on a different network from your base monitor, you need to set up DNS. This means that on the Add SQL Server screen, when you enter the name of a cluster or node, SQL Monitor can detect all the nodes on the cluster.
If you're only monitoring standalone machines, you don't need to set up DNS. Instead, on the Add SQL Server screen, enter the IP address of the machine you want to monitor.
If your base monitor is in a different region from your monitored servers (eg if it's in America and you're monitoring servers in Asia), you might experience latency. Depending on the impact of the latency, it might be useful to set up a base monitor in the same region as the servers you're monitoring.
Please see the testing data collection methods page to troubleshoot any connectivity problems you may be having.