SQL Server deployment
Applies To: Dynamics CRM 2013
If your organization uses Microsoft SQL Server for applications other than Microsoft Dynamics CRM, 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 CRM will have on the existing installation of SQL Server. For information about monitoring SQL Server, see Performance Monitoring and Tuning How-To Topics.
For best results, we recommend that you install the Microsoft Dynamics CRM databases on a computer that is running SQL Server and that will support only Microsoft Dynamics CRM and no other databases or database applications.
In This Topic
SQL Server deployment considerations
Language locale collation and sort order
Disk configurations and file locations
SQL Server program file location
SQL Server data file location
Microsoft Dynamics CRM database renaming considerations
SQL Server transparent data encryption
SQL Server deployment considerations
Microsoft Dynamics CRM is a database-intensive application. Before you deploy Microsoft Dynamics CRM 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 CRM Server 2013. Some database applications may modify the SQL Server system tables. If this occurs, problems with Microsoft Dynamics CRM and data may result.
Indexing. Full-text indexing must be installed. This is required for Microsoft Dynamics CRM knowledge-base functionality.
Compatibility level. During an upgrade or a new installation, Microsoft Dynamics CRM Server Setup sets the database compatibility level to 100, which is the compatibility level of Microsoft SQL Server 2008.
Autogrowth. By default, Microsoft Dynamics CRM organization database files are created to have an autogrowth setting of 256 megabytes. Earlier versions of Microsoft Dynamics CRM 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 Microsoft SQL Server 2008 and Microsoft SQL Server 2012, which has demonstrated resource issues with SQL Server during intensive use of Microsoft Dynamics CRM. More information: 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: max degree of parallelism Option
RCSI. Running Microsoft Dynamics CRM that uses a SQL Server configured for read committed snapshot isolation (RCSI) will receive commercially reasonable support. Commercially reasonable support is defined as all reasonable support efforts by Microsoft Customer Support Services that do not require Microsoft Dynamics CRM code fixes
Language locale collation and sort order
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 |
Disk configurations and file locations
For the default instance of SQL Server, the default directory for both program and data files is \Program Files\Microsoft SQL Server\MSSQL<ver>.MSSQLSERVER\MSSQL\, where <ver> is the major version of SQL Server, such as 10 for Microsoft SQL Server 2008 or 11 for Microsoft SQL Server 2012. You can specify a file path other than the default for both program and data files.
Note
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 CRM databases on your partitions, especially for these files, and specify the existing databases when you run Microsoft Dynamics CRM Server Setup. The databases created by Microsoft Dynamics CRM are noted in the specified data file location. For more information, see SQL Server data file location later in this topic.
By default, Shared Tools are installed in \Program Files\Microsoft SQL Server\100\Tools on the system drive. This folder contains the default and named files shared by all instances of SQL Server. Tools include 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.
SQL Server program file location
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.
Important
Program files cannot be installed on a removable disk drive.
SQL Server data file location
Each SQL Server database consists of one or more database files and one or more transaction log files. Microsoft Dynamics CRM creates at least two databases:
MSCRM_CONFIG. This database contains Microsoft Dynamics CRM metadata, such as configuration and location information that is specific to each organization database.
OrganizationName_MSCRM. This is the organization database where Microsoft Dynamics CRM data is stored, such as all records and activities. Microsoft Dynamics CRM Server 2013 supports multiple organizations so that you can have multiple-organization databases.
Microsoft Dynamics CRM also relies on the SQL Server system databases to store Microsoft Dynamics CRM 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.
The transaction log is used for recovery, if a failure occurs, and to roll back (undo) transactions (writes) that cannot be finished. You may also periodically back up the transaction log as a way to perform an incremental backup while users are working in the application, with very low effect on available server resources.
To have the best chance of recovery if there is a disk failure, and the best performance for the application, put the database files and transaction log files on separate sets of physical disks. The location that you specify for a file does not have to be the original location for data files specified during Microsoft SQL Server Setup. You can select an alternative location for the database and transaction log files any time that you create or change the database. For more information, see the note about disk fault tolerance and performance in Disk configurations and file locations earlier in this topic.
If the partition that contains a database file has failed and the database has become unusable, but the partition that contains the transaction log is still available, you can back up the transaction log for that database. This can be the last backup in your back-up set. When you restore, this transaction log backup, made after the failure, will be the last restored backup. If all transaction log backups in the back-up set are restored successfully, you will have restored all the committed (100 percent successful) transactions up to the moment of the failure. This limits the data loss.
When the database files and transaction log files are on separate sets of disks, performance is optimized. Transaction log files can be write-intensive during periods when a lot of data is being added, changed, or removed from the application.
For example, you have a server wherein drive C is the system partition (the drive where the Windows and program file folders are located).The Windows pagefile is also located on drive C. Drives D and E are RAID-5 partitions on separate sets of physical disks. Select the partitioning scheme for the database files that will give you the combination of performance and disk fault tolerance that you want. Drive D contains only data files for one or more databases, and drive E contains only log files for one or more databases. If you verify that performance will decrease because one database will have much more hard disk activity than other databases, you should put them all on separate sets of disks. If you estimate that data will significantly grow over time, make sure drive D has at least 100 gigabytes (GB) available for the database files. Because the log files will be truncated every time that a transaction-log backup is performed, make sure drive E has at least 10 GB available. Specify the location of the database file to be on drive D and the transaction log file to be on drive E when you create the database.
Note
It is best to dedicate a partition to SQL Server data files. We recommend that you do not put a data file on the same partition as a Windows pagefile because of the degree of fragmentation that will occur.
By default, the directory where all database files and transaction log files are located is \Program Files\SQL Server\MSSQL<ver>.MSSQLSERVER\MSSQL\Data. When you run Microsoft SQL Server Setup, you can specify a different location as the default location for data files. The data file location is the root directory where Microsoft SQL Server Setup creates the folders that contain database and log files, in addition to directories for the System log, back-up, and replication data. Microsoft SQL Server Setup creates database and log files for the master, model, tempdb, and msdb databases. If you are selecting different locations for each file in the application, you do not have to change the default setting.
Note
Data files cannot be installed on a file system that uses compression.
Specifying file paths
Because you can install multiple instances of SQL Server on one computer, an instance name is used in addition to the user-specified location for program and data files. For tools and other shared files, instance names are not required.
Default-instance file path for program and data files
For the default instance of SQL Server, the default SQL Server directory name (MSSQL.10) is used as the default instance name, with the directory that you specify.
For example, if you specify the SQL Server default instance to be installed on D:\MySqlDir, the file paths are as follows:
D:\MySqlDir\MSSQL<ver>.MSSQLSERVER\MSSQL\Binn (for program files)
D:\MySqlDir\MSSQL<ver>.MSSQLSERVER\MSSQL\Data (for data files)
Note
The program and data file locations can be changed, depending on the drive configuration of the computer that is running SQL Server.
Microsoft Dynamics CRM database renaming considerations
As described earlier, a Microsoft Dynamics CRM 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 CRM system will not function correctly.
Organization databases, OrganizationName_MSCRM, can be renamed by following the guidelines and considerations described here.
Organization database names
Microsoft Dynamics CRM organization databases use both a display and a unique name.
Display name. This is the name that appears in the Microsoft Dynamics CRM 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.
Organization database renaming
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:
Warning
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.
Important
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 CRM Server Setup or imported by a supported Microsoft Dynamics CRM method.
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.
Import the renamed organization database to your existing Microsoft Dynamics CRM deployment by using the Import Organization Wizard in Deployment Manager.
During the import, enter into the organization database a display name and unique name that are unrelated to the original database name.
Follow the instructions on your screen to complete the import.
Ensure that Microsoft Dynamics CRM users have the new URL that will be created as a result of the organization rename.
SQL Server transparent data encryption
The Microsoft SQL Server Transparent Data Encryption feature is supported for use with Microsoft Dynamics CRM. 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.
See Also
SQL Server installation and configuration
SQL Server requirements and recommendations for Microsoft Dynamics CRM
Additional resources for SQL Server
© 2016 Microsoft Corporation. All rights reserved. Copyright