SQL Server deployment

 

Updated: November 1, 2016

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

[This topic is pre-release documentation and is subject to change.]

If your organization uses Microsoft SQL Server for applications other than Microsoft Dynamics 365, performance may degrade as resources are consumed by other applications. If you use a computer that is running SQL Server that is used for other applications, you must carefully analyze the effect that Microsoft Dynamics 365 will have on the existing installation of SQL Server. For information about monitoring SQL Server, see Performance Monitoring and Tuning Tools.

For best results, we recommend that you maintain the Microsoft Dynamics 365 databases on a computer that is running SQL Server and that will support only Microsoft Dynamics 365 and no other databases or database applications.

Microsoft Dynamics 365 is a database-intensive application. Before you deploy Microsoft Dynamics 365 to an instance of SQL Server, you should consider the following requirements and database configurations:

  • Modification of system tables. The SQL Server system tables should not be modified before you install Microsoft Dynamics 365 Server. Some database applications may modify the SQL Server system tables. If this occurs, problems with Microsoft Dynamics 365 and data may result.

  • Indexing. Full-text indexing must be installed. This is required for Microsoft Dynamics 365 knowledge-base functionality.

  • Compatibility level. During an upgrade or a new installation, Microsoft Dynamics 365 Server Setup sets the organization database compatibility level to 100, which is the compatibility level of Microsoft SQL Server 2008.

  • Autogrowth. By default, Microsoft Dynamics 365 organization database files are created to have an autogrowth setting of 256 megabytes. Earlier versions of Microsoft Dynamics 365 used the default setting of 1 megabyte autogrowth. If you perform intensive database transactions, such as large data imports, consider increasing the autogrowth value to improve performance. For information about how to change the autogrowth setting for a database, see the SQL Server Management Studio Help.

  • Max server memory. We recommend that, if you run SQL Server on a computer that is also running other applications, that the SQL Server max server memory be set to no more than one half of the installed RAM. By default, max server memory is set to 2147483647 megabytes in recent versions of SQL Server, which has demonstrated resource issues with SQL Server during intensive use of Microsoft Dynamics 365. More information: MSDN: Server Memory Options

  • Max degree of parallelism. We recommend if you experience poor SQL Server performance, which can occur due to complex index statements, that the SQL Server max degree of parallelism be set to 1 to help improve overall application performance on multiprocessor systems. More information: MSDN: Configure max degree of parallelism Option

  • RCSI. Running Microsoft Dynamics 365 that uses a SQL Server configured for read committed snapshot isolation (RCSI) is supported for use with this version of Microsoft Dynamics 365 Server.

Installing SQL Server in a language other than English (U.S.) may require changing the Collation designator. The following table indicates the Collation designator to use for some of the available languages.

Windows Locale

Locale Identifier (LCID)

Collation Designator

Code Page

Danish

0X406

Danish_Norwegian

1252

Dutch (Standard)

0X413

Latin1_General

1252

English (United States)

0X409

Latin1_General

1252

French (France)

0X40C

French

1252

German (Germany)

0X407

Latin1_General

1252

Italian

0X410

Latin1_General

1252

Portuguese (Brazil)

0X416

Latin1_General

1252

Spanish (Traditional Sort)

0XC0A

Modern_Spanish

1252

For the default instance of SQL Server, the default directory for data files is \Program Files\Microsoft SQL Server\MSSQL<ver>.MSSQLSERVER\MSSQL\DATA, where <ver> is the major version of SQL Server, such as 11 for Microsoft SQL Server 2012. You can specify a file path other than the default for both program and data files.

System_CAPS_noteNote

The default locations for program and data files are not necessarily the best locations. For the best combination of disk fault tolerance and performance, consider the RAID specifications available from hardware vendors. You can create the Microsoft Dynamics 365 databases on your partitions, especially for these files, and specify the existing databases when you run Microsoft Dynamics 365 Server Setup. The databases created by Microsoft Dynamics 365 are noted in the specified data file location. For more information, see SQL Server data file location later in this topic.

By default, tools are installed in \Program Files\Microsoft SQL Server\110\Tools on the system drive. This folder contains the default and named files shared by all instances of SQL Server. Tools include Microsoft SQL Server Management Studio, the T-SQL command line utility, and the OSQL SQL query tool.

