asked on

custom SSRS migration from one server to another

I am doing a custom SSRS migration from server A to server B, where A is SQL v2005 and B is v2012. I'd like to outline my steps and was hoping for EE input as to whether I am missing anything.

Server B is already built; I installed SQL Server and SSRS, and restored the ReportServer databases from Server A.
There are 1000+ SSRS Subscription Agent jobs on Server A that execute many different SSIS packages (previously dts, now dtsx).
I have migrated/upgraded all of these packages to Server B, and tested all of them successfully.
I have created two procs;
usp_delAgentJobs goes in and removes all SSRS-sub jobs and five non-SSRS jobs from server B.
usp_createAgentJobs collects all job definition from Server A and creates the jobs with jobsteps on server B

Both procs have been tested successfully several times. With the previously restored ReportServer database, the hope is that it can all be driven within these two procedures.

Basically, the customer isn't sure when they are going to move from serverA to serverB, and they want to be able to recreate the environment on serverB on demand, whenever they are ready to go. They may attempt the cutover several times, which is why they want to do it this way --- whenever they're ready, blow out everything on serverB and recreate it clean from serverA. The two procedures handle that.

As I said, this all started out with a restore of ReportServer from Server A to B. So all of my tables are in synch, ReportSchedule, Subscriptions, Catalog, etc. Thus, I believe all of my Agent job timed subscriptions will work, such as this:

EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='092954fd-d609-48d7-816f-509272a3fd6c'

The one thing I don't believe I've addressed yet is the report output location. All 1000+ reports write to a directory on the old server. The same reports on the new server need to write to a new directory, on the new server.

Would that not be in the report definition itself?

Is there anything else I've missed?

Russell Fox

The output location would be saved in the subscription, so if you used a share name in the original subscription ("\\someshare\somefolder") then you're okay, or if the local path is the same from server A to B (both A and B have the folder "c:\MyReports").

You may also need to backup/restore the encryption keys - I recently went through this process and it's pretty painless, but I'm not sure it can be scripted. Check out this how-to page which covers the entire migration process - it helped me out a lot.

The other change you may need to consider is that if people are actively using the report server they may have bookmarked report locations that will no longer work, i.e.:
http://ReportServerA/Reports/Pages/Report.aspx?ItemPath=TheNinjaReport.rdp
is now
http://ReportServerB/Reports/Pages/Report.aspx?ItemPath=TheNinjaReport.rdp

You can save this trouble in the future by creating a share name that points to whatever physical server is active at the time. Otherwise, I think your solution is sound. It's very handy, and kudos to Microsoft, that the subscription and schedule information is stored in tables and can easily move from one server to another. There may be some way to alter the output location by changing the information in those tables, but this is deep magic and just as likely to break everything as fix anything. Anti-kudos to Microsoft for such an obfuscated web of nonsense to store the subscription and schedule information, but they want you to buy the Enterprise edition so you have access to data-driven subscriptions.

HTH!

dbaSQL

ASKER

Russell, this is very helpful. If the output is stored in the subscription, then I will not see the actual path unless I open up the rdl in Report Mgr and view the subscription. Is that what you are saying?

Russell Fox

Yes, you would want to review all of the subscriptions on the current server to figure out where the output is pointed for each, but here's a handy query I stole from TechNet - the Description field shows the output location, "Send email to..." or "Save in \\servername...", etc.:

USE [ReportServer];  -- You may change the database name. 
GO 
 
SELECT USR.UserName AS SubscriptionOwner 
      ,SUB.ModifiedDate 
      ,SUB.[Description] 
      ,SUB.EventType 
      ,SUB.DeliveryExtension 
      ,SUB.LastStatus 
      ,SUB.LastRunTime 
      ,SCH.NextRunTime 
      ,SCH.Name AS ScheduleName       
      ,CAT.[Path] AS ReportPath 
      ,CAT.[Description] AS ReportDescription 
FROM dbo.Subscriptions AS SUB 
     INNER JOIN dbo.Users AS USR 
         ON SUB.OwnerID = USR.UserID 
     INNER JOIN dbo.[Catalog] AS CAT 
         ON SUB.Report_OID = CAT.ItemID 
     INNER JOIN dbo.ReportSchedule AS RS 
         ON SUB.Report_OID = RS.ReportID 
            AND SUB.SubscriptionID = RS.SubscriptionID 
     INNER JOIN dbo.Schedule AS SCH 
         ON RS.ScheduleID = SCH.ScheduleID 
ORDER BY USR.UserName 
        ,CAT.[Path];

Open in new window

dbaSQL

ASKER

Again, very helpful. I will check it out now and let you know if I have any further questions. Thanks!!

dbaSQL

ASKER

One further question, Russell. As I mentioned in the first post, there are 1000+ reports, and your handy query returns 1055 records, as expected. The ReportPath gives the same output for many reports, yet the description is different. For example, this report path is returned for one of the records:

/Parish Tracker/Parish Tracker CSV

And this is the Description:

Save in \\serverA\BuffaloCSFTP as Parish Tracker CSV

