This section describes the Archiving Server database schema for Office Communications Server 2007 R2. The schema is subject to change in future releases.

List of Tables

The database schema consists of the following tables.

Supporting Tables

Table	Description
ClientVersions	Stores the clients (both client type and version number) of each client involved in a call with information captured in this database.
Computers	Stores the name of each computer that hosts a Front End Server.
ContentTypes	Stores the IM content types used in sessions captured in this database.
Dialogs	Stores information about the DialogID for each peer-to-peer session in the database.
Pools	Stores the names of pool on which IM messages are captured.
Users	Stores the user URIs of users who have participated in sessions recorded or archived in this database.

Tables for Messages in IM Conferences

Table	Description
Conferences	Stores information about all conferences that were archived or whose details were recorded, including ConferenceURI, and start and end time.
ConferenceMessageRecipientList	For each message sent in a conference, stores a list of recipients.
ConferenceMessages	Archives the content of all the messages sent in a conference.

Tables for Peer-to-Peer IM Archiving

Table	Description
SessionDetails	Stores information about every peer-to-peer session, including start and end time, user ID, response code, and message count for each user.
Messages	Archives the content of all the messages sent in one-on-one IM sessions.

The tables in the following list are used internally by Office Communications Server (their details are not described in this document).

Tables for Internal Use by Office Communications Server

Table	Description
DbConfigDateTime	For internal use only.
DbConfigInt	For internal use only.
DbErrorMessage	For internal use only.

Table Details

This section details the columns in each of the Archiving database schema tables.

ClientVersions Table

The ClientVersions table is a supporting table that stores a list of the various client types and versions that have participated in sessions recorded in the database. Each record in the table represents one client version.

Column	Data Type	Key/Index	Details
VersionId	int	Primary	Unique number identifying this client type and version.
Version	nvarchar(256)		Version name.

Computers Table

The Computers table is a supporting table that stores information about the various Front End Servers. Each record in the table represents one Front End Server

Column	Data Type	Key/Index	Details
ComputerId	int	Primary	Unique number identifying this Front End Server.
Computer	nvarchar(16)		Front End Server host name.

ContentTypes Table

The ContentTypes table is a supporting table that stores information about the various types of IM content.

Column	Data Type	Key/Index	Details
ContentTypeId	int	Primary	Unique number identifying this IM content type.
ContentType	nvarchar(256)		Content type name (for example, text/plain, text/rtf) or a MIME type.

Dialogs Table

The Dialogs table is a supporting table that stores the information about DialogIds for peer-to-peer sessions.

Column Data Type Key/Index Details

Column	Data Type	Key/Index	Details
DialogId	int	Primary	Unique number identifying this SIP dialog instance.
ExternalChecksum	Int		Checksum of the ExternalId. This field is used to increase the speed of database searches.
ExternalId	varbinary(775)		SIP dialog Id, stored as a binary. The format of the binary is: dialog;from-tag;to-tag This data can be converted to text format by using: `cast(cast(ExternalId as varbinary(max)) as varchar(max))`

DialogId

int

Primary

Unique number identifying this SIP dialog instance.

ExternalChecksum

Int

Checksum of the ExternalId. This field is used to increase the speed of database searches.

ExternalId

varbinary(775)

SIP dialog Id, stored as a binary. The format of the binary is:

dialog;from-tag;to-tag

This data can be converted to text format by using: cast(cast(ExternalId as varbinary(max)) as varchar(max))

Pools Table

The Pools table is a supporting table that stores information about the various Pools. Each record in the table represents one Pool.

Column	Data Type	Key/Index	Details
PoolId	int	Primary	Unique number identifying this Pool.
PoolFQDN	nvarchar(256)		Pool FQDN

Users Table

The Users table is a supporting table; each record in the table stores information about one user involved in calls or sessions that have records in the database.

Column	Data Type	Key/Index	Details
UserId	int	Primary	Unique number identifying this user.
UserUri	nvarchar(450)

Conferences Table

Each record in this table contains call details about one conference.

Column	Data Type	Key/Index	Details
ConferenceUri	nvarchar(450)
Checksum	Int		Checksum of ConferenceUri; used to increases the speed of database searches.
ConfInstance	Int		Useful for recurring conferences; each instance of a recurring conference has the same ConferenceUri, but will have a different ConfInstance.
SessionIdTime	datetime	Primary	Time that the conference request was captured by the Archiving agent. Used only as a primary key to uniquely identify a session.
SessionIdSeq	int	Primary	ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session. *
ConferenceStartTime	datetime
ConferenceEndTime	datetime
PoolId	int	Foreign	Unique number indentifying the pool on which the message is captured, reference to Pools table

* For most sessions, SessionIdSeq will have the value of 1. If two sessions start at exactly the same time, the SessionIdSeq for one will be 1, and for the other will be 2, and so on.

ConferenceMessageRecipientList Table

Each record in this table represents one combination of IM conference message and recipient. A message that is sent to multiple recipients generates one record for each recipient.

Column	Data Type	Key/Index	Details
MessageId	int	Primary	Unique number identifying this message in an IM conference.
SessionIdTime	datetime	Primary, Foreign	Time that the conference request was captured by the Archiving agent.
SessionIdSeq	int	Primary, Foreign	ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session.
UserId	Int	Primary, Foreign	Unique number identifying this user, referenced from the Userstable.
Date	datetime		Message captured time.

ConferenceMessages Table

This table archives all messages sent in IM Conferences. Each record represents one message.

Column	Data Type	Key/Index	Details
MessageId	uniqueidentifier	Primary	GUID identifying this message.
SessionIdTime	datetime	Primary, Foreign	Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session. Referenced from the Conferencestable.
SessionIdSeq	int	Primary, Foreign	ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session. Referenced from the Conferencestable.
Date	datetime
FromId	int	Foreign	UserId of the message sender, referenced from the Userstable.
ContentTypeId	Int	Foreign	IM content type of this message. Referenced from the ContentTypestable.
ComputerId	int	Foreign	Id of the Front End Server used for this message. Referenced from the Computerstable.
Body	ntext		Content of the message body.
Reserved1	tinyint		Reserved for Microsoft use.
Reserved2	tinyint		Reserved for Microsoft use.

SessionDetails Table

Each record represents one peer-to-peer session, which could be a VoIP-VoIP phone call, 2-party IM session, or other type of session. To find the modalities used during a session, you must do a table join with the Mediatable. Session type is not stored in the SessionDetails table.

Column	Data Type	Key/Index	Details
SessionIdTime	datetime	Primary	Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session.
SessionIdSeq	int	Primary	ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session.
DialogId	Int	Foreign	SIP dialog ID, referenced from the Dialogstable.
User1Id	Int	Foreign	Id of one user in the session, referenced from the Userstable.
User2Id	int	Foreign	Id of the other user in the session, referenced from the Userstable.
SessionStartedById	int	Foreign	Id of the user who started the session, referenced from the Userstable.
ComputerId	Int	Foreign	Id of the Front End Server used for this session.
PoolId	Int	Foreign	Id of the Pool in which the session was captured.
User1ClientVerId	Int	Foreign	Client version used by User1, referenced from the ClientVersionstable.
User2ClientVerId	int	Foreign	Client version used by User2, referenced from the ClientVersionstable.
InviteTime	datetime
ResponseTime	datetime
ResponseCode	Int		SIP response code to the session invitation.
SessionEndTime	datetime

* For most sessions, SessionIdSeq will have the value of 1. If multiple sessions start at exactly the same time, the SessionIdSeq for one will be 1, for another will be 2, and so on.

Messages Table

This table archives all messages sent during one-to-one IM sessions. Each record represents one message.

Column	Data Type	Key/Index	Details
MessageIdTime	datetime	Primary	Time the message was sent.
MessageIdSeq	int	Primary	ID number to identify the message. Used in conjunction with MessageIdTimeto uniquely identify a message. *
SessionIdTime	datetime	Primary, Foreign	Time of session request; used in conjunction with SessionIDSeqto uniquely identify a session.
SessionIdSeq	int	Primary, Foreign	ID number to identify the session. Used in conjunction with SessionIDTimeto uniquely identify a session.
FromId	int	Foreign	UserId of the user sending the message, referenced from the Userstable.
Told	int	Foreign	UserId of the user receiving the message, referenced from the Userstable.
ContentTypeId	int	Foreign	Unique number identifying this IM content type, referenced from the ContentTypestable.
ComputerId	int	Foreign	Id of the Front End Server used for this message, referenced from the Computerstable.
Body	ntext		Content of the message body.
Toast	bit		TRUE is this message was a toast message.
ContextNote	Bit		TRUE is this message was a context note.
Reserved1	tinyint		Reserved for Microsoft use.
Reserved2	tinyint		Reserved for Microsoft use.

* For most sessions, MessageIdSeq will have the value of 1. If multiple messages are sent at exactly the same time, the MessageIdSeq for one will be 1, for another will be 2, and so on.