carloscastilla - Fotolia

Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Scaling out or scaling up with Microsoft SSRS

Basit Farooq explains the pros and cons of scaling out and scaling up when increasing the workload on the SQL Server Reporting Services (SSRS).

A scalable system is one that, with the incremental addition of more system resources, can support an increasing workload without needing to have radical changes to the system design or architecture. One potentially scalable system is SQL Server Reporting Services (SSRS), a server-based reporting platform created by Microsoft for SQL Server. It includes tools to help create, manage and deploy reports.

There are several options for scaling the Microsoft SSRS environment. For example, you could add more servers to the existing reporting platform. You could also try scaling out by increasing system resources, such as processors, memory and disk space on existing servers.

Remote report server catalog databases

The SSRS setup allows you to choose which instance of SQL Server -- local or remote -- to use to host the report server catalog databases. You need to decide which configuration of SSRS suits your needs and resources.

The least expensive option, in terms of licensing, is to install all of Microsoft SSRS onto the same server. However, this would cause the report server catalog database and the ASP.NET report server application to compete for the same resources.

If you install the report server catalog databases on a separate server, this eliminates any competition for resources and gives you the resources of an additional server, but involves extra software and hardware costs. However, if you can put report server catalog databases on a pre-existing dedicated server running, the configuration will not incur additional costs for software licenses. You will also benefit from establishing a system architecture that can continue to scale out in the future without needing any fundamental changes. However, installing the report server catalog databases on a separate server increases network traffic, because the queries against the report server catalog database are made over the network.

Carefully consider the costs and benefits of installing the report server catalog databases on a separate server to decide whether separating the report server catalog databases is justified as a starting point for further scaling out your SSRS environment. Provided that you have the network bandwidth and can justify the extra hardware and software costs, you should consider using a remote report data server as a first step to scaling your SSRS environment.

Scale out or scale up?

The first step to take after deciding to scale SSRS is to decide if you want to scale out or scale up. When you scale out, you add a new server to the existing infrastructure to increase resources. When you scale up, you add new resources, such as processors and memory, to existing servers. Each option comes with its own costs and benefits.  

When scaling out SSRS, you add new servers that are exact copies of the existing servers. These cooperate to share the processing for a particular part of the system. You can scale out your existing SSRS deployment by adding extra report server instances, each of them running on a separate server, but sharing the same report server catalog databases. Using a scale out deployment for SSRS, you can increase the number of users who can access reports at the same time and improve the availability of the report server.

The choices you make when scaling effect more than workload capability of the server. There are also indirect costs and benefits. For instance, scaling out by adding a new server causes less down time than a server upgrade; since you can prepare a new sever before you add it to your system. Scaling out also offers better availability because, if one server fails, the overall system still continues to operate. It also adds more memory to your system without the requirement for 64-bit architecture that comes with scaling up. ASP.NET applications, such as SSRS, are specifically tuned to take advantage of a scale out configuration.

But, scaling out can be expensive enough to make scaling up seem advantageous. Scaling out can be more expensive than scaling up. With more servers come increased data center operational costs to power and cool the added servers. Also, scaling, with its larger number of servers, requires more potentially costly software licenses. You also need to take into account the need for more database administrators, network administrators and storage administrators to handle the additional servers, all of whom need salaries.

Scaling up your SQL Server Reporting Services environment does not incur additional costs that come with more servers and you can still get performance benefits by adding processors and memory. However, the performance gain per unit cost will show diminishing returns towards the higher end as the hardware is proportionally more expensive.

Next Steps

See the best ways to prepare for your SQL Server scale out

Learn about the features of SQL Server Reporting Services in SQL Server 2014 from expert Brian Larson

Find out how SQL Server Reporting Services fits into SQL Server 2012's business intelligence features

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)