How To Migrate Reporting Services From SQL Server 2005 To SQL Server 2012

SSRS Migration Process:

This is for migrating SSRS from SQL Server 2005 on Windows Server 2003 to SQL Server 2012 on Windows Server 2012 R2 Standard 64 bit in Native Mode

Install SQL Server 2012

Install the 2012 version of SQL Server on your new server

Install SQL Server Reporting Services on 2012

This can be done via the Wizard or command prompt, and can be done as part of the initial SQL Server Installation

NOTE: Report Manager and SSRS have removed duplicate features and are no longer interchangeable. If you are looking for functionality in one that appears to be missing, try checking the other.

NOTE: From 2008 forward isapi filters, client certs,  and IP address restrictions are not supported and reporting solutions which rely on this must be redesigned prior to upgrade.

NOTE: IIS is not required to set up SSRS from 2008 forward.

Backup Report Server encryption key

Without the Key, encrypted data will not be accessible on the new server.

1. Reporting Services Configuration Tool
   1. start > all programs > microsoft sql server 2005 > configuration tools > Reporting Services configuration manager
   2. In the left column, click on Encryption Keys
   3. Click on the Backup button in the right pane
   4. Set an easy to remember password and select a save location for all backup files in the popup window – I prefer a folder on the c drive named appropriately
   5. The task should complete without errors

Backup DB, application, config files

Config files are located in the installation directory – backup or make a copy to a safe location. Backup of .config files is less important if doing a side by side migration, but you may want to transfer to the new server for reference purposes.

EX: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services

SharePoint has a different path:

EX: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

http://technet.microsoft.com/en-us/library/ms155866.aspx 

1. Config Files
   1. Machine.config for ASP.NET (if modified for report server operations) NOTE: .NET 3.0 and v3.5 are layered on top of 2.0, and use the 2.0 machine.config
      • \Windows\Microsoft.Net\Framework\v2.0.xxx\config\
   2. Reportingservicesservice.exe.config
      • <path>/ReportServer/bin/reportingservicesservice.exe.config
   3. Rsmgrpolicy.config
      • <path>/ReportManager/rsmgrpolicy.config
   4. Rsreportserver.config NOTE: If not using Windows Integrated Authentication, <AuthenticationTypes> node must be updated – supported types are Kerberos, NTLM, Basic, Negotiate (digest, .net passport, and anonymous are not supported from 2008 forward)
      • <path>/ReportServer/rsreportserver.config
   5. Rssvrpolicy.config
      • <path>/ReportServer/rssvrpolicy.config
   6. Rswebapplication.config (obsolete, all apps read rsreportserver.config – do not need to migrate!)
   7. Web.config for Report Server
      • <path>/ReportServer/web.config
   8. web.config for Report Manager ASP.NET
      • <path>/ReportManager/web.config
   9. Custom CSS for reporting environment must be moved manually
2. Databases (2) – the two DB’s are interdependant and must both be moved NOTE: If creating ad hoc backups be sure to use the “with Copy_Only” option! This will avoid interrupting your differential backup plans. You must do this using t-sql query in 2005. http://technet.microsoft.com/en-us/library/ms156421
   1. Reports DB .bak – create a full SQL backup with Data and Logs
   2. ReportsTempDB .bak – create a full SQL backup with Data and Logs

Move ReportServer DB and app files to new 2012 installation

Put all your files somewhere on the new server, for example the c: drive. You will need enough space on the drives for your DB .bak files. Jobs, cached reports, subscriptions, temp report, user sessions, and snapshots are carried over. Schedules will be recreated on first restart of reporting services.

1. Make sure Reports.bak and ReportsTemp.bak are available on the 2012 server
2. Open SSRS and connect to new server instance
3. Instance > Databases > Right click on “ReportServer” DB
4. Tasks > restore > database
5. Click on the Device radio button
6. Click on the elipse button […]
7. In the popup > click on the “add” button
8. Navigate to the location you put your backup on the 2012 server NOTE: You can also use Restore and Move in a query to do the restore in “one” step.
9. select your .bak > click ok
10. Make Sure you are restoring TO the correct DB
11. Check backup information > Type should be full
12. Check Files location – you should have logs and data files restored to appropriate location for your environment (same as MOVE query action)
13. If the DB names are different, meaning the .mdf files are named differently, you will need to select the With Replace option
14. Make sure Options > Recovery State is set to RestoreWithRecovery > ok
15. You should get a notice confirming restore 
16. Do the same for ReportsTemp DB
17. Add NT SERVICE\ReportServer to both DB > security > User settings

