SQL Server requirements and recommendations for Microsoft Dynamics 365

  • Article

 

Applies To: Dynamics 365 (on-premises), Dynamics CRM 2016

The following requirements and recommendations apply to new and existing installations of SQL Server.

In this topic

General recommendations

Improve performance when you use Microsoft Dynamics 365 with SQL Server 2016

General recommendations

  • Microsoft Dynamics 365 Server requires an instance of Microsoft SQL Server Reporting Services be installed, running, and available. All installations of the supported SQL Server editions can be used as the reporting server. However, the Reporting Services edition must match the SQL Server edition.

  • For a list of supported SQL Server versions, see SQL Server editions.

  • Microsoft Dynamics 365 Server isn’t supported with SQL Server that’s running on Windows Server 2008 or Windows Server 2003.

  • When Microsoft Dynamics 365 Server and SQL Server are installed on different computers, they must be in the same Active Directory domain.

  • Microsoft Dynamics 365 Server Setup and Deployment Manager support the default instance or a named instance of SQL Server.

  • Although you can install SQL Server by using either Windows Authentication or mixed-mode authentication, Windows Authentication is a prerequisite for Microsoft Dynamics 365.

  • The service account that SQL Server uses to log on to the network must be either a domain user account (recommended) or the Network Service account (you can’t use a local user account on the server). Using a low-privilege account strategy is recommended to help avoid compromising the security of the server.

  • The SQL Server service must be started. This service should be configured to automatically start when the computer is started.

  • SQL Server Agent must be started. This service should be configured to automatically start when the computer is started.

  • SQL Server Full-Text Search must be installed and started. This service should be configured to automatically start when the computer is started.

  • Microsoft Dynamics 365 Server Setup requires a network library to authenticate SQL Server. By default, TCP/IP network libraries are enabled when you install Microsoft SQL Server. SQL Server can use both TCP/IP or Named Pipes for authentication. However, the computer that is running SQL Server must be configured for at least one of the two network libraries.

  • We recommend that the computer that is running SQL Server be located on the same local area network (LAN) as the computer that is running the Microsoft Dynamics 365 Server Back End Server roles. For a description of the server roles, see Microsoft Dynamics 365 server roles.

  • The computer that is running SQL Server must be configured to have sufficient disk space, memory, and processing power to support the Microsoft Dynamics 365 environment. For more information, see Microsoft Dynamics 365 Server hardware requirements.

  • Although it’s optional, consider accepting the SQL Server default settings for Collation Designator, Sort Order, and SQL Collation. Microsoft Dynamics 365 supports the following collation orders:

    • Case-sensitive

    • Case-insensitive

    • Accent-sensitive

    • Accent-insensitive

    • Binary sort order (such as Latin1_General_100_BIN)

    Note

    Microsoft Dynamics 365 sets the collation order at the database level. This setting might differ from that set at the SQL Server level.

  • Review all SQL Server installation options and be prepared to make the needed selections when you run Setup. For more information, see Installation for SQL Server.

  • If you plan to install SQL Server in a location other than the default file location, see File Locations for Default and Named Instances of SQL Server.

    You should also consider where the Microsoft Dynamics 365 databases are located on the server, and the hard-disk configuration that will support them.

    Note

    To achieve the best combination of disk fault tolerance and performance, consider the many specifications for redundant array of independent disks (RAID) available from hardware vendors. Format the disks where the SQL Server database files reside for the fault-tolerance requirements of the application and performance parameters for the I/O activity occurring on that partition.

  • We recommend that Common Runtime Language (CLR) be enabled on the SQL Server that hosts the Microsoft Dynamics 365 organization database. CLR can show a significant improvement in performance for some features such as time zone conversions when you run dashboards, charts, advanced finds, and Microsoft Dynamics 365 interactive service hub experience. More information: Enabling CRL Integration

Improve performance when you use Microsoft Dynamics 365 with SQL Server 2016

The following settings can help improve Microsoft Dynamics 365 application performance and should be configured on the SQL Server 2016 instance where the Microsoft Dynamics 365 organization databases are serviced.

  • Make sure you are running SQL Server 2016 with Cumulative Update 2 (CU2). Server & Tools Blog: Cumulative Update #2 for SQL Server 2016 RTM

  • Configure the SQL Server instance where the Microsoft Dynamics 365 organization database is maintained with Trace Flag 1224 . This setting disables lock escalation based on the number of locks and helps reduce CPU usage on SQL Server 2016.

    To determine the existing trace flag 1224 setting run the following T-SQL statement. A return value of 0 for status in the output indicates trace flag 1224 is not enabled.

    DBCC TRACESTATUS (1224)

    To set trace flag 1224 as a startup option, add the SQL Server database engine startup option -T1224. More information: Database Engine Service Startup Options

  • If not already set, configure Microsoft Dynamics 365 organization databases to use compatibility level 110, which is the compatibility level for SQL Server 2012. More information: View or Change the Compatibility Level of a Database

  • Configure the Microsoft Dynamics 365 organization database to use indirect checkpoints and a target recovery time of 60 seconds. By default, new databases created with SQL Server 2016 use indirect checkpoints and have a default target recover time of 60 seconds. More information: Change the Target Recovery Time of a Database (SQL Server)

    To determine the existing target recovery time, run the following T-SQL statement, where <dbname> is the name of the organization database. A return value of 0 indicates a target recover time has not been set.

    SELECT target_recovery_time_in_seconds FROM sys.databases WHERE name = ‘<dbname>’

    To set the target recover time of a database to 60 seconds, run the following T-SQL statement.

    ALTER DATABASE <dbname> SET TARGET_RECOVERY_TIME = 60 seconds;

For information about SQL Server 2016 performance benefits, see SQL Server Customer Advisory Team blog post: How SQL Server 2016 Cumulative Update 2 (CU2) can improve performance of highly concurrent workloads.

See Also

SQL Server installation and configuration
SQL Server deployment

© 2016 Microsoft. All rights reserved. Copyright