Join the Microsoft Dynamics AX Community at https://community.dynamics.com/ax/ today!

Saturday, June 22, 2013

Copying AX Environments via SQL Backup/Restore

Copying a production Dynamics AX 2012 or 2012 R2 database to a test or development environment comes with a number of challenges.  By far the easiest way to accomplish this is by a raw database backup and restore using SQL Server's built-in facilities, though several problems often result.

If the backup of the production system is made while online, then the restored database will appear to have online AOS instances by virtue of their record still present in the SysServerSessions table, and new AOS instances can refuse to start and complain about the maximum number of allowed AOS per licensing.  Part of my restore script is to purge this table.  Since a newly started AOS will automatically insert its own record in this SysServerSession table, no harm is done, and in fact when a newly started AOS finds that it is the only AOS it will automatically set itself up as a batch server, eliminating some further manual configuration post-copy.

The database is imprinted with a GUID in the SysSqmSettings table from which the AX client creates its AUC client cache files, and so replication of the database in its pure form causes the client to use those production AUC files against the copied environment too, as though it were merely one more node of an AOS cluster.  This can cause some bleed-over from production to test and development environments and vice versa, resulting in some very strange behavior, including odd compile errors, and even data corruption.

The solution to these problems and more is to scrub that imprinting from the restored database before first starting the AOS for that environment.

Typically I use the follow short script.

delete from SYSCLIENTSESSIONS
delete from SYSSERVERSESSIONS
delete from SYSSERVERCONFIG
delete from SYSCLUSTERCONFIG
update s set s.GLOBALGUID = cast(cast(0 as binary) as uniqueidentifier) from SYSSQMSETTINGS s