Yesterday I started a series of posts on DB restore scripts for Dynamics AX by providing a list of data entities in your AX database that should be updated when restoring from one environment to another.
Today I will start drilling into the details of each of these items starting with SSRS (SQL Server reporting services) configurations.
In the Dynamics AX 2012 client these configurations are located under System Administration -> Setup -> Business Intelligence -> Reporting Services -> Report Servers.
Depending on the configuration of your source system (e.g. Production) you may have multiple servers setup here and typically your destination would only have a single server (Development) therefore your script would need to do the following
1. Remove all but one of the SSRS configurations
2. Update the remaining one to be default and update the relevant data fields.
The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.
Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME'
Declare @REPORTINSTANCE varchar(50) = 'AX'
Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX'
Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX'
Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]'
Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]'
Declare @REPORTFOLDER varchar(20) = 'DynamicsAX'
Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS';
delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS)
update SRSSERVERS set
SERVERID=@REPORTSERVERID,
SERVERURL=@REPORTSERVERURL,
AXAPTAREPORTFOLDER=@REPORTFOLDER,
REPORTMANAGERURL=@REPORTMANAGERURL,
SERVERINSTANCE=@REPORTINSTANCE,
AOSID=@AOS,
CONFIGURATIONID=@REPORTCONFIGURATIONID,
DESCRIPTION=@REPORTDESCRIPTION
where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution.
Note: These changes assume everything is setup correctly on the SSRS side.