SQL tables

The MA supports two tables, a single-value table and an optional multi-value table. You can use most any type of columns for each table.

The different object types can share the columns and you can exclude some columns from certain object types by specifying this in the schema XML.

NOTE - the name of data columns must be unique in your tables for your object types, i.e. you cannot have a column named 'accountname' in both the single-value table and the multi-value table and use that with the same object type.

For increased performance, it is recommended to index the key, backreference and watermark columns in both tables.

Single-value table

This table is mandatory and the table is required to have one column that has unique values and functions as the primary key for the records.

Purpose Type Data type Recommended name Description
Primary key Mandatory any _id A column that has a unique value for every record. Typically, this column is of type string, uniqueidentifier or int/bigint.
Object class Optional string _objectclass A column that holds the type of object for the record/row.
Watermark Optional rowversion or datetime _watermark If delta import is to be supported, a column of either type rowversion or datetime must exist and be updated on changes to the record. The column type must be either rowversion or datetime, no other type is currently supported.
IsDeleted Optional bit _isdeleted If soft-delete is supported, a column of type bit must exist. If a record is soft-deleted, this value of this column is set to true (1) and if the record is not deleted, the value is either NULL or false (0).

Besides the columns specified above, you can have any number of column with information that your records need. You can use all data types for the columns and the MA will convert the different data types to the appropriate FIM/MIM datatype. Using the schema XML, you can override the default data types suggested by the MA.

Multi-value table

This is a optional table and has any number of columns that are marked as multi-valued. The table must have a column that has the same type as the primary key column in the single-value table and this column is used as a back reference to an object in the single-value table.

Purpose Type Data type Recommended name Description
Backreference Mandatory same as primary key in single-value table _refid Hold the primary key of the record from the single-value table that has this multivalue record.
Watermark Optional Same as corresponding column from single-value table _watermark If delta import is to be supported, a column of either type rowversion or datetime must exist and be updated on changes to the record. The column type must be either rowversion or datetime, no other type is currently supported.
IsDeleted Optional bit MUST have same name as column in single-value table If soft-delete is supported, a column of type bit must exist. If a record is soft-deleted, this value of this column is set to true (1) and if the record is not deleted, the value is either NULL or false (0).

Besides the columns specified above, you can have any number of column with information that your records need. You can use all data types for the columns and the MA will convert the different data types to the appropriate FIM/MIM datatype. Using the schema XML, you can override the default data types suggested by the MA.

Sample tables

Below are scripts to create a sample single-value and multi-value table with all relevant attributes.

CREATE TABLE [dbo].[object](
	[_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_objects__id]  DEFAULT (newid()),
	[_objectclass] [nvarchar](50) NOT NULL,
	[_watermark] [timestamp] NOT NULL,
	[_isdeleted] [bit] NULL,
	[accountname] [nvarchar](50) NULL,
	[firstname] [nvarchar](50) NULL,
	[lastname] [nvarchar](50) NULL,
	[displayname] [nvarchar](250) NULL,
	[managerid] [uniqueidentifier] NULL,
	[employeenumber] [int] NULL,
	[managedby] [uniqueidentifier] NULL,
 CONSTRAINT [PK_object] PRIMARY KEY CLUSTERED 
(
	[_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[objectmv](
	[_watermark] [timestamp] NOT NULL,
	[_isdeleted] [bit] NULL,
	[_backrefid] [uniqueidentifier] NOT NULL,
	[member] [uniqueidentifier] NULL,
	[proxyaddresses] [nvarchar](250) NULL,
	[departmentname] [nvarchar](250) NULL,
	[othertelephone] [nvarchar](50) NULL
) ON [PRIMARY]

Last edited Nov 12, 2015 at 5:57 PM by Granfeldt, version 14