BLOG zzy.my

转载 http://blog.insidelync.com/2011/04/the-lync-server-databases/

Microsoft SQL 2012 is NOT supported for all Microsoft Lync 2010 roles yet. The Microsoft KB article “SQL Server 2012 is not supported in some Lync Server 2010 server roles” details which Lync 2010 server roles are not supported.

While investigating a recent Lync issue, I had to reacquaint myself with the main databases used in a Lync deployment. In this blog post, I have documented my experiences for the benefit of others.

The Types and Uses of Databases in Lync

Microsoft Lync Server 2010 makes extensive use of Microsoft SQL. The two flavors of the core Front-End Lync Server role requires SQL:

• ships with, installs, and uses Microsoft SQL Server 2008 Edition (64-bit).Lync 2010 Server Standard EditionExpress
• Lync 2010 Server Enterprise Edition requires Microsoft SQL Server 2008 SP1 or 2005 SP3 (64-bit) on a dedicated server. As of April 20, 2011 support was added for Microsoft SQL Server 2008 R2.

The 32-bit versions of SQL are not supported. You can read more about the supported Database Software prerequisites here: Microsoft Lync 2010 Database Software and Clustering Support.

Lync Server 2010 uses the following SQL databases:

1) Central Management Store (CMS)

The CMS store is used by the Central Management Service to maintain a current Lync Server 2010 Topology for the entire Lync deployment (Topology, Policies, Voice Routes, etc…). Here is what you need to know about the CMS:

• The database used for this purpose is called “”; it maintains the Lync configuration as published by the Topology Builder.Xds
• There is only one master copy of CMS database which is automatically installed on the first instance of a Standard or Enterprise Edition Lync pool. For Enterprise Edition pools this first instance will reside on the SQL back-end database for the pool.
• Every subsequent Lync server in the topology gets a read-only copy of it. A topology change on the master is replicated to each read-only copy on each Lync server. This is a key element to Lync Server’s survivability feature set – if the network connection between a Lync Edge server goes down for example, the Edge server still knows about the topology and can keep functioning.

Changes are replicated to all Lync server roles except the Lync Edge using the Windows file copy SMB protocol on port 445. Changes are replicated to the Edge role via HTTPS on port 4443. The Windows service “Lync server replica replicator agent” is responsible for receiving the snapshot and uploading the local copies of the databases. It then sends a status update to the Master Replicator (also a windows service) running on the CMS.

2) Pool Configuration Store

The pool back-end database is the heart of Lync functionality. The Registrar, User Services, and the Address Book use this database for registration, routing, presence information & conferences, replicating user information, in-band provisioning, and address book functionality. This is commonly referred to the Lync “back-end” database, and one exists for each Lync pool. The following 3 important databases are used for the core feature set of Lync:

• Rtc: stores persistent user data such as user contact lists, scheduled conferences, and access control lists.
• Rtcdyn: stores dynamic Lync user data such as presence information.
• Rtcab & Rtcab1: stores the raw Lync address book information (i.e. that is pulled from AD). The Lync Address Book server alternates use of these databases: one of them is used to service address book queries while the other is being updated. Once the updates are done, they switch roles. Theses databases contain a table called AbAttribute which specifies which AD fields will be used in the Lync Address Book (database and ultimately the Lync address book files). If you are having permission issues with either of these databases, see Access to the Lync Server Address Book Databases.

3) Application Store

Lync server uses the following databases for the Call Park and Response Group applications:

• Cpsdyn: stores dynamic system information for the Call Park application
• Rgsdyn: stores dynamic runtime operational information for the Call Park application
• Rgsconfig: stores persistent configuration data for the Response Group application

4) Archiving and Monitoring Store

This store is used by the Lync Archiving Server Role and the Monitoring Server Role. There are 3 separate databases used:

• LcsLog: stores Instant Messaging and Conferencing data for archiving purposes (used by the Archiving Role).
• LcsCdr: stores the Call Details Records (used by the Monitoring Role)
• QoEMetrics: stores the Quality of Experience data (used by the Monitoring Role)

5) Location Store

Lync server uses this database (named “lis”) to hold a network ‘wiremap’ that maps network elements (e.g. subnet, WAP’s, routers) to real civic addresses to provide the new Location and Emergency Services Support (E9-1-1) features.

All of the individual SQL databases used for each Lync purpose is listed here: SQL Server Data and Log File Placement.

“RTC” – A Source of Confusion

A common source of confusion when trying to understand the Lync databases is the presence of multiple Lync SQL instances and databases which share the name “RTC”. Here is a breakdown:

1. An SQL instance named “RTC” is used to store the back-end pool databases, including the master CMS database. This is installed on the first Front-End server or back-end pool that was installed in the Lync Organization. RTC is the default name for this instance – it can be changed or removed (to use the base SQL instance).
2. Another SQL instancenamed “RTCLOCAL” which is a SQL Express Instance present on each Lync Front-End server which is used to hold a replica of the CMS database, and the databases necessary for registration and routing.
3. The presence of an SQL database named “rtc” which holds the persistent configuration information for the pool (such as user contact lists, scheduled conferences, etc…).

The diagram below illustrates these Lync SQL instances and databases in an example Lync organization with 2 pools and 3 front-end servers. In this organization, the Standard Edition Pool and associated Front-End server was the first to be installed in this organization. Remember, there is only one master copy of CMS database which is automatically installed on the first instance of a Standard Edition or Enterprise Edition Lync pool. This database is shown in red (“xds”). The other “xds” databases shaded in gray are read-only copies replicated to the other 2 Enterprise Edition Front-End servers.

Putting It All Together

PowerShell