NOTE: You can set Recovery State to NoRecovery which keeps the database in a RESTORING state, this lets you review log backups to make sure you have the right one – if you are restoring from a full .bak including logs, you should not need this. If you do use it, remember to perform a second backup WithRecovery.

NOTE: While you can do all of this with a Query, it is not necessary.

NOTE: If you get an error stating “exclusive access could not be obtained because the database is in use”, you should kill processes using DB – in this case Reporting Services – stop and restart after restore is complete.

 

Move & Update Custom Assemblies & Extensions

2005 custom authentication must be recompiled

html 3.2 and html owc not supported from 2008 forward

The new location of bin folders is:

• \Program files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin
• \Program files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\bin

Modify the config files and add entries as needed

Configure Report Server

On the new 2012 server, run wizard or edit via Reporting Services Configuration Manager. You may want to keep settings the same or use the opportunity to update for security or other reasons.

ServerName\MSSQLSERVER

Make sure the Report Service is started.

Service account

Usually this will be the built in ReportServer account.

Web Service URL

test via RSCM > Web Service URL > Report Server Web Service URLs

Database

Make sure you are connected to the database, usually using the ReportServer account

Report Manager URL

Also test RSCM > Report Manager URL > Report Manager Site Identification

Email Settings

Set this appropriately to your environment – you may want to contact your exchange guru.

Execution Account

Should not be ReportServer – use something with minimal permissions to perform read operations only.

Give Account RSExec (and public) on the following DBs:

• ReportServer
• ReportServerTempDB
• Master
• MSDB

NOTE: if you get the error message

The report server installation is not initialized. (rsReportServerNotActivated)

Go to your report server catalog database, open the Keys table, and delete the row where your old computer name is specified. The restart the server.

Encryption Keys

Restore your encryption key

Encryption keys > Restore > navigate and select key > you should see confirmation

Scale-out Deployment

You may need to remove old server listing here.

However, you may get the error below.

ERROR:

Microsoft.ReportingServices.WmiProvider.WMIProviderException: No report servers were found. —> System.Management.ManagementException: Invalid namespace
   at System.Management.ManagementException.ThrowWithExtendedInfo(ManagementStatus errorCode)
   at System.Management.ManagementScope.InitializeGuts(Object o)
   at System.Management.ManagementScope.Initialize()
   at System.Management.ManagementScope.Connect()
   at ReportServicesConfigUI.WMIProvider.RSInstances.GetInstances(String machineName)
   — End of inner exception stack trace —
   at ReportServicesConfigUI.WMIProvider.RSInstances.GetInstances(String machineName)
   at ReportServicesConfigUI.WMIProvider.RSInstances.GetInstance(String machineName, String instanceName)
   at ReportServicesConfigUI.Panels.ClusterManagementPanel.ConfigureWebFarm(Object sender, RSReportServerInfo[] rsInfos)

Solution:

ReportServer DB > Tables > Keys > Delete row containing old db name

NOTE: You may need to re-restore the encryption key again, as this appears to be set with each row.

Accessing the Report Manager Url should now work:

Edit RSReportServer.config

If the original was modified, change 2012 version to reflect changes in original 2005 version as needed.

Configure Access Control Lists (ACL)

If needed, configure the ACL for the Reporting Services service group.

Testing Phase

Test! Make sure you have the real end users do some testing at this point and sign off on the upgrade as successful!

Verify Reporting

this is different for SharePoint mode or Native mode, as are many things, I am only addressing Native Mode here.

1. Check Report Manager URL Ex: http://server:80/reportserver
2. Check Web Service URL Ex: http://server:80/reportserver
3. Make sure Report Service is running Administrative Tools > Services
4. Run a Report from the /reportserver address (web service)

Review Reports

They will be automatically updated the first time they are processed, if it cannot be updated, it is processed in backward compatibility mode appropriate for the report – new errors may appear which may or may not be a new issue or just better error warnings

Error: User Cannot Access Reports Website

User ‘username’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

Solution:

The permissions need to be set up on the new server for SSRS access. You may want to use this as an opportunity to clean up permissions or may just match what is on the old SSRS site.

1. Reports site > site settings
2. Security tab > Click on New Role Assignment
3. Type in group or username and select Role (admin or user) > ok
4. Home > folder settings > Security > New Role Assignment
5. Set appropriate permissions for user > ok

Decommission & Cleanup

Remove/decommission apps/servers/tools when new instance is operational – I recommend you keep an old server around for 3-6 months inactive if you have the capacity (vm or physical).

Possible Issues

Firewall – make sure the port is open

Encryption – make sure you export and restore the key from old to new server