A common task in the administration of a Dynamics AX installation is restoring databases from a live application to a development environment or quality assurance (QA) environment. However when doing so one needs to be very careful to re-configure the database so that it behaves correctly in its new context. For example you would need to re-configure your SSRS servers to point to the correct DEV/QA SSRS instance. Many of these changes can be done via the front-end, but it is very useful to script these in SQL so that nothing is missed and alot of effort is saved.
The following post aims to list as many of the potential data items that need to be changed when doing a DB restore. I will in the following days be posting the relevant SQL alongside the front-end equivalent and ultimately a full DB restore script that you can use when doing such a restore. Some of these may need to be adapted for your specific configurations and you may need to add your own based on customization etc.
Infrastructure setups
- SSRS Servers – Re-point or recreate your SSRS (SQL Server Reporting services) instances (View Details)
- SSAS Servers – Re-point or recreate your SSAS (SQL Server Analysis services) instances (View Details)
- Configure AX AOSs and batch AOSs – Live environments may typically have multiple AOS’ and batch servers. Cleaning up and reconfiguring these references will assist in getting new batch jobs and SSRS setups up and running. (View Details)
- Batch Jobs/Batch Groups (View Details)
- Reconfigure batch jobs and batch groups to use the new server configurations as soon as the AOS is started up (e.g. Workflow processing)
- Disable certain critical batch jobs – This is especially important if there are batch jobs that should NOT run in a DEV/QA environment. E.G. Automatic placement of orders etc… These jobs should typically never be run outside of live so we want to disable them before the AOS even starts up.
- Service accounts – (Optional) Reset your AX service accounts (workflow execution and business connector proxy) if live and DEV/QA differ. (View Details)
- Help server – (Optional) Re-point your help server URL if necessary if live and DEV/QA differ. (View Details)
- Reset outgoing email server – (Optional) if live and DEV/QA differ. (View Details)
- Reset Data Migration Framework’s shared folder (View Details)
- Enterprise portal websites – Re-point your enterprise portal website urls to the DEV/QA instances.
Data safe guards (Optional)
- Reset all email templates to have a sender name and address that clearly shows that they originate from a DEV/QA system. (View Details)
- Reset Customer/Vendor/User email addresses – We may not want to inadvertently send out mails to clients, vendors or users while performing tests. These may include alert or workflow emails. (View Details)
- Disable users – You may require that only certain users have access to DEV/QA databases for testing, scripting the disabling of the users in bulk will help prevent the wrong people from accessing the environment and save you having to do this manually. (View Details)
- Clean up sensitive data if needed – E.G. Bank accounts, credit cards etc.
- Set user status bar options – It is sometimes useful to set your users status bar options in DEV/QA so that you can easily identify critical bits of information relating to their sessions. (View Details)
Clean Ups
- Clean up SYSServerSessions
- Clean up SYSClientSessions
Hats off to you Jono. Fantastic helpful blog. Upon asking a colleague for scripts to restore an instance over another, they forwarded me your link. What a nice surprise to see your picture and know we’ve rubbed shoulders in the past!
Hi Celeste. I’m so glad its helpful to you, hopefully I’ll be finished with the posts towards the end of the week.