SQL Server Report Service Migration from Server A to Server B

Pre-Requisites on SERVER B

1.       Install Reporting Services on SERVER B.

2.       Set following properties to False in “C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config” file:

·        IsSchedulingService>False

·         IsNotificationService>False

·         IsEventService>False

Restart the Reporting Service.

Migration of DBs

1.       Take the Full backups of the following DBs from SERVER A and restore it on SERVER B:

·         ReportServer

·         ReportServerTempDB

2.       Configure Reporting Services on SERVER B by going to START -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> Reporting Services Configuration. Configure all the steps manually.

3.       Configuring Encryption Key: Open the above config tool on SERVER A and take the Encryption key backup. Restore the same encryption key on SERVER B using the above configuration tool.

4.       Set the following property values to true in “reportserver.config” file:

·        IsSchedulingService>False

·         IsNotificationService>False

·         IsEventService>False

Restart the Reporting Service.

Once these values are set, it will populate the SQL Server agent with subscription jobs.  All these jobs need to be disabled immediately. Else the subscription mails will start immediately unless the email execution account is not specified or the above config values are set back to false.

Issues and Errors

1.       You get the following error when you try to run the report URL:

·         "The feature: "Scale-out deployment" is not supported in this edition of Reporting Services."

Solution:

Delete SERVER A encryption key from the restored system. Run the following commands on SERVER B:

o   rskeymgmt -l -i InstName

o   rskeymgmt -i InstName -r SourceEncryptionKeyID

SQL Server Reporting Services Flow

1.       SQL Server Agent Jobs of subscriptions populates the Event table with subscription details.

2.       The following XML Config values determine if the subscriptions in the event table should be executed:

·        IsSchedulingService>False

·         IsNotificationService>False

·         IsEventService>False

Restart the Reporting Service.

3.       Once the Email Execution Account is setup, the subscriptions will be sent out if the above config values are set to true.

Revert Back to Server A as Reporting Server

                Stop Service on Server B

1.       Stop the Service “SQL Server Reporting Services(InstanceName)” on Server B

2.       Empty the Email execution account from Report Config.

3.       Empty the Events Table in ReportServer DB.

4.       Disable all the SQL Server Agent subscription jobs

5.       Set the Scheduling, Events and Notification service property values to ‘false’ in “Reportserver.config” file. Restart the Reporting Service.

Start Service on Server A

1.       Start the Service “SQL Server Reporting Services(InstanceName)” on Server B

2.       Enter the email execution account from Report Config.

3.       Empty the Events Table in ReportServer DB.

4.       Enable all the SQL Server Agent subscription jobs

5.       Set the Scheduling, Events and Notification service property values to ‘true in “Reportserver.config” file. Restart the Reporting Service.

Important Queries on Report Server DB:

1.       To determine which SQL Server Job is associated with which subscription:

SELECT      Schedule.ScheduleID AS SQLAgent_Job_Name,

            Subscriptions.Description AS sub_desc,

            Subscriptions.DeliveryExtension AS sub_delExt,

            [Catalog].Name AS ReportName,

            [Catalog].Path AS ReportPath, Subscriptions.SubscriptionID

FROM        ReportSchedule

      INNER JOIN

            Schedule ON

            ReportSchedule.ScheduleID = Schedule.ScheduleID

      INNER JOIN

            Subscriptions ON

            ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID

      INNER JOIN

            [Catalog] ON

            ReportSchedule.ReportID = [Catalog].ItemID AND

            Subscriptions.Report_OID = [Catalog].ItemID

      INNER JOIN [Event] E ON

            E.[EventData] = Subscriptions.SubscriptionID

2.       Disable all the Subscription Jobs:

Select 'EXEC msdb.dbo.sp_update_job @job_name = '''

            + msdb.dbo.sysjobs.name

            + ''',@enabled = 0'

FROM        msdb.dbo.sysjobs

            INNER JOIN

                  msdb.dbo.sysjobschedules

                  ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id

            INNER JOIN

                  msdb.dbo.sysschedules

                  ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id

            INNER JOIN

                  ReportServer.dbo.ReportSchedule a

                  ON CONVERT(VARCHAR(100),a.ScheduleID) = msdb.dbo.sysjobs.name

            INNER JOIN

                  ReportServer.dbo.ReportSchedule c

                  ON msdb.dbo.sysjobs.name = CONVERT(VARCHAR(100),C.ScheduleID)

            INNER JOIN

                  ReportServer.dbo.Subscriptions d

                  ON c.SubscriptionID = d.SubscriptionID

            INNER JOIN ReportServer.dbo.Catalog e

                  ON d.report_oid = e.itemid

3.       Get which Stored Procedures are used in Reports:

;WITH xmlnamespaces (

    default

    'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',

    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd

)

SELECT

    NAME AS reportname,

    q.value('@Name[1]', 'VARCHAR(50)') AS datasetname,

    x.value('DataSourceName[1]', 'VARCHAR(50)') AS datasourcename,

    x.value('CommandText[1]', 'VARCHAR(50)') AS spname

FROM

(

    SELECT NAME,

    CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml

    FROM reportserver.dbo.catalog

) a

      CROSS apply

            reportxml.nodes('/Report/DataSets/DataSet') d(q)

      CROSS apply

            q.nodes('Query') r(x)

WHERE x.value('CommandText[1]', 'VARCHAR(50)') LIKE '%SPName%'