One the great features introduced with Lync server is PowerShell access to almost everything in the Lync system. You can install, install, and configure the various Lync databases from PowerShell. See Database and Management Server Cmdlets for a list of Lync Management Server cmdlets.

One of the more useful cmdlet’s is the Get-CsConfigurationStoreLocation cmdlet. This cmdlet will tell you the location of the Active Directory Service Control Point (SCP) for the Central Management Store. This AD location allows Lync server to locate the Lync CMS SQL database (server + instance) – a crucial setting to the operation of Lync.

Note: the default AD location for the CMS SCP object is “CN=Topology Settings, CN=RTC Service, DC=<example.com>”. The msRTCSIP-BackEndServer attribute contains the SQL server and instance name of the master CMS copy.

The back-end pool database is crucial for Lync login and function. The back-end for any pool can be viewed in the Lync Topology builder, or you can retrieve the SQL server and instance name for any back-end pool database using the following PowerShell cmdlets:

1. Get-CsService -Registrar -Pool Fqdn <poolFqdn> (poolFqdn = Fqdn of the pool you want)
2. Get-CsService -UserServer –PoolFqdn <poolFqdn> (note the “UserDatabase” property for step #3)
3. Get-CsService -UserDatabase –PoolFqdn <use the UserDatabase property from step #2>

The SQL user database for the pool is the SqlServer property appended with the \SqlInstance property. You can also be able to do this with the Get-CsTopology cmdlet but it is not as straightforward.

To check the status of the local CMS replica on each Lync role, use the Get-CsManagementStoreReplicationStatus cmdlet. To force the replication services to replicate the CMS database to a specified Lync server, use the Invoke-CsManagementStoreReplication cmdlet.

In addition to the CMS replication and usage, a Lync Front-End server communicates with the back-end pool database using an SQL connection. I believe the Tabular Data Stream protocol (TDS) is used for the SQL queries which can use TCP or named pipes as the transport. The follow references provide further information on this: [MS-SSTDS]: Tabular Data Stream Protocol Version 4.2.

Side-Note:

If you ever need to manually create the databases used by a particular Lync role, check-out the Install-CsDatabase cmdlet. It has come in handy several times. For example, to manually create the Lync Monitoring Databases, you could use this command:

> Install-CsDatabase -DatabaseType Monitoring –SqlInstanceName <SQL Instance> –SqlServerFqdn <SQL Monitoring Backend FQDN>

Similarly, you can remove the databases associated with a particular Lync role with the Uninstall-CsDatabase cmdlet.

Collocation

The back-end database for a Lync pool requires a separate SQL instance, but you can collocate the archiving and monitoring databases with it. You cannot collocate the back-end database with any other Lync server roles including the Front-End server (unless it is a Front-End Standard Edition – in which case the database is installed with the Lync Front-End Server).

Planning the Database Deployment

The location of the Lync SQL database files and associated transaction logs on disk can significantly affect performance. The latter part of this Microsoft TechNet reference as good information the placement and distribution of the database files: SQL Server Data and Log File Placement.

You can view the recommended SQL hardware specifications on TechNet here: Server Hardware Platforms.

If you require custom Microsoft SQL network configurations, such as running the SQL Server back-end instance on a non-standard static SQL port or the requirement to run with the SQL Server Browser service disabled, have a look at this article by Peter Schmatz on NextHop: http://blogs.technet.com/b/nexthop/archive/2011/04/12/using-lync-server-2010-with-a-custom-sql-server-network-configuration.aspx.

Lastly, if you need insight into the type of database performance or impact based on Lync activity, the TechNet article “Database Activity for Capacity Planning” lists the average and peak disk activity for the backend pool databases: http://technet.microsoft.com/en-us/library/gg412771.

Backup and Recovery

A good article on Instruction for Backing up your Lync environment, including backing up the key SQL Lync databases, is available in this Microsoft TechNet blog article:

>http://blogs.technet.com/b/uc_mess/archive/2011/03/17/lync_2d00_server_2d00_2010_2d00_backup_2d00_instructions.aspx

Troubleshooting Database Connectivity Issues

Access to SQL databases and SQL permissions can be a complex topic. Here are two common issues you can check however if you suspect a Lync component is having difficult accessing one of it’s databases.

1] Ensure the Appropriate Lync Server Firewall Rules were added to the local Windows Firewall Service.

During an install of Lync 2010 Server, the necessary rules are added to the local Windows Firewall to allow the various Lync services to talk to the SQL databases. I’ve seen cases where there were only a small subset or none of these rules, and components such as the Monitoring Role cannot contact the monitoring store (SQL database).

This can happen if the Windows Firewall service was stopped during part or all of the Lync Install. The good news is that you just need to run the Lync bootstrapper on the local machine (start the Lync Server Deployment Wizard | Install or Update Lync Server System | Setup or Remove Lync Server Components) and it will check and add any missing rules. Doug Deitterick has an excellent post about this.

2] Check the Permissions on the SQL Server.

For installing Lync components with the Lync Administrator and currently logged on user (should be the same account) needs to have SQL system administrator access – which amounts to having the Microsoft SQL fixed system administrator role named “sysadmin“ on the SQL Instance in that the Lync component needs to leverage.

More Information

1. A very good write-up on the CMS: What is Central Management Store (CMS): http://blogs.technet.com/b/jenstr/archive/2010/10/13/what-is-central-management-store-cms.aspx
2. Schema References for the CDR, QoE, and Group Chat Databases: http://technet.microsoft.com/en-us/library/gg398370.aspx
3. Microsoft Lync Server 2010 Enterprise Edition Deployment Guide: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=4cf4bed4-2f76-4b99-adcb-60653521cc70
4. Microsoft TechNet - Permissions Required for Database and Lync Server Installation
5. Microsoft TechNet SQL Server – Permissions Hierarchy (Database Engine)