Topic Last Modified: 2011-01-28

The Group Chat database schema consists of the following tables.

Active Directory Sync

Table Description

ADCookie 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 Description

PrincipalType 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 Description

PrincipalInvites 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 Description

ServerIdentity 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.


Table Description

ComplianceData 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.