ApplicationXtender - Database Restore

Owned by Chad Nissen
Mar 10, 2021
6 min read

Pay close attention to the following when working with AX Databases


Applies to

  • ApplicationXtender Desktop 16.3, 16.6, 20.3, 7.0, 8.1

 

Summary

Tips and tricks when managing AX Datasources

Data Sources in ApplicationXtender
The following guide outlines some important details about managing data sources in AX. This is intended for AX Support Technicians and Administrators when working with ApplicationXtender Databases. Always consider maintaining current database backups for AX, in order to ensure maximum availability and consistency of data in the AX Database.

 

Cause
Sometimes a problem occurs, forcing AX Administrators to resort to backups. This includes anything for a RDMBMS server failure, to a Disaster-recovery effort, or test the upgrade or migration of an existing working database.

The following should be closely considered when restoring an AX Database in an environment.

Resolution

Important note about Restoring AX Database Backups:

Production Restore:

  • Pay close attention to the AE_SEQ table when you restore the database into an existing environment, as you may cause Data-Loss if you restore older sequence data (Document ID and Object ID) due to the gap between the backup and the current state of the system.

  • Ensure that the Server the database is restored to is the same as the one which is referenced in the XCSM.CONFIG and CM_CONFIG tables, otherwise you may need to truncate CM_CONFIG and reconfigure the database in AX Admin (details below).

Non-Production Restore (Dev/Testing):

  • Consider running a TRUNCATE CM_CONFIG when restoring the database to another environment. This clears the linkage to any existing environments by removing the global configuration data (details below).

  • Consider changing the paths in the AE_PATHS table. Changing the paths to something other than the production paths ensure data does not cross over into production from a dev or test environment.

  • Clear the AX Render Cache, to remove any references to files that exist in the previous environment's render cache.

Data Source Groups and the Global XCSM Configuration:

Each ApplicationXtender environment is represented as a "Group" of data sources, all adhering to the same "Global" Configuration. A data source Group can consist of databases hosted on different  RDBMS servers using different supported database platforms. The Global configuration is stored in the CM_CONFIG table for each data source and is synchronized between all data Sources whenever settings are modified in ApplicationXtender Administrator. This configuration is deployed to clients and servers in a local configuration file: XSCM.CONFIG.  The XSCM.CONFIG profile is synchronized to the client from the group whenever the AX Data Source Selector (DSS) is used to refresh the database connection(s), the AX Administrator is used to adjust a group configuration, or the Component Registration Wizard is used to register AX components on any given AX Server.

Master vs. Secondary Datasources:
The  Master data source is the data source that holds the "Default" data source role in ApplicationXtender Administrator. This information is stored in the CM_CONFIG  table using the All.All.Datasources idname. Below is an example of the data stored in the CM_CONFIG table for a data source group The Encrypted NativeString, InitString, and Password fields are changed to XXX in order to save space in the document. These fields are encrypted by AX Administrator, in order to preserve security (This data is written to the local XCSM.CONFIG file). The master database also stores the default SYSOP password for the datasource group. You can only have one SYSOP password for the datasource group, and you will receive an error from AX Admin when adding a datasource from an existing AX group if the SYSOP password is out of sync.

SELECT datastream FROM cm_config WHERE idname='All.All.Datasources'
 
<ConfigInfo>
            <LastUpdate>2012-12-14T21:02:01.116Z</LastUpdate>
            <Version>2</Version>
</ConfigInfo>
<DataSourceConfigMgr>
      <DefaultDataSource>AX65-SQL2K8</DefaultDataSource>
      <DataSources>
            <DataSource>
                  <References />
                  <Name>AX65-SQL2K8</Name>
                  <LicenseServer>AX-65-LICENSESVR</LicenseServer>
                  <SecurityModel>WinNT</SecurityModel>
                  <DirectoryName />
                  <NativeString>XXX</NativeString>
                  <InitString>XXX</InitString>
                  <Authentication>
                        <UserName />
                  </Authentication>
                  <SvcDatasourceLogin>
                        <Service>Generic</Service>
                        <LoginName>sysop</LoginName>
                        <Password>XXX</Password>
                  </SvcDatasourceLogin>
                  <State>Visible</State>
                  <Description />
                  <Schema/>
                  <FailoverDS />
                  <UseIntegratedSecurity>false</UseIntegratedSecurity>
                  <JdbcString> </JdbcString>
            </DataSource>
            <DataSource>
                  <References />
                  <Name>AX65-SQL2K5</Name>
                  <LicenseServer>AX-65-LICENCESVR</LicenseServer>
                  <SecurityModel>AxDB</SecurityModel>
                  <DirectoryName />
                  <NativeString>XXX</NativeString>
                  <InitString>XXX</InitString>
                  <Authentication>
                        <UserName />
                  </Authentication>
                  <SvcDatasourceLogin>
                        <Service>Generic</Service>
                        <LoginName>sysop</LoginName>
                        <Password>XXX</Password>
                  </SvcDatasourceLogin>
                  <State>Visible</State>
                  <Description />
                  <Schema />
                  <FailoverDS />
                  <UseIntegratedSecurity>false</UseIntegratedSecurity>
                  <JdbcString>XXX</JdbcString>
            </DataSource>
      </DataSources>
