Dynamics AX 2009, Restoring a Backup of a SQL Database to another Domain or Environment

Looking into the possibilities where we need to restore a Microsoft Dynamics AX database to a different environment.

1. When moving a production database from one AD Domain to another AD Domain

2. When restoring a production database into a test or development environment

3. When bringing Customer data in house for testing

Restore the SQL Backup

Each Scenario will begin by making a full SQL server backup which will be restored to the new environment. This step assumes you are working with a qualified DBA to perform the backup and restore of the SQL database and will start with the Dynamics AX specific steps.

Checking Kernel and Application Version in the Database

Depending on the situation you may not know the Kernel Version or may not be using the same Application folders to use in the new environment. Normally this will not be the case, but depending on your testing you may need to check the database to get the correct Dynamics AX Kernel and Application version.

Select * from SYSSETUPLOG where DESCRIPTION = 'finished' order by CREATEDDATETIME desc

The record at the top of the list would indicate the last time the setup or upgrade checklist was run, note the APPBUILD and KERNELBUILD version numbers.

If you setup the new environment with the correct Kernel and application version this should ensure the Upgrade Checklist doesn't appear when connecting into the restored database.

AOS Load Balancing and Batch Processing

AOS load balancing has moved from the AOS configuration in version 4.0 to being setup in the client under Administration | Setup in AX 2009.

In AX 2009 server side batch scheduling is also processed by the AOS.

Taking these two roles into account there are several Microsoft Dynamics AX forms and tables that would need to be updated when restoring a database to a new environment.

Below is the list of forms and tables affected:

Form Name :

        Server Configuration.

        Cluster Configuration.

        Batch Groups.

Table Name :

       SysServerConfig.

      BatchServerConfig

      SysClusterConfig

      BatchServerGroup

      Batch

When a new AOS is attached to the database it will be recognized by the system, but it will not be setup as part of load balancing or for batch processing. The following SQL scripts are examples of how you can change the records to use the new AOS instance in the restored environment.

update batch set SERVERID = '01@NewAOS' where serverid = '02@OldAOS'

update sysserverconfig set enablebatch = 1 where serverid = '01@NewAOS'

Depending on your requirements you may want to use the dynamics AX forms to make the changes.

Enterprise Portal, Reporting Extensions, and Analysis Extensions

Microsoft Dynamics AX 2009 also holds parameters relating to Web Site URLs for Enterprise Portal, SSRS website, and Workflow and AIF web services. In addition machine server names for the OLAP and SSRS server are held in the database.

Non-interactive Business Connector Configuration

For all connections using the business connector, the connection to the AOS and through AOS to the database is controlled in the Client Configuration under the Configuration Target drop down list. For the business connector choose Business Connector (non-interactive use only) then on the connection tab verify the Server Name and Port are using the correct, Test or DEV environment AOS.

Restore OLAP database which should already contain reduced cubes to match the customer license keys

Adjust the Dynamics Database (for OLTP) connection string, held in the OLAP database by using SQL management tools and connect to Analysis Services. Under the Dynamics AX database choose the Data Sources folder and adjust the properties on the Dynamics Database data source.

List of forms in the Dynamics Databases that holders server specific information

Form Name :

        Reporting Servers.

       SRSServersForm

       BIOlapAdministration

       EPWebSiteParameters

Table Name :

       SRSServers

      BIANALYSISSERVER

      BICONFIGURATION

      EPWEBSITEPARAMETERS

      EPGLOBALPARAMETERS

NOTE: this may not be a complete list depending on your particular scenario

It is recommended to redeploy new ODC files after the above forms have been updated to the test or dev Enterprise Portal.

Re-deploy SSRS reports to re-populates the SSRS web site reports

Re-deploy any Enterprise Portal changes that exist in the AOT to the test EP website

List of Connection strings that reference server and database names

ODC Files  ---> SharePoint Document Library

Microsoft Dynamics AX (4.0 Perspectives)  ---> Reports Web site

SharedLibrary.DynamicsAXOLAP ---> Reports Web site

Microsoft Dynamics Database (for OLTP) ---> SQL Manager (Analysis Services)

Depending on the method you use you should ensure all connections strings have correct server names and databases the list above outlines the connections strings

Sample Scripts

Below are some sample selects statements you should run to check the value of SERVERID; if needed you may use the update statements below each of the Select statements to update the values in the tables to fit your environment. See a complete list of tables on MSDN http://msdn.microsoft.com/en-us/library/cc652476.aspx

Select * from BatchServerGroup
--Update BatchServerGroup set SERVERID = '01@NewAOS' where SERVERID = '02@OldAOS'

Select * from BATCHSERVERCONFIG
--Update BATCHSERVERCONFIG set SERVERID = '01@NewAOS' where SERVERID = '02@OldAOS'

Select * from SYSCLUSTERCONFIG

select * from SYSSERVERCONFIG
--Update SYSSERVERCONFIG set SERVERID = '01@NewAOS' where SERVERID = '02@OldAOS'

Select * from SYSSERVERSESSIONS
--Update SYSSERVERSESSIONS Set AOSID = '01@NewAOS', Instance_Name = '01' where AOSID = '02@OldAOS'

Select * from Batch
--Update BATCH set SERVERID = '01@NewAOS' where SERVERID = '02@OldAOS'

select * from SYSCLIENTSESSIONS
--delete SYSCLIENTSESSIONS