Skip to content

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

by on April 2, 2014

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 Modeimage

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 paneimage
    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 errorsimage

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 […]image
  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 okimage
  10. Make Sure you are restoring TO the correct DBimage
  11. Check backup information > Type should be fullimage
  12. Check Files location – you should have logs and data files restored to appropriate location for your environment (same as MOVE query action)image
  13. If the DB names are different, meaning the .mdf files are named differently, you will need to select the With Replace option image
  14. Make sure Options > Recovery State is set to RestoreWithRecovery > okimage
  15. You should get a notice confirming restoreimage 
  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.image

 

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

image

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

image

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

image

Scale-out Deployment

You may need to remove old server listing here.

image

However, you may get the error below.

ERROR:

image

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 nameimage

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:

image

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/reportserverimage
  2. Check Web Service URL Ex: http://server:80/reportserverimage
  3. Make sure Report Service is running Administrative Tools > Servicesimage
  4. Run a Report from the /reportserver address (web service) image

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 Assignmentimage
  3. Type in group or username and select Role (admin or user) > okimage
  4. Home > folder settings > Security > New Role Assignment
  5. Set appropriate permissions for user > okimage

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

Advertisements
6 Comments
  1. Emerson Bruce permalink

    The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is ”. The expected version is ‘162’. (rsInvalidReportServerDatabase)

  2. Emerson Bruce permalink

    Reporting Services Configuration Manager > Database > Change Database > select restored DB > Click OK > SQL will upgrade the DB

    NOTE: Run through the configuration manager to reapply settings to the Reporting DB’s

  3. Reblogged this on go2analytics and commented:
    A Traditional Way to Migrate SSRS from SQL 2005 to SQL 2012

  4. Nagaraju permalink

    we followed the same steps..still we are getting same issue.
    User ‘username’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
    Please provide me the alternate solution for this.

    • erm – try going to the server and running IE as Admin – you have UAC (User Access Control) enabled! ^_^
      Good Luck!

Trackbacks & Pingbacks

  1. Reporting Services – Error – This configuration section cannot be used at this path. This happens when the site administrator has locked access to this section using from an inherited configuration file | Daniel Adeniji's - Learning in th

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: