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%'