Microsoft SQL Server Setup also installs files in the Windows system directory. The system file location cannot be changed.

The SQL Server program files are located in \Program Files\Microsoft SQL Server\MSSQL<ver>.MSSQLSERVER\MSSQL\Binn.

The binary file location is in the root directory where Setup creates the folders that contain program files and other files that typically do not change this path as you use SQL Server. Although these files are not read-only, the folders do not contain data, logs, back-up files, or replication data. Therefore, the space requirements for these files should increase only marginally as SQL Server is used, and over time as updates are applied.

System_CAPS_importantImportant

Program files cannot be installed on a removable disk drive.

Each SQL Server database consists of one or more database files and one or more transaction log files. Microsoft Dynamics 365 creates at least two databases:

  • MSCRM_CONFIG. This database contains Microsoft Dynamics 365 metadata, such as configuration and location information that is specific to each organization database.

  • OrganizationName_MSCRM. This is the organization database where Microsoft Dynamics 365 data is stored, such as all records and activities. Microsoft Dynamics 365 Server supports multiple organizations so that you can have multiple-organization databases.

Microsoft Dynamics 365 also relies on the SQL Server system databases to store Microsoft Dynamics 365 configuration information. These databases include the master and msdb databases. The database files that accompany a database contain all its data and properties. Transaction log files contain a record of the write activity in the database, such as when a row is added, changed, or removed. Transaction log files are binary and cannot be used for auditing database activity.

For backup and recovery information for SQL Server databases including transaction log files, see MSDN: Back Up and Restore of SQL Server Databases.

As described earlier, a Dynamics 365 (on-premises) deployment contains the following databases:

  • A single MSCRM_CONFIG database

  • One or more (for multi-tenant deployments) OrganizationName_MSCRM databases

The configuration database, MSCRM_CONFIG, cannot be renamed. If the MSCRM_CONFIG database is renamed, the Microsoft Dynamics 365 system will not function correctly.

Organization databases, OrganizationName_MSCRM, can be renamed by following the guidelines and considerations described here.

Microsoft Dynamics 365 organization databases use both a display and a unique name.

  • Display name. This is the name that appears in the Microsoft Dynamics 365 application, such as the upper-right corner of the main application screen. The display name can contain spaces and be up to 250 characters long.

  • Unique name. This is the name that is used to create the URL to connect to the application and is appended with _MSCRM. It is also the physical name of the database as it appears in SQL Server applications, such as Microsoft SQL Server Management Studio. This name cannot contain spaces and cannot be more than 30 characters long.

The display name may be changed by using the Edit Organization Wizard in Deployment Manager. The basic steps are to disable the organization, and then run the Edit Organization Wizard. For more information, see the Deployment Manager Help.

Although we do not recommend it, you can change the name of an organization’s unique database name (OrganizationName_MSCRM). To change the database unique name, follow these steps:

System_CAPS_warningWarning

Renaming the unique database name for an organization has not been fully tested by Microsoft and may cause unexpected results. We cannot guarantee that problems caused by performing this procedure can be resolved. Rename the organization database unique name at your own risk.

System_CAPS_importantImportant

Before you start the following procedure, take a full back up of the organization database that you want to rename.

The following steps require you to already have a functioning organization database that was created by Microsoft Dynamics 365 Server Setup or imported by a supported Microsoft Dynamics 365 method.

  1. Restore the backup of the organization database to your SQL Server that uses the name that you want and that is supported by SQL Server.

  2. Import the renamed organization database to your existing Microsoft Dynamics 365 deployment by using the Import Organization Wizard in Deployment Manager.

  3. During the import, enter into the organization database a display name and unique name that are unrelated to the original database name.

  4. Follow the instructions on your screen to complete the import.

  5. Ensure that Microsoft Dynamics 365 users have the new URL that will be created as a result of the organization rename.

The Microsoft SQL Server transparent data encryption (TDE) feature is supported for use with Microsoft Dynamics 365. However, based on test results conducted internally, using this feature can cause a decrease in overall performance of approximately 10% when run against an encrypted database with the same workload.

Currently, Microsoft Dynamics 365 database tables do not support SQL Server in-memory online transaction processing (OLTP). For more information about OLTP, see In-Memory OLTP (In-Memory Optimization)

© 2016 Microsoft. All rights reserved. Copyright

Community Additions

ADD
Show: