How to Configure SSRS 2008 R2

Introduction:

This post helps you understanding how to configure SSRS 2008 R2 service. Installation is nothing much difficult, we can just choose SSRS in features list while installing SQL Server 2008 R2.

After Installing SQL Server

MSSQL Service account should have full rights on all data and log drives
Exclude Data, Log, Tempdb, any Backup file paths, and the SQL Server Binaries folders from AntiVirus Scans.

Remove Builtin\Admins from sysadmin fixed server role.

Check the login MSSQL Service admin account has sysadmin permissions if not assign it.
Enable TCP/IP and change default port from 1433 and add the port number to firewall exception list
Enable remote connections
Set Model database to SIMPLE recovery
Set cost threshold of parallelism sp_configure option based on the anticipated load.

usemaster

sp_configure’show advanced options’, 1;

reconfigure;

sp_configure’cost threshold for parallelism’, 10;

reconfigure;

Alter database to allocate default data/log file size as below

Data and Log file sizes

Database Data Log

Tempdb 2097152KB 512000KB

Model 1048576KB 512000KB

ReportServerTempDB 3145728KB 1048576KB

/*** TempDB CAPS ***/

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 2097152KB )
GO

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’templog’, SIZE = 512000KB )
GO

Same way change the data and log file sizes for reporting temp db.

Set the selected isolation to off mode on reporting db and reporting temp db

/*** Report Server DB / Report Server Temp DB Isolation ***/
/*** Update with the proper database names ***/

USE MASTER

ALTERDATABASE<ReportServer$SQL2008>

SETALLOW_SNAPSHOT_ISOLATIONOFF

ALTERDATABASE<ReportServer$SQL2008>

SETREAD_COMMITTED_SNAPSHOTOFF

ALTERDATABASE<ReportServer$SQL2008TempDB>

SETALLOW_SNAPSHOT_ISOLATIONOFF

ALTERDATABASE<ReportServer$SQL2008TempDB>

SETREAD_COMMITTED_SNAPSHOTOFF

Configuring SSRS 2008 R2

On the successful installation of SQL Server 2008 R2
Once the SQL Server is installed we can go ahead and configure the SSRS.
Step1: Open SSRS configuration manager

SSRS 2008 R2 1

Step2: Select server name and instance name

SSRS 2008 R2 – 2

Step3: Once it is connected the screen will be appear like below

SSRS 2008 R2 – 3

Step4: Go to service account tab and give the service account details. SSRS service runs under the account specified

SSRS 2008 R2 – 4

Step5: When you click on apply it ask for backup location for encryption key. Specify a location and give a password
Note: Update the installation document with this password

SSRS 2008 R2 – 5

Step6: Select credential type as “Current User – Integrated Security” and click on Ok.

Step7: Check the results window and make sure that no errors occurred

Step8: Go to next tab Web Service URL and click on advanced

Step9: Click onRemove it removes the row for 8080 and click on add button to add a new port

Step10: Give the new TCP port 80 and click on ok

Step11: It removes the url with 8080 and reserves the url with 80. Check the results window and make sure that no errors occurred.

Step12: Go to the next tab “Database” and select change Database.

Step13: Select “Choose an existing report server database”

Step14: Select the database server and test the connection

Step15: Select report server database

Step16: The mode should be native.

Step17: Give the windows credentials

Step18: Click on next

Step19: Check the progress and make sure no errors and click on finish

Step20: Go to next tab Report Manager URL. Remove the port 8080 add new port 80 and click on ok.

Step21: Check the result window.

Step22: Fill the columns with smtp server details

Step23: Go to the next tab “Execution Account” and give the credentials and apply changes

Step24: Add the port number 80 to the firewall exceptions list
Step25: Check the successful configuration
http://localhost/Reports_SQL2008R2
http:// localhost /Reportserver_SQL2008R2
We can get these url’s from TABs “Report Manager URL” and “Web Service URL”

Web Service URL