Improved High Availability Support for SQL Clusters
[Updated March 31 2017 for R2 Kernel]
The following kernel hot-fixes for Microsoft Dynamics AX 2012 R3 and R2, KB 3141722 (R3) & KB 4017756 (R2) , improve the way we handle the tables created in TempDB. As you may be aware, TempDB is a transient database, that means when SQL is started or after a cluster node failover, the TempDB database is created empty. Whenever the AOS creates a table in TempDB we also create a reference to that in an internal pool within the AOS service. The reason for this is that once a TempDB table is used, we don’t drop it, but truncate to retain an empty copy to be reused again by another session.
There are also different types of tables created in TempDB by the AOS to handle certain situations. The AOS service was designed to handle a failover in regards to some of these table types, however there were some that were not covered. This fix now handles all situations by introducing a new internal thread that will periodically check the internal pool of temp tables used against the actual tables residing in TempDB. If it finds that they are not present, then the pool is cleaned up.
Out of the box, this new feature is disabled, but it can be enabled by setting the string value “synctempdbpoolfrequency” in the AOS service registry settings. The value set in this key is used to determine the polling frequency of thread that validates the internal temp table pool to the TempDB tables. When setting this, chose a suitable value that will not cause an overhead to the database, a suitable suggested value would be between 30 to 60 (seconds).
The “synctempdbpoolfrequency” registry value is setup under:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dynamics Server\6.0\<instance_number>\<configuration_name>
See example below where the instance was 01 and the configuration was named “Debug”
You can download this from Lifecycle Services here: https://fix.lcs.dynamics.com/Issue/Resolved?kb=3141722
As kernels fixes are cumulative, you can install any later kernel as well. The build number for this kernel is 6.3.3000.1480.
