AlwaysOn – Example AX TEST environment configuration concepts and points of interest
Consider a scenario where you are running AX 2012 and your Dynamics AX databases are part of a SQL Server AlwaysOn Availability Group (AG). I’ll discuss some concepts and points of interest below which are relevant when running AX 2012 RTM, R2 or R3. The screenshots are from a VM TEST environment with CONTOSO DEMO DATA that is running AX 2012 R2 CU7 on SQL Server 2012.
1. The environment
There are 4 VMs in the TEST environment
A Domain Controller
SQL Node 1
SQL Node 2
An AOS
I’ve installed an AX Client installed on the AOS machine.
I’ve installed SSIS on the AOS machine.
I’ve installed the DIXF Service on the AOS machine.
I’ve configured an AG Listener called AXLSN.
This blog post doesn’t aim to tell you how to set up your environment. I’m just showing you what my VM TEST environment looks like and mentioning some points of interest and potential gotchas, because I see cases involving AlwaysOn AGs from time to time.
For further details, see the official AX and SQL content on this topic:
SQL Server topology recommendations for availability and performance [AX 2012]
https://technet.microsoft.com/en-gb/library/dd362068.aspx
SQL Server 2012 – Overview of AlwaysOn Availability Groups (SQL Server)
https://msdn.microsoft.com/en-us/library/ff877884(v=sql.110).aspx
2. SSIS
Regardless of where you install and run SSIS, you need to consider what SSIS is going to talk to. In an AlwaysOn AG, in the most basic form, one SQL Node is Primary and one Secondary. I want SSIS to talk to the Primary SQL Node so I’ve configured SSIS on the AOS to talk to the AG Listener, i.e. AXLSN. I’ve done this by editing the MsDtsSrvr.ini.xml file. For more details on this topic, see the link below:
Configuring the Integration Services Service (SSIS Service)
https://msdn.microsoft.com/en-us/library/ms137789.aspx
This is what I see when I run the SQL Server Service Configuration Manager on the AOS:
This is what my edited MsDtsSrvr.ini.xml file looks like where ServerName has been set to AXLSN. The file is located on the AOS where SSIS has been installed.
3. Microsoft Dynamics AX Server Configuration Utility
When it comes to telling the AOS what SQL Server instance to connect to, I’ve specified the AG Listener AXLSN there for similar reasons. I want my AOS to be able to connect to the current Primary SQL Node without knowing if that is SQL Node 1 or SQL Node 2.
This is what my configuration Database Connection looks like:
4. Microsoft Dynamics AX Services on the AOS
As mentioned above, I’m running my AOS and the DIXF Service on the AOS, so this is what I see when I look for Dynamics AX services on the AOS:
5. Points of interest – Database Information – Logon server
You may have noticed that the Logon server value displayed in the Database Information form (SysSqlStatus form) in AX does not return the AG Listener (AXLSN). In fact, it returns the SQL Server instance name of the SQL Node that was Primary when the AOS was started.
In my configuration, that is CNW12N1\INS1:
The Logon server value is returned by the \Classes\SysSQLSystemInfo\getLoginServer method, and it retrieves the SQL Server instance name of the SQL Node that was Primary when the AOS was started.
Engineering is currently looking at this, but if you want this call to return your AG Listener then you can hard code it to do so:
// Disclaimer: // Microsoft provides programming examples for illustration only, without warranty // either expressed or implied, including, but not limited to, // the implied warranties of merchantability or fitness for a particular purpose. // This mail message assumes that you are familiar with the programming language that // is being demonstrated and the tools that are used to create and debug procedures. // // This source code is freeware and is provided on an "as is" basis without warranties of any kind, // whether express or implied, including without limitation warranties that the code is // free of defect, fit for a particular purpose or non-infringing. The entire risk as to the // quality and performance of the code is with the end user. SysSQLSystemInfo::getLoginServer()
6. Points of interest – User Options
Another area of Dynamics AX where you can see the impact of the fact that Logon server returns the SQL Server instance name of the SQL Node that was Primary when the AOS first started, is under User Options. Following an AG Failover, you may see this sort of error when you try to update User Options if you haven’t failed back to the original Primary node:
The target database, ‘MicrosoftDynamicsAX’, is participating in an availability group and is currently not accessible for queries…
In this scenario, \Classes\SIGSigningProvider_SQLServer_Server\getNewConnection is calling \Classes\SysSQLSystemInfo\getLoginServer and it once again retrieves the SQL Server instance name of the SQL Node that was Primary when the AOS was started.
Engineering is currently looking at this, but if you this want call to return your AG Listener then you can hard code it do so:
// Disclaimer: // Microsoft provides programming examples for illustration only, without warranty // either expressed or implied, including, but not limited to, // the implied warranties of merchantability or fitness for a particular purpose. // This mail message assumes that you are familiar with the programming language that // is being demonstrated and the tools that are used to create and debug procedures. // // This source code is freeware and is provided on an "as is" basis without warranties of any kind, // whether express or implied, including without limitation warranties that the code is // free of defect, fit for a particular purpose or non-infringing. The entire risk as to the // quality and performance of the code is with the end user. SIGSigningProvider_SQLServer_Server::getNewConnection()
7. Points of interest – Redirecting calls to SQL Nodes to the AG Listener
During the course of some research into how Dynamics AX works when the AX databases participate in an AlwaysOn AG, I established a basic and low tech way to mitigate the impact of calls to SysSQLSystemInfo::getLoginServer() returning the SQL Server instance name of the SQL Node that was Primary when the AOS was started (this not necessarily being the SQL Server instance name of the CURRENT Primary SQL Node).
You can implement this on your AOS by editing the local HOSTS file so that any calls to SQL Node 1 and SQL Node 2 are redirected to your AG Listener:
// Disclaimer: // Microsoft provides programming examples for illustration only, without warranty // either expressed or implied, including, but not limited to, // the implied warranties of merchantability or fitness for a particular purpose. // This mail message assumes that you are familiar with the programming language that // is being demonstrated and the tools that are used to create and debug procedures. // // This source code is freeware and is provided on an "as is" basis without warranties of any kind, // whether express or implied, including without limitation warranties that the code is // free of defect, fit for a particular purpose or non-infringing. The entire risk as to the // quality and performance of the code is with the end user. 1. Make a note of the following: SQL Node 1 IP address and hostname, e.g. sqlnode1 – 192.168.0.11 SQL Node 2 IP address and hostname, e.g. sqlnode2 – 192.168.0.12 SQL AG Listener address and name, e.g. AXLSN – 192.168.0.55 2. On your AOS where the batch jobs are running, edit the hosts file that you can find under C:\Windows\System32\drivers\etc 3. Add the necessary information to redirect all requests to your SQL nodes to the AG Listener, e.g. 192.168.0.55 sqlnode1 # redirect to AXLSN ip address 192.168.0.55 sqlnode2 # redirect to AXLSN ip address 4. Close the hosts file 5. Ping the two SQL nodes and the AG Listener from the AOS where you made the change, 6. Check that all resolve to the same correct ip address (in the example above, it is 192.168.0.55)
8. Points of interest – Kernel Version
Running a recent kernel build is as important as ever when you are running Dynamics AX and your databases participate in an AlwaysOn AG. There are fixes to how AOS deals with temporary tables stored in TempDb, for example, and how AOS detects Batch jobs that may have been running when an AG failover occurred.
9. Testing
As always, ensure you’ve set up a proper Dynamics AX on AlwaysOn TEST environment first, and that you are familiar with the impact of your AG Failover actions before you perform them in PROD.
