Reporting Services: Tip # 1

Establish a standard that all Data Connections, especially Shared Data Sources, must include the application name parameter of the connection string. An example is below:

Data Source=epicsql;Initial Catalog=Reporting_Srvs;application name=SDS_Clarity_PROD_Reporting_Srvs;

Our standard calls for placement of the name of the Data Source as the application name.

Why? It will come in handy when the DBA monitors activity generated by the RS server against the SQL server.

The image shows the Processes option of the new Current Activity window in SQL Manager. Now the DBA can see clearly activity has been sent by a specific Data Source of the Reporting Services server. This helps to troubleshoot performance problems.