Migrate Reporting Services Database to another Instance for 2008/2012
Let suppose there are two SQL Server Instances:
SQLServerA
SQLServerB
Here we are migrating Reports from
Source Server - SQLServerA to Target Server - SQLServerB
1). Backup the encryption key and the RS Databases - ReportServer & ReportServerTempdb database from SQLServerA
2). Stop the reporting services in SQLServerB
3). Restore these databases on SQLServerB on with target reporting database name (ReportServerTempdb & ReportServer)
4). Start reporting services on SQLServerB
5). Reset the database connection to ReportingServices on the target machine using Microsoft Reporting Services Configuration Manager
6). Restore the encryption key on SQLServerB
---Restore the encryption key from the backup which you have taken in step 1
After once you open the URL of target server, you might get an error stated -
Scale-out deployment configuration error:
This is because when doing step6 the old server will be added for scale-out deployment on the target machine. If the source and target machine are using different licenses of Reporting Services you might encounter issues that some features are not supported when migrating to a less featured sql server license.
The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)
Normally that should not impose a problem since you would be able to remove the old server from scale-out deploment from the list in Microsoft Reporting Services Configuration Manager.
Solution:
7). On the SQLServerA
Run this command in Query analyser
For SQL2008/2008R2
SELECT * from ReportServer.dbo.Keys
For SQL2012
SELECT * from ReportServer2012.dbo.Keys
and make note of the InstallationId value for the non-null record
8). On ServerB server,
Run this command in Query analyser
For SQL2008/2008R2
SELECT * from ReportServer.dbo.Keys
For SQL2012
SELECT * from ReportServer2012.dbo.Keys
and you should see 3 records or more. One null record, and other records that have values in the MachineName field (these should be the old and new servers name). The InstallationId value from previous step should be in there with the old server's name
9). On the SQLServerB server, delete the record that matches the old server's InstallationId.
for example in this case:
Run this command in Query analyser
DELETE FROM [ReportServer].[dbo].[Keys]
WHERE MachineName = 'SQLServerA'
How to configure URL:
Default URL:
https://servername/Reports/Pages/Folder.aspx
Like in ServerA for default instance:
https://ServerA/Reports/Pages/Folder.aspx
Named instance like ServerA\Dev
http:// ServerB/Reports_Dev/Pages/Folder.aspx
restart the reporting services later and check your reporting services.
Let suppose there are two SQL Server Instances:
SQLServerA
SQLServerB
Here we are migrating Reports from
Source Server - SQLServerA to Target Server - SQLServerB
1). Backup the encryption key and the RS Databases - ReportServer & ReportServerTempdb database from SQLServerA
2). Stop the reporting services in SQLServerB
3). Restore these databases on SQLServerB on with target reporting database name (ReportServerTempdb & ReportServer)
4). Start reporting services on SQLServerB
5). Reset the database connection to ReportingServices on the target machine using Microsoft Reporting Services Configuration Manager
6). Restore the encryption key on SQLServerB
---Restore the encryption key from the backup which you have taken in step 1
After once you open the URL of target server, you might get an error stated -
Scale-out deployment configuration error:
This is because when doing step6 the old server will be added for scale-out deployment on the target machine. If the source and target machine are using different licenses of Reporting Services you might encounter issues that some features are not supported when migrating to a less featured sql server license.
The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)
Normally that should not impose a problem since you would be able to remove the old server from scale-out deploment from the list in Microsoft Reporting Services Configuration Manager.
Solution:
7). On the SQLServerA
Run this command in Query analyser
For SQL2008/2008R2
SELECT * from ReportServer.dbo.Keys
For SQL2012
SELECT * from ReportServer2012.dbo.Keys
and make note of the InstallationId value for the non-null record
8). On ServerB server,
Run this command in Query analyser
For SQL2008/2008R2
SELECT * from ReportServer.dbo.Keys
For SQL2012
SELECT * from ReportServer2012.dbo.Keys
and you should see 3 records or more. One null record, and other records that have values in the MachineName field (these should be the old and new servers name). The InstallationId value from previous step should be in there with the old server's name
9). On the SQLServerB server, delete the record that matches the old server's InstallationId.
for example in this case:
Run this command in Query analyser
DELETE FROM [ReportServer].[dbo].[Keys]
WHERE MachineName = 'SQLServerA'
How to configure URL:
Default URL:
https://servername/Reports/Pages/Folder.aspx
Like in ServerA for default instance:
https://ServerA/Reports/Pages/Folder.aspx
Named instance like ServerA\Dev
http:// ServerB/Reports_Dev/Pages/Folder.aspx
restart the reporting services later and check your reporting services.
No comments:
Post a Comment