Reporting Service TempDB Partitioning during a Scale-Out

13 Nov
November 13, 2011

When scaling-out your SQL Server Reporting Service, it is essential to consider how your I/O will behave and how you can better utilize your disk configuration for optimum read and write.

One strategy is to partition the Reporting Service TempDB, which can be split onto multiple disks to better take advantage of the underlying SAN or a RAID configuration, and improve request parallelism, especially important when considering a large multi-user deployment of Reporting Service.

What we are actually doing is assigning more than one physical file to the ReportServer$TempDB‘s PRIMARY, which allows us to partition TempDB’s main FileGroup into independently stored files.

This can be accomplished by the following SQL Server query (Warning! this will actually drop the SSRS TempDB database and all custom objects within the db).

DROP DATABASE ReportServer$<Instance-Name>TempDB

CREATE DATABASE ReportServer$<Instance-Name>TempDB  
( NAME = ReportServer<Instance-Name>TempDB1,
   FILENAME = 'C:\ReportServer<Instance-Name>TempDB1.mdf',
   SIZE = 2GB,
   FILEGROWTH = 250),
( NAME = ReportServerTemp<Instance-Name>DB2,
   FILENAME = 'C:\ReportServer<Instance-Name>TempDB2.ndf',
   SIZE = 2GB,
   FILEGROWTH = 250),
. . .
( NAME = ReportServer<Instance-Name>TempDB10,
   FILENAME = 'C:\ReportServerTemp<Instance-Name>DB10.ndf',
   SIZE = 2GB,
   FILEGROWTH = 250)
( NAME = ReportServer<Instance-Name>TempDBLog,
   FILENAME = 'L:\ReportServer<Instance-Name>TempDB_Log.ldf',
   SIZE = 2GB,
   FILEGROWTH = 250)  


You will need to adjust the initial TempDB partition sizes as well as file growth strategy as per the expected load. It is highly recommended to monitor the new partitioned deployment by setting up counters and data collectors.

Since we dropped the ReportServer$TempDB database while rebuilding the PRIMARY FileGroup into 10 partitions, we need to setup the SSRS security role on the database again, this can be accomplished by the SQL Server statement below.

USE ReportServer$<Instance-Name>TempDB
EXEC sp_addrole 'RSExecRole'
          0 votes
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>