The Lync Server Databases

June 7th, 2012 UpdateMicrosoft 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:

  • Lync 2010 Server Standard Edition ships with, installs, and uses Microsoft SQL Server 2008 Express Edition (64-bit).
  • 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 “Xds”; it maintains the Lync configuration as published by the Topology Builder.
  • 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 instance named “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.

To check whether a user has sysadmin rights to a particular SQL instance, follow these steps (as outlined in http://support.microsoft.com/kb/946686).
I will summarize them here with a couple of slight modifications:
  • Start SQL Server Management Studio and Connect to the SQL Instance.
  • Expand the Security node, and then click Logins.
  • Find the Windows User (Lync Administrator or Group) and right-click the user’s name, and then click Properties.
  • Click the check-boxes to give them these two roles:
    • sysadmin
    • public

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)
Be Sociable, Share!

90 comments to The Lync Server Databases

  • Mike

    Curtis,
    You were right about it not being the “Publish Topology” that does it, it’s simply installing the SQLExpress, and the link you gave me on installing 2013′s databases on separate drives doesn’t really say how to keep them off of C when you only have 2 drives. And today, I found out that I can’t get the backward-compatible SQL 2005 that is installed by Lync to go anywhere but C: drive so I have more research to do on that as well.
    As far as the Forums go, I’ll hit them up tonight… have to check on some other posts in there anyway (smile).

    Thanks for the pointers and the reply.
    Mike

  • Mike

    Curtis,
    I am running up against “security” and their requirement that no data files are on the C drive. I found a way to install SQLExpress (RTC and RTCLocal) on the D drive (Lync 2010 Standard Ed), but when I publish the topology, SQL logs end up on the C drive (CsData folder) which I can’t find a way to stop. I know I’m probably just missing something minor in my call-out of SQLExpress. I copied the command line used by the installer, and added /installshareddir, /installsharedwowdir, /instancedir to keep all the data on D.

    Is the problem with the way SQLExpress is being installed or the way the topology is being published? Or is there no way I can control the creation of the CdData folder on C drive and will have to find a way to justify it’s existence?

    Thanks for the info!
    Mike

  • Ralf

    Hi Curtis,
    I have running a Lync 2013 enviroment with enterprise edition pool. Monitoring server is running on the back end SQL. There is still a Lync 2010 standard edition server running and a seperate Lync monitoringserver in SQL express. Is it possible to move the monitoring to the new one. In topology builder I can’t get in the Fqdn of the 2013 monitoring server.

    • Hi Ralf,

      It is not possible to ‘move’ your existing Lync 2010 Standard Edition monitoring role to the new one. You need to install a new Lync 2013 monitoring role and associate the Lync pool with the new monitoring role.

      re: “In topology builder I can’t get in the FQDN of the 2013 monitoring server”.

      In Lync Server 2013 the Monitoring role (and Archiving) are collocated on the Front End Server (you just enable it). Then in the pool properties (in Topology Builder under Monitoring (CDR and QoE)), you specify the FQDN of the monitoring SQL server store. Is monitoring enabled on the pool and are you able to enter the FQDN of the SQL server store that houses the Monitoring db? Note: you will have to create one (using the “New…” button next to where you specify the FQDN) if you have never created one before.

      Hope that helps,
      Curtis

  • Chris Wood

    Hi Curtis,

    I have inherited the Lync server as a DBA, I have managed to get into the db via the back door as all admin passwords had been lost. I now noticed that the instance has crashed as the LcsLog database has reached the 4GB maximum for express edition. Can you tell me where i can configure it to remove older messages? or is it safe to remove the messages directly from the dbo.message table in the LcsLog database ?

    I tried to fire up the Lync Control Panel as read something about an archiving tab, but it says it cannot find appopriate URL and ask me to enter a location. What should I be entering here ?

  • Tom

    Hi Curtis,

    Great info! I am looking at extracting user and usage data from Lync for billing purposes. Eg. what services is a user provisioned for, etc. Do you know if Lync already has some interfaces to get this data or should I just go direct to the DB? If I go direct to the DB do you know which tables since info resides?

    Thanks,

    Tom.

    • Hi Tom,

      There are several interfaces to get Lync data. For user configuration and provisioning the best is PowerShell – specifically the Lync Management Shell (which is PowerShell with the Lync management module loaded). Microsoft TechNet has a comprehensive Lync Powershell reference (http://technet.microsoft.com/en-us/library/gg398306.aspx), or you can use my Lync PowerShell Cheat Sheet to get started (http://www.insidelync.com/PSCheatSheet).

      The get user usage data you are likely best to go direct to the CDR database and possible the QoE database depending on what you need). If you looking in the “More Information” section at the bottom of my blog post, I have a link to the Schema References for the CDR, QoE, and Group Chat Databases.

      Fyi – if you are looking for a complete solution, the company I work for, Quest Software (now Dell) has a Lync usage and chargeback/billing solution called MessageStats (http://www.quest.com/messagestats/).

      Hope that helps,
      Curtis

      • Tom

        Thanks Curtis! Is it possible to get all users and changed users since x date using PowerShell?

      • Tom

        Hi Curtis, I have been looking into PowerShell and it looks very cool. To clarify my last question is it possible through PowerShell to get the user policy changes along with the date of the change? Basically to see the user version history over time.

        Thanks,

        Tom.

  • Curtis,

    I love this article and I refer back to it constantly. Would you be able to make a similar one for 2013 soon. I know the DB’s are similar, but would love to have a similar article to link others to for an explanation.

    Kind Regards,
    Chris

  • Ketan Shah

    Do you know, backend database updates are required with October 2012 Lync Server 2010 update (CU7)?

    From update description it is not clear that this is required or not.

    Thanks,

    Ketan Shah

  • Baf

    Hi! Do you know where I can find info about schema of the rtcab database?

    P.S. What about adding subscription on comments plugin to your blog? It would be very usefull :)
    It is embeded in the WordPress JetPack

  • Baf

    Hi!

    Can you explain for what purposes if I want to install standalone Monitoring server on 2008 SQL Standart and add it to the SE pool I need also to install RTCLOCAL instance on the SQL Express? If RTCLOCAL instance is so needed for functioning, why it is not using already installed SQL Standart?

    • Hi,

      You do not need to install the RTCLOCAL instance on your Monitoring server SQL backend. Lync will automatically install an RTCLOCAL instance for you (that holds the CMS – in the Xds database). Lync will use whatever backend SQL standard edition you specify for the Monitoring role.

      Curtis

      • Baf

        Can you explain a bit more?

        1. On the Monitoring server there would be 2 instances of SQL Server or not?
        2. If no, why I have 2 instances? :D Without installing RTCLocal via Lync Setup I can’t get LcsCDR и QoEMetrics DB on Monitoring server, so I can’t install any reports. May be this is because I’m testing Standart Edition Lync?

        • There will be 2 SQL instances used on the monitoring backend SQL server:
          #1] The Default SQL instance will hold the monitoring databases (LcsCDR, QoEMetrics, etc…), and,
          #2] The RTCLOCAL instance is used to hold the CMS Xds database.

          Microsoft TechNet “Adding a Monitoring Server to the Topology” explains how to add the Monitoring server to your Topology by specifying the FQDN of the monitoring server and selecting the SQL instance to use as your SQL server backend (or you can create a new SQL store I believe). You do not have to manually create the SQL instance yourself (by default). There is also instructions on how to associate a Lync Standard Edition pool with a Monitoring Server.

          It just occurred to me that your confusion is likely with the part of deployment where you must also install an “instance” of SQL Server Reporting Service (aka SRS) – NOT to be confused with an instance of SQL – which is needed to deploy the actual Lync Monitoring Server Reports. This is confusing because the documentation refers to an installation of SQL Server Reporting Services as an “instance”. An instance of SRS = some software (Windows service), an SQL database (called ReportServer), and some data (the actual reports).

          The SQL database used by the Lync Monitoring Reports (db name = “ReportServer”) can exist in the same SQL instance as your Lync monitoring server databases (the default SQL instance) and does not require another SQL instance.

          Normally you install and configure an instance of SRS when you install the SQL server that you will use for the monitoring role – see Installing SQL Server with Reporting Services.

          And then part of Deploying the Monitoring Server Reports is to specify this “instance” of SQL Server Reporting Services.

          Regardless, to answer your question there are two instances used (the default instance for the monitoring data and likely the reports themselves, and the RTCLOCAL instance of the CMS database). There could be a third if you optionally created a completely separate SQL instance for the SRS database.

          Hope that helps,
          Curtis

  • Freddie

    My Trial Lync server DB failed, hardware failure. Now I cannot reinstall because the database cannot be accessed. So my question to you, is it safe to wipe the DB and rebuild?

    Running script: C:\Windows\system32\cscript.exe //Nologo “C:\Program Files\Common Files\Microsoft Lync Server 2010\DbSetup\xdssetup.wsf” /setsecurity /sqlserver:lync.MYDOMAIN.net\rtc /publisheracct:MYDOMAIN\RTCUniversalServerAdmins;”RTC Local Administrators” /replicatoracct:MYDOMAIN\RTCUniversalConfigReplicator;”RTC Local Config Replicator” /consumeracct:MYDOMAIN\RTCUniversalReadOnlyAdmins;”RTC Local Read-only Administrators” /role:master /verbose
    —————
    Installed SQL Server 2005 Backward Compatibility version is 8.05.2312
    Connecting to SQL Server on lync.mydomain.net\rtc
    SqlMajorVersion : 10
    SqlMinorVersion : 0
    SqlBuildNo : 2531
    SQL version is acceptable: 10.0.2531.0
    Default database data file path is C:\Program Files\Microsoft SQL Server\MSSQL10.RTC\MSSQL\Data
    Default database data file path is C:\Program Files\Microsoft SQL Server\MSSQL10.RTC\MSSQL\Data
    Default database log file path is C:\Program Files\Microsoft SQL Server\MSSQL10.RTC\MSSQL\Data
    Opened database xds
    xds database is not available for use. Status = 256
    —————
    Exit code: ERROR_DATABASE_NOT_AVAILABLE (-11)
    —————

  • When gaps or errors are encountered in the official Lync documentation, please let us know by emailing lyncdoc@microsoft.com. Or talk to DrRez on Twitter or Facebook. It’s always helpful to include a link to the specific doc in question. We care. We are listening.

  • Frustrated Guy

    I am building a Enterprise Edition starting with 1 front end first without Archiving and monitoring

    However, the Back-End SQL server is located in another forest with two way trust

    When I complete all the steps (Prepare Schema, Topology build/publish, Install Local store (Step1), Install components (Step2), certificates (Step3)

    When I try to start the services, Front End service stay at “Starting” for a very long time.

    I look into the event viewer and saw the following even

    “Lync Server Service Startup Pending as Registrar BackEnd is not yet Available.

    Service Startup will be delayed until the local Registrar BackEnd become available”

    Another error is about the SQL server in different forest

    “Failed to connect to back-end database. Lync Server will continuously attempt to reconnect to the back-end. WHile this condition persists, incoming messages will receive error responses.

    Back-end server: SQLserverFQDN Database:rtc Connection string of:…….etc

    What should I do? Please advice

    • Hi,
      I have never experienced that error. You should search for similar errors and post this one on the Microsoft Lync Forums: http://social.technet.microsoft.com/Forums/en-US/category/ocs.

      You should try connecting to the back-end SQL pool database with SQL Management Studio on another machine – this will verify that the database is accessible remotely.

      Hope that helps,
      Curtis

    • Richard

      I have seen exactly this kind of behavior in a huge multinational worldwide organization, where dozens of AD domains and forrests were deployed, and about approx. 250.000 users (read: a quarter million AD users) were discovered. In such scenario, it took for Lync about 2-3 hours to contact and query all the domains in all the forrests for all the user data. After 2-3 hours, the RTCAB and RTCAB1 databases grew 3-4 Gigabytes, the syncronization was finished and the Front End service finally finished starting and communicating with the BackEnd properly.

  • Horace Bigley

    Should Number 2 be Content Management Store or Central Management Store?

    • I think you are referring to #1 … yes, it should be “Central” not “Content” (I’ve seen a couple of early references that use the “Content” version to describe to specific Xds database but “Central” is used much more often). I’ve corrected it, thanks for the feedback.

      Curtis

  • Josh Lynch

    Thanks Curtis, just wanted to be sure.
    Also, I just published my topology and all is well on that, except that on my sql server, I had created a second volume (D:\) for DBs and Logs (SAN storage).
    I noticed that it placed basically everything for CSData on the C drive and not where my default DBs in database settings are set to point to.

    Is there some special process for telling Lync where I want to redirect the DBs to, or can I just change the location in SQL and move everything to point to D:\ as with any SQL deployment?

    Josh

  • Josh Lynch

    Curtis,

    So in enterprise config, do I need to create an RTC instance on the BE sql server before installing the pilot lync 2010? or when i run “define new front end pool” on define the sql store, do i need to just do a default instance or create \RTC instance as well and select that?

    Josh

  • Cheng Wang

    Hi
    I have a problem that is when I excute the the PowerShell cmdlet(Get-CsSite),it will wait for about 10 seconds,then response me with a SQL exception,just like this:

    SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified

    so how can i solve the problem,thanks very much.

  • Rick

    Curtis,

    I am attempting to do a Lync 2010 SE trail and when I am running the Lync Server Setup components I am getting an error in the DB setup (below). I am at a bit of a loss as there is not a lot of infor out there on this. I welcome your thoughts……

    —————
    Installed SQL Server 2005 Backward Compatibility version is 8.05.2312
    Connecting to SQL Server on SERVER FQDN\rtc
    SqlMajorVersion : 10
    SqlMinorVersion : 0
    SqlBuildNo : 2531
    SQL version is acceptable: 10.0.2531.0
    (Database rtcdyn doesn’t exist.)
    Creating database rtcdyn
    DbFile : PhysicalName = c:\csdata\backendstore\rtc\dyndbpath\rtcdyn.mdf
    LogFile: PhysicalName = p:\csdata\backendstore\rtc\dynlogpath\rtcdyn.ldf
    Failed to create database rtcdyn
    Error (
    name: Error
    description:
    number: -2147216381
    message:
    )
    —————
    Exit code: ERROR_CREATE_DB (-4)
    —————

    • Hi Rick, yes I have heard of this issue before. The SQL instance you are using for the Lync file store is using a non-default model database (specifically the initial size and auto growth configuration).

      I am not experienced enough in the SQL world to know how this happens but you can read about the issue and how to resolve it in this Microsoft KB article: Lync Server 2010 Install-CsDatabase command returns the error – Failed to create database (http://support.microsoft.com/kb/2502792).

      Hope that helps,
      Curtis

  • Mark

    “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.” Re the comment about Enterprise Edition pool, it might be worth making it clear that the first instance in a EE deployment is the SQL back end, so actually, the CMS master is on the SQL back end. (When I first read this sentence, I mis-understood it to mean that the CMS master will be on the first Lync FE server installed!) This is described in more detail here: http://blogs.technet.com/b/jenstr/archive/2010/10/13/what-is-central-management-store-cms.aspx.

  • john

    Hi,
    I have a problem wtih LYNC what it means? can you plz. help me…
    Alert: Failed to connect to the back-end database
    Source: LS Call Details Reporting Component [uccpoclync02.ucc.bns]
    server=(local)\lyncgcmon;
    database=LcsCDR

  • Sam A

    We are in the planning stages of moving our backend SQL from a standalone SQL server to a new cluster. There is little to no MSFT documentation on this and what we have found, while seemingly accurate, needs to be tested to ensure we don’t hose ourselves.

    Do you know of either a documented MSFT procedure for this or, have any experiences of your own you can provide?

    thanks

  • Janne

    Hi. I have been searching for info about whether the collation of the SQL instance is important or not? Do you have any info on this?

    • Hi Janne, just to clarify your question – do you mean #1] “collocation”, as in, what other Lync SQL instances can be installed on the same SQL server, or are you asking about #2] database ‘collation’ (as in how data is sorted and compared)?

      For #2 I do not have any information on this.

      For #1, the TechNet document “Server Collocation in an Enterprise Edition Front End Pool Deployment” gives a good explanation of this:

      Databases
      You can collocate each of the following databases on the same database server:

      Back-end database
      Monitoring database
      Archiving database

      You can collocate any or any or all of these databases in a single SQL instance or use a separate SQL instances for each, with the following limitations:

      Each SQL instance can contain only a single back-end database, single Monitoring database, and single Archiving database.
      The database server cannot support more than one Front End pool, one Archiving Server, and one Monitoring Server, but it can support one of each, regardless of whether the databases use the same SQL instance or separate SQL instances.
      You can collocate a single Monitoring Server, a single Archiving Server, or both on with the databases. You can also collocate a file share with the databases, as described later in this section.

      You should not collocate the Group Chat database and compliance database (for Group Chat Server) with any database server or with Group Chat Server. These collocations are not supported.

      Hope that helps,
      Curtis

  • Matt

    Hi,

    Im getting loads of errors on the lync monitoring server for the archiving server (event ID 30633):

    The service failed to purge records from the database or files on data compliance folder

    SQL Native Error: 229
    Error Code: 0
    Cause: This can occur if there is any error in SQL Server, the data conferencing server file share is unavailable, the Archiving service does not have write permissions, or the network connections between the Archiving Server and the data conferencing server file share are not operating correctly.
    Resolution:
    Check the archiving database, data conferencing server file status, access permissions, and network connections.

    Any ideas? they get generated every minute! I cant find anything on this error at all.

    Thanks
    matt

    • Hi Matt,

      I have never experienced that, but the error message looks explicit – the archiving service periodically purges records from it’s database and the compliance folder, and it appears it is encountering an error while doing so.

      The error details indicate that it is an SQL problem (SQL error 229). It looks like the Archiving Service credentials might not have the appropriate permission to purge records from the database. I would start by ensuring that the account the Archiving Service is running under has the ability to purge from the Archiving database.

      In addition to the SQL permissions you might want to check that the data conferencing file share a] available from the monitoring server, and b] with the credentials that the archiving service is running under (it needs write permissions to purge from this folder).

      Hope that helps,
      Curtis

  • martin

    Having a secondary SE for failover works. I have set it up in production. You have to correctly configure topology and DNS SRV, but it is easily set up and clients registers.

    You have restricted functionality such as no contact list, and one is a pain for us: no response group (it is a local application).

    When it comes to PSTN it gets a bit more tricky: Inbound/outbound pstn calls work, their is a trick to use the same gateway with 2 different mediation servers pool, and you have to configure you gateway for alternative routing as well for incoming.

    http://technet.microsoft.com/en-us/library/gg398971.aspx
    To solve multiple Mediation Servers interacting with the same gateway peer entity, you need to configure multiple virtual gateways. Each gateway would be associated with a different FQDN, which DNS would resolve to the same IP address.

    and of course it results in a tls issue if fqdn are different thus:
    For this scheme to work for gateway peers that support TLS, the FQDN for each virtual gateway needs to be in the subject name or subject alternative name part of the certificate provided by the gateway peer.

    our audiocodes could not create a CSR with a alternative name, we had to add this attribute when requesting to the CA, which must allow adding SAN when signing certs (not the default in Windows CA).

    • Hi Martin,
      Thanks for confirming a production case that did that and the other information.

      One thing that caught my eye was “You have restricted functionality such as no contact list”. So after “fail-over”, users in the failed pool do not have any contact lists? This is significant. I thought (was hoping) that Lync might cache the contact lists of it’s backup registrar, but I guess not.

      Curtis

      • martin

        No, it’s not :(

        Actually our setup is on VMs and we can afford downtime, assuming it occurs so rarely we won’t notice it :) If it is a hardware failure, getting back the VM is almost as fast as failing over backup registrar.

        Secondary SE is more a SBA for our disaster recovery site. In case of disaster, we would have degraded telephony immediately in service while we recover the production lync within our disaster recovery protocol, which may take a bit longer (although we have replication of vms).

  • Mikael Andreasson

    Hi Curtis

    Nice blog.
    If i understand right:)
    To deploy a Dedecated Lync Monitoring server and reuse a dedicated 32-bit SQL Server 2005, this will not work, because it has to be 64-bit SQL Server, is that right? Do you know why?

    • Hi, yes that is my understanding – a 64-bit SQL server is the only supported configuration for the Monitoring reports (reference the Microsoft Lync Server 2010 Monitoring Deployment Guide: http://www.microsoft.com/download/en/details.aspx?id=8207).

      Interestingly it is worded as follows: “Lync 2010 supports the following 64-bit versions of SQL” (SQL Server 2008 Enterprise & Standard with SP1 and 2005 with SP3). It does not specifically exclude 32-bit :-) , so it would be interesting to see if it worked, but there is no doubt that the only supported configuration is 64-bit.

      As to why, I would guess that the Monitoring Server reports which get published to the 64-bit version of Microsoft SQL Server Reporting Services (SRS) will not work on a 32-bit version of SRS. And of course the 64-bit version of SRS requires a 64-bit version of SQL….

      Curtis

  • Michael

    Hi Curtis,
    I see some good database size estimation formulas in the Lync pages for the CDR and QoE databases, but not for the others. Do you know where I can find these or do you know any size estimates for the other databases for, say, 20,000 users?
    Thanks,
    -Mike-

    • Hi,

      For what databases specifically – the back-end database?

      Curtis

      • Michael

        All of them… I’m trying to come up with disk space requirements based on our number of users, but can only find size estimating formulas for the monitoring databases CDR and QoE, like “Based on the Lync Server user model, the CDR database grows 31.5 KB per user per day, and the QoE database grows 28 KB per user per day”
        So, from your posting above, there are several other databases that are installed with Lync – I’m trying to determine their size.
        Thanks again!
        -Mike-

        • Hi Mike,

          Good question – there are many references to capacity planning for database activity but little in the way of sizing for the databases other than the Monitoring and Archiving.

          The best reference I know of is Chapter 2 Determining Your Infrastructure Requirements in the Microsoft Lync Planning Guide.

          In the section on Hardware Recommendations for Back End Servers and Other Database Servers it quotes “Local storage with at least 72 GB free disk space“. This is the recommendation for a Back end server database (which would house the databases talked about in my article) for a 80,000 user pool.

          Normally I see the backend servers with a system disk and another disk for the backend databases that is at least 200 Gb. These are small to medium size deployments (100 – 8,000 users), and they haven’t had any storage problems.

          I’ll poke around and see if I can get some better numbers.

          Curtis

          BTW, there is the scenario-based Microsoft Lync Server 2010 Capacity Calculator which is a spreadsheet is focused on helping users determine their hardware requirements, but it just outputs the number of backend databases required, but again does not focus the storage considerations.

        • Michael

          Hi Curtis,
          Thanks for the info – I appreciate it!
          -Mike-

        • Some more info…a fellow Lync MVP confirmed that the in his experience the backend DB takes up very little space, and reminded me that the OCS 2007 R1/R2 documentation stated that a 100,000 user pool would use up to 10GB of space for the backend DB.

          It is mostly configuration information, which is why it doesn’t occupy a large about of disk.

          The stated 72 Gb should be more than adequate.

          Curtis

  • TB

    Hi Curtis.

    Great information. I wasn’t aware that pool information of “other” pools were stored in the RTCLOCAL instance of a Lync SE server. (Did I read that correctly?)

    Anyhow, what I was wondering was that if this was the case, would you be able to setup 2 Lync SE servers…1 in a main site (with all users in this pool)and 1 in a “disaster recovery” site (no users in pool) and use them as a “backup registrar” for each other in the topology…and expect that users from the main site’s pool still be able to logon to their lync clients and send/receive IM’s along with being able to share an application or desktop even when/if the main site experiences an outage?

    Thanks,
    TB

    • Hi,

      Yes this is my understanding – the Lync SE will have information about the entire Topology – including other pools via a replica of the CMS in the SQL RTCLOCAL instance. If the Lync SE server was the first to be installed, the CMS master will reside on this Lync SE server as well (although it is recommended you install the CMS on an Enterprise Edition pool). This architecture allows Lync servers to function when connectivity to the rest of the Lync Topology is not available.

      Excellent question on whether two Lync SE servers can act as backup registrar’s for each other. I believe the answer is ‘yes’. In my lab I just set the backup register for an Enterprise pool to be a Lync SE pool, so I don’t see why that would not hold true for two Lync SE servers/pools. I haven’t tried a fail-over yet.

      If I find anything to the contrary I will update it.

      If you are looking to do this you should pose this question in the Microsoft Lync Forums also – it will get a wider audience of experise: http://social.technet.microsoft.com/Forums/en-US/category/ocs

      Thanks,

  • soder

    It is generally a wise practice to check the lync documentation every month to see, what has changed since the last document update. At the time of writing this, still a lot of the sections needs some clarification and addition, although a great progress can be seen since the initial 2010 december version. By the time Lync 2012 is RTM, I hope the documentation for Lync 2010 will be marked as “from now this is called a usable document”, the only problem that this shoul have already happened on day #1 and not after 2 years ;) Keep posting comments to the documentation, otherwise it will never reach the quality that we IT pros demand from MS. And dont give up, if your valuable comments will meet deaf ears…

  • Patrick

    Hi Curtis,

    you mentionned that Lync 2010 Enterprise Edition needs SQL Server Enterprise Edition. But in the newly posted “Microsoft Lync Server 2010 Supportability Guide (May 18th, 2011)” and “Microsoft Lync Server 2010 Enterprise Edition Deployment Guide (May 18th, 2011)” documents, there’s no mention of this. All they say is that 32-bit versions of SQL Server (2005, 2008 2008 R2) are not supported.

    Could you please provide references where you found it?

    Thanks

    Patrick

    • Hi Patrick,

      The reference was the Microsoft TechNet Lync Server 2010 Database Software and Clustering Support article: http://technet.microsoft.com/en-us/library/gg398990.aspx.

      On April 20th this article only listed SQL Enterprise Edition as the supported database for a Lync pool back-end database (which did not mean it wouldn’t work, it just was not officially supported). I was also told at the time that the scalability of SQL Enterprise was required.

      This document, and the others you quoted, have been updated (as part of the May 2011 documentation updates) to include support for SQL Standard Edition.

      Thanks for noticing that and commenting – I’ve updated the blog article,
      Curtis

  • Justin

    Thanks Curtis that makes sense

  • Justin

    Hi Curtis
    Thanks for the useful article. I have a Lync SE server and I notice that there are 2 sql instances on it RTC and RTCLOCAL as explained in your article. However what is confusing me is a particular table. There is a table called dbo.registrarendpoint in the rtcdyn database within the sql instance rtc. This table contains useful stuff such as client lync client versions being used. However the table is empty. To get at the information you need to connect to the RTCLOCAL instance. Please excuse me as I am not a SQL person and my question but based on your explanation I dont understand a) why the table is empty b) if the RTCLOCAL instance only houses the CMS why does it have the above table in it as well. Apologies if I am asking a daft question but I’m not really a SQL person and it may simply be something to do with accessing tables through various instances.
    Thanks
    Justin

    • Hi Justin,

      Excellent question – I focused on the databases used in the CMS because that was the issue I was researching.

      In my blog article I explain that RTCLOCAL holds “…the databases necessary for registration and routing” in addition the CMS replica.

      The RTCLOCAL instance has a copy of the other pool databases needed for a Lync Front-End to function if it cannot reach any other Lync servers (such as the “rtc”, “rtcdyn”, “rtcab1″ databases). My understanding is that any dynamic connection information (e.g. client connections) for a particular Front-End is stored in the rtcdyn database in RTCLOCAL. All the pool persistent information (e.g. homed users) is stored in the backend RTC database and replicated to the RTCLOCAL instances on each front-end (for resiliency purposes).

      So in your SE case, the dynamic connection information will be stored in the dbo.registrarendpoint table in the RTCLOCAL instance, not the RTC instance. I haven’t figured out exactly what dynamic information (if any) is replicated from RTCLOCAL to RTC.

      Hope that helps,
      Curtis

  • Just to be clear – you’re saying you can’t house an Enterprise Edition Lync on a Standard Edition SQL Server? I don’t believe that’s the case is it?

    You can have SQL 2008/2005SP3 Standard as a back-end provider?

    • Hi Mark,

      I said that Lync Enterprise Edition (EE) requires SQL Enterprise Edition. Maybe “supported” is a better word than “requires” – this is how it is worded in the Lync Database Software pre-requisites for the back-end of a Lync Front-End pool: http://technet.microsoft.com/en-us/library/gg398990.aspx.

      I have only every installed Lync EE with SQL EE. I don’t see why it would not technically work on Standard though, and maybe the “supported” for SQL Enterprise comes from the high availability and increased workload features of SQL Enterprise that Lync EE *might* need.

      I’ll try to get a better answer around the supportability of SQL Standard Edition with Lync EE.

      Curtis

    • Just an update – I am told from a supportability perspective, for the back-end of an Lync Enterprise Pool, you need the scale of SQL Enterprise.

  • [...] The Microsoft Lync 2010 Server Databases | Inside Lync – Overview of the multiple databases used in Lync 2010 infrastructure [...]

  • [...] information about the Microsoft Lync databases can be found here: The Lync Server Databases. Be Sociable, Share! Tweet(function() {var s = document.createElement('SCRIPT'), s1 = [...]

  • [...] With every Lync server SQL express is installed with a RTCLOCAL instance and with Standard Edition there is additionally a RTC instance, here is a better explanation of the difference that I can do The Lync Server Databases [...]

  • [...] 9.11 בחלון ה "Define the SQL store", נקליק על כפתור ה Next. יש לציין כי במידה והתקנו את גרסת ה Standard, אופציה זו לא תהיה זמינה. גרסת ה Standard תתקין באופן אוטומטי את גרסת ה Microsoft SQL Server 2008 Express ולא ניתן להשתמש בגרסה אחרת. רק בגרסת ה Enterprise ניתן להשתמש בגרסת SQL מלאה כגון: SQL Server 2008 R2 חיצוני או מקומי. ראה: Database Software and Clustering Support, The Lync Server Databases. [...]

  • [...] been scratching my head all these times to find that someone had already solved the mystery:  The Lync Database. Kudos to [...]

  • [...] A Great overview of Lync Databases and their uses from Curtis Johnstone [...]

  • [...] > http://blog.insidelync.com/2011/04/the-lync-server-databases/ Share and Enjoy: [...]

You must be logged in to post a comment.