Generating Delta Views Using Triggers

The ability to process delta data is a key feature of Microsoft® Identity Integration Server 2003 (MIIS 2003). If a connected data source does not provide a built-in (native) mechanism to generate delta data for a data import operation, you must build a solution that mimics the delta data behavior based on the functionalities provided by the connected data source. An example of such a solution is the delta view in a management agent for SQL Server.

A delta view provides the most recent identity data changes for a delta import operation. It is the responsibility of the solution designer to develop a delta view and processes that provide only the most recent identity data changes.

This document discusses one option for generating a delta view that is based on the trigger approach. In case of the SQL management agent, a master table contains the most recent state of object data in a SQL database and the changes table contains the most recent object level changes applied to the masters table. By using a well-defined set of triggers on the master tables, you can determine whether a change that is about to be applied to a master table requires to be tracked in the changes table.

What This Document Covers

This document is part of a series of documents that discuss challenges many businesses face when they use a management agent for SQL Server for identity integration. All documents in this series are based on a simple test environment that uses Active Directory® and a Microsoft SQL Server™ database as connected data sources for a server running MIIS 2003.

This document shows how to develop a delta view for a management agent for SQL Server using triggers. Specifically, it describes how to configure a testing environment to determine object-level delta data that includes changes applied to multi-value attributes. After completing the procedures in this document, you will be able to successfully create a delta view based on the trigger approach for objects maintained in an objects table and a multi-value table.

This document is based on user and group objects that MIIS 2003 maintains in Active Directory and synchronizes to a SQL Server database. The delta view in this document provides the most recent changes that MIIS 2003 received from Active Directory in the form of an export run profile. The delta view provides the required data for a confirming import operation from your management agent for SQL Server.

The following illustration shows the setup for connected data sources discussed in this document.

The document uses the following management agents:

• A management agent for Active Directory, named MyADMA

• A management agent for SQL Server, named MySQLMA

The script and code in the document assume ideal data. Advanced coding practices, such as developing structured code and handling invalid data, are beyond the scope of this document.

The following illustration shows the metadirectory design of the scenario discussed in this document.

Prerequisite Knowledge

This document assumes that you have a basic understanding of the following information technology (IT) concepts and tasks:

• Managing SQL Server databases, including creating databases, tables, and views

• Managing Active Directory, including managing organizational units, groups and users, and domain controllers

• Administering MIIS 2003, including the concepts described in Getting Started with MIIS 2003 Walkthrough (https://go.microsoft.com/fwlink/?LinkId=83357)

A description of how to set up MIIS 2003 and Active Directory is out of the scope of this document.

For an introduction to essential MIIS 2003 concepts, see the following documents:

• Getting Started with MIIS 2003 Walkthrough (https://go.microsoft.com/fwlink/?LinkID=83357).

For a description of all MIIS 2003 documentation, see Microsoft Identity Integration Server 2003 Documentation Roadmap (https://go.microsoft.com/fwlink/?LinkID=82465).

Audience

This Guide is intended for IT planners, systems architects, technology decision makers, consultants, infrastructure planners, and IT personnel who plan and develop MIIS 2003 solutions using a management agent for SQL Server.

Time requirements

This the procedures in this document require 90 to 120 minutes for a new user to complete. An experienced MIIS 2003 user can complete them in 40 to 60 minutes.

Note

These time estimates assume the testing environment is already configured and ready for testing to begin and do not include the time required to set up the test environment.

Scenario Description

Fabrikam, a fictitious corporation, uses Active Directory as a primary source for user and group management data. Periodically, they use MIIS 2003 to synchronize this data from Active Directory to the central SQL Server database. To ensure efficient data synchronization and confirm delta import operations, Fabrikam configures a delta view and fills this view with the delta data. This document describes the steps Fabrikam uses to generate the delta view based on a database triggers approach.

The testing environment

To perform the procedures in this document, your testing environment should have the following characteristics:

• One Active Directory domain controller (DC1)

• One server, named MIISSrv1, hosting MIIS 2003 with Microsoft Visual Studio® .NET 2003 development system installed

  This server requires Microsoft Windows Server® 2003, Enterprise Edition and Microsoft SQL Server 2000.

• One server, named, SQLSrv1, hosting SQL Server

  This server can run either SQL Server 2000 or SQL Server 2005.

In addition, this document assumes that all servers are running Windows Server 2003 and that all computers are members of the Fabrikam.com forest.

The following illustration shows the infrastructure used in the scenario for this document.

Note

It is possible to test the results of the procedures in this document on a computer that has all of these characteristics. However, to complete the procedures in this document, you must use SQL Server 2000 as the database backend.

You must have accounts with sufficient rights for the management agent for SQL Server and the management agent for Active Directory. This document uses the domain administrator account for both management agents. However, in a production environment, use appropriately locked-down accounts. For information about locking down accounts, see MIIS 2003 Security Considerations Guide (https://go.microsoft.com/fwlink/?LinkID=58877).

Before You Begin

This document provides you with the information you must have to develop a delta view for a management agent for SQL Server using the database trigger approach. To keep the procedures in this document independent from the version of SQL Server you are using, and to significantly reduce the time required to complete these procedures, you can use the scripts in the Appendix to automate and simplify administrative tasks.

For your convenience, the Appendix has two database table viewers—TableViewer and DeltaViewViewer. As you develop each component of the delta view, these viewers help you determine whether the component provides the expected results. TableViewer and DeltaViewViewer are HTA applications that are preconfigured to display the content of tables or views in an HTML page. You will find instructions for using both viewers later in this document.

This section includes a complete list of the scripts and code in this document and provides instructions for running the scripts and creating the viewers.

Scripts and code in this document

The following table shows the scripts and code that are included the Appendix.

Appendix	Description
Appendix A	HTA code to view the content of the scenario tables for this document
Appendix B	HTA code to view the contents of the delta view
Appendix C	Script to populate Active Directory objects
Appendix D	Script to create a database
Appendix E	Script to create the Objects table
Appendix F	Script to create the References table
Appendix G	Script to create the Changes Table
Appendix H	Script to create the schema objects
Appendix I	The provisioning code snippet
Appendix J	The ProvisioningtoSQL code
Appendix K	Script to clear the objects table
Appendix L	Script to apply changes to the Active Directory data
Appendix M	Script to set the database triggers
Appendix N	Script to create the delta view

Running the scripts

The scripts in this document are designed to run locally on a computer. You run a script that configures Active Directory objects on the Active Directory domain controller, and you run a script that configures SQL Server components on the computer running SQL Server.

To run a script

1. From the Appendix, copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixA.vbs.

3. Although the name of the file is irrelevant, it must have the .vbs file name extension.

4. To run the script, double-click the icon for the.vbs file.

   Exceptions to this procedure are noted in the Appendix.

Configuring the database viewers

As mentioned earlier, this document includes two viewers for the data in the scenario tables. The following sections provide information for creating the viewers.

Creating TableViewer

TableViewer is a HTML application (HTA) you can use to see, in one view, the content of all scenario tables that are part of this document.

To implement TableViewer

1. In Appendix A, copy the HTA code, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as an .hta file, for example, C:\TableViewer.hta.

For more information about using the supplied scripts, see Running the scripts.

To run TableViewer

• In SQL Server, double-click the TableViewer icon.

Creating DeltaViewViewer

DeltaViewViewer is a HTML application (HTA) you can use to see, in one view, the content of all scenario tables that are part of this document.

To implement DeltaViewViewer

1. In Appendix B, copy the HTA code, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as an .hta file, for example, C:\DeltaViewViewer.hta.

3. For more information about using the supplied scripts, see Running the scripts.

To run DeltaViewViewer

• In SQL Server, double-click the DeltaViewViewer icon.

Note

The TableViewer and DeltaViewViewer do not display any content at this point. You will find instructions on how to use these viewers later in this document.

Implementing the Procedures in This Document

As mentioned earlier, this document shows how to develop a delta view for a management agent for SQL Server using triggers. It describes how to configure a testing environment to determine object-level delta data that includes changes applied to multi-value attributes.

In this document, you configure Active Directory, SQL Server, and MIIS 2003 environments. You then populate these environments with test data and develop a delta view. Finally, you modify your management agent for SQL Server and test your delta view.

To implement the procedures in this document, you complete the following steps in the order shown:

1. Configure the Active Directory environment.

2. Configure the initial SQL Server environment.

3. Update of the metaverse schema.

4. Create the management agents.

5. Configure the object deletion rule.

6. Enable object provisioning.

7. Configure run profiles.

8. Generate data for the delta view.

9. Develop the delta view.

10. Modify the configuration of your management agent for SQL Server.

11. Test the delta view.

Later topics provide more detail about these steps.

Note

You must complete the configuration of the connected data sources before you configure MIIS because the management agent configuration depends on the availability of some connected data source components. For example, the import scope of the Active Directory management agent is limited to the newly created organizational unit. Each management agent performs a schema detection in a early state of the configuration phase, which is why the required connected data source structure must be in place before you can configure a management agent.

Configuring the Active Directory Environment

The Active Directory environment in this document is based on four test users and one security group. All objects are located in the organizational unit named MIISObjects.

The following illustration shows the Active Directory objects for this document.

As shown in the following illustration, the four test users (U1, U2, U3, and U4) are members of the security group (G1).

You can use the Active Directory Users and Computers snap-in to create the Active Directory environment for this document or you can use the script in Appendix C to create the environment. For more information about using the supplied scripts, see Running the scripts.

All objects are located in the Active Directory organizational unit named MIISObjects.

Note

To complete the procedures in this document, you do not have to enable the users or assign passwords to them.

To create the required objects using the Active Directory Users and Computers snap-in

• For information about using Active Directory Users and Computers snap-in, see Help.

To create the required objects using the script

1. In Appendix C, copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixC.vbs.

3. To run the script, double-click the icon for the.vbs file.

Configuring the Initial SQL Server Environment

The initial SQL Server environment for this document has a new SQL Server database, three tables, and two data viewers.

The following table shows the required initial SQL Server environment for this document.

Required element	Description
One new SQL Server database	Stores all required data for this document.
One objects table	Stores the Active Directory data.
One references table	Stores the Active Directory data.
One changes table	Stores the identity data updates
Two data viewers (TableViewer, DeltaViewViewer)	Display the content of the scenario tables.

The complete SQL environment contains a delta view, also. You will find more details on developing the delta view later in this document.

Configuring the SQL Server database

To store the Active Directory and delta view data, you must create a database on the computer running SQL Server. The following illustration shows the database, named ADObjects, on which this document is based.

Creating the SQL Server Database

You can use the tools provided by SQL Server to create the database manually or you can use the script in Appendix D to create the database. If you create the database manually, you must save it with the name ADObject. For more information about using the supplied scripts, see Running the scripts.

To configure the SQL Server database using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To configure the SQL Server database using the script

1. In Appendix D copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixD.vbs.

3. To run the script, double-click the icon for the.vbs file.

Configuring the SQL Server tables

To store the Active Directory and delta view data, you must create the following tables:

• One objects table

• One references table

• One changes table

The following illustration shows the required table structure.

The Structure of the objects table

The structure of an objects table in a real-world solution depends on the requirements of your scenario. In this document, the data stored for each object has the following columns:

• ObjectID

• ObjectType

• SamAccountName

• DistinguishedName

• Description

The following illustration shows the attributes that are maintained for each Active Directory object in the objects table.

The ObjectID column acts as unique identifier (ID) for each object stored in the objects table. A convenient way to maintain a unique identifier in SQL Server is the implementation of a column in which the IDENTITY property is set. By setting this property, you can specify both an identity number for the first row inserted into the table (Identity Seed property) and an increment (Identity Increment property) to be added to the seed to determine successive identity numbers.

When you insert values into a table with an identifier column, Microsoft® SQL Server™ automatically generates the next identity value by adding the increment to the seed. This configuration ensures that each new record in the table is assigned a unique identifier calculated by SQL Server. Each new record is automatically assigned a new unique identifier that is a counter, which is an increment of one of the last-used identity counter value.

The following illustration shows the configuration of a SQL Server column that automatically generates a unique ID in the form of a counter that is incremented by one for each new record added to the table.

In this document, MIIS 2003 also uses the ObjectID column as anchor. An anchor is a unique identifier that is used by MIIS 2003 to link objects in the connector space with objects in a connected data source.

In a SQL Server table, object data is stored as one record per object. In this document, all objects (the user object type and the group object type) are stored as records in the same objects table. By adding an ObjectType column to the objects table, you can distinguish between individual object types. The ObjectID and the ObjectType columns do not allow null values, but the other columns do. This is because you must add two dummy records as examples for different object types: one record for a user and one record for a group. When MIIS 2003 configures a management agent for SQL Server, it extracts the object type schema from the existing objects table. This is why this table must have one record for each supported object type when you configure a management agent for SQL Server.

Note

When you configured the ObjectID attribute, you specified a unique identifier for each object from Active Directory. However, the value of the ObjectID attribute is not intuitive. More intuitive unique identifiers are samAccountName and distingusihedName, which are attributes for a security principle in Active Directory. In this document, we use intuitive attribute names to help you follow the data flow.

The Description field provides an attribute that you can use to test the impact of attribute level changes to this solution. By changing the description field of one of the scenario objects, you can implement a simple attribute-level change.

Creating the objects table

The objects table is required to store object data that MIIS 2003 synchronized from Active Directory.

The following table shows the definition of the objects table.

Column name	Data type	Length	Allow nulls
ObjectID	int	4	Unchecked
ObjectType	char	20	Unchecked
SamAccountName	char	256	Checked
DistinguishedName	char	256	Checked
Description	char	256	Checked

The ObjectID value is the unique identity (ID) of each object created in the objects table. To enable SQL Server to automatically calculate the value, you must define this column as Identity, and then set the Identity Seed value to 1 and the Identity Increment value to 1.

You can use the tools provided by SQL Server to create the objects table or you can use the script in Appendix E to create the table. If you create the objects table manually, you must save the table with the name tblObjects. For more information about using the supplied scripts, see Running the scripts.

To create the objects table using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To create the objects table using the script

1. In Appendix E copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixE.vbs.

3. To run the script, double-click the icon for the.vbs file.

4. Use TableViewer to confirm that the delta view returns the results you expect.

The structure of the references table

In Active Directory, group membership data is stored as a reference attribute. A reference attribute contains a pointer to another object. The actual group membership data is stored in an attribute named member, which contains the distinguished name of each object that is a member of a group.

The following illustration shows the logical design of a group object in Active Directory.

In a common database, each row in the database represents a single object. For example, the following objects table has one row for each user and group object that was imported from Active Directory.

ObjectID	ObjectType	SamAccountName	DistinguishedName	Description
75	User	U2	CN=U2,OU=MIISObjects,DC=Fabrikam,DC=com	Test user
77	Group	G1	CN=G1,OU=MIISObjects, DC=Fabrikam,DC=com	Test group

When an object in a single row has an attribute that can have more than one value, that attribute is referred to as a multi-value attribute. MIIS 2003 has no standard table configuration for objects in a single row that have multi-value attributes. For example, the following objects table combines each value of a multi-value attribute into a single column (member).

ObjectID	ObjectType	SamAccountName	DistinguishedName	Description	Member
75	User	U2	CN=U2,OU=MIISObjects, DC=Fabrikam,DC=com	Test user	-
77	Group	G1	CN=G1,OU=MIISObjects, DC=Fabrikam,DC=com	Test group	75, 78, 79

MIIS 2003 does not support a table in which multiple values exist in a single column. Because a single column for a single row can have more than one value, MIIS 2003 provides no guaranteed way of changing a single value.

To import objects with multi-value attributes from a database, you must specify a primary table and a secondary table for multi-value attributes. The secondary table must reside in the same database as the primary table. When you configure your management agent on the Connect to Database page of Management Agent Designer, you can specify the primary table and the multi-value table. You use the primary table to import all objects into the connector space and export all objects from the connector space. You use the secondary, multi-value table to import and export the multi-value attributes. The multi-value table used in this document has three columns

• An ObjectID column to track the object with which the multi-value attribute is associated

• An AttributeName column to track the name of the multi-value attribute

• A ReferenceID column to track the ObjectID value for the object referenced by a value of a multi-value attribute

The following table shows these columns.

ObjectID value	AttributeName	ReferenceID
77	member	75
77	member	78
77	member	79

Creating the references table

The references table is required to store the multi-value membership data of the Active Directory group used in this document. The following table shows the required definition of the references table.

Column name	Data type	Length	Allow nulls
ObjectID	int	4	Unchecked
AttributeName	char	20	Unchecked
ReferenceID	int	4	Unchecked

You can use the tools provided by SQL Server to create the references table or you can use the script in Appendix F to create the table. If you create the references table manually, you must save it with the name tblReferences. For more information about using the supplied scripts, see Running the scripts.

To create the references table using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To create the references table using the script

1. In Appendix F copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixF.vbs.

3. To run the script, double-click the icon for the.vbs file.

Adding schema objects to the objects table

This document supports multiple object types for the management agent for SQL Server. The object types are provided by the ObjectType column in the master table. When you configure your management agent for SQL Server, the supported object types must be available in the objects table. This is why you must add two schema records to the objects table: one for a sample user and one for a sample group.

The following table shows the initial content of the objects table.

tblObjects
ObjectID	ObjectType	SamAccountName	DistinguishedName	Description
1	User	null	null	null
2	Group	null	null	null

Note

The ObjectType is the only field that requires a specific value.

You must add two records to the objects table. For both records, you have to provide only the ObjectType data. Add one record with the user object type and one record with the group object type. For more information about using the scripts in this document, see Running the scripts.

You can use the tools provided by SQL Server to add the schema objects to the objects table or you can use the script in Appendix H to add the objects. If you add the objects manually, you must add two records to the objects table. For both records, only the ObjectType data is required. Add one record with the user object type and one record with the group object type. For more information about using the supplied scripts, see Running the scripts.

To add schema objects to the objects table using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To add schema objects to the objects table using the script

1. In Appendix H copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixH.vbs.

3. To run the script, double-click the icon for the.vbs file.

4. Use TableViewer to confirm that the delta view returns the results you expect

Creating the changes table

The changes table is required to store the new updates that are applied to the objects and the references table. The following table shows the required definition of the references table.

Column name	Data type	Length	Allow nulls
ObjectID	int	4	Unchecked
DeltaOperation	char	10	Unchecked

You can use the tools provided by SQL Server to create the references table or you can use the script in Appendix G to create the table. If you create the references table manually, you must save it with the name tblChanges. For more information about using the supplied scripts, see Running the scripts.

To create the changes table using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To create the changes table using the script

1. In Appendix G copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixG.vbs.

3. To run the script, double-click the icon for the.vbs file.

Updating the Metaverse Schema

To simplify the provisioning logic for this document, you must have two new metaverse object types: ADGroup, and ADUser. The following attributes are required for each object type:

• ADGroup: DistinguishedName, SamAccountName, description, member

• ADUser: DistinguishedName, SamAccountName, description

The following illustration shows the newly-created object types in Metaverse Designer.

To update the metaverse schema

1. In MIIS 2003, open Identity Manager.

2. Switch to Metaverse Designer.

3. On the Actions menu, click Create Object Type.

4. In the Object type name box, type ADUser.

5. In the Available attributes box, select description.

6. Click New attribute.

7. In the Attribute name box, type DistinguishedName.

8. To add the new attribute to this object type, click OK.

9. Click New attribute again.

10. In the Attribute name box, type SamAccountName.

11. To add the new attribute to this object type, click OK.

12. On the Actions menu, click Create Object Type.

13. In the Object type name box, type ADGroup.

14. In the Available attributes box, select DistinguishedName, SamAccountName, member, and description.

15. To add the new object type, click OK.

Creating the Management Agents

For this document, you must create two management agents: one for Active Directory and one for SQL Server.

Creating the management agent for Active Directory

To create the management agent for Active Directory, you use the Create Management Agent Wizard.

To create a management agent for Active Directory

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. On the Actions menu, click Create to start the Create Management Agent Wizard.

4. Specify the required parameters for each page, and then click Next.

5. Click Finish to create the management agent.

Create Management Agent page

On this page, you select the type of management agent you want to create, and then name it.

To complete the Create Management Agent page

1. In the Management agents for list, select Active Directory.

2. In the Name box, type MyADMA, and then click Next.

Connect to Active Directory Forest page

On this page, you enter the name of your Active Directory forest and provide data for the account that this management agent uses to connect to that forest.

Note

In a real-world scenario, you can use whatever name you choose for the forest and domain, and any user account that has sufficient rights.

To complete the Connect to Active Directory Forest page

1. In the Forest name box, type fabrikam.com.

2. In the User name box, type administrator.

3. In the Password box, type the administrator's password.

4. In the Domain box, type fabrikam, and then click Next.

Configure Directory Partitions page

On this page, you select your directory partition and the container (organizational unit) that contains the Active Directory objects that are part of this document.

To complete the Configure Directory Partitions page

1. In the Select directory partitions box, select DC=Fabrikam,DC=Com.

2. Click Containers to open the Select Containers dialog box.

3. In the Select Containers dialog box, verify that only MIISObjects is selected.

4. To close the Select Containers dialog box, click OK.

5. On the Configure Directory Partitions page, click Next.

Select Object Types page

On this page, you select the object types you plan to manage with a management agent. For this document, you must select the object types specified in the following procedure.

To complete the Select Object Types page

1. In the Select Object Types box, select the following object types:

   • container

   • domainDNS

   • group

   • organizational unit

   • user

2. Click Next.

Select Attributes page

On this page, you specify the attributes in your scenario. For this document, you must select the attributes specified in the following procedure.

To complete the Select Attributes page

1. In the Attributes box, select the following attributes:

   • cn

   • description

   • member

   • sAMAccountName

2. Click Next.

Configure Connector Filter page

You do not have to configure anything on this page.

To complete the Configure Connector Filter page

• Click Next.

Configure Join and Projection Rules page

On this page, you configure the required join and projection rules for your scenario. This document requires you to configure a projection rule for the user object type and the group object type.

The following illustration shows the Configure Join and Projection Rules dialog box after you have applied all projection rules for this document.

The following table shows the data source object type and the metaverse object type pairs for which you must configure a projection.

Projection rule	Data source object type	Metaverse object type
Rule 1	user	ADUser
Rule 2	group	ADGroup

To complete the Configure Join and Projection Rules page

1. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data Source Object Type column, select the data source object type shown for that row in the table.

   2. To open the Projection dialog box, click New Projection Rule.

   3. Select Declared.

   4. In the Metaverse object type list, select the metaverse object type shown for that row in the table.

   5. Select ADUser.

   6. To close the Projection dialog box, click OK.

2. Click Next.

Configure Attribute Flow page

On this page, you provide the import and export attribute flow rules for your scenario. This document requires you to configure import attribute flow rules for the user and group objects of the management agent for Active Directory:

The following illustration shows the Configure Attribute Flow dialog box after you have applied all attribute flow rules for the user object.

The following table shows the data source and metaverse attribute pairs for which you must configure a flow rule.

Flow rule	Data source attribute	Metaverse attribute
Rule 1	<dn>	Distinguished Name
Rule 2	Description	Description
Rule 3	sAMaccountName	SamAccountName

To complete the Configure Attribute Flow page for the object type user

1. In the Data source object type box, select User.

2. In the Metaverse object type box, select ADUser.

3. Under Mapping Type, select Direct.

4. Under Flow Direction, select Import.

5. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data source attribute list, select the data source attribute shown for that row in the table.

   2. In the Metaverse attribute list, select the metaverse attribute shown for that row in the table.

   3. Click New.

The following illustration shows the Configure Attribute Flow dialog box after you have applied all attribute flow rules for the group object.

The following table shows the data source and metaverse attribute pairs for which you must configure a flow rule.

Flow rule	Data source attribute	Metaverse attribute
Rule 1	<dn>	Distinguished Name
Rule 2	Description	Description
Rule 3	sAMaccountName	SamAccountName
Rule 4	member	member

To complete the Configure Attribute Flow page for the object type group

1. In the Data source object type box, select Group.

2. In the Metaverse object type box, select ADGroup.

3. Under Mapping Type, select Direct.

4. Under Flow Direction, select Import.

5. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data source attribute list, select the data source attribute shown for that row in the table.

   2. In the Metaverse attribute list, select the metaverse attribute shown for that row in the table.

   3. Click New.

Configure Deprovisioning page

You do not have to configure anything on this page.

To complete the Configure Deprovisioning page

• Click Next.

Configure Extensions page

You do not have to configure anything on this page.

To complete the Configure Extensions page

• To create the management agent, click Finish.

Creating the management agent for SQL Server

To create the management agent for SQL Server, you use the Create Management Agent Wizard.

To create a management agent for SQL Server

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. On the Actions menu, click Create to start the Create Management Agent Wizard.

4. Specify the required parameters for each page, and then click Finish to create the management agent.

Create Management Agent page

On this page, you select the type of management agent you want to create, and then name it.

To complete the Create Management Agent page

1. In the Management agents for list, select SQL Server.

2. In the Name box, type MySQLMA, and then click Next.

Connect to Database page

On this page, you enter the location of the data that is managed by this management agent and data about the account that is used by this management agent to connect to the database.

To complete the Connect to Database page

1. In the Server box, type the name of your SQL Server.

2. In the Database box, type ADObjects.

3. In the Table/View box, type tblObjects.

4. In the Multivalue Table box, type tblReferences.

5. Select the Windows integrated authentication option button.

6. In the User name box, type administrator.

7. In the Password box, type the administrator's password.

8. In the Domain box, type fabrikam.

9. Click Next.

Configure Columns page

On this page, you provide details about the columns in your source tables that MIIS 2003 detected. For this document, you must set an anchor, configure the multi-value settings, and then specify object types.

To set the anchor

1. To open the Set Anchor dialog box, click Set Anchor.

2. In the Available attributes box, select ObjectID.

3. To add the attribute to the Selected attributes box, click Add.

4. To close the Set Anchor dialog box, click OK.

To configure multi-value settings

1. To open the Multi-value settings dialog box, click Multi-value.

2. In the Specify the attribute name box, select AttributeName.

3. Select the Number attribute column check box, and then select ReferenceID.

4. To open the Multi-value Attribute dialog box, click New.

5. In the Name box, type member.

6. In the Type box, select Number.

7. Select the Reference (DN) check box.

8. To close the Multi-value Attribute dialog box, click OK.

9. To close the Multi-value settings dialog box, click OK.

To specify object types

1. Click Object Type to open the Set Object Type dialog box.

2. Select the Object type column option button, and then select ObjectType.

3. To close the Set Object Type dialog box, click OK.

4. Click Next.

Configure Connector Filter page

You do not have to configure anything on this page.

To complete the Configure Connector Filter page

• Click Next.

Configure Join and Projection page

You do not have to configure anything on this page.

The complete the Configure Join and Projection page

• Click Next.

Configure Attribute Flow page

On this page, you provide the import and export attribute flow rules for your scenario. This document requires you to configure export attribute flow rules for the user and group objects of the management agent for SQL Server:

The following illustration shows the Configure Attribute Flow dialog box after you have applied all attribute flow rules for the group object.

The following table shows the data source and metaverse attribute pairs for which you must configure a flow rule.

Flow rule	Data source attribute	Metaverse attribute
Rule 1	Description	description
Rule 2	Distinguished Name	Distinguished Name
Rule 3	member	member
Rule 4	SamAccountName	SamAccountName

To complete the Configure Attribute Flow page for the object type group

1. In the Data source object type box, select Group.

2. In the Metaverse object type box, select ADUser.

3. Under Mapping Type, select Direct.

4. Under Flow Direction, select Export.

5. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data source attribute list, select the data source attribute shown for that row in the table.

   2. In the Metaverse attribute list, select the metaverse attribute shown for that row in the table.

   3. Click New.

The following illustration shows the Configure Attribute Flow dialog box after you have applied all attribute flow rules for the user object.

The following table shows the data source and metaverse attribute pairs for which you must configure a flow rule.

Flow rule	Data source attribute	Metaverse attribute
Rule 1	Description	description
Rule 2	Distinguished Name	Distinguished Name
Rule 3	SamAccountName	SamAccountName

To complete the Configure Attribute Flow page for the object type user

1. In the Data source object type box, select User.

2. In the Metaverse object type box, select ADUser.

3. Under Mapping Type, select Direct.

4. Under Flow Direction, select Export.

5. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data source attribute list, select the data source attribute shown for that row in the table.

   2. In the Metaverse attribute list, select the metaverse attribute shown for that row in the table.

   3. Click New.

Configure Deprovisioning page

On this page, you configure the deprovisioning synchronization rule for your scenario. The deprovisioning synchronization rule defines what should happen to a connector space object that was disconnected during outbound synchronization. This document requires you to stage a deletion on the disconnected object.

To complete the Configure Deprovisioning page

• Select Stage a delete on the object for the next export operation, and then click Next.

Configure Extensions page

You do not have to configure anything on this page.

To complete the Configure Extensions page

• To create the management agent, click Finish.

Configuring the Object Deletion Rule

In a scenario that synchronizes objects from Active Directory to SQL Server, it is important to include a solution for the case where objects are deleted in Active Directory. Because Active Directory is authoritative for the objects that are discussed in this document, you can safely delete metaverse objects after MIIS 2003 removes the link between an object in the Active Directory connector space and an object in the metaverse during inbound synchronization. You delete metaverse objects by setting the object deletion rule to delete metaverse objects when they are disconnected from an object in the Active Directory connector space.

For this document, you must configure two object deletion rules: one for the ADGroup object type and one for the ADUser object type.

The following illustration shows Metaverse Designer after you have applied all object deletion rules.

The following table shows the object types and the management agent pairs for which you must configure an object deletion rule.

Object deletion rule	Object types name	Management agent name
Rule 1	ADGroup	MyADMA
Rule 2	ADUser	MyADMA

To configure the object deletion rule for the ADUser and ADGroup object types

1. In MIIS 2003, open Identity Manager.

2. Switch to Metaverse Designer.

3. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Name column of the Object types table, select the object types name shown for that row in the table.

   2. From the Actions menu, select Configure Object Deletion Rule to open the Configure Object Deletion Rule dialog box.

   3. Select the Delete metaverse object when connector from this management agent is disconnected option button.

   4. In the management agents box, select the management agent name shown for that row in the table.

   5. To close the Configure Object Deletion Rule dialog box, click OK.

Enabling Object Provisioning

Enabling object provisioning has the following steps:

1. Write code for the provisioning method.

2. Build a metaverse rules extension.

3. Enable the metaverse rules extension.

First, you write code for your provisioning method.

Note

This document provides you with the two object types—the user object type and the group object type—that you must implement in the provisioning method to decrease your code development time.

During provisioning, MIIS 2003 has to detect only whether a change was applied to an object that is of one of the new types. If it detects a change, it calls another custom method, ProvisionToSQL.

The following code shows an abbreviated version of the provisioning code.

Public Sub Provision(…) Implements …
   Try
      If (mventry.ObjectType.Equals("ADUser")) Then ProvisionToSQL(True, mventry)
      If (mventry.ObjectType.Equals("ADGroup")) Then ProvisionToSQL(False, mventry)

   Catch ex As Exception
      Throw ex
   End Try
End Sub

The ProvisionToSQL method reacts only if no connector to the SQL connector space is available. The ProvisionToSQL method uses the isUser Boolean parameter to determine the correct object type for a new connector.

In this document, the SQL Server database calculates and maintains the anchor for new objects.

To provision objects that will be exported to connected data sources that are in charge of managing the anchor attribute, you must assign a temporary, unique distinguished name to the attribute. You can create this distinguished name manually based on attributes on the metaverse object, or you can use the System.Guid.NewGuid method to create the temporary distinguished name. When the object is exported to the data source, the data source changes the distinguished name property from the temporary value to the permanent value.

The following code shows the ProvisionToSQL method.

Private Sub ProvisionToSQL(ByVal isUser As Boolean, _
                           ByVal mventry As MVEntry)
   Try
      Dim myMA As ConnectedMA = mventry.ConnectedMAs("MySQLMA")
      If myMA.Connectors.Count <> 0 Then Exit Sub

      Dim objectType As String = "Group"
      If isUser Then objectType = "User"

      Dim obCS As CSEntry
      obCS = myMA.Connectors.StartNewConnector(objectType)

      Dim DN As ReferenceValue
      DN = myMA.EscapeDNComponent(System.Guid.NewGuid().ToString)

      obCS.DN = DN
      obCS.CommitNewConnector()
   Catch ex As Exception
      Throw ex
   End Try
End Sub

Next, you build the metaverse rules extension, which is based on your provisioning code in Visual Studio .NET.

Finally, you enable the metaverse rules extension in MIIS 2003. The following illustration shows the Options dialog box in MIIS 2003, which you use to create the rules extension.

To build a metaverse rules extension

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the Tools menu, select Options to open the Options dialog box.

4. Select the Enable metaverse rules extension check box.

5. Select the Enable Provisioning rules extension check box.

6. Click Create Rules Extension Project to open the Create Extension Project dialog box.

7. In the Project name box, type MVExtension.

8. Select Launch in VS.Net IDE, and then click OK to start Visual Studio .NET.

9. To open Visual Studio .NET, click OK.

10. Copy the code snippet from Appendix I, and then paste it into the body of the Provision method for your new project.

11. Copy the code for the ProvisionToSQL method from Appendix J, and then paste it under the Provision method.

12. From the Build menu, select Build Solution.

13. In MIIS 2003, in the Options dialog box, click Browse.

14. In the list of available files, select MVExtension.dll.

15. To activate your metaverse rules extension, click OK.

Configuring Run Profiles

This topic provides instructions for creating and configuring the required run profiles. For this document, you must configure several run profiles for the management agent for Active Directory and the management agent for SQL Server.

The following illustration shows the Configure Run Profiles for dialog box after you have configured all run profiles for MyADMA, the management agent for Active Directory.

The following table shows the run profiles that you must create for the management agent for MyADMA.

Profile	Run profile name	Step type
Profile 1	Delta Import	Delta Import (Stage Only)
Profile 2	Delta Synchronization	Delta Synchronization
Profile 3	Full Import	Full Import (Stage Only)
Profile 4	Full Synchronization	Full Synchronization

To create the run profiles for the management agent for Active Directory

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. In the management agent list, select MyADMA.

4. On the Actions menu, click Configure Run Profiles to open the Configure Run Profiles for dialog box.

5. For each run profile in the table immediately above this procedure, complete the following steps:

   1. To open the Configure Run Profile Wizard, click New Profile.

   2. In the Name box, type the profile name shown the table, and then click Next.

   3. In the type list, select the step type shown in the table, and then click Next.

   4. Click Finish to create the run profile.

For this document, you use the Configure Run Profile Wizard to configure two parameters for each run profile.

The following illustration shows the Configure Run Profiles for dialog box after you have configured all run profiles for MySQLMA, the management agent for SQL Server.

The following table shows the run profiles that you must create for the management agent for MySQLMA.

Profile	Run profile name	Step type
Profile 1	Full Import	Full Import (Stage Only)
Profile 2	Export	Export

To create the run profiles for the management agent for SQL Server

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. In the management agent list, select MySQLMA.

4. On the Actions menu, click Configure Run Profiles to open the Configure Run Profiles for dialog box.

5. For each run profile in the table immediately above this procedure, complete the following steps:

   1. To open the Configure Run Profile Wizard, click New Profile.

   2. In the Name box, type the profile name shown the table, and then click Next.

   3. In the type list, select the step type shown in the table, and then click Next.

6. Click Finish to create the run profile.

Generating Data for the Delta View

After you set up the infrastructure, you must populate the environment with data. In this document, you synchronize object data from Active Directory to SQL Server. The data includes the Active Directory objects you have already created and changes you will make to that data. The delta view extracts these changes from the SQL Server tables.

Establishing the delta view has the following steps:

1. Delete the schema objects in the objects table.

2. Synchronize the Active Directory objects to SQL Server.

3. Verify the result of full synchronization.

4. Apply changes to the Active Directory data.

5. Develop the delta view based on triggers.

6. Synchronize the Active Directory changes to the SQL Server connector space.

You must have two synchronization cycles because the objective of this document is to show an implementation example of a delta view, which requires the availability of delta data that is in the scenario of this document provided by these two synchronization cycles.

Deleting the schema objects in the objects table

At this point, you can safely delete the schema object from the objects table. You can use the tools provided by SQL Server to delete the schema objects from the objects table or you can use the script in Appendix K to delete the objects. For more information about using the supplied scripts, see Running the scripts.

To delete the schema objects in the objects table using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To delete the schema objects in the objects table using the script

1. In Appendix K copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixK.vbs.

3. To run the script, double-click the icon for the.vbs file.

4. Use TableViewer to confirm that the delta view returns the results you expect.

Synchronizing the Active Directory objects to SQL Server

In this step, you fully synchronize the new Active Directory objects to SQL Server. Fully synchronizing these objects has the following steps:

1. Import into the connector space of the management agent for Active Directory.

2. Synchronize within MIIS 2003.

3. Export to the SQL Server tables.

4. Run a confirming import operation to import the Active Directory objects into MIIS 2003 from the management agent for SQL Server.

You run the appropriate run profiles to accomplish these steps.

The following table shows the order in which you must run the run profiles to fully synchronize your Active Directory objects.

Step	Management agent name	Run profile name
1	MyADMA	Full Import
2	MyADMA	Full Synchronization
3	MySQLMA	Export
4	MySQLMA	Full Import

To start a run profile in MIIS 2003

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. For each row in the table immediately above this procedure, complete the following steps:

   1. Select the management agent shown for that row in the table.

   2. On the Actions menu, click Run to open the Run Management Agent dialog box.

   3. In the Run profiles list, select the run profile shown for that row in the table, and then click OK to start it.

Verifying the result of full synchronization

After synchronizing the Active Directory objects to SQL Server, the four users from Active Directory (U1, U2, U3, and U4), the group (G1), and the group membership should be in your master tables.

To verify whether all Active Directory objects have successfully synchronized to the SQL Server database, you can use tools provided by SQL Server or use TableViewer. For more information about using TableViewer, see Configuring the database viewers.

The following table shows the content of the master tables after a successful synchronization of the Active Directory objects to SQL Server.

To verify the result of full synchronization using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To verify the result of full synchronization using TableViewer

• In SQL Server, double-click the TableViewer icon.

Applying changes to the Active Directory data

To generate delta data for the delta view, you must apply some changes to the Active Directory data structure, and then synchronize these changes to the SQL Server.

You should apply the following changes to your Active Directory structure:

• Delete user U1.

• Modify the description field of user U2 to New Description.

• Create new user U5.

• Add U5 to group G1.

• Create new group G2.

• Add user U5 to group G2.

You can apply these changes directly to Active Directory or use the script in Appendix J to apply the changes. For more information about using the supplied scripts, see Running the scripts.

To apply changes to the Active Directory data using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To apply changes to the Active Directory data using the script

1. In Appendix L, copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixL.vbs.

3. To run the script, double-click the icon for the.vbs file.

4. Use TableViewer to confirm that the delta view returns the results you expect.

Completing a delta synchronization of the Active Directory object changes to SQL Server

In this step, you synchronize the changes applied to your Active Directory objects to SQL Server. Synchronizing these objects has the following steps:

1. Import the objects into the connector space of the management agent for Active Directory.

2. Synchronize the objects in MIIS 2003.

3. Export the objects to the SQL Server tables.

You run the appropriate run profiles to accomplish these steps.

The following table shows the order in which you must run the run profiles to fully synchronize your Active Directory objects.

Step	Management agent name	Run profile name
1	MyADMA	Delta Import
2	MyADMA	Delta Synchronization
3	MySQLMA	Export

To start a run profile in MIIS 2003

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. Use the information in the table immediately before this procedure to identify the management agent you want to use.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the Run profiles list, select the run profile you want, and then click OK to start it.

Completing a delta synchronization of the Active Directory object changes to the SQL Server connector space

In this step, you synchronize the changes applied to your Active Directory objects to SQL Server. Synchronizing these objects has the following steps:

1. Import the objects into the connector space of the management agent for Active Directory.

2. Synchronize the objects in MIIS 2003.

You run the appropriate run profiles to accomplish these steps.

The following table shows the order in which you must run the run profiles to fully synchronize your Active Directory objects.

Step	Management agent name	Run profile name
1	MyADMA	Delta Import
2	MyADMA	Delta Synchronization

To start a run profile in MIIS 2003

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. Use the information in the table immediately before this procedure to identify the management agent you want to use.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the Run profiles list, select the run profile you want, and then click OK to start it.

Developing the Delta View

This topic introduces the components that are necessary to extract the delta data from the master tables.

A delta view extracts changes from a specified set of SQL Server tables. This document explains how to extract these changes based on the trigger approach. This approach is based on master tables that contain the most recent state of the object data in SQL and a changes table that contains the most recent updates applied to the master tables. To fill the changes table with data, you need to configure first triggers on the master table that determine whether the changes table needs to be updated when new identity data is applied to the master tables. The delta view combines the information in the changes table with the data in the objects table into a record set that contain the most recent object level changes including the modification type (Add, Modify, Delete).

The following table shows the Appendix for each script related to developing the delta view.

Appendix	Description
Appendix M	Script to set the database triggers
Appendix N	Script to create the delta view

Defining triggers

SQL Server supports using triggers as a kind of stored procedure. Triggers are executed when a specified data modification, such as an attempt to delete a row, is attempted on the table on which the trigger is defined.

In the scenario of this document, the objects and the references table are both updated with new identity data by the run of an export run profile on the SQL Server management agent. To capture the applied updates, you need to define triggers on both tables for the following events:

• When a row has been added to the objects table

• When a row has been deleted from the objects table

• When a row has been modified in the objects table

• When a row has been added to the references table

• When a row has been deleted from the references table

Each of these events represents a potential delta change that may have to be captured / stored in the changes table. Potential means that not all change events for an object require an update the changes table. For example, if several modify triggers are processed for the same object, only one record needs to be added to the change table, since the scope of this document is to only track object level changes. If an add and a modify trigger was initiated for the same object, only the add event needs to be captured in the changes table. The same applies to a delete and modify event.

A trigger has an associated table that contains the affected records. For example, in case of a delete event, the delete trigger is processed with a temporary table called "deleted” that contains the deleted records. In case of an add or a modify event, the name of the table is "inserted”. You can use the content of these temporary tables to extract the data that has changed for the changes table.

In case of an add trigger on the objects table, records in the changes table with the same ObjectID value as the records in the inserted table have to be deleted from the changes table. As a next step, the records from the inserted table are added to the changes table with a modification type of "Add”. The following SQL statement shows an example of this.

CREATE TRIGGER trAddedObjects ON dbo.tblObjects FOR INSERT AS
DELETE FROM tblChanges WHERE ObjectID IN (SELECT ObjectID from inserted)
INSERT INTO tblchanges SELECT DISTINCT inserted.ObjectID, 'Add' AS DeltaOperation    FROM inserted

In case of a delete trigger on the objects table, records in the changes table with the same ObjectID value as the records in the deleted table have to be deleted from the changes table. As a next step, the records from the deleted table are added to the changes table with a modification type of “Delete”. The following SQL statement shows an example of this.

CREATE TRIGGER trDeletedObjects ON dbo.tblObjects FOR DELETE AS 
DELETE FROM tblChanges WHERE ObjectID IN (SELECT ObjectID from deleted)
INSERT INTO tblchanges
SELECT DISTINCT deleted.ObjectID, 'Delete' AS DeltaOperation
FROM deleted

In case of a modified trigger on the objects table, a record for a row in the inserted table is only added to the changes table if a record with the same ObjectID value does not exist yet. In this case, the records from the inserted table are added to the changes table with a modification type of “Modify”. The following SQL statement shows an example of this.

CREATE TRIGGER trModifiedObjects ON dbo.tblObjects FOR UPDATE AS
INSERT INTO tblchanges 
SELECT DISTINCT inserted.ObjectID, 'Modify' AS DeltaOperation
FROM inserted LEFT OUTER JOIN tblChanges 
ON inserted.ObjectID = tblChanges.ObjectID
WHERE (tblChanges.ObjectID IS NULL)

An add trigger on the references table, translates to a modify event. In case of the scenario in this document, an add trigger on the references table means that a member has been added to a scenario group. You only need to capture this event if the changes table doesn’t have a record for the affected group object yet. The following SQL statement shows an example of this.

CREATE TRIGGER trAddedReferences ON tblReferences FOR INSERT AS
INSERT INTO tblchanges
SELECT DISTINCT inserted.ObjectID, 'Modify' AS DeltaOperation
FROM inserted LEFT OUTER JOIN
tblChanges ON inserted.ObjectID = tblChanges.ObjectID
WHERE (tblChanges.ObjectID IS NULL)

A delete trigger on the references table, also translates to a modify event. In case of the scenario in this document, a delete on the references table means that a member has been removed from a scenario group. You only need to capture this event if the changes table doesn’t have a record for the affected group object yet. The following SQL statement shows an example of this.

CREATE TRIGGER trDeletedReferences ON tblReferences FOR DELETE AS
INSERT INTO tblchanges
SELECT DISTINCT deleted.ObjectID, 'Modify' AS DeltaOperation
FROM deleted LEFT OUTER JOIN
tblChanges ON deleted.ObjectID = tblChanges.ObjectID
WHERE (tblChanges.ObjectID IS NULL)

You can use the tools provided by SQL Server to define the required triggers or you can use the script in Appendix M. For more information about using the supplied scripts, see Running the scripts.

To define the triggers using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To define the triggers using the script

1. In Appendix M, copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixM.vbs.

3. To run the script, double-click the icon for the.vbs file

Building the delta view

A delta view lists the most recent changes that MIIS 2003 applied to your SQL server tables. Each record in this list must have a column that identifies the type of change—Add, Delete, or Modify. The previous section explained how to extract the ObjectID attribute value and the change type value by using triggers from the scenario tables and how to store these values in the changes table. The objective of the delta view is to combine the information in the changes table and the data in the objects table. For deletes, there is no need to provide the complete record of a deleted object. All you need to provide is the Object_ID attribute value and the ChangeType attribute value.

Since the objects table does not contain information about the deleted objects anymore, you can build the data for the delta view by using a LEFT OUTER JOIN of the changes table and the objects table. The following SQL statement shows an example of this.

SELECT          tblChanges.ObjectID,
                tblChanges.DeltaOperation, 
                tblObjects.ObjectType,
                tblObjects.SamAccountName,
                tblObjects.DistinguishedName,
                tblObjects.Description
FROM            tblChanges 
LEFT OUTER JOIN tblObjects 

ON tblChanges.ObjectID = dbo.tblObjects.ObjectID

In this document, you must encapsulate the SQL statement within a view named vwDelta.

If you run the delta view in SQL server, it returns the records of all added, deleted and modified objects. The view should return the most recent records for the objects 6, 7, 3, 2 and 5, as shown in the following table, which shows TableViewer results based on data in the scenario tables.

Note

Because MIIS 2003 processes objects in random order, the values for the ObjectID attributes in your test environment may differ from those shown in this table.

You can use the tools provided by SQL Server to create the delta view or you can use the script in Appendix N to create the view. For more information about using the supplied scripts, see Running the scripts.

To build the delta view using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To build and verify the delta view using the script

1. In Appendix N, copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixN.vbs.

3. To run the script, double-click the icon for the.vbs file.

4. Use DeltaViewViewer to confirm that the delta view returns the results you expect.

Modifying the Configuration of Your Management Agent for SQL Server

Before you can test the delta view, you must modify the configuration of your management agent for SQL Server. First, you update the delta view configuration, and then you create a delta import run profile.

To update your management agent for SQL Server

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MySQLMA.

4. On the Actions menu, click Properties.

5. From the Management Agent Designer list, select Connect to Database.

6. In the Delta View box, type vwDelta.

7. In the Password box, type the password for the administrator account.

8. From the Management Agent Designer list, select Configure Columns.

9. In the Configure Columns pane, click Configure Delta to open the Configure Delta dialog box.

10. From the Change type attribute list, select Delta Operation.

11. In the Modify box, type Modify.

12. In the Add box, type Add.

13. In the Delete box, type Delete.

14. To close the Configure Delta dialog box, click OK.

15. To close the Management Agent Designer, click OK.

To create the Delta Import run profile

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MySQLMA.

4. On the Actions menu, click Configure Run Profiles to open the Configure Run Profiles for dialog box.

5. To open the Configure Run Profile Wizard, click New Profile.

6. In the Name box, type Delta Import, and then click Next.

7. From the type list, select Delta Import (Stage Only), and then click Next.

8. To create the run profile, click Finish.

Testing the Delta View

As a last step, you need to test the newly created delta view. This test consists of the following steps:

• Exporting the staged updates to the SQL Server database

• Verifying that the delta view contains the expected data

• Running a confirming import on the SQL management agent

The following sections provide more detail information for each step and instructions.

Exporting the staged updates to the SQL Server database

After the required triggers are defined, you can export the identity data updates that are staged in the SQL Server connector space to the SQL Server database.

To export the stated updates to the SQL Server database

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. On the Actions menu, click Run to open the Run Management Agent dialog box.

4. In the Run profiles list, select the Export, and then click OK to start it.

Verifying that the delta view contains the expected data

At this point, you need to verify whether the scenario tables and the delta view contain the expected data. To do so, you can use the two viewers (TableViewer and DeltaViewViewer).

During the first complete synchronization cycle, you exported the four new users (U1, U2, U3 and U4) that were all members of the group G1 to the SQL Server database. The following illustration shows the TableViewer result after the first complete synchronization cycle.

Then, you applied the following changes to your Active Directory data:

The user U1 was deleted

The user U2 was modified

The user U5 was created

The group G2 was created

The user U5 was added to the group G2

You imported the changes into the connector space, synchronized them within MIIS and exported them into the SQL Server database. If you look at the list of applied changes, you will notice, that you have also applied a change to the Group G1 by deleting the user U1. As a result of the deletion of U1 the member attribute of the group G1 was also modified. Before you use TableViewer to examine the content of your scenario tables, you should write down your expectations. The following table shows an example of this.

Change	Description	Expected Result
1	The user U1 was deleted	Delete 4, Modify 3
2	The user U2 was modified	Modify 1
3	The user U5 was created	Add X
4	The group G2 was created	Add X
5	The user U5 was added to the group G2	Nothing

The changes table should have two new records (Delete 4, Modify 3) as a result of the deleted user U1. In addition to that, it should have one record for the modified user U2 (Modify 1). It should also have two records with a delta operation Add for the new user U5 and the new group G2. However, there is no way to predict the value for the ObjectID of the new objects, since the processing order in MIIS is random. Adding the user U5 to the group G2 should not result in a record in the changes table since there is already a record with the delta operation Add, which takes precedence over a Modify. The following illustration shows an example of the TableViewer after the changes have been exported to the SQL Server database:

As a last verification step, you should use the DeltaViewer to examine the content of the delta view. It should display the records of the updated objects as discussed above. For deleted objects, there is no technical need to track anything besides the anchor attribute value and the delta operation type.

The following illustration shows an example for this:

Running a confirming import on the SQL management agent

In MIIS 2003, a confirming import is always the required next step after an export run profile to complete an export operation. After running the delta import operation, verify that the following results are reported in the synchronization statistics:

• Adds: 2

• Updates: 2

• Deletes: 1

The following illustration shows the run statistics for a successful delta import operation.

To run a delta import operation

1. On the Actions menu, click Run to open the Run Management Agent dialog box.

2. In the list of run profiles, select Delta Import.

3. To start the run profile, click OK.

Appendices

Appendix A: HTA Code to View the Content of the Scenario Tables for This Document

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>SQL Table Viewer</title>

<style type="text/css">
body      {background-color:wheat;}
table     {width:100%;font-family:Tahoma; font-size:70%;background-color:lightgrey;}
td.tbname {font-weight:bold; background-color:papayawhip;}
td.head   {font-weight:bold; background-color:buttonface;}
td.data   {background-color:cornsilk;}
</style>

<script type="text/vbscript">
<!--
Option Explicit

Const AD_OPEN_STATIC = 3
Const AD_LOCK_OPTIMISTIC = 3
Const AD_USE_CLIENT = 3

'Customizable parameters:

Const MAX_ROWS      = 100
Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"

'Add the names of the tables or views you want to display here:
Dim tableNames
tableNames = array("tblObjects", _
                   "tblReferences")

Sub GetTableData
   Dim objConnection
   Set objConnection = CreateObject("ADODB.Connection")
   objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                      "Trusted_Connection=Yes;Initial Catalog=" & _
                      DATABASE_NAME & ";" 
   Dim objRecordset
   Set objRecordset = CreateObject("ADODB.Recordset")
   objRecordset.CursorLocation = AD_USE_CLIENT

   Dim tableName, tableData, oTable, oRow, oTbNameCell, oCell
   Set oTable = document.getElementById("dataTable")
      
   For Each tableName in tableNames
      Set oRow              = oTable.insertRow()
      Set oTbNameCell       = oRow.insertCell() 
      oTbNameCell.ClassName = "tbname"
      oTbNameCell.InnerText = tableName 
     
      objRecordset.Open "SELECT * FROM " & tableName , objConnection, _
                        AD_OPEN_STATIC, AD_LOCK_OPTIMISTIC
      
      If Not objRecordset.eof Then
         objRecordset.MoveFirst
         Set oRow   = oTable.insertRow()
         Dim curField
         For each curField in objRecordset.fields
            Set oCell  = oRow.insertCell() 
            oCell.ClassName = "head"
            oCell.InnerText =  Trim(curField.Name) 
         Next
        oTbNameCell.colSpan = objRecordset.fields.count
      End If
  
      Dim rowCounter 
      rowCounter = 0
      Do While Not objRecordset.eof
         Set oRow   = oTable.insertRow()
         For each curField in objRecordset.fields
            Set oCell  = oRow.insertCell() 
            oCell.ClassName = "data"
            oCell.InnerText =  Trim(curField) 
         Next
         objRecordset.MoveNext

         rowCounter = rowCounter + 1
         If rowCounter = MAX_ROWS Then
            Exit Do
         End If
      Loop
      objRecordset.Close
   Next
   objConnection.Close
End Sub
-->
</script>

<HTA:APPLICATION ID="oMyApp"
APPLICATIONNAME="SQLTableViewer"
APPLICATION="yes"
CAPTION="yes"
SINGLEINSTANCE="yes"
SYSMENU="yes">
</HTA:APPLICATION>
</head>

<body onLoad="GetTableData">
<table ID="dataTable"></table>
</body>
</html>

Appendix B: HTA Code to View the Content of the Delta View

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>SQL Delta View Viewer</title>

<style type="text/css">
body      {background-color:wheat;}
table     {width:100%;font-family:Tahoma; font-size:70%;background-color:lightgrey;}
td.tbname {font-weight:bold; background-color:papayawhip;}
td.head   {font-weight:bold; background-color:buttonface;}
td.data   {background-color:cornsilk;}
</style>

<script type="text/vbscript">
<!--
Option Explicit

Const AD_OPEN_STATIC = 3
Const AD_LOCK_OPTIMISTIC = 3
Const AD_USE_CLIENT = 3

'Customizable parameters:
Const MAX_ROWS      = 100
Const SQL_SERVER    = "miissrv1"
Const DATABASE_NAME = "ADObjects"
Const VIEW_NAME     = "vwDelta"


Sub GetTableData
   Dim objConnection
   Set objConnection = CreateObject("ADODB.Connection")
   objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                      "Trusted_Connection=Yes;Initial Catalog=" & _
                      DATABASE_NAME & ";" 
   Dim objRecordset
   Set objRecordset = CreateObject("ADODB.Recordset")
   objRecordset.CursorLocation = AD_USE_CLIENT

   Dim tableName, tableData, oTable, oRow, oTbNameCell, oCell
   Set oTable = document.getElementById("dataTable")
      
   Set oRow              = oTable.insertRow()
   Set oTbNameCell       = oRow.insertCell() 
   oTbNameCell.ClassName = "tbname"
   oTbNameCell.InnerText = VIEW_NAME
     
      objRecordset.Open "SELECT * FROM " & VIEW_NAME, objConnection, _
                        AD_OPEN_STATIC, AD_LOCK_OPTIMISTIC
      
      If Not objRecordset.eof Then
         objRecordset.MoveFirst
         Set oRow   = oTable.insertRow()
         Dim curField
         For each curField in objRecordset.fields
            Set oCell  = oRow.insertCell() 
            oCell.ClassName = "head"
            oCell.InnerText =  Trim(curField.Name) 
         Next
        oTbNameCell.colSpan = objRecordset.fields.count
      End If
  
      Dim rowCounter 
      rowCounter = 0
      Do While Not objRecordset.eof
         Set oRow   = oTable.insertRow()
         For each curField in objRecordset.fields
            Set oCell  = oRow.insertCell() 
            oCell.ClassName = "data"
            oCell.InnerText =  Trim(curField) 
         Next
         objRecordset.MoveNext

         rowCounter = rowCounter + 1
         If rowCounter = MAX_ROWS Then
            Exit Do
         End If
      Loop
      objRecordset.Close
   objConnection.Close
End Sub
-->
</script>

<HTA:APPLICATION ID="oMyApp"
APPLICATIONNAME="SQLDeltaViewViewer"
APPLICATION="yes"
CAPTION="yes"
SINGLEINSTANCE="yes"
SYSMENU="yes">
</HTA:APPLICATION>
</head>

<body onLoad="GetTableData">
<table ID="dataTable"></table>
</body>
</html>

Appendix C: Script to Populate Active Directory Objects

'Name       : TRigger01.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the Active Directory sample data

Option Explicit
Const OU_NAME    = "MIISObjects"
Const ADS_PROPERTY_APPEND = 3 

Dim objRoot, objDomain
Set objRoot   = GetObject("LDAP://rootDSE")
Set objDomain = GetObject("LDAP://" & objRoot.Get("defaultNamingContext"))

'Create OU: 
Dim objContainer
Set objContainer = objDomain.Create("organizationalUnit", "OU=" + OU_NAME)
msgbox "Creating container: " + OU_NAME, 64,"CreateObjects"    
objContainer.SetInfo

'Create group: 
Dim objGroup
Set objGroup = objContainer.Create("Group", "cn=G1")
objGroup.Put "sAMAccountName", "G1"
msgbox "Creating group: G1",64,"CreateObjects"
objGroup.SetInfo

'Create users and add them to new group: 
Dim i, szSamName, objUser
For i = 1 To 4
    szSamName = "U" & i
    Set objUser = objContainer.Create("User", "CN=" + szSamName)
    objUser.Put "sAMAccountName", szSamName
    msgbox "Creating user: " + szSamName,64,"CreateObjects"
    objUser.SetInfo

    objGroup.PutEx ADS_PROPERTY_APPEND, "member", _
                   Array(objUser.distinguishedName)

    msgbox "Adding user to group: " + szSamName,64,"CreateObjects"
    objGroup.SetInfo
Next

msgbox "Command completed successfully!", 64, "CreateObjects"

Appendix D: Script to Create a Database

'Name       : Trigger02.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the SQL database for the SQL walkthrough

Option Explicit

Const SQL_SERVER = "localhost"
Const DB_NAME    = "ADObjects"

Dim sqlCmd
sqlCmd = "CREATE DATABASE " & DB_NAME 
Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=master;" 
objCmd.ActiveConnection = objConnection
objCmd.CommandText = sqlCmd
objCmd.Execute
objConnection.Close

msgbox "Command completed successfully!", 64, "Create Database"

Appendix E: Script to Create the Objects Table

'Name       : Trigger03.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the objects table

Option Explicit
Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"

Dim tableDefinition
tableDefinition  = "CREATE TABLE [tblObjects] (" & _
             "[ObjectID] [int] IDENTITY (1, 1) NOT NULL ," & _
             "[ObjectType] [char] (20) " & _
             "COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ," & _
             "[SamAccountName] [char] (256) COLLATE " & _
             "SQL_Latin1_General_CP1_CI_AS NULL ," & _
             "[DistinguishedName] [char] (256) COLLATE " & _
             "SQL_Latin1_General_CP1_CI_AS NULL ," & _
             "[Description] [char] (256) COLLATE " & _
             "SQL_Latin1_General_CP1_CI_AS NULL ," & _
             ") ON [PRIMARY]"

Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & _
                   DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection
objCmd.CommandText = tableDefinition  
objCmd.Execute
objConnection.Close

msgbox "Command completed successfully!", 64, "Create Objects Table"

Appendix F: Script to Create the References Table

'Name       : Trigger04.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the references table

Option Explicit
Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"

Dim tableDefinition
tableDefinition  = "CREATE TABLE [tblReferences] (" & _
                   "[ObjectID] [int] NOT NULL ," & _
                   "[AttributeName] [char] (20) COLLATE " & _
                   "SQL_Latin1_General_CP1_CI_AS NOT NULL ," & _
                   "[ReferenceID] [int] NOT NULL" & _ 
                   ") ON [PRIMARY]"

Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & _
                   DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection
objCmd.CommandText = tableDefinition  
objCmd.Execute
objConnection.Close

msgbox "Command completed successfully!", 64, "Create References Table"

Appendix G: Script to Create the Changes Table

'Name       : Trigger05.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the changes table

Option Explicit
Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"

Dim tableDefinition
tableDefinition  = "CREATE TABLE [tblChanges] (" & _
                   "[ObjectID] [int] NOT NULL ," & _
               "[DeltaOperation] [char] (10) COLLATE " & _
                   "SQL_Latin1_General_CP1_CI_AS NOT NULL " & _ 
                   ") ON [PRIMARY]"
Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & _
                   DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection
objCmd.CommandText = tableDefinition  
objCmd.Execute
objConnection.Close

msgbox "Command completed successfully!", 64, "Create Changes Table"

Appendix H: Script to Create the Schema Objects

'Name       : Trigger06.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the schema objects

Option Explicit
Const SQL_SERVER = "localhost"
Const DATABASE_NAME = "ADObjects"

'Add the list of SQL commands here:
Dim tableObjects
tableObjects = array("User", _
                     "Group")

Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection

Dim tableObject
For each tableObject in tableObjects 
   objCmd.CommandText = "Insert into tblObjects (ObjectType) " & _
                        "Values('" & tableObject & "')"
   objCmd.Execute
Next

objConnection.Close

msgbox "Command completed successfully!", 64, "Create Schema objects"

Appendix I: The Provisioning Code Snippet

Try
      If (mventry.ObjectType.Equals("ADUser")) Then ProvisionToSQL(True, mventry)
      If (mventry.ObjectType.Equals("ADGroup")) Then ProvisionToSQL(False, mventry)

   Catch ex As Exception
      Throw ex
   End Try

Appendix J: The ProvisionToSQL Code

Private Sub ProvisionToSQL(ByVal isUser As Boolean, _
                           ByVal mventry As MVEntry)
   Try
      Dim myMA As ConnectedMA = mventry.ConnectedMAs("MySQLMA")
      If myMA.Connectors.Count <> 0 Then Exit Sub

      Dim objectType As String = "Group"
      If isUser Then objectType = "User"

      Dim obCS As CSEntry
      obCS = myMA.Connectors.StartNewConnector(objectType)

      Dim DN As ReferenceValue
      DN = myMA.EscapeDNComponent(System.Guid.NewGuid().ToString)

      obCS.DN = DN
      obCS.CommitNewConnector()
   Catch ex As Exception
      Throw ex
   End Try
End Sub

Appendix K: Script to Clear the Objects Table

'Name       : Trigger07.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to clear the content of the Objects table

Option Explicit

Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"
Const SQL_CMD       = "Delete from tblObjects"

Dim objConnection
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & _
                   DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection
objCmd.CommandText = SQL_CMD 
objCmd.Execute
objConnection.Close

msgbox "Command completed successfully!", 64, "Clear Objects Table"

Appendix L: Script to Apply Changes to the Active Directory Data

'Name       : Trigger08.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to apply changes to the Active Directory test data

Option Explicit

Const OU_NAME    ="MIISObjects"
Const ADS_PROPERTY_APPEND = 3 

Dim objRoot, objContainer, ouDN
Set objRoot = GetObject("LDAP://rootDSE")
ouDN = "OU=" & OU_NAME & "," & objRoot.Get("defaultNamingContext")
Set objContainer = GetObject("LDAP://" & ouDN)

'Delete user U1: 
objContainer.Delete "user", "CN=U1"
objContainer.SetInfo

'Modify user U2:
Dim objUser
Set objUser = GetObject("LDAP://cn=U2," & ouDN)
objUser.Put "Description", "New Description"
objUser.SetInfo

'Create user U5 and add user to group:
Set objUser = objContainer.Create("User", "CN=U5")
objUser.Put "sAMAccountName", "U5"
objUser.SetInfo

Dim objGroup
Set objGroup = GetObject("LDAP://CN=G1," & ouDN)
objGroup.PutEx ADS_PROPERTY_APPEND, "member", _
                   Array(objUser.distinguishedName)
objGroup.SetInfo

'Create new group G2
Set objGroup = objContainer.Create("Group", "cn=G2")
objGroup.Put "sAMAccountName", "G2"
objGroup.SetInfo

'Add U5 to new group G2
objGroup.PutEx ADS_PROPERTY_APPEND, "member", _
               Array(objUser.distinguishedName)
objGroup.SetInfo
msgbox "Command completed successfully!", 64, "Modify AD Data"

Appendix M: Script to Set the Database Triggers

'Name       : Trigger09.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the sceanrio triggers

Option Explicit

Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"

Dim trAddedObjectsDef
trAddedObjectsDef =       "CREATE TRIGGER trAddedObjects " & _
                          "ON tblObjects FOR INSERT AS " & _
                          "DELETE FROM tblChanges WHERE ObjectID IN " & _
                          "(SELECT ObjectID from inserted) " & _
                          "INSERT INTO tblchanges " & _
                          "SELECT DISTINCT inserted.ObjectID, " & _
                          "'Add' AS DeltaOperation " & _
                          "FROM   inserted"

Dim trDeletedObjectsDef
trDeletedObjectsDef =     "CREATE TRIGGER trDeletedObjects " & _
                          "ON dbo.tblObjects FOR DELETE AS " & _
                          "DELETE FROM tblChanges WHERE ObjectID IN " & _
                          "(SELECT ObjectID from deleted) " & _
                          "INSERT INTO tblchanges " & _
                          "SELECT DISTINCT deleted.ObjectID, " & _
                          "'Delete' AS DeltaOperation " & _
                          "FROM   deleted"

Dim trModifiedObjectsDef
trModifiedObjectsDef =    "CREATE TRIGGER trModifiedObjects " & _
                          "ON dbo.tblObjects FOR UPDATE AS " & _
                          "INSERT INTO tblchanges " & _
                          "SELECT DISTINCT inserted.ObjectID, " & _
                          "'Modify' AS DeltaOperation " & _
                          "FROM   inserted LEFT OUTER JOIN " & _
                          "tblChanges ON " & _
                          "inserted.ObjectID = tblChanges.ObjectID " & _
                          "WHERE  (tblChanges.ObjectID IS NULL)"


Dim trAddedReferencesDef
trAddedReferencesDef =    "CREATE TRIGGER trAddedReferences " & _
                          "ON tblReferences FOR INSERT AS " & _
                          "INSERT INTO tblchanges " & _
                          "SELECT DISTINCT inserted.ObjectID, " & _
                          "'Modify' AS DeltaOperation " & _
                          "FROM   inserted LEFT OUTER JOIN " & _
                          "tblChanges ON " & _
                          "inserted.ObjectID = tblChanges.ObjectID " & _
                          "WHERE  (tblChanges.ObjectID IS NULL)"

Dim trDeletedReferencesDef 
trDeletedReferencesDef =  "CREATE TRIGGER trDeletedReferences " & _
                          "ON tblReferences FOR DELETE AS " & _
                          "INSERT INTO tblchanges " & _
                          "SELECT DISTINCT deleted.ObjectID, " & _
                          "'Modify' AS DeltaOperation " & _
                          "FROM   deleted LEFT OUTER JOIN " & _
                          "tblChanges ON " & _
                          "deleted.ObjectID = tblChanges.ObjectID " & _
                          "WHERE  (tblChanges.ObjectID IS NULL)"

Dim sqlCmdList
sqlCmdList = array(trAddedObjectsDef, _
                   trDeletedObjectsDef, _
                   trModifiedObjectsDef, _
                   trAddedReferencesDef, _
                   trDeletedReferencesDef)

Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & _
                   DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection


Dim sqlCmd
For each sqlCmd in sqlCmdList 
   objCmd.CommandText = sqlCmd
   objCmd.Execute
Next
objConnection.Close
msgbox "Command completed successfully!", 64, "Create SQL Triggers"

Appendix N: Script to Create the Delta View

============================================================================
'Name       : CreateDeltaView.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the delta view for the trigger scenario
'============================================================================
Option Explicit

Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"


Dim vwDeltaDef
vwDeltaDef = "CREATE VIEW vwDelta " & _
             "AS " & _
             "SELECT tblChanges.ObjectID, tblChanges.DeltaOperation, " & _
             "tblObjects.ObjectType, tblObjects.SamAccountName, " & _
             "tblObjects.DistinguishedName, tblObjects.Description " & _
             "FROM tblChanges LEFT OUTER JOIN " & _
             "tblObjects ON tblChanges.ObjectID = tblObjects.ObjectID"
'============================================================================
Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection
objCmd.CommandText = vwDeltaDef 
objCmd.Execute
objConnection.Close
'============================================================================
msgbox "Command completed successfully!", 64, "Create Delta View"
'============================================================================

See Also

Other Resources

https://go.microsoft.com/fwlink/?LinkId=86172