You can see the actual servername is in the description, yet it is not included in the ReportPath. I went through all of the records and the report path is the same for all of them. Share names. Be that the case, I just need to get the same share names onto the new server, pathed accordingly. Yes?

Also, I just attempted to go into the Report Mgr on ServerB, and fail to open it with this error:

An error has occurred during report processing. (rsProcessingAborted)
The report server is unable to access encrypted data. Apply a back-up key or delete all encrypted content. (rsEncryptedDataUnavailable)

Presumably this is the encryption key. I will restore it now and see if I can get into the Report Mgr.

Russell Fox

Answering backwards:

B: Yes, that's definitely the encryption key: backup on A and restore on B, however, it cannot be used on two servers at the same time (learned that the hard way), so A will be offline while you're testing B.

A: My guess is that "/Parish Tracker/Parish..." is pointing to the local server, probably even within the reporting system folders, but when I try to enter something like that on my system I get an error, "The path must conform to Uniform Naming Convention...". You might want to check the history and see if that subscription is even running. Otherwise, yes, just get them all pointed at a UNC path that will be valid on both A and B and you should be good to go! I know that's a bit daunting with 1055 subscriptions.

Note that if you do have the Enterprise edition of SQL Server (not Standard or Express, etc.), you can use data-driven subscriptions instead, where you can configure output paths in a database instead of in the subscription screen. SO MUCH EASIER but so much more expensive. HTH!

dbaSQL

ASKER

B. Unfortunately, the responsible party cannot tell me the password for the encryption key backup, and I cannot create a new one without it. I wonder if I delete the encrypted content (in Reporting Services Config Mgr), and then perform another backup/restore, I should be able to get around this. Don't you think?

A. All subs are running on the old server, SERVERA. If I go into the Report Manager for the Parish Tracker report, and then into Subscriptions, this is the path:
\\serverA\BuffaloCSFTP

You can see in my previous post, that corresponds to the report description, but not the actual path, which the handy query outputs like this: /Parish Tracker/Parish Tracker CSV

Russell Fox

You can re-create the encryption keys with a new password, or delete the encrypted content: MSDN

The [ReportPath] field is just a pointer to the report itself - nothing to do with the subscriptions. That report is being saved as a CSV file in the folder "\\serverA\BuffaloCSFTP". That's not great because it's saving locally (\\serverA...) and the UNC will break unless the old server is taken offline and either the new server is renamed "serverA" or you set up a share name so requests for "serverA" to go some folder on serverB, like:

"serverA" = "\\serverB\ReportOutput"

dbaSQL

ASKER

>>The [ReportPath] field is just a pointer to the report itself - nothing to do with the subscriptions.
You said the output path is saved in the subscription, so I was interpreting the output from the path in the handy query as exactly that.

>>and the UNC will break unless the old server is taken offline
the cutover from old to new is going to be staged. they will want to run the process I am creating to get serverB up to speed and test it, and it will very likely occur multiple times before serverA is decommissioned.

If the report path is NOT in the ReportServer tables, then I need only to ensure the reports all exist locally, and can be accessed (without the encrypted key problem), and the same shares and directory structure exist on serverB. If the UNC is not ideal, but IS there a way to handle this programmatically, without having to open every 1055 reports?

ASKER CERTIFIED SOLUTION

Russell Fox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dbaSQL

ASKER

>>Another option would be to set up the new server from scratch,
That is pretty much exactly what I did. SERVERB was built from the ground up, SSIS packages and SSRS reports upgraded/migrated to new server, ReportServer db was restored, and all Agent/Subscriptions were created with the procedures I've coded. All guids for all reports exist in SERVERB just as they do in SERVERA.

I missed the encryption keys, but I will take care of that, and I think the only piece left is the output path. With the CAST below, I am returning only the output paths as they exist, for every report:

SELECT
CAST(s.ExtensionSettings AS XML)
.value('(/ParameterValues/ParameterValue[Name="PATH"]/Value)[1]','varchar(50)')
FROM dbo.Subscriptions s

Here are some examples:

\\serverA\FTP_documents\StJohnBoston
\\serverA\FTP_documents\StTeresaAvila
\\serverA\FTP_documents\StGregory
\\serverA\FTP_documents\StPaul

At this point, couldn't this just be an update, replacing 'ServerA' with 'ServerB' ?

Russell Fox

I think so, but definitely test it out!

dbaSQL

ASKER

Testing as soon as I can, Russell. If you don't mind, I will keep this open until I can test it out. Will let you know.

Russell Fox

Yep, and try to keep track of any "gotchas" during the process that you can post here - might help someone else later.

dbaSQL

ASKER

Yep. Definitely will.

dbaSQL

ASKER

I have not completed this yet, but I don't want to keep it open any longer. Thank you for your input, Russell.
I have used the information you provided in this post to perform the manipultion below, and retrieve the path per report. I will come back here at a later time, and post the whole solution. It may be helpful to other SSRS admins.

SELECT
CAST(s.ExtensionSettings AS XML)
.value('(/ParameterValues/ParameterValue[Name="PATH"]/Value)[1]','varchar(50)')
FROM dbo.Subscriptions s