Clustered SQL Server instance – 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 hosted on a Clustered SQL Server instance. 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 R3 CU10 on SQL Server 2014.

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 the SQL Server instance to use the virtual servername AXCL1.

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.

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 2014 – Windows Server Failover Clustering (WSFC) with SQL Server
https://msdn.microsoft.com/en-us/library/hh270278(v=sql.120).aspx

2. SSIS

Regardless of where you install and run SSIS, you need to consider what SSIS is going to talk to. When you are running a clustered SQL Server instance, in the most basic form, one SQL Node is Active and one Passive. I want SSIS to talk to the Active SQL Node so I’ve configured SSIS on the AOS to talk to virtual servername , i.e. AXCL1. 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:

SqlConfigMgrSSISRunning

This is what my edited MsDtsSrvr.ini.xml file looks like where ServerName has been set to AXCL1. The file is located on the AOS where SSIS has been installed.

MsDtsSrvr.ini.xml.axcl1

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 virtual servername AXCL1 there for similar reasons. I want my AOS to be able to connect to the active SQL Node without knowing if that is SQL Node 1 or SQL Node 2.

This is what my configuration Database Connection looks like:

AXServerConfig.axcl1

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:

AXServices

5. Points of interest – Database Information – Logon server

In earlier kernel builds, you may have noticed that the Logon server value displayed in the Database Information form (SysSqlStatus form) in AX did not return the virtual servername (AXCL1). We have fixed this in more recent kernel builds so you should expect to see something like this if you are on a recent kernel (i.e. the virtual servername AXCL1):

DatabaseInfo.axcl1

6. Points of interest – Which SQL Node is active?

You can run this sort of query in SSMS to establish which SQL Node is active:

--------------------------------------------------------------------------------------------

-- 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 -- AX Instance Config

USE Master
GO

SELECT getdate() as myCurrentDateTime, 
@@SERVERNAME as myServerName,
SERVERPROPERTY('IsClustered') as [Clustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as [ActiveNode], 
os.Cores, df.Files 
FROM 
(SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os, 
(SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df; 
GO

-------------------------------------------------------------------------------------------- 

SQLActiveNode

7. Points of interest – Kernel Version

Running a recent kernel build is as important as ever when you are running Dynamics AX and your databases reside on a clustered SQL Server instance. 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 a failover occurred.

8. Testing

As always, ensure you’ve set up a proper Dynamics AX on a clustered SQL Server instance TEST environment first, and that you are familiar with the impact of your Clustered SQL Server instance failover actions before you perform them in PROD.