MVP top tips: PowerShell and SQL Stored Procedure
From Lync MVP Desmond Lee which is the 45thin the series.
First paragraphs: Quoted from the link to the full article:
“PowerShell is the window to managing many aspects of a Lync Server 201x environment. Coupled with the deployment of the Monitoring Server service (or separate role in 2010), extensive data can be collected to support operational and troubleshooting demands. By installing the optional Monitoring Reports on selected SQL Server Reporting Services (SSRS) instances, common types of reports are easily available at your disposal.
As Lync deployments become ever more popular and widespread, it became evident that the Lync Management Reports shipped with the product do not address certain reporting requirements needed in the field. Since persistent and dynamic Lync data are stored in various SQL databases in the backend as well as on each Front-End Server, the act of firing up SQL Management Studio, connecting to the right server and executing a SQL query will enable you to put together a user-defined report. You can find many excellent blog posts that walk you through the intricacies of constructing the often complicated looking SQL statements.
Microsoft discourages building SQL queries to pull information directly from the underlying database tables for a good reason. Besides the inherent complexity and tediousness involved, such as the use of multiple table JOINs, the database schema, table relationships and naming conventions may change in future updates and product versions. Hence the risk of breaking customized SQL queries is very real indeed.”