I first heard about SQL Server Report Service (SSRS) when it was announced as an add on to SQL 2000. If my memory is correct, it was originally presented as a beta product, something to be added in future releases. But the clamor for it was so great that Microsoft released it as a separate product. When SQL 2005 was released SSRS was part of the installation, along with Analysis Service (SSAS) and Integration Service (SSIS).
I started playing with the original beta, mainly just writing and publishing simple reports to see how easy it was to work with. My company started using SSRS with the SQL 2005 release for a few new reports we were developing. Later we upgraded to SQL 2008 as a replacement for the reporting engine we were using at the time (don’t laugh, it was MS Access 97).
But there was a learning curve. It wasn’t just a matter of installing the service and publishing reports. I had to learn how to troubleshoot the server, not just from a SQL standpoint. I had to find out how to identify slow running reports, why they were running so slowly, and how to optimize the server to run smoothly. In this series I would like to share what I’ve learned along the way. I’ve written about some of the same topics a few years back, but this time I want to tie them all together and cover some things I skipped over earlier. I hope you find these posts helpful. And please, feel free to add your own experiences in the comments section.
This first post covers installing SSRS. It’s a basic task you’re probably already familiar with, but not everyone is. I should also mention that I’m only going to talk about the native configuration, saving the SharePoint side of things for another day. I also won’t discuss setting up a server farm here, again that will be covered later.
Setting Up The Scenario
You’re the DBA at your company, and you’re in charge of keeping the production SQL servers humming along. You know how to maintain those servers; run DBCC CHECKDB on a regular basis, backup the databases, check for bottlenecks that affect performance, help teammates tune their queries, and any other tasks that come up. One day your boss calls you in for a talk.
“DBA, ” your boss says, “our customers are complaining about their reports running slowly. Sometimes they can’t even connect when we know the server is running. I want you to see what’s happening on those report servers. They’re just another SQL instance after all, so you’re just the person to check it out and fix it. Oh, and we need you to set up another SSRS instance. It’ll come in handy later.”
“I’m on it. Should be a piece of cake.” you reply, and you head back to your desk to get started on installing the new sever.
For this installation I’m going to use the developer edition of SQL 2014 but SSRS is not an enterprise edition only feature, you can also use standard. The process isn’t much different for SQL 2008 R2 or SQL 2012, the Reporting for SharePoint feature was a separate install.
The server you’re installing on should meet the same hardware and software requirements as the SQL edition you’re planning on using. You will need a SQL instance to install SSRS on as the service will require 2 databases. You can add SSRS as a feature to an existing instance or choose database engine. I’m going to choose new instance.
Next, choose the features you want for your new instance. You will need to include the database engine as SSRS will create two new databases. For our new server I’m also going choose Reporting Services – Native and Management Tools.
Continue with your install and you’ll finally get to the Reporting Services Configuration screen. You’ll see two choices under Native Mode: Install and Configure and Install Only. The difference between these options is that the Install and Configure will create the two databases and SSRS with all the default settings. Whichever option you choose you should manually configure the service later. I’ll have much more on configuration in my next post.
We’re didn’t choose to integrate with SharePoint so that option isn’t available. For this instance we’ll choose Install and Configure and complete the SQL instance installation.
And that’s just about all that needed to get an SSRS instance up and running quickly. If you open Management Studio and connect to your new SQL instance you’ll see the two new databases SSRS will be using, ReportServer and ReportServerTempDB. Both will be covered in detail in a separate post. For now, briefly, the ReportServer database is where the reports live and the ReportServerTempDB stores session information for SSRS.
Next week I’ll discuss SSRS configuration.