Microsoft Lync Server 2010 Reference: Group Chat Database Schema
Microsoft Lync Server 2010
Published: March 2012
This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, may change without notice.
Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.
This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.
Copyright © 2012 Microsoft Corporation. All rights reserved.
Contents
Group Chat Database Schema
List of Group Chat Server Tables
Group Chat Server Table Details
ADCookie Table
PrincipalMemberDifference Table
ADUpdate Table
PrincipalMembers Table
PrincipalMeta Table
SkippedAffiliations Table
PrincipalType Table
Principal Table
PrincipalAffiliations Table
Node Table
Tree Table
RoleType Table
ScopePrincipal Table
PrincipalRole Table
SiopWhiteList Table
PurgedRoles Table
PurgedScoped Table
EnumAttribute Table
EnumValue Table
PrincipalInvites Table
Chat Table
LastInviteId Table
LastChatId Table
Preference Table
FileToken Table
ServerIdentity Table
AdminLock Table
SystemRevision Table
ActivePeers Table
Version Table
Config Table
Logs Table
ComplianceData Table
ComplianceFanout Table
ComplianceParticipant Table
ComplianceTranslator Table
LastComplianceID Table
ComplianceVersion Table
Sample Group Chat Database Queries
Microsoft Lync Server 2010 Reference: Group Chat Database Schema
Group Chat Database Schema
This documents the schema of the Microsoft Lync 2010 Group Chat database in Microsoft Lync Server 2010communications software.
In This Section
List of Group Chat Server Tables
Group Chat Server Table Details
Sample Group Chat Database Queries
List of Group Chat Server Tables
The Group Chat database schema consists of the following tables.
Active Directory Sync
Table / DescriptionADCookie Table / Contains the current Lightweight Directory Access Protocol (LDAP) Sync cookies. Each row corresponds to an Active Directory Domain Services (AD DS) domain that Microsoft Lync Server 2010, Group Chat is actively monitoring for changes. (Only the Active Directory domains that are relevant for Lync Server 2010, Group Chat are represented in this table.)
PrincipalMemberDifference Table / Contains group membership changes (both added and removed members) that have not yet been processed by the later Active Directory Sync steps and is one of the temporary tables (along with ADUpdates table) used by first step Active Directory Sync.
Membership changes are stored, processed, or both, only for groups that are listed in the Principal table or that already have members listed there.
ADUpdate Table / Contains changes to AD DS that have not yet been processed by the later Active Directory Sync steps and is one of the temporary tables (along with the PrincipalMemberDifference table) used by first step Active Directory Sync.
Changes to AD DS are stored, processed, or both only for principals that are already listed in the Principal table.
PrincipalMembers Table / Contains principal memberships.
PrincipalMeta Table / Contains the principals that have to be refreshed from AD DS.
SkippedAffiliations Table / Contains affiliations that could not be refreshed for some reason, usually due to Active Directory access errors.
This table is for informational purposes only. Its content is not used.
The principals with affiliations that could not be refreshed properly are kept in the PrincipalMeta table and given another chance to be refreshed.
Principals, Affiliations, Nodes, Scopes, and Roles
Table / DescriptionPrincipalType Table / Contains principal types to categorize what is in tblPrincipal. This table is static. It is set up during database creation and does not change.
Principal Table / Contains all principals (users, folders, groups, and so on). Group Chat Server handles this as a flat heterogeneous list. Various columns are based on the type of each principal.
Most of these principals are cached copies of objects stored in AD DS. Creating the cached copy in the Principal table of these Active Directory objects is referred as provisioning.
Some principals are created with more intention than others, and some Active Directory objects are ignored altogether.
PrincipalAffiliations Table / Contains principal affiliations that describe memberships in Active Directory security groups, Active Directory containers, federated groups, and so on.
Node Table / Contains the console tree (with category and chat room nodes), as managed in Microsoft Lync Server 2010, Group Chat Admin Tool.
Group Chat Server Table Details / Contains redundant information that can be computed from the Node table. This information helps optimize various types of searches through the console tree.
RoleType Table / Contains role types and their associated permission sets. This lookup table is static.
ScopePrincipal Table / Contains scopes assigned to nodes. Each node either inherits attributes from one of its parents (which can be quickly looked up by using the Node.scopeDefinerId table), or has a set of principals defined in the ScopedPrincipal table that together define a scope.
PrincipalRole Table / Contains explicit roles assigned to nodes. Determining effective roles requires more steps for the following reasons:
Roles inherit attributes throughout the console tree.
Principals can inherit roles through affiliations.
The Member role can be overridden (the overriding node can be quickly looked up by using the Node.roleDefinerId table) and obeys scope (“Voiced” is in the same category, although the user interface allows setting this only in chat rooms, rendering issues like inheritance and overriding irrelevant).
SiopWhiteList Table / Contains the registered Add-ins that can be associated with nodes.
PurgedRoles Table / Contains roles that have been deleted (purged) from the PrincipalRole table.
PurgedScoped Table / Contains scopes that have been deleted (purged) from the ScopedPrincipal table.
EnumAttribute Table / Contains only the hardcoded “Visibility” and “Behavior” attributes used in the Node table.
EnumValue Table / Contains the values of the hardcoded “Visibility” and “Behavior” attributes used in the Node table.
Invites, Chats, and other Client Support
Table / DescriptionPrincipalInvites Table / Contains invites for all provisioned users in the system for all nodes with Auto Invite enabled.
Chat Table / Contains all chat messages.
LastInviteId Table / Contains the last invite ID generated (and used in the PrincipalInvites table) for each user.
LastChatId Table / Contains the last chat ID generated (and used in the Chat table) for each user.
Preference Table / Contains user client preferences.
FileToken Table / Contains temporary tokens for file transfer purposes. Each time a file is uploaded or downloaded, the Channel service generates a token that the client uses to access the Web service file store.
Server Support
Table / DescriptionServerIdentity Table / Contains the active servers in the Group Chat Server pool.
AdminLock Table / Contains the administrator lock to run some administrator commands. The system revision entry in the SystemRevision table is incremented after each release of the lock.
SystemRevision Table / Contains the revision number entry used (along with the AdminLock table) for achieving consistency across multiple clients.
ActivePeers Table / Contains current peer-to-peer connections between Channel Servers.
Version Table / Contains the non-compliance part of the Group Chat database version.
Config Table / Contains the Group Chat Server configuration.
Logs Table / Table not used at present.
Compliance
Table / DescriptionComplianceData Table / Contains the compliance events that have not yet been processed by all the registered translators.
This table includes chat-related events, such as chat messages and file downloads. (Participant events are tracked by the ComplianceParticipant table.)
(The servers that processed the events in this table are listed in the ComplianceFanout table.)
ComplianceFanout Table / Contains the servers that processed a compliance event. This is tightly coupled with the ComplianceData table.
ComplianceParticipant Table / Contains current participants per channel and per server. It is maintained based on join and part compliance events received from the Channel service.
ComplianceTranslator Table / Contains registered translators.
LastComplianceID Table / Contains the times of the latest processed compliance event for each translator.
ComplianceVersion Table / Contains the compliance part of the Group Chat database version.
Group Chat Server Table Details
The following topics detail the columns in each of the Group Chat database schema tables.
In This Section
ADCookie Table
PrincipalMemberDifference Table
ADUpdate Table
PrincipalMembers Table
PrincipalMeta Table
SkippedAffiliations Table
PrincipalType Table
Principal Table
PrincipalAffiliations Table
Node Table
Group Chat Server Table Details
RoleType Table
ScopePrincipal Table
PrincipalRole Table
SiopWhiteList Table
PurgedRoles Table
PurgedScoped Table
EnumAttribute Table
EnumValue Table
PrincipalInvites Table
Chat Table
LastInviteId Table
LastChatId Table
Preference Table
FileToken Table
ServerIdentity Table
AdminLock Table
SystemRevision Table
ActivePeers Table
Version Table
Config Table
Logs Table
ComplianceData Table
ComplianceFanout Table
ComplianceParticipant Table
ComplianceTranslator Table
LastComplianceID Table
ComplianceVersion Table
ADCookie Table
The tblADCookie table contains the current Lightweight Directory Access Protocol (LDAP) Sync cookies.
Columns
Column / Type / DescriptionprinGuid / GUID, not null / Principal GUID of the domain being monitored.
prinDCHost / nvarchar (255) / Fully qualified domain name (FQDN) of the current domain controller used for Active Directory Domain Services Sync. Has informational value.
adcContent / image (binary) / Active Directory Sync cookie.
lastUpdated / datetime / Time stamp with the row update time.
lockedUntil / datetime / Time until the row is locked for changes. This is part of a software interlock mechanism that ensures that only one of the Channel Servers does the Active Directory Sync at a time.
Keys
Column(s) / DescriptionprinGuid / Primary key.
prinGuid / Foreign key with lookup in Principal.prinGuid table.
PrincipalMemberDifference Table
The PrincipalMemberDifference table contains group membership changes (both added and removed members) that have not yet been processed by the later Active Directory Domain Services (AD DS) Sync steps.
Columns
Column / Type / DescriptionprinGuid / GUID, not null / Principal GUID of the group that changed.
memberADPath / nvarchar (256) / Distinguished name of the member.
memberRemoved / bit, not null / False if the member was added. True if the member was removed.
Key
Column / Description<prinGuid, memberADPath> / Primary key.
ADUpdate Table
The ADUpdate table contains Active Directory Domain Services (AD DS) changes that have not yet been processed by the later Active Directory Sync steps.
Columns
Column / Type / DescriptionprinGuid / GUID, not null / Principal GUID of the object that changed.
prinADPath / nvarchar (256), not null / Distinguished name of the object.
prinAttributesChanged / bit, not null / True if at least one attribute of the object changed.
prinMembersChanged / bit, not null / True if the membership changed.
prinAffiliationsChanged / bit, not null / Not used.
prinDeleted / bit, not null / True if the object was deleted.
lastUpdated / datetime, not null / Time stamp of when the row was inserted.
PrincipalMembers Table
The tblPrincipalMembers table contains principal memberships.
Columns
Column / Type / DescriptionprinID / int, not null / Principal ID.
memberADPath / nvarchar (256), not null / Distinguished name of a member. A member does not have to be a principal (in Principal table).
Keys
Column / Description<prinID, memberADPath> / Primary key.
prinID / Foreign key with lookup in tblPrincipal.prinID.
PrincipalMeta Table
The PrincipalMeta table contains the principals that have to be refreshed from Active Directory Domain Services (AD DS).
Columns
Column / Type / DescriptionprinID / int, not null / Principal ID.
prinAffiliationsDirty / bit, not null / True if principal affiliations have to be refreshed.
prinAttributesDirty / bit, not null / True if principal attributes have to be refreshed.
prinDeleted / bit, not null / True if the principal has been deleted.
tryCount / int / Number of attempts to refresh the principal from AD DS that have happened so far.
lastTry / datetime / Time stamp from the latest attempt to refresh the principal. Can be null if no refresh has been attempted yet.
nextTry / datetime / Time stamp for the next scheduled refresh. Can be null if no further refresh has been scheduled.
Keys
Column / DescriptionprinID / Primary key.
prinID / Foreign key with lookup in Principal.prinID table.
SkippedAffiliations Table
The SkippedAffiliations table contains the affiliations that could not be read for some reason (usually due to Active Directory Domain Services (AD DS) access errors).
Columns
Column / Type / DescriptionprinID / int, not null / Principal ID.
affDescription / nvarchar (256), not null / A string identifying the affiliation.
The format is: guid: {0} uri: {1}> id: {2}
updatedBy / int, not null / ID of the principal that updated this row. It is always 1 (system user) because Active Directory Sync is the only source for these entries.
Keys
Column(s) / Description<prinID, affDescription> / Primary key.
prinID / Foreign key with lookup in Principal.prinID table.
PrincipalType Table
The PrincipalType table contains principal types to categorize what is in the Principal table.
Columns
Column / Type / DescriptionptypeID / smallint, not null / Principal type ID.
ptypeDesc / nvarchar (256), not null / Description of the type.
ptypeIsSystemUser / bit, not null / True if the type corresponds to the principals that are used for internal purposes.
ptypeIsUser / bit, not null / True if the type is a user type.
ptypeIsFederated / bit, not null / True if the type is a federated concept (group or user).
Key
Column / DescriptionptypeID / Primary key.
Principal Values
ID / Role / Description / SystemUser / User / Federated1 / Any / Generic principal with no known type. Not used in tblPrincipal table.
2 / AnyUser / Generic principal of user type. Not used in Principal table. / Yes
3 / AnyGroup / Generic principal with group semantic. Not used in Principal table.
4 / SystemUser / Principal used internally by Microsoft Lync Server 2010, Group Chat. / Yes
5 / User / Regular user (not federated). / Yes
6 / ExternalUser / Carried over for MindAlign 6.x compatibility. Not used currently. / Yes
7 / FederatedUser / Federated user. / Yes / Yes
8 / DC / Active Directory Domain Services (AD DS) domain controller.
9 / Group / Active Directory security group.
10 / Folder / Active Directory container or organizational unit.
11 / Federated Group / Federated group. / Yes
See Also
Principal Table
Principal Table
The Principal table contains all principals, including users, folders, and groups.
Columns
Column / Type / DescriptionprinID / int, not null / Principal ID.
prinGuid / uuid, not null / Principal GUID. This is broadly used as an alternate primary key because its meaning crosses over into the Active Directory Domain Services (AD DS) space. (The GUID for a cached principal is equal to the corresponding Active Directory object GUID.)
prinUri / nvarchar (256), not null / Principal URI. The sip scheme is used for users, and ma-grp is used for almost everything else.
prinName / nvarchar (256) / Display name. Used only by user types.
prinCompanyName / nvarchar (256) / Company name. Used only by user types.
prinEmail / nvarchar (256) / Email. Used only by user types.
prinADPath / nvarchar (256) / Domain name of the Active Directory object that the principal is a cached version of. Can be Null for types that are not Active Directory objects (such as federated users, system users, and federated groups).
prinADUserPrincipalName / nvarchar (256) / User’s user principal name (UPN). Used only by regular user types.
prinDisabled / bit, not null / True if the principal is considered disabled. This is used when a user leaves the company or the user’s SIP capabilities are disabled.
prinTypeID / smallint, not null / Principal type (from PrincipalType table).
prinAllowedPermSet / bigint, not null / Principal user rights and permissions (as set in the Manage Users and User Groups Microsoft Lync Server 2010, Group Chat Admin Tool window) when prinUseInheritedPerms is False.
Primary bits:
0: True if the principal can post files.
1: True if the principal is an administrator with administrator rights and permissions to manage users and groups (in Manage Users and User Groups).
2: True if the principal is an administrator with administrator rights and permissions to manage nodes (categories and chat rooms).
prinUseInheritedPerms / bit, not null / False if the principal has the user rights and permissions defined by prinAllowedPermSet.
True if the principal inherits the user rights and permissions from its groups, containers, and so on, in an additive manner.
prinAddedBy / int / Principal ID of the creator.
prinAddedOn / bigint, not null / Time stamp for the creation time.
prinUpdatedBy / int / ID of the principal that last updated this.
prinUpdatedOn / bigint, not null / Time stamp for the last update.
prinVerifiedOn / datetime, not null / Date and time of the last Active Directory Sync refresh for the principal.
Keys
Column / DescriptionprinID / Primary key.
prinTypeID / Foreign key with lookup in PrincipalType.ptypeID table.
PrincipalAffiliations Table
The PrincipalAffiliations table contains the principal affiliations that describe memberships in locations including Active Directory Domain Services (AD DS) security groups, in Active Directory containers, in federated groups.
Columns
Column / Type / DescriptionprincipalID / int, not null / ID of the affiliated principal.
affiliationID / int, not null / ID of the principal representing the affiliation. Each principal (except system-user-types) has a self-affiliation as well.
index / int, not null / Index. The value for self-affiliations is -1, and for the other affiliations it increases sequentially from 1 within each <principalID, affiliationId> bucket.
updatedBy / int, not null / Principal that did the latest update. This is usually 1, which means Active Directory Sync.
Keys