Till now, we do not have any third party tool to monitor the SQL server, but we have implemented many custom alerts and report to help us in this regards.As our environments are growing, we have decided to evaluate couple of third party monitoring tools. Yesterday we have installed the Red Gate SQL monitor, but we were not able to add any of our servers to this tool to enable the monitoring. Sql Monitor was not able to establish the connection with the servers, that we need to monitor.On searching Red Gate website, we found the basic requirement to work this tool is , PerfMon should be able to connect to the remote servers from the server where the Sql Monitor is installed.In our case, both servers are in different domains.
The first issue was, we were not able to ping the server using the name as the DNS were not resolving the name correctly.We have fixed the issue by adding an entry in the hosts file (C:\Windows\System32\drivers\etc) as given below
Now we are able ping the server using the server name without any restart. When we tried to access the remote server through PerfMon, it was throwing an error "unable to connect to the machine". Then we have realized that, to connect the a remote server through PerfMon, the remote server should be able to communicate through port number 445. Telnet test showed us the communication between the local server and remote server through port number 445 is blocked. On opening the port, we were able to connect the server using telnet. You may not face this issue,if both servers are in the same domain as the communication through port 445 may be open by default.Again when we tried to connect the remote server, it was throwing the same error but surprisingly we were able to connect the server using the Red Gate Sql Monitor tool. Again we tried with PerfMon, it was not able to connect the remote server . Then we have realized that , PerfMon is trying to the connect the remote server using the logged in user account which will not work by default as both servers are in different domain. The next step to run the perfmon using a different user account which has access to the remote server. This can be achieved by running the below command from command prompt. (open the command prompt window with Run As Administrator option).
runas /netonly /user:MyserverDomain\MyUser "perfmon"
On running this command, it will ask for the password. On entering the password, it will open the PerfMon and you should be able to access the remote server. If you are still facing the issues, follow the below step:
- Open the registry(regedit) on the local machine.
- Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib
- Right click on Perflib and select Permission
- Add LOCAL SERVICE with Full Control
- Restart the Remote Registry Service (services.msc)
If you liked this post, do like my page on FaceBook
www.PracticalSqlDba.com
