SQL Server topology recommendations for availability and performance
Important
This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.
Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
The choice topology of the Microsoft SQL Server environment that supports Microsoft Dynamics AX is driven primarily by requirements for availability and performance.
General topology recommendations
We recommend the following guidelines as a baseline for your topology:
Follow the documented best practices for SQL Server.
Use a dedicated server that is running SQL Server 2012, SQL Server 2008 R2 or SQL Server 2008.
Use a single instance of SQL Server that is dedicated to running the Microsoft Dynamics AX production database.
Store your test and development databases on a separate server from the production database.
Availability recommendations
SQL Server provides several options that can help you achieve high availability: Windows Server Failover Clustering, SQL Server database mirroring, SQL Server log shipping, and SQL Server 2012 AlwaysOn Availability Groups. Of these options, failover clustering and availability groups provide the least amount of downtime. However, replication, database mirroring, log shipping, and availability groups can satisfy other requirements. For example, some of these options can be used to provide a reporting environment that can reduce the load on your production server. The high availability option that you select for your implementation of Microsoft Dynamics AX depends on your availability requirements, a cost/benefit analysis, and the risk tolerance of your organization.
Important
Before you select a high availability option for SQL Server, we strongly recommend that you contact your value-added reseller (VAR) or Microsoft Support to make sure that the option that you want to use is supported.
Determine and document your availability needs carefully, and test the solution that you select to make sure that it provides the expected availability. The following table lists supported high availability configurations.
High availability configuration |
Support status |
|---|---|
Windows Server Failover Clustering (WSFC) |
Supported |
Log shipping |
Supported |
Transactional replication |
Supported. Requires KB 2765281. |
Snapshot replication |
Supported |
Database mirroring |
Not supported, because database information change and AOS restart are required when failover occurs. |
Merge replication |
Not supported, because complex resolution is required to guarantee data integrity |
SQL Server 2012 AlwaysOn Availability groups |
Supported. Both synchronous and asynchronous secondary configurations are supported. |
If you are running SQL Server 2012, we recommend that you deploy AlwaysOn Availability Groups.
If you are running SQL Server 2008, we recommend that you deploy a Windows Server Failover Cluster with one active node and one inactive node.
Availability groups and failover clusters do not require a restart of the Application Object Server (AOS) service.
The following figure shows a SQL Server failover topology.
.png "SQL Server failover topology")
When you use SQL Server failover clustering, note the following behavior:
The failover is transparent to AOS, and the service typically does not require a restart.
In-process transactions are rolled back, and the user may have to reenter data that was being entered at the time of failure.
Note
We recommend that a failover cluster be configured so the active node will fail over to an inactive node. If the active node fails over to another active node in the cluster, you must make sure there is sufficient capacity to sustain the Microsoft Dynamics AX database workload, otherwise performance may be degraded significantly.
Warning
Microsoft SQL Server Reporting Services cannot be installed on a failover cluster, because you cannot run the Reporting Services service as part of a failover cluster. However, you can install the report server database on a computer that has a failover cluster installed.
For more information about availability options, see:
Performance recommendations
The performance of the database can significantly affect overall Microsoft Dynamics AX performance.
To achieve the best performance, the SQL Server environment and storage subsystem must be correctly configured. For more information, see Configure SQL Server and storage settings.