Export (0) Print
Expand All

SQL Server topology recommendations for availability and performance [AX 2012]

Updated: August 9, 2013

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.

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.

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.

ImportantImportant

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

Supported

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.

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.

NoteNote

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.

Caution noteCaution

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:

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.


Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft