Article
07/22/2014

List of Group Chat Server Tables

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.

Compliance

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.