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
go
sp_configure’show advanced options’, 1;
GO
reconfigure;
GO
sp_configure’cost threshold for parallelism’, 10;
GO
reconfigure;
GO
- 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
GO
ALTERDATABASE<ReportServer$SQL2008>
SETALLOW_SNAPSHOT_ISOLATIONOFF
GO
ALTERDATABASE<ReportServer$SQL2008>
SETREAD_COMMITTED_SNAPSHOTOFF
GO
ALTERDATABASE<ReportServer$SQL2008TempDB>
SETALLOW_SNAPSHOT_ISOLATIONOFF
GO
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
Report Manager URL
Step26: Assign appropriate roles to the users from Site Settings (Right upper corner)
