Microsoft Dynamics crm planning Guide

SQL Server installation and configuration

• 
  Microsoft Dynamics CRM requires SQL Server 2005 for storing the databases that contain Microsoft Dynamics CRM data and metadata.
  
• 
  Reports in Microsoft Dynamics CRM depend on SQL Server Reporting Services, an add-in for SQL Server. Reporting Services includes two server components that are used to store, display, and manage reports: Report Server and Report Manager. A third component, Report Designer, is used to customize reports and write new reports. The Report Designer component is typically installed on a workstation, instead of on the computer that is running SQL Server.
  
• 
  Microsoft Dynamics CRM Server Setup does not install SQL Server 2005 or SQL Server Reporting Services.
  

• 
  You can install SQL Server on the same computer as Microsoft Dynamics CRM, on a separate computer, or you can use multiple computers that are running SQL Server.
  
• 
  You can install SQL Server Reporting Services on the computer that stores the Microsoft Dynamics CRM databases, or on a separate report server that is running SQL Server.
  
• 
  Multiple Microsoft Dynamics CRM front-end servers that run in a network load balancing cluster can use the same computer that is running SQL Server.
  

• 
  SQL Server requirements common to most scenarios.
  
• 
  Considerations about how to use one computer that is running SQL Server with multiple computers that are running Microsoft Dynamics CRM Server.
  

SQL Server requirements and recommendations for Microsoft Dynamics CRM

• 
  Microsoft Dynamics CRM requires an installation of Microsoft SQL Server Reporting Services. 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.
  
• 
  Microsoft Dynamics CRM 4.0 is not supported on Microsoft SQL Server 2000.
  
• 
  Microsoft Dynamics CRM 4.0 is not supported on SQL Server that is running on Windows 2000 Server.
  
• 
  If Microsoft Dynamics CRM Server and SQL Server are installed on different computers, they must be in the same Active Directory domain.
  
• 
  Microsoft Dynamics CRM Server Setup supports 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 CRM.
  
• 
  The service account that SQL Server uses to log on to the network must be a domain user account (recommended), the Network Service account, or the Local System account (you cannot 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 can be configured to automatically start when the computer is started.
  
• 
  SQL Server Agent must be started. This service can be configured to automatically start when the computer is started.
  
• 
  SQL Server Full-Text Search must be installed and started.
  
• 
  Microsoft Dynamics CRM Server Setup requires a network library to authenticate SQL Server. By default, both Named Pipes and TCP/IP network libraries are enabled when you install SQL Server 2005. SQL Server can use both TCP/IP and Named Pipes for authentication. However, it 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 Microsoft Dynamics CRM Server.
  
• 
  The computer that is running SQL Server must be configured to have sufficient disk space, memory, and processing power to support the Microsoft Dynamics CRM environment.
  
• 
  Although it is optional, consider accepting the SQL Server default settings for Collation Designator, Sort Order, and SQL Collation. Microsoft Dynamics CRM supports the following collation orders:
  

• 
  Case-sensitive
  
• 
  Case-insensitive
  
• 
  Accent-sensitive
  
• 
  Accent-insensitive
  
• 
  Binary sort order (such as Latin1_General_BIN for SQL Server 2005 or Latin1_General_100_BIN for SQL Server 2008)
  

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

• 
  Make sure that the computer meets the system requirements for SQL Server. For more information, see one of the following:
  

• 
  SQL Server 2005 System Requirements (http://go.microsoft.com/fwlink/?linkid=53211)
  
• 
  SQL Server 2008 System Requirements (http://go.microsoft.com/fwlink/?LinkId=149063)
  

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

• 
  Installing SQL Server 2005 (http://go.microsoft.com/fwlink/?LinkId=149069)
  
• 
  Installing SQL Server 2008 (http://go.microsoft.com/fwlink/?LinkId=149070)
  

• 
  If you plan to install SQL Server in a location other than the default file location, see one of the following:
  

• 
  File Locations for Default and Named Instances of SQL Server 2005 (http://go.microsoft.com/fwlink/?LinkID=149066)
  
• 
  File Locations for Default and Named Instances of SQL Server 2008 (http://go.microsoft.com/fwlink/?linkid=102987)
  

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.

• 
  If you are using an operating system with regional settings other than English (United States), or if you are customizing character-set or sort-order settings, review topics on collation settings. For more information, see International Considerations for SQL Server 2005 (http://go.microsoft.com/fwlink/?LinkId=149067) or International Considerations for SQL Server 2008 (http://go.microsoft.com/fwlink/?linkid=92514).
  

Running SQL Server 2008 with Microsoft Dynamics CRM

SQL Server deployment

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.

Considerations

• 
  Modification of system tables. The SQL Server system tables should not be modified before you install Microsoft Dynamics CRM Server. 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 the Microsoft Dynamics CRM knowledge-base functionality.
  

Language locale collation and sort order

Disk configurations and file locations

The default locations for program and data files are not necessarily the best locations. As noted earlier, 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 chapter.

By default, Shared Tools are installed in \Program Files\Microsoft SQL Server\90\Tools on the system drive. This folder contains the default and named files shared by all instances of SQL Server. Tools include SQL Server Books Online and Dev Tools.

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

SQL Server program file location

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 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 not increase as SQL Server is used.

Program files cannot be installed on a removable disk drive.

SQL Server data file location

• 
  OrganizationName_MSCRM. This is the organization database where Microsoft Dynamics CRM data is stored, such as all records and activities. Microsoft Dynamics CRM 4.0 Enterprise supports multiple organizations so that you can have multiple-organization databases.
  
• 
  MSCRM_CONFIG. This database contains Microsoft Dynamics CRM metadata such as configuration and location information that is specific to each organization database.
  

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 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 prior note about disk fault tolerance and performance.

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, of course, 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 lots of data is being added, changed, or removed from the application.

For example, if you have a server wherein the 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 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 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.

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\Microsoft SQL Server\Mssql.1\Mssql\Data. When you run 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 Setup creates the folders that contain database and log files, in addition to directories for the System log, back-up, and replication data. 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.

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.

For the default instance of SQL Server, the default SQL Server directory name (Mssql.1) 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.1\Mssql\Binn (for program files)

D:\MySqlDir\Mssql.1\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.

SQL Server 2008 transparent data encryption

Additional resources for SQL Server

• 
  Microsoft SQL Server Web site (http://go.microsoft.com/fwlink/?linkid=53219)
  
• 
  SQL Server Books 2005 Online (http://go.microsoft.com/fwlink/?linkid=99647)
  
• 
  Microsoft SQL Server Support Center (http://go.microsoft.com/fwlink/?linkid=92519)