Migrating Reporting Services Subscriptions

Question

Migrating Reporting Services Subscriptions

siby.s.joseph

SSC Veteran

Points: 254
More actions
March 13, 2009 at 11:44 pm

#198013

Hey all,
We are currently in the process of upgrading our systems and migrating reporting services subscriptions also becomes a part of it.The current scenario is that we are moving from SQL Server 2005 to SQL Server 2008 and we will have to migrate the subscriptions which run into thousands .Is there a way or a tool that could enable us to do the same ?
All i could find out was the RS Scripter ,but i am not sure will that be effective while migrating it to 2008.Kindly give in ur suggestions on this ..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

brian.hodson Valued Member Points: 51 More actions · Answer 1

If you find a way to move subscriptions I'd love to hear about it. I'm doing a simular thing; consolidating a SQL RS 2000 and 2005 instance to a 2008 instance.

chad-1157832 SSC Journeyman Points: 81 More actions · Answer 2

Did anyone figure out a way to do this? My company is upgrading from 2005 to 2008 and I need to migrate our subscriptions.

Clifton Collins Default port Points: 1433 More actions · Answer 3

I would review this tool for your use: http://www.sqldbatips.com/showarticle.asp?ID=62

[font="Arial"]Clifton G. Collins III[/font]

sgalbra Mr or Mrs. 500 Points: 556 More actions · Answer 4

That link doesn't work any more, but ultimately you can find the download from here: http://sqlserverfinebuild.codeplex.com/wikipage?title=Install%20Reporting%20Services%20Scripter

I had an error when trying to run the exe file.

I did have some success with ReportSync. However, I wanted to preserve Subscriptions and Execution Log data. You can find it at this link: http://code.google.com/p/reportsync/[/url]

Microsoft has documented a way to change just the database location at this link: http://technet.microsoft.com/en-us/library/ms156421.aspx. However their instructions have errors in their code. I'm surprised they didn't test their code before they published it. I tried to add a suggestion/comment for them, but of course the form submission ended with an error.

In the end, the Microsoft's documentation didn't handle what I needed to do, which was to move SSRS to a new server as well as the database. I also wanted to change the database name. I could follow the Microsoft Technet instructions (with corrections to their code and changes to the database names and logical file names), and additionally restore the old production SSRS encryption key to the new SSRS server. However, this did not account for me wanting to change the database name.

Apparently the SSRS Tempdb database name is hard-coded into a bunch of objects. To fix this after migrating the database I had to use the "Generate Scripts" utility in SQL Server Management Studio and script all Procedures, Functions, and Views into one file (scripted for Drop and Create). Then I did a Search and Replace on all of the old TempDB names and replaced with the new name. After running that modified script, there also was one trigger that needed to be modified. It was called "Schedule_UpdateExpiration" on the "Schedule" table.

I am still testing this but so far it is good. If you need more information I'll try to respond, or if it is better to write a detailed article I may have time to do so. I am surprised that this process isn't already well-documented.

sgalbra Mr or Mrs. 500 Points: 556 More actions · Answer 5

One other thing, you may want to check the "Scale-Out Deployment" page in the Reporting Services Configuration Manager after doing this. I noticed that my old server was added into the list here, and I didn't want that. So I removed it.