Description: You are configuring a Skype For Business server to be monitored in Prognosis and want to confirm SQL connectivity.
Solution: There is a simple pair of commands you can run to confirm connectivity to the LCSCDR and QOEMETRICS databases. Below are the directions to follow.
Testing the SQL Connection with IROSQL:
1. Open a command window with the "run as another user" option and use the windows user that is valid for that SQL connection
- In WIndows 2008 hold down the shift key while right clicking the CMD shortcut, and then select "Run as another user"
- In WIndows 2012, Make sure you are in the 'Apps' view (Where you see all the shortcuts to the installed apps), right click on the 'Command Prompt' shortcut and down the bottom there will be an option appear. Click "Run as a different user".
2. Set your path to the C:\Prognosis\Reporter\Bin directory (THis needs to be a server that had 'Managing Role selected. See bottom of this article for a download link to a standalone irosql.exe file if you do not have a server suitable for this)
3. Then execute the below similar commands:
Command to run for QoEMetrics: irOsql -E -S 127.0.0.1,1433 -Q "Use QoEMetrics; select * from Pool"
Command to run for LcsCDR: irOsql -E -S 127.0.0.1,1433 -Q "Use LcsCDR; select * from Pools"
Obviously change the IP address above to that of the Lync SQL Server.
Expected results: Both queries should return data.
Note: Be careful with the quotes.
“ will cause an error
" will work
Same test but with a SQL Type Account rather than Windows Authentication:
Same as above but with following differences:
1. Open an Administrator Command Prompt on C:\Prognosis\Reporter\Bin (Again on a server that had Managing selected at install time or using the standalone irosql at the bottom)
2. The Syntax for both commands is different:
Command to run for QoEMetrics: irOsql -U YOURUSER -P YOURPASS -S 127.0.0.1,1433 -Q "Use QoEMetrics; select * from Pool"
Command to run for LcsCDR: irOsql -U YOURUSER -P YOURPASS -S 127.0.0.1,1433 -Q "Use LcsCDR; select * from Pools"
Note: With the above, your substiuting in YOURUSER -P YOURPASS and the IP and port offcource
Standalone irosql.exe download
https://s3.amazonaws.com/supportcases/5006000000Y9De4AAF%2Fa0W60000005peDMEAY%2Firosql.zip?response-content-disposition=attachment%3Bfilename*%3DUTF-8%27%27irosql.zip&AWSAccessKeyId=AKIAJX7O7XYWNTQLW5EA&Expires=2085688823&Signature=PObeQT9iPjsOrJkPALqG971PkxY%3D
