Problem
I have read the previous tips about SSRS Best Practices for performance and report design. I want to know what other best practices a DBA or developer should follow to improve SQL Server Reporting Services.
Solution
This tip assumes that you have read the previous tips for SSRS Best Practices for performance and report design. If not, I recommend that you read those tips.
In this tip I will discuss a few more best practices a developer or DBA should follow while working on a SSRS project.
1: Enable Caching
It is always better to categorize your reports based on: frequently accessed reports, reports run against live data, reports run against historical data or non-live data. Categorizing reports will help you to take advantage of the Report Caching feature in SSRS.
Generally we should enable Report Caching in these below situations:
- Reports that are frequently accessed should have their cache enabled, this is because every time the report runs it will not hit the report server to process and render the report.
- Reports that are running against historical data or non-live data should also have report cache enabled.
- Big reports or slow running reports should have report cache enabled. Having a version of your reports in memory will greatly influence the server’s performance because the user will access the cached copy every time.
For detailed information please read How to enable caching in SSRS.
2: Run Report Caching and Snapshots during off peak hours
As we discussed in the previous best practice tips, generally we enable report caching when a report doesn’t run against live data or takes a long time to process. It is always better to run report caching or report snapshots during the middle of the night or non-peak hours. It will help the users to run the reports once they are in the office. As users will be accessing the reports from a cached copy there will not be a performance impact on the report server during peak-hours, it will allow users to run other live reports much faster.
For detailed information on SSRS Snapshots please read this tip.
3: Monitor Performance of Database Source system
Performance of the database source systems must be fast and the health of the database source system should be periodically monitored. If the database source system is slow then it will impact report execution time and the report will run slow and the report query will take a long time to retrieve the data.
4: Monitor Performance of Report Server system
Performance of the Report Server systems must be fast and the health of the Report Server system should be periodically monitored. If the Report Server system is slow then it will impact report processing time and reports will be slow.
5: Use saved authentication when configuring report data sources
Windows Authentication is often the most recommended option, but it doesn’t always work well in SSRS. If you configure a report data source to connect to a SQL Server database using Windows Authentication then it only works if the database is on the same server as the SSRS server. But if you need to connect to another physical server, a double-hop authentication is needed. If double-hop authentication is not properly configured then your report will not run. Therefore, you are better off configuring a data source to use a SQL Authentication login.
6: Set Report Execution Timeout
In order to avoid long execution times due to bad or inefficient queries, set the Report Execution Timeout so that your query does not eat up all your resources. Reports that are fairly large will have long execution times, so setting lower timeouts for these does not apply as much. This is more of a preventive measure than anything else.
7: Monitoring Execution Log Data to Identify Slow Reports
Monitor the execution log to identify where the report server is spending most time such as data retrieval, processing and rendering. This will help to find slow reports and then you can take proper action to improve report performance.
For detailed information please read this tip SSRS Execution Log.
8: Backup Encryption Key
SSRS uses encryption to protect your sensitive data (i.e. connection strings and passwords). The report definition, connection strings and passwords are stored in the back-end Report Server database and in configuration files and they are encrypted using an encryption key that’s stored in SSRS.
If you move SSRS to another server, you need to decrypt all the sensitive information which was encrypted by the encryption key from the old server. If you don’t have the same encryption key which was used to encrypt the data then you can’t decrypt all the encrypted data. Therefore, proper encryption key management is very important.
When you install SSRS, the first thing you should do is use the Reporting Services Configuration Manager and take a back up of the encryption key and keep a copy of this file in a safe place. If you need to do a SSRS migration, you can use the same configuration manager to restore the key from the original server. Otherwise, you will have to manually re-create all your data sources and other encrypted content. That’s not something you want to do, especially if your SSRS server is not functional and you are quickly trying to bring up SSRS on another server.
For detailed information please read this tip SSRS Encryption Key.
9: Back up the SSRS Report Server Databases
Because a report server is a stateless server, all application data (i.e. reports, database connection strings, scheduling and user information) is stored in the reportserver and reportservertempdb databases that run on a SQL Server Database Engine instance. It is always recommended to take a backup of the reportserver and reportservertempdb database periodically.
For detailed information please read this msdn article on SSRS Report Server database backups.
10: Practice SSRS migration to another server
Migrating SSRS to another server is not a difficult task, you can easily do it if you have done it in the past so it is recommended to practice a SSRS migration at least once. This exercise will help you bring a SSRS server back online faster if it ever dies.
For detailed information please read this tip Migrating SQL Reporting Services to a new server.
11: SQL Server Reporting Services Security Best Practices
I have seen many SSRS installations where developers and database administrators assign individuals access to the reports, which is not a good practice. It is recommended to control the security at the folder level or by using Windows Active Directory Groups.
For a detailed explanation please read this tip SSRS Security.
12: Keep all reports under source control
Generally, a company has several people developing reports and deploying them to the server without having a central location to store the files and keep them versioned. It is recommended to use source control software such as SVN or SourceSafe to store the files in a central location with the reports versions.
Next Steps
- Review the first two tips in this series
- Always check out and follow best practices while working with SSRS, SSIS and SSAS projects.
- Check out msdn article on report performance.
- Check out SSIS best practices here.
- Check out my all tips.
