Microsoft has always been pretty good at one thing, ease on install.

One of the things I always says is the greatest thing about SQL Server is any idiot can install it. The other thing I also say is the worst thing about SQL Server is any idiot can install it.

This has carried over to the other fine products that ship with SQL Server. The one thing I’ve had pretty good luck using as a novice since it was introduced is SQL Server Reporting Services. As someone who hasn’t had the best experience over the years with crystal reports SSRS was a breath of fresh air.

One of the things I’ve never had to do though is move SSRS to a new server. To start with I did what I always do; I went to MSDN and read what I could find on moving or migrating to a new server. I was feeling pretty confident that I could pull this off.

I had backed up my databases and my key so I could import it on reinstall.

My first mistake was hubris that I could do what I thought was correct and second guess the documentation.

I installed SQL Server plus all the other services, including SSRS just like I was doing a clean install. The server didn’t start out with the same name and that was part of the problem. I copied all the files I needed from the old server and shut it down. Next we renamed the server and I renamed the SQL Server instance to its new name. Once that was done I restored the old SSRS databases and the encryption key. I fired up SSRS when to look at a report and got an odd error.

This version doesn’t support multiple instances.

My second mistake was assuming the documentation on MSDN was complete. After staring over and following the instructions there I ended up variations on the previous error, or the reports wouldn’t generate period.

What should have been a two hour migration was stretching into two days. I had to pull the trigger and bring the old hardware back on line.

Having a fall back plan saved us from having any impact on the business but it did set me back a week until the next available maintenance window was available.

I now had a whole week to research the issue and make sure I had a successful migration, so I got cracking.

I went to my buddy Mr. Google and asked him where the hell I messed up and if anyone else had come up against this. As I thought I wasn’t the first, but there was a twist it wasn’t just one thing it was a few things that I was bumping up against. Luckily between MSDN and some of the fine folks on the SQLServerCentral forums I was able to piece together a successful migration strategy.

The list of obstacles I had to overcome.

This was a named instance and the $ played havoc with the Reporting Services Configuration Manager.
This was a migration of existing data plus the security key to the server.
The server name was also being changed after install to reflect the old server name.

The instructions from MSDN tell you not to choose SSRS to be configured during the install with the default values.
This causes a key part not to be configured, IIS. This is a problem because without IIS configured you can’t view the reports!
This by its self isn’t an issue if you are working with a default instance of SQL Server. But, as I indicated, I’m dealing with a named instance.
After you have installed SQL Server and SSRS you are suppose to use the Reporting Services Configuration Manager to setup IIS. But, since the named instance has the $ in it, the Reporting Services Configuration Manager kicks back the virtual name with:

ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified is not valid. Make sure the specified name is not too long and doesn’t contain illegal characters. (example: 😉
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)

Very nice, if you let the installer configure the site at install time it gladly puts the $ in the name for you like http://<servername>/ReportServer$<instancename>.

I had puzzled out the IIS part and gleaned the rest from Tim Benninghoff and his post on SSC.
Also, the bits about restoring the encryption key came from MSDN, none of the other articles talk about a named instance at all and assume it is a default instance you are moving.

So, I followed these steps to restore the IIS folder structure and move my instance of SSRS to the new server.
Remember, please validate any changes you make to your server, and always your success isn’t guaranteed just because you followed these instructions.

On the original source server:

1. Back up the ReportServer database, the ReportServerTempDB database.
2. Back up the encryption key by using the Reporting Services configuration Manager. It will ask you for a password to backup the key DON’T FORGET IT!
3. Using the Internet Information Services Manger expand the Application Pools folder, right click on the ReportServer$<instancename> go to all tasks and Save Configuration File.
   I recommend giving it a .xml extension to make things easier on the restore side.
4. Go to the Web Sites folder and usually under the Default Web Site there will be two entries with gears next to them one named Reports$<instancename> and the other ReportServer$<instancename>.
   Right click on them and save the configuration file like you did with the application pool.

On your new destination server:

1. Stop IIS service by running the iisreset /stop command at the command prompt.
2. Stop Reporting Services using Reporting Services Configuration Manager click Server Status, and then click Stop on the Report Server Status page. If Stop is unavailable, the service has already been stopped.
3. Restore the backup databases from the source server.
4. Import the IIS application pool configuration to IIS on the new server. From Internet Information Manager, right click on your Application Pools folder and select New>Application Pool (from file)… Use the Browse button to locate your .xml file you should see the new pool in the list.
5. Import the IIS virtual directory configuration to IIS on the new server. From Internet Information Manager, right click on your Default Website and select New>Virtual Directory (from file)…
   Use the Browse button to locate your .xml files, and then click Read File Select the Virtual Directory name that appears in the white box and click the OK button.
   You should see your new Virtual Directory appear with the name Reports$<InstanceName> and ReportServer$<InstanceName>
6. Modify the following registry settings under HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.(instance#)Setup:
   RSVirtualRootApplication – Reports$<InstanceName>
   RSVirtualRootApplicationPath – IIS://(ServerName)/W3SVC/1/Root/Reports$<InstanceName>
   RSVirtualRootServer – ReportServer$<InstanceName>
   RSVirtualRootServerPath – IIS://(ServerName)/W3SVC/1/Root/ReportServer$<InstanceName>
   Also, you may need to add the $<InstanceName> to the following keys if they don’t already exist:
   ApplicationPoolConfigured_RM
   ApplicationPoolConfigured_RS
7. Modify the following .config files for Reporting Services:
   In C:Program FilesMicrosoft SQL ServerMSSQL.(instance#)Reporting ServicesReportManager, modify RSWebApplication.config so that the ReportServerVirtualDirectory node contains ReportServer$<InstanceName>
   In C:Program FilesMicrosoft SQL ServerMSSQL.(instance #)Reporting ServicesReportServer, modify rsreportserver.config so that the UrlRoot node contains http://(ServerName)/reportserver$<InstanceName>
8. In the Reporting Services Configuration Manager confirm the new paths.
9. Start Reporting Services from the Reporting Service Configuration Manager.
10. Start IIS service by running the iisreset /start command at the command prompt.
11. Remove the encryption key from the source server. I used the rskeymgmt utility to do this.
12. Examine the encryption keys that exist. Based on the type of the instance of SQL Server 2005 installed on the destination server, you type the following in the command prompt window.
    Default instance
    Type rskeymgmt -l, and then press Enter
    Named instance
    Type rskeymgmt -l -i InstName, and then press Enter
    You will see two encryption keys listed. One is from the source server, and the other is from the destination server. The format of the encryption keys listed is as follows:
    ComputerNameInstNameEncryptionKeyID
    Note ComputerName is the name of the destination server.InstName is the placeholder for the name of the instance of SQL Server 2005 installed on the destination server. EncryptionKeyID is the placeholder for the ID of the encryption key.
13. Delete the encryption key from the source server. Based on the type of the instance of SQL Server 2005 installed on the computer, you type the following in the command prompt window.
    Default instance
    Type rskeymgmt -r SourceEncryptionKeyID, and then press Enter
    Named instance
    Type rskeymgmt -i InstName -r SourceEncryptionKeyID, and then press Enter
    Note SourceEncryptionKeyID is the placeholder for the ID of the encryption key from the source server that is obtained in step 2.
14. Repeat step 2. You will see that only the encryption key of the destination server exists now.
15. Restore the backup encryption key from the source server.
    In the Reporting Services Configuration Manager, click Encryption Keys, and then click Restore in the Encryption Key page.
    In the Encryption Key Information window, type the password you use to back up the encryption key from the source server, locate the backup encryption key from the source server, and then click OK.
16. Make sure that the same user account as the ASP.NET service account is set on the Rsreportserver.config file. To do this, follow these steps:
    In the Reporting Services Configuration Manager, click Web Service Identity. Notice the ASP.NET Service Account information.
    Open the SQLInstall:Program FilesMicrosoft SQL ServerMSSQL.XReporting ServicesReportServer folder. Right-click the Rsreportserver.config file, and then click Properties.
    Note SQLInstall is the placeholder for the drive where SQL Server 2005 is installed. MSSQL.X is the placeholder for the Instance ID of the instance of SQL Server 2005 Report Services that is running on the destination server. To obtain the Instance ID, click Server Status in the Reporting Services Configuration Manager, and then notice the Instance ID information. Usually it’s ID 3 if you are running multiple instances or ID 2 if not.
    In the Rsreportserver properties dialog box, click the Security tab.
    if the ASP.NET service account is not listed, add it. Make sure that the ASP.NET service account has Read and Read & Execute permissions.
    Click OK to close the Rsreportserver properties window.
17. If you did not have an instance of SSRS already configured then you will need to execute the following script to add the RSExecRole and the appropriate permissions.
    --create RSExecRole role in master and assign permissions
USE [master]
GO
CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]
GO 
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole 
GO 
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole 
GO 
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole 
GO 
--create RSExecRole role in msdb and assign permissions
USE [msdb]
GO
CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]
GO
-- Permissions for SQL Agent SP's 
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole 
GO 
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole 
GO 
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole 
GO 
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole 
GO 
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole 
GO 
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole 
GO 
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole 
GO 
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole 
GO 
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole 
GO 
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole 
GO 
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole 
GO 
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole 
GO 
-- SQL Server 2005 requires that the user is in the SQLAgentOperatorRole 
IF EXISTS (SELECT * FROM sysusers WHERE issqlrole = 1 AND name = N'SQLAgentOperatorRole') 
BEGIN 
    EXEC msdb.dbo.sp_addrolemember N'SQLAgentOperatorRole', N'RSExecRole' 
END 
GO

Once all that is done, if you had an instance already configured you may need to remove the virtual folders from the default website.

I also restarted everything again and tested it all out.

Good luck and I hope you find this useful; trying to glean all of this from different resources and separate what was really going on by the cryptic messages SSRS pumps out can be difficult in the best of times!