</DataSourceConfigMgr>

Database Availability:
In cases where a given AX database is unavailable for processing of the CM_CONFIG, the AX Administrator will highlight the unavailable database in red and will allow the administrator to reconfigure or remove the database. If the default database for a group is unavailable at the time AX Admin is launched, AX Admin may not start, generating an error "Could not connect to any datasource". It s important to understand the importance of the default database and its availability to an AX environment:

AX Desktop Applications:
When using AX Desktop, the database which is marked as default should always be available, when connecting to any database in the data source group, as it is considered the owner of the master configuration. When clients log in to AX Desktop via a secondary database (one not marked as default) the secondary database CM_CONFIG is compared to the Master CM_CONFIG to determine synchronization and to load the data source list. If the database is determined to be out-of-sync with the Default, it is considered part of a different configuration group, which will generate an error in AX Desktop. The database can be pulled into the configuration group using AX Admin. Additionally, if the Master database is unavailable at login time, the AX Desktop Application will most likely hang with the "Loading Data Sources " status, as the connection attempt is made to the master, resulting in an error/timeout, even if the data source they are connecting to is available.

AX Web Access:  
AX Web components are registered in the CM_CONFIG table using the Component Registration Wizard. If the default data source is not available at Registration time, the registration process cannot complete. Once registered, errors related to default data source availability will generally only occur in the AX Web Event log following an IIS reset or in the AX Web Access interface whenever the User selects the unavailable data source at login time or from the data source list after login.
 
Maintaining Multiple Groups:
It is possible to maintain multiple "Groups" of data sources for an environment; however, this requires manual manipulation and management of the XSCM.CONFIG profile on a given host, in order to switch between data source Groups. If a client has an XSCM.CONFIG profile for one Group, and attempts to connect the AX data source selector to a database in another group, AX will generate an error indicating that the group is out of sync. If you do this in AX Admin, you will have the option to "sync" the data source with the group being edited.

Use the following guidelines when moving databases between groups:
Note: Although not required, it is recommended that the CM_CONFIG table be truncated whenever moving a database from one group to another. This guarantees that no information from the previous group will make it into the new group, as AX may sync the data source group s configuration if the newly-added data source happens to be the default data source of the previous group.

  1. Open AX Admin for the group containing the data source being moved.

  2. Highlight the data source and click the Remove button to remove it from the group.

  3. Save the configuration and exit (This is where it is a good idea to Truncate the CM_CONFIG).

  4. Open AX Admin using the XSCM.CONFIG profile for the data source group the data source is being moved to.

  5. Use the add button to start the wizard, which will add the data source group to the list.

AE_SEQ Table:

The AE_SEQ table contains data related to the document and object ID values the system uses to track documents and pages. This is one of the most critical tables in AX, as it tells ax clients how to name the image files, as well as how to store the document indexes and page data. If this data is overwritten, or the sequences reset to 0 or an older value, AX will overwrite existing Data and cause Data-Loss to occur. Pay close attention to this table when planning to retore an AX Database into an existing environment (replacing an existing Database). The following describes how to identify this scenario:

The AE_DT# table contains the index data for images in a particular application. 
You can also use the Docid to determine if the Sequence is off. And you can query to see if there are duplicate docs
 
This query shows the highest document ID value in the AX DB. The value in AE_SEQ for a given app should NEVER be less than the value returned by this query:
 
select MAX(docid) from ae_dt186
 
This query shows the docid rows in a DT table where the count is > 1. This shouldn't occur in AX and represents a data problem introduced by the customer:
 
SELECT docid, COUNT(*) TotalCount
FROM ae_dt186
GROUP BY docid
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

 

CASO Knowledge Base