Checking Database entries after restoring a Microsoft Dynamics 2009 SQL Database to another Domain or Environment

This BLOG post describes some common steps after restoring a Dynamics AX 2009 SQL Database to another environment under several different scenarios.

SUMMARY

Consider the following scenarios where you may need to restore a Dynamics AX database to a different environment

1. When testing a Dynamics AX upgrade
2. When bringing Dynamics AX 2009 data in house for testing
3. When restoring a production database into a test or development environment
4. When moving a production database from one AD Domain to another AD Domain

Note: Since the specific requirements of your particular testing or scenario may not be known there may be additional steps beyond the common considerations listed here. The KB’s referanced below will require a Partner Source or Customer Source login.
 

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 versions installed to use with the restored database.

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.
 

Setting Database Permissions for the AOS service account

The first issue you will face is making sure you can start the AOS against the newly restored database, in order to do so you need to make sure the AOS service account has the correct database permissions.

If the AOS fails to start check the application event log for the specific errors:

• Refer to the Microsoft Dynamics™ AX 2009 Installation Guide in the section on Create a SQL Server database manually or see the MSDN topic AOS Security
• See KB article 911380 – Error message when you try to start the AOS for Microsoft Dynamics AX 4.0: “Error 100: Cannot create another system semaphore
• See KB article 937756 – Error message when you try to start a server that is running the AOS service in Microsoft Dynamics AX 4.0: “Windows could not start the Dynamics Server$01 on Local Computer
   

Changing the Dynamics AX account with the new Domain Account information

In order for your account to log into the restored database environment using a Dynamics AX client, your domain account must be a user in the system.

See KB article 931461 – You cannot log on to an instance of Microsoft Dynamics AX 4.0 Server after you move the instance to a new domain. The steps from this KB article will allow access to the system for one user account, depending on your situation you can then create additional scripts or use the import wizard to give other users access.
 

 Changing the System Service Accounts 

The System service accounts held in the Dynamics AX database need to be updated with the Business Connector Proxy Account used for the IIS Application Pools and the SSRS Services.  The Workflow accounts will also need to be set in order to process the workflow batch jobs and for the Synchonization Service.

The System Accounts are setup under Administration | Setup | Security | System Service Accounts

Depending on if you need all of reporting and EP running in the new system these would need to be updated.

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 Dynamics AX 2009.

In Dynamics AX 2009 server side batch scheduling is also processed by the AOS, instead of the client, with this change in mind there are several AX forms and tables that would need to be updated when restoring a database to a new environment. If these changes are not made batch jobs will not execute with can also affect upgrade testing.

Below is the list of forms and tables affected:

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.

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

Enterprise Portal, Reporting Extensions, Workflow, and Analysis Extensions

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

Some testing environments may require these additional components being functional while others may not.

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 by the Client Configuration under the Configuration Target drop down list.  For the business connector configuration choose the target of Business Connector (non-interactive use only) check the connection tab and verify the Server Name and Port you are using is correct in the new environment.

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

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

SharePoint (WSS/MOSS) Considerations

You can get the correct SiteID GUID from the WSS database to update the EPWEBSITEPARAMETERS and EPGLOBALPARAMETERS tables with by using the same Web Service call that Enterprise Portal call. Before making any changes make sure you have a working Enterprise Portal Web site in the new environment.

To do this open IE from the IIS server and go to the website URL for the test Enterprise Portal and use the following relative path /_layouts/ep/WSSAdmin.asmx.  Once the web page opens you will get a link for the GetEPWebInfo call which you can click on, and then in the next page you will enter the URL.
 

Analysis Extensions and the OLAP database

OLAP database

If needed restore the OLAP database which should already contain reduced cubes to match the customer license keys and configuration.

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.

• 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

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.

In the SSRS reports manager site edit the SharedLibrary.DynamicsAXOLAP datasource.
 

Unique GUID per Dynamics AX environment

If you experience incorrect caching information or strange form behaviour you may be using the same Client Cache file when switching between database environments.

In order to ensure that a unique AUC file is created per instance for the AX client to use, and also to ensure that the AX instances have a unique Global GUID, update the GlobalGUID in the SysSQMSettings table with an empty GUID (00000000-0000-0000-0000-000000000000).

After making this change restart the AOS service, and it will then generate a new GUID that allows a new AUC file to be created for the AX client to use.

See also Identical AX 2009 AUC File Created For Multiple AX Installations