January 13, 2017 at 9:10 am
Hi
My company currently runs SSRS 2008 in production, and we've stood up an SSRS 2016 server and are in the process of loading all of our reports into SQL Server Data Tools 2015 projects to upgrade the RDL verisons and then will deploy the upgraded reports to the new report server. That is all going well and seems pretty simple and error-free.
In order to cut over in production from the 2008 RS to the 2016 RS, we'll need to get the existing data driven subscriptions and schedules on our currentl 2008 server to exist on the new 2016 version. I have been searching around but can't find an article on how to do this (only how to migrate from one server to another, not specifically from an old version to a new version). The schema of the DB tables for the Schedule and Subscriptions tables between 2008 and 2016 seems to be identical (I haven't used a compare tool yet, just eyeballed it). So we might be able to simply copy the data from the old system to the new one (accounting for any changes in report locations / datasource names / datasource locations, etc).
Has anyone here done this before / have any suggestions or things to look out for?
Thanks!
January 13, 2017 at 9:25 am
agerard 65681 - Friday, January 13, 2017 9:10 AMHiMy company currently runs SSRS 2008 in production, and we've stood up an SSRS 2016 server and are in the process of loading all of our reports into SQL Server Data Tools 2015 projects to upgrade the RDL verisons and then will deploy the upgraded reports to the new report server. That is all going well and seems pretty simple and error-free.
In order to cut over in production from the 2008 RS to the 2016 RS, we'll need to get the existing data driven subscriptions and schedules on our currentl 2008 server to exist on the new 2016 version. I have been searching around but can't find an article on how to do this (only how to migrate from one server to another, not specifically from an old version to a new version). The schema of the DB tables for the Schedule and Subscriptions tables between 2008 and 2016 seems to be identical (I haven't used a compare tool yet, just eyeballed it). So we might be able to simply copy the data from the old system to the new one (accounting for any changes in report locations / datasource names / datasource locations, etc).
Has anyone here done this before / have any suggestions or things to look out for?
Thanks!
Are you simply backing up and restoring the ReportServer database on the new Server, or are you re-deploying all the reports manually?
I know for an older 2008R2 installation that i migrated from one server to another, which was nothing more than grabbing the encryption key for the service, and restoring the database over the top of a newly created default ReportServer db, once the SSRS service started, any missing SQL Agent Jobs that were related to any subscriptions in the SSRS database were automatically recreated, and I'd expect an upgrade to do exactly the same thing.
I don't think you'd need to actually script them out, just to put them back, if you go the backup and restore route
Lowell
January 13, 2017 at 9:49 am
Lowell - Friday, January 13, 2017 9:25 AMAre you simply backing up and restoring the ReportServer database on the new Server, or are you re-deploying all the reports manually?
I know for an older 2008R2 installation that i migrated from one server to another, which was nothing more than grabbing the encryption key for the service, and restoring the database over the top of a newly created default ReportServer db, once the SSRS service started, any missing SQL Agent Jobs that were related to any subscriptions in the SSRS database were automatically recreated, and I'd expect an upgrade to do exactly the same thing.I don't think you'd need to actually script them out, just to put them back, if you go the backup and restore route
I wasn't planning on restoring a copy of the DB (not for any reason other than I didn't realize that was a possibility). Are there really zero database changes between 2008 (we're not using R2) and 2016?
My plan was to upgrade the RDLs by importing them into a SQL Server Data Tools 2015 project and then deploying them to the new report server. This process, while technically "manual", can be done for many reports at once, so it's really not a deal breaker for us. The reason I want all of the RDLs upgraded is so that we can stop using VS2008, which doesn't play nicely with TFS 2015 (which we recently upgraded to from 2013 so we could move our TFS back-end off of what is now our development server which was running SQL 2014 on to a new SQL server which is running SQL 2016). So if we simply restore the Report Server DB from the old server on to the new one (seriously, the DB schema is totally identical between RS 2008 and RS 2016?!?), it wouldn't upgrade the RDLs and we'd have to go through that process anyway. Not to mention we want to completely reorganize the folder structure for where data sources are stored and what they are named. seems easier to do that from the ground up rather than to import the current structure and need to make dozens of changes...
January 23, 2017 at 9:09 am
Since the new server is not yet in production, I tried the backup/restore encryption key / reportserver / reportserverTempDB from the old 2008 server onto the new 2016 server. It worked, but it's not working...
All reports, datasources, subscriptions, and schedules were copied over. The reports themselves work. However, the schedules and subscriptions seems to be corrupted in some way. The SQL Agent jobs were not recreated. When I go to edit the schedules on a subscription to make it fire to check if it will work, I get the "Loading..." screen when I hit apply and it hangs out there forever.
I see this error being logged massively in the Portal.WebHost and ReportServer logs:
Microsoft.ReportingServices.Portal.WebHost!library!8!01/23/2017-11:04:25:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.;
It's written over 2 GB of these errors in the last 30 minutes. Asking uncle Google now for where this is coming from and how I might go about fixing it.
January 23, 2017 at 9:21 am
Perhaps related to problems trying to create SQL Agent jobs? In the log just before it starts mass logging that error, I see this:
schedule!WindowsService_0!1a3c!01/23/2017-11:17:43:: i INFO: Schedule (126052f1-af5e-4ce3-bacf-000bd07b68f1) not present, being added.
Then it wrote 20 MB of that same error in literally 30 seconds.
January 23, 2017 at 9:26 am
January 23, 2017 at 9:39 am
The workaround listed in the web link posted above works (but for some reason underscores were stripped out, i had to add them to get the script to work).
DECLARE @ownerLogin VARCHAR(100)
DECLARE @scheduleId VARCHAR(36)
SET @ownerLogin=N'<your SSRS service account>'
DECLARE JobCreateCursor CURSOR FAST_FORWARD READ_ONLY
FOR SELECT CAST(ScheduleID AS VARCHAR(36)) AS ScheduleId FROM schedule
OPEN JobCreateCursor
FETCH NEXT FROM JobCreateCursor INTO @scheduleId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_add_job @job_name = @scheduleId,@owner_login_name=@ownerLogin
FETCH NEXT FROM JobCreateCursor INTO @scheduleId
END
CLOSE JobCreateCursor
DEALLOCATE JobCreateCursor
January 23, 2017 at 9:40 am
agerard 65681 - Monday, January 23, 2017 9:26 AM
This does discuss how to resolve the problem, did it work for you?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 23, 2017 at 10:56 am
Thom A - Monday, January 23, 2017 9:40 AMagerard 65681 - Monday, January 23, 2017 9:26 AMThis does discuss how to resolve the problem, did it work for you?
yup. thanks.
February 9, 2017 at 6:53 am
I read through the comments on this thread and hopefully haven't missed anything. I don't think you can restore an SSRS database up to a newer version. This is what I would do:
First, leave production alone.
Restore a copy of production (assuming you have your servers virtualized).
Upgrade that copy to SQL Server 2016. If you can't use this box as your new production server then go ahead and do the backup and restore (2016 to 2016). Make sure your versions are exactly the same.
February 9, 2017 at 7:08 am
I was able to restore the 2008 RS database onto the 2016 server. After dealing with the initial issues getting the RS back up (covered above) it's working fine.
Thanks!
February 9, 2017 at 7:28 am
That is interesting to know!
Even though it worked for you, you may want to review Microsoft's documentation on upgrades (for SSRS) - both in-place and migration.
Hope you have a good week!
February 9, 2017 at 8:18 am
Yup, I did. I always like to bounce the idea off of some experienced DBAs here as well just to make sure my bases are covered.
Thanks!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply