Installing, configuring and running SQL Server Reporting Services is a non-trivial set of tasks. To successfully perform these, a company needs people with skills related to databases, system administration, report creation and data analysis. Most initial deployments and their usage can take advantage of several best practices in order to improve performance, maintenance and the production of quality reports.
Safeguard the Encryption Key
A number of items such as passwords and connection strings are encrypted within SSRS and stored within the Report Server database or configuration files. Many an installer has forgotten to back up the encryption key and later regretted it. Keep a copy of the key in a password-protected file and place copies in both the SSRS server and another safe spot outside the server. When migrating SSRS to another server, restoring this key will avoid having to re-create encrypted items such as your data sources.
Simplify Security Control
Sysadmins should be in the habit of granting access privileges by Active Directory groups rather than to individuals. Furthermore, reports themselves should be categorized into folders, and access privileges should be assigned per folder. Hierarchical access can be created via subfolder privilege cascading.
Use SQL Authentication in Lieu of Windows Authentication
Windows Authentication for connecting to report data sources is only reliable if the SQL Server database and the SSRS server are on the same machine, which often presents an inconvenience to users trying to access remote machines. To enable access from another machine, use SQL Authentication instead or supply account credentials that work on the remote machine.
Performance Improvement Tips
Optimize SELECT Statements
A bit of fine-tuning of SELECT statements goes a long way to improving SSRS performance by reducing the amount of data fetched to only that which is necessary. Get in the habit of specifying only the column names relevant to your query instead of using SELECT * statements.
Profile Slow Queries
Take advantage of the GUI-based SQL Server Profiler to analyze long-running queries and stored procedures to see if you can wring out even better performance. Note that the profiler will be deprecated in later releases of SQL Server except for Analysis Services workloads.
Use WHERE to Filter at the Database
Learn to use the WHERE clause in your queries for data filtering whenever possible. It is far more efficient for data filtering to take place at the database level and not at the report level. Filtering at the report level should only be used when multiple report items, such as matrices, lists and charts, are sharing a single dataset and each requires different methods of filtering.
Shared Data Sources
Expert programmers know the old maxim that any problem can be solved by introducing another level of indirection. This is important in SSRS reports as well. For example, if at least several of your reports use a common data source connection, do not refer to this connection directly within each report. Instead, set up a project-level Shared Data Source and refer to that within reports. If the source of report data changes at a later time, you only have to modify it in one place for all reports to pick up the new data source.
Maintaining SSRS Report Server Catalog
Within the SSRS software architecture is an SSRS report server catalog, which caches data used to serve up previous reports without re-creating them afresh from the SQL Server database. The catalog tables should be regularly re-indexed and database statistics updated to improve query performance.
Report Design Tips
There are several ways in which you can make SSRS reports more accessible to end users, such as maintaining uniformity of the font type and its size throughout the report, avoiding too many colors, saving space by proper textbox orientation and using alternating row colors. If the report is exported into multiple formats, always perform each export and check the output for readability.
Improving Content Quality
For reports that require the most up-to-date information, prominently display the time and date of the last refresh. This practice helps end users evaluate the report’s relevance. Also, display the data filter parameters, which assists readers’ report comprehension. When presenting visuals, such as pie charts and bar graphs, avoid using too many data categories as the smaller ones will clump together. If necessary, break out the smaller categories into a secondary visual.
SQL Server Reporting Services is a key component for modern enterprise Business Intelligence solutions. It provides a huge array of features essential to extracting and presenting the most relevant data to business decision makers. As such, its complexity requires careful deployment and optimization to achieve the best performance possible and high quality, informative reports for its end users. The tips presented here will help your organization utilize SSRS to best advantage.