x

Replacing a reporting server

I need to replace our reporting server. The old server is SQL 2008 R2. The new server will be SQL 2012 and will have a different name. We use the classic Report Manager, not SharePoint. How do I get our reports on the new server? I doubt I can just restore a backup of our ReportServer database as it won't get any of the changes an in-place upgrade would make. I can easily re-deploy all the reports but the security on them has got so many manual tweaks. I have a query to list the security out but it would take me days to set it all up again by hand.

Any suggestions how I can get this done with the minimum of effort, please?

more ▼

asked Oct 29, 2012 at 09:04 AM in Default

avatar image

David Wimbush
10.7k 31 34 43

David, have you thought about scripting the permissions? This would assume that the SSRS Path will stay the same.

select C.UserName, D.RoleName, E.Path, E.Name from dbo.PolicyUserRole A inner join dbo.Policies B on A.PolicyID = B.PolicyID inner join dbo.Users C on A.UserID = C.UserID inner join dbo.Roles D on A.RoleID = D.RoleID inner join dbo.Catalog E on A.PolicyID = E.PolicyID order by C.UserName

http://stackoverflow.com/questions/6600480/ssrs-determine-report-permissions-via-reportserver-database-tables

In the past I have used the RSscripter to extract all user reports and migrate to the new server.

Oct 29, 2012 at 10:50 AM sp_lock
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

David, the simple backup and restore should work. Steps to do:

  1. Backup your encryption key on the old SSRS 2008R2 Server

  2. Bacup your ReportServer and ReportServerTemp dbs.

  3. Move them and restore on SSRS 2012 instance

  4. Start the Reporting Services Cofiguration manager and in the Database Tab choose Change Database - the RSCM will take care about upgrading the db to the 2012 version.

  5. In the RSCM go the the Encryption Keys tab and choose the Restore to restore the encryption key backed up in step 1.

After those speps you whould have fully running SSRS on the 2012 version with all your reports and everything properly set-up.

This works perfectly for me and migrated serveral SSRS instances in that way.

more ▼

answered Oct 29, 2012 at 11:11 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Thanks, Pavel. I'll give that a shot and report back.

Oct 29, 2012 at 11:14 AM David Wimbush

This worked except I got an error when browsing to the Report Manager:

The feature: "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported) (rsRPCError)

I Googled and found this page: http://msdn.microsoft.com/en-us/library/ms156421(v=sql.100).aspx.

From the community section at the bottom:

This error message is essentially saying that running Reporting Services with multiple encryption keys is not possible without upgrading to Reporting Services Enterprise edition. Fortunately the solution (from http://support.microsoft.com/?kbid=842425) is simple. Run

&

gt; rskeymgmt -l

to determine the guid of the source encryption key. then run
&

gt; rskeymgmt -r

to remove the source key. rskeymgmt -l listed 2 keys, one from the old server and one from the new. It wasn't clear which was the 'source' so I guessed at the old server. Problem solved. All the reports are there. Just had to change the Data Sources and figure out how to recreate the SQL Agent jobs for the subscriptions.
Oct 31, 2012 at 03:06 PM David Wimbush

I did the similar steps as above over the weekend and found that my Data Sources lost the connection string and had to re-add them. But restoring the DB files did work for me. :) I did, however, forget to backup the encryption key, and found myself having to delete from the new Reporting Server and this resolved that issue also.

Oct 31, 2012 at 03:49 PM Noonies

@David, if everything went ok, then SSRS Should take care about creating the agent jobs. SSRS periodically checks the subscriptions table and should re-create them.

The problem with Scale-out deployment normally should not happen. It looks like something didn't went perfectly and both old and new servers registered with the database. But as you have mentioned, you have found the solution :-)

Oct 31, 2012 at 04:57 PM Pavel Pawlowski

@noonies, the problem is that you have not backed up the encryption key, as you have mentioned.

The Data Sources content is encrypted and as you ahve to delete all the encrypted data, you have lost all your connetion strings. It is a good practice to automatically backup the encryption key, once the SSRS instance is set-up.

You will need the encryption key, even when restoring the db on the same server, because of disaster recovery for example.

Oct 31, 2012 at 05:00 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

I haven't done this step myself but I'd test out taking a project / solution and upgrading it to a new report server and see how I like the results. we don't have a hideous number of reports so upgrading each solution would be viable on a time required basis and would give good granularity to the process so each team can have their reports upgraded when its convenient.

more ▼

answered Oct 29, 2012 at 12:14 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x411
x102
x90

asked: Oct 29, 2012 at 09:04 AM

Seen: 1768 times

Last Updated: Oct 31, 2012 at 05:00 PM

Copyright 2018 Redgate Software. Privacy Policy