Migrating SSRS to 2016 - Subscriptions and Eliminating Errors in the Error Log

Migrating from SQL Reporting Services 2012 to 2016 is a fairly straightforward process that doesn't require a lot of attention here. What I do want to talk about is SSRS subscriptions. When you migrate an SSRS instance from one server to another, unless you also restore msdb those subscription jobs won't be coming with you. The subscriptions still exist in your ReportServer database and because the subscriptions are still in the ReportServer database and not in msdb, SSRS starts recording errors to the SSRS error logs to the tune of around 32MB a minute worth of logs. This happens because SSRS is looking for the Agent job for a subscription in the ReportServer database but it's not there so it logs an error. And it will keep checking over and over and over (Microsoft will likely change this behavior, we can hope). So how do we keep our logs in check and fix our subscriptions? Just run the following script:

DECLARE 
   @ownerLogin VARCHAR(100)
  ,@scheduleId VARCHAR(36)

SET @ownerLogin=N'MyDomain\ssrsAdmin' --SSRS Service Account which is also an admin in Report Manager

DECLARE JobCreateCursor CURSOR STATIC FORWARD_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

Notice we aren't creating any job steps here, that's because SSRS will do that for us. Once you create the Agent jobs SSRS goes and validates those jobs and adds the steps as part of the subscriptions. If you try to go to the properties of one of the newly created jobs you will get an error until SSRS runs it's linking process, then you will be able to view the jobs and the subscription will run as it did on the old server. That and you won't be filling up the drive with SSRS error logs.

Configure Dynamic Data Sources in and SSRS Report

Configuring a dynamic data source, a data source you call at run-time, has many purposes. Say you would like to run a production report against one or multiple test environments or run performance reports against all SQL servers you manage; with dynamic data sources you could pick which server you want to run the report against at run-time without having to modify the report itself.

In our example below we are going to use a table that holds all of the SQL Server names we want to use in the dynamic data source. We'll query this table to generate a drop down list for our server selection, which then builds a dynamic connection string to a particular server. 

So the first step is to create an SSRS report. Create an embedded data source named SetupDataSource source and build your report from the this data source. You won't be switching to the dynamic data source until the very end. If you want to query a table to return a list of server names you will also want to use the SetupDataSource to query the table. For the report content, I would start with something simple, like querying your server wait stats.

Once you have built your report, create a new report parameter named "SQLServerName":

Once you have set the name go to the Available Values tab and click the radio button for "Get values from a query". We already created a data set to query the table with my server names. The table has a single column with server names only. If you don't want to select from a table you could instead manually enter your values under "specify value". In our example we've used "get values from a query", the data set is named SelectServer, the value field is ServerName, and the label field is ServerName:

Next we need to create the dynamic data source--this is an embedded data source. We are going to create a dynamic data source named DynamicDataSource with the type as Microsoft SQL Server:

Now we need to setup the connection string using an expression. Click the expression button to bring up the editor:

The data source is the SQL server name—we get this name from the parameter we created "SQLServerName". The initial catalog is the database, in this case master. Enter the following (pictured above) and the click OK.

="data source=" & Parameters!SQLServerName.Value & ";initial catalog=master"

Now that we have the dynamic data source setup, go back to each of your datasets (for example, the dataset you created for server wait stats) and change the dataset to use the embedded, dynamic data source and click OK. When you do this you will get an alert:

It can't update the field list because the data source is dynamic now. Just click OK to continue. Now go ahead and preview your report—you should see a drop down list with the SQL servers we setup in the SQLServerName report parameter. Select the server you want to run against and click Run Report. You should now see a list of wait stats for the selected server. 

Tip: I always query the SERVERPROPERTY to return the server name so that I know which server I'm connected to, it's an easy way to verify you are on the correct server.