Windows Management And Scripting BlogA wealth of tutorials on various Windows Operating Systems related topics like setting up Windows 7/2003/2008/Azure SQL Server 2005,2008 and Azure, troubleshooting and more.
Best practice for SSRS deployment
While SQL Server Reporting Services (SSRS) platform is not hard to learn and work with, it is still a honestly complex technology. Successful utilization of SSRS requires a amalgamation of database, administration, report building and data analysis skills. Such a amalgamation of expertise is often hard to place together, especially in less vital companies where one self might wear many hats.
As a consultant, I have seen several SQL Server Reporting Services deployments that could have benefited from a few simple SSRS best practices. Here are a few.
Back up the encryption key.
SSRS uses encryption to care for sensitive data in its configuration. Things like connection strings and passwords are stored in the back-end ReportServer database and in the configuration files. They are encrypted using an encryption key that’s stored in SSRS. If you go SSRS to another server, you need to use the same encryption key to decrypt all encrypted data. Therefore, proper encryption key management is extremely vital.
When you install SSRS, the first thing you must do is use the Reporting Services Configuration Manager and back up the encryption key to a password-protected file. Keep a copy of this key file on the SSRS server and also in a safe spot somewhere on the network. If you ever need to migrate SSRS to another server, you can use the same configuration manager to restore the key from the original server. Otherwise, you will have to manually re-initiation 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 result in up SSRS on another server. Even even if Microsoft has emphasized the importance of keeping a backup of the encryption key, I still sometimes find myself at a client site and learn that the key isn’t backed up.
Use Windows Committed Index groups to control security.
Systems administrators have long been following the practice of making Windows groups and granting privileges to the assemble instead of assigning privileges to individual user accounts. This practice makes a lot of sense, since you can easily add or remove users from a assemble and make your security management much simpler. But I don’t see this practice as widely used among developers and database administrators. I’ve seen many SSRS installations where whoever was managing privileges assigned individuals access to intelligence or report folders instead of making groups like Marketing or Management to simplify administration.
Use report folders to control security.
Just as it makes sense to use Windows groups instead of user accounts, you’ll gain a similar advantage by managing security at the folder level. Assemble your intelligence into logical groups, place them in a report folder and then assign privileges to the folder rather than to individual intelligence. SSRS also allows you to cascade privileges to the subfolders so you can design a hierarchy of privileges in which higher privilege groups can view all folders, while other groups can view only intelligence closer to the root folder.
Use saved certification when configuring report data sources.
While using Windows Certification is often the most not compulsory choice, it doesn’t always work well in SSRS. If you configure a report to use Windows Certification to connect to a SQL Server database, 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 “dual-hop” certification is needed — one hop between the browser and SSRS and the other hop between SSRS and the database server. I had to troubleshoot this issue when a report was working while the user was using a browser on the SSRS server but stopped working when SSRS was accessed from another machine, resulting in dual-hop certification. Theoretically, dual-hop certification must work if you by the book configure the certification protocol Kerberos on the network, but I haven’t seen much success in that area. You are surpass off configuring a data source to use a SQL Certification login, or specify a Windows tab that must be used to connect to SQL Server.
Back up the SSRS back-end databases.
SSRS uses ReportServer and ReportServerTempDB databases, and you must back those up to a place other than the SQL Server machine they run on. You will need them if your server dies and you need to re-initiation the SSRS environment; otherwise you will have to redeploy all your intelligence and redo all configurations. I’ve seen companies making backups to a local drive, but if you lose the total machine, those will do you no excellent.
Practice SSRS migration to another server.
Migrating SSRS to another server is relatively simple: Back up the ReportServer and ReportServerTempDB databases and the encryption keys. Next, restore them on another SQL Server and configure the new SSRS server to use them. Once you restore the encryption key, your new SSRS environment must be identical. This is a excellent exercise, because if your SSRS server ever dies, you will be able to result in a new server online much quicker.
Keep all intelligence under source control.
Very often, a company has several public developing intelligence and deploying them to the server without having a central place to store the files and keep them versioned. Developers are used to working with source control software such as SourceSafe or SVN, but business users are not used to them. Since they often build and deploy intelligence, they must use the same procedure and discipline to check new intelligence into a source control and check them out if they need to make modifications. Aside from having your intelligence in a central place, where they are versioned and backed up, you’ll find it much simpler to build a new SSRS environment, pulling the intelligence from source control as opposed to collecting the report definition files from the individuals who developed each report.
While the SSRS best practices in this article are intuitive and simple to apply, not every company has them in place. I highly recommend that you check your SSRS configuration and make the not compulsory configurations. In addition, remember to back up the keys, the databases and practice migrating to another server. After all that work, your SSRS administration will require less time, and you will be surpass set to deal with an unexpected migration to a new SSRS server.
Related posts:
You must be logged in to post a comment Login
