SQL Server Data and Log File Placement

 

Topic Last Modified: 2011-05-11

A critical exercise in the planning and deployment of Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2 for your Microsoft Lync Server 2010, Enterprise Edition is the placement of data and log files onto physical hard disks for performance. The most critical files for performance are the Rtcdyn log and the Rtc log. Placing these files on their own hard disk or redundant array of independent disks (RAID) set is the optimal scenario. The database files and what they are responsible for is detailed in the following table.

Data and Log Files for Central Management Store

Central Management store database files	Data file or log purpose
Xds.ldf	Transaction log file for the Central Management store
Xds.mdf	Maintains the configuration of the current Lync Server 2010 topology, as defined and published by Topology Builder
Lis.mdf	Location Information service data file
Lis.ldf	Transaction log for the Location Information service data file

Data and Log files for User, Conferencing, and Address Book

Core Lync Server 2010 database files	Data file or log purpose
Rtcdyn.mdf	Maintains transient user data (presence runtime data)
Rtcdyn.ldf	Transaction log for Rtcdyn data
Rtc.mdf	Persistent user data (for example, access control lists (ACLs), contacts, Standard Edition server or Enterprise Edition Front End pool, scheduled conferences)
Rtc.ldf	Transaction log for Rtc data
Rtcab.mdf	Real-time communications (RTC) address book database is the SQL Server repository where Address Book service information is stored
Rtcab.ldf	Transaction log for Address Book Service
Rtcab1.mdf	Real-time communications address book database is the SQL Server repository where Address Book service information is stored. (Secondary copy for performance)
Rtcab1.ldf	Transaction log for Address Book service

Data and Log Files for Call Park and Response Group

Application database	Data file or log purpose
Cpsdyn.mdf	Dynamic information database for the Call Park application
Cpsdyn.ldf	Transaction log for Call Park application data file
Rgsconfig.mdf	Lync Server Response Group service data file for the configuration of the services
Rgsconfig.ldf	Transaction log file for the Response Group application configuration
Rgsdyn.mdf	Response Group service data file for runtime operations
Rgsdyn.ldf	Transaction log for the Response Group service runtime data file

Data and Log Files for Archiving and Monitoring Server

Archiving and Monitoring database files	Data file or log purpose
LcsCdr.mdf	Data store for the call detail recording (CDR) process of the Monitoring Server
LcsCdr.ldf	Transaction log for call detail recording (CDR) data
QoEMetrics.mdf	Quality of Experience data file stored from the Monitoring Server
QoEMetrics.ldf	Transaction log for Monitoring data
Lcslog.mdf	Data file for the retention of instant messaging and conferencing data on an Archiving Server
Lcslog.ldf	Transaction log for Archiving data

In this topic, references are made to disk and to RAID set. Note that in the configuration of SQL Server resources, referring to a disk means a single hardware device. A hard disk drive with two partitions, one holding log files and the other partition holding data files, is not the same as two disks, each dedicated to either log or data files.

In reference to RAID sets, there are a number of different RAID technologies from various vendors. And, with the proliferation of storage area networks (SAN), RAID sets dedicated to a single system are rarer. You should consult with your RAID or SAN vendor to determine what the best configuration is for your disk layout when configuring for SQL Server performance with Lync Server 2010.

Note also that not all disk drives are created equally; some perform better than others. Even drives from the same manufacturer can vary in performance because of rotational speed, hardware cache size, and other factors. It is advisable that you test your disks to determine the better performing disks, and then place the more critical and speed sensitive log and data files – specifically, Rtcdyn log and Rtc log – onto the better performing disks.

Disk and Database File Distribution

There are a number of potential solutions for the placement of the files. Each of the possible combinations has pros and cons. The optimal distribution requires six hard disks or distinct RAID sets. We recommend that the two most active files, the Rtcdyn log file and Rtc log file, should always be placed on their own disks or RAID set.

Six-Disk Distribution

The distribution scenario using six disks is typically only used in cases where you want to collocate the Archiving and Monitoring databases on the same SQL Server. This solution uses six physical disks or dedicated RAID sets. The distribution of the data and log files is shown in the following diagram.

Advantages – Very high performing, low latency, and little contention for disk I/O when compared to other solutions.

Disadvantage – More costly than other solutions. At minimum, you require six hard disks.

Five-Disk Distribution

The five-disk distribution uses five disks or five RAID sets. The Rtcdyn and Rtc log files are separated onto their own disks. Archiving and Monitoring logs and data are placed onto their own disks, respectively. The remaining log files and data files are placed on the fifth disk.

Advantages – Provides good performance for the Rtcdyn and Rtc log files, while reducing the overall number of disks required.

Disadvantages – All other log files are located on a single disk, potentially causing I/O performance latency for other workloads. Impact is minimized if the Archiving Server, the Monitoring Server, or both are not deployed.

Four-Disk Distribution

The four-disk distribution uses four disks or four RAID sets. This configuration is considered to be the best performing of the recommendations due to the low latency and low contention for disk I/O resources. The Rtcdyn and Rtc log files are separated onto their own disks. The remaining log files are placed on the third disk, while the data files are placed on the fourth.

Advantages – Provides good performance for the Rtcdyn and Rtc log files, while reducing the overall number of disks required.

Disadvantages – All other log files are located on a single disk, potentially causing I/O performance latency for other workloads. Impact is minimized if the Archiving Server, the Monitoring Server, or both are not deployed.

Three-Disk Distribution

The Three Disk Distribution uses three disks or RAID sets. The Rtcdyn and Rtc log files are placed on drive one and drive two. The remaining log and data files are placed on disk three.

Advantages – Less costly than six and four disk solutions. Placing the Rtcdyn and Rtc log files on their own hard disks allows for good performance for the frequently updated transaction log files.

Disadvantages – All other log and data files are placed on the remaining disk. Increased potential for latency due to increased I/O of all other workloads on the single disk. If Monitoring and Archiving are not deployed, the impact is lessened, but performance impact is still a concern.

Two-Disk Distribution

Two-disk distribution uses two hard disks or RAID sets. The Rtcdyn and Rtc log files are both placed on the first disk. All other log and data files are placed on the second hard disk.

Advantages – Lowering of cost is the primary advantage. Log and data files for other workloads on the second disk will lessen the overall impact on the Rtcdyn and Rtc log files.

Disadvantages – Combining the Rtcdyn and Rtc log files will begin to negatively impact performance.

Single-Disk Distribution

The single-disk distribution uses a single hard disk or RAID set. The Rtcdyn and Rtc log files are placed on the single disk along with the other log and data files.

Advantages – significantly lower cost than other solutions. Small user numbers may be able to experience acceptable performance.

Disadvantages – Disk latency and disk loading from all workload logs and data files on the single disk will have an impact on performance. Deployment of Monitoring and Archiving roles will impact performance further.