Generating Delta Views Using Snapshots

  • Article

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 snapshot approach. This snapshot approach uses master tables and snapshot tables as a basis to determine the required delta information. The content of a snapshot table represents the last state of object data that MIIS 2003 imported. A master table contains the most recent state of object data in a SQL database. The delta view is the difference between the tables, which is the identity data that MIIS 2003 has not imported.

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 a snapshot. 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 snapshot 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.

Setup for connected data sources

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.

The inner scenario

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 document:

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 that 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 snapshot 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.

Infrastructure used in the scenario

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 snapshot 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 three database table viewers—TableViewer, DeltaViewViewer and SimpleViewer. 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. SimpleViewer is a command-line script that displays only the data of the first column of the table or view that you specified as parameter. 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

Script to populate Active Directory objects

Appendix B

Script to create a database

Appendix C

Script to create the objects table

Appendix D

Script to create the references table

Appendix E

Script to create the snapshot tables

Appendix F

Script to create the schema objects

Appendix G

The provisioning code snippet

Appendix H

The ProvisionToSQL code

Appendix I

Script to clear the objects table

Appendix J

HTA code to view the content of the tables for this document

Appendix K

Script to apply changes to the Active Directory data

Appendix L

Script to take a snapshot of a master table

Appendix M

Script to display the first column of a table or view

Appendix N

Script to create a view to extract the IDs of added objects

Appendix O

Script to create a view to extract the IDs of deleted objects

Appendix P

Script to create a view to extract the IDs of modified objects

Appendix Q

Script to create a view to extract the IDs of modified objects from the references tables

Appendix R

Script to create a view to extract the IDs of objects that have not been added or deleted

Appendix S

Script to create a view to extract the IDs of modified objects from the references tables that have not been added or deleted

Appendix T

Script to create a view to extract the IDs of all modified objects from the objects and references tables

Appendix U

Script to create the delta view

Appendix V

Script to view the contents of the DeltaView

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. In 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:\Appendix.vbs.

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

  3. 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 three viewers for the data in the scenario tables. The following sections provide information for creating the viewers.

Creating SimpleViewer

SimpleViewer is a script you can use to display the first column of a table or view. Although you can use most scripts in this document from a command-line prompt and from the Windows shell, you use SimpleViewer from a command-line prompt only. SimpleViewer requires the name of a table or view as a parameter that lists the content of the first column of the specified table or view.

To implement SimpleViewer

  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:\SimpleViewer.vbs.

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

To run SimpleViewer

  1. In SQL Server, open a command-line prompt.

  2. Run SimpleViewer with the following command: CScript SimpleViewer.vbs [table/view name].

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 E, 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 V, 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

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 a snapshot. 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.

Active Directory objects

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

The four test users in security group G1

You can use the tools provided by SQL Server to create the Active Directory environment for this document or you can use the script in Appendix A 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 SQL Server tools

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

To create the required objects using the script

  1. In Appendix A, 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. 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.

Configuring the Initial SQL Server Environment

The initial SQL Server environment for this document has a new SQL Server database, several tables, and two data viewers. For convenience, the following terms are used throughout this document for the tables:

  • A master table is an object table or a references table that is not a snapshot table. Together, they are called master tables.

  • A snapshot table is a copy of a master table. Specifically, it is called an objects snapshot table or a references snapshot table.

  • Scenario tables are all tables collectively.

You will find more details about these tables later in this topic.

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.

Two snapshot tables

Store the snapshot data from the master tables.

Three data viewers (SimpleViewer, DeltaViewViewer and TableViewer)

Display the content of the scenario tables and views.

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.

ADObjects database

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 B 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 B 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:\AppendixB.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.

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

  • Two snapshot tables

The following illustration shows the required table structure.

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.

Attributes for each AD 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.

Configuration of a SQL Server column

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

  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.

The structure of the references table

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 D to create the table. If you create the references table manually, you must save the table 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 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.

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

Creating the snapshot tables

You must create a snapshot table named tblObjectsSnapshot for the objects table and a snapshot table named tblReferenceSnapshot for the references table.

You can use the tools provided by SQL Server to create the snapshot tables or you can use the script in Appendix E to create the tables. If you create the snapshot tables manually, use the table definitions in Creating the objects table and Creating the references table. For the name of the snapshot table, append “Snapshot” to the name of the master table. For example, for the master table named tblObjects, name the corresponding snapshot table tblObjectsSnapshot. For more information about using the supplied scripts, see Running the scripts.

To create a snapshot table using SQL Server tools

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

To create a snapshot 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.

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

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

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.

Updating the metaverse schema

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.

Configure Join and Projection Rules dialog box

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.

Configure Attribute Flow dialog box

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.

Configure Attribute Flow dialog box

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.

Configure Attribute Flow dialog box

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.

Configure Attribute Flow dialog box

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

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

  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.

Metaverse Designer

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.

Options dialog box in MIIS 2003

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 B, 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 C, 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.

Configure Run Profiles for dialog box

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.

Configure Run Profiles for dialog box

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. Take a snapshot of the master tables.

  4. Verify the result of full synchronization.

  5. Apply changes to the Active Directory data.

  6. Complete a delta synchronization of the Active Directory object changes to SQL Server.

  7. Verify the result of delta synchronization.

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 I 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 I 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:\AppendixI.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.

081d4eaf-e9a7-429f-bd7f-09171655944b

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 J, 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:\AppendixJ.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.

Taking a snapshot of the master tables

Because you are about to synchronize changes within your environment, you must take a snapshot of the current content of your objects and references tables. The snapshot tables contain the object data without the newly-generated changes. The most recent changes from Active Directory will be merged into the master tables.

You can create the snapshot tables manually by copying the content of the master tables into the empty snapshot tables or you can use the script in Appendix J to create the snapshot tables. For more information about using the supplied scripts, see Running the scripts.

To take a snapshot of the master tables manually

  • Copy the content of the master tables into the empty snapshot tables.

To take a snapshot of the master tables 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.

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.

Verifying the result of delta synchronization

After completing the second export operation on MySQLMA, verify that all Active Directory objects have been successfully synchronized to the SQL Server database and that the master and the snapshot tables contain the expected data.

You can do verify the result of delta synchronization by opening the objects table and the references table with the tools provided by SQL Server or by using TableViewer. For more information about using TableViewer, see Configuring the database viewers.

The following tables show examples of master tables and snapshot tables after a successful delta synchronization.

master and snapshot tables

By visually comparing the content of each master table and its corresponding snapshot table, you can determine whether the content of these tables differs. For example, you might see the following differences:

  • The objects snapshot table has a record for U1, but the objects table does not.

  • The objects table has a record for U5, but the objects snapshot table does not.

  • U2 has a value for the Description column in the objects table, but it has no value for this attribute in the objects snapshot table.

  • The references table has a record for the ReferenceID 6, but the references snapshot table does not.

  • The references snapshot table has a record for the ReferenceID 5, but the references table does not.

Such differences are a result of changes that were synchronized from your Active Directory environment into SQL Server.

To verify the result of delta synchronization using SQL Server tools

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

To verify the result of delta synchronization using TableViewer

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

  2. Review the content of the scenario tables. Verify that the content of each master table differs from the content of its corresponding snapshot table as explained above.

Developing the Delta View

This topic introduces the components that are necessary to extract the delta data from the master tables and the snapshot 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 snapshot approach. This approach is based on a master table that contains the most recent state of the object data in SQL and a snapshot table that contains the last state of the identity data that was imported into MIIS. The goal of the delta view is to compare both states, to extract objects with changes and to group them based on the modification type (Add, Modify, Delete).

In a SQL database, views can be nested, which means a specified view can use another view as source. This topic introduces the all building blocks of a complete delta view that is based on a snapshot approach. These building blocks are a collection of views that extract the exact information from the source tables that is required to accomplish a specific goal. The resulting delta view represents a fully working solution. However, you might want to fine tune this view for a production environment.

To make it simpler to follow the development flow, the names of these views indicate the order in which they are introduced in this document. However, in a production environment, you should assign descriptive names to them.

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

Appendix Delta view Description

Appendix M

vw01

Creates a view to extract the ObjectID values of added objects

Appendix N

vw02

Creates a view to extract the ObjectID values of deleted objects

Appendix O

vw03

Creates a view to extract the ObjectID values of modified objects from the objects tables

Appendix P

vw04

Creates a view to extract the ObjectID values of modified objects from the references tables.

Appendix Q

vw05

Creates a view to extract the ObjectID values of objects that have neither been added nor deleted.

Appendix R

vw06

Creates a view to extract the ObjectID values of modified objects from the references tables that have neither been added nor deleted

Appendix S

vw07

Creates a view to extract the ObjectID values of all modified objects from the objects and the references tables

Appendix T

vw08

Creates the delta view

Patterns for storing object changes in the objects table and scenario tables

If you compare the content of the objects table with the content of the snapshot tables, you will see the following patterns for storing object changes in the objects table and the snapshot tables:

  • Records of objects without applied changes are the same in the master table and the snapshot table

  • Records of modified objects are in both tables but with different values for at least one attribute

  • Deleted objects appear in the snapshot table but not in the master table

  • Added objects appear in the master table but not in the snapshot table

The following table shows an example of test data in the objects tables:

Patterns for storing object changes

To develop the delta view, you must translate these patterns into SQL queries.

The information in this section describes how to do develop the required SQL queries and use them to build a delta view.

Isolating added objects

Added objects have a record in the master table, but not in the snapshot table. One method to isolate added objects is to use a Left Outer Join on the objects table and the objects snapshot table. Using a Left Outer Join returns all records from the objects table, including the records that do not have a matching join partner in the snapshot table. Then, to isolate objects that exist only in the objects table, the records that have a NULL value for the match criterion in the snapshot table are filtered. The following SQL statement shows an example of this.

SELECT tblObjects.ObjectID
FROM   tblObjects LEFT OUTER JOIN 
       tblObjectsSnapshot ON tblObjects.ObjectID = tblObjectsSnapshot.ObjectID 
WHERE (tblObjectsSnapshot.ObjectID IS NULL)

In this document, you must encapsulate the SQL code within a view. In this procedure the view is named vw01. You can use the tools provided by SQL Server to create this view or you can use the script in Appendix M to create the view. For more information about using the supplied scripts, see Running the scripts.

If you run this view in SQL Server, it returns the ObjectID values for the added objects. As the following table shows, the view should return values of 6 and 7 because these are the IDs for the new group G2 and the new user U5, according to the scenario tables in this document.

f70ed4fb-6057-4a11-b946-ece916e6fc29

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.

After you have created the view, use SimpleViewer to confirm that the view returns the results you expect. For information about creating SimpleViewer, see Configuring the database viewers.

The following command-line output shows SimpleViewer results based on the data in the scenario tables.

C:\>cscript SimpleViewer.vbs vw01
Microsoft ® Windows Script Host Version 5.6
Copyright © Microsoft Corporation 1996-2001. All rights reserved.

Displaying vw01

ObjectID
========================
6
7
Command completed successfully!

To create a view that isolates added objects using SQL Server tools

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

To create a view that isolates added objects 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.

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

Isolating deleted objects

The approach to isolate the deleted objects is similar to the approach used to isolate added objects. Deleted objects have a record in the snapshot table but not in the master table. One method to isolate deleted objects is to use a Right Outer Join on the objects and the snapshot table. Using a Right Outer join returns all records from the snapshot table, including the records that do not have a matching join partner in the objects table. Then, to isolate objects that exist only in the snapshot table, the records the records that have a NULL value for the match criterion in the objects table are filtered. The following SQL statement shows an example of this.

SELECT tblObjectsBackup.ObjectID
FROM   tblObjects RIGHT OUTER JOIN
       tblObjectsBackup ON tblObjects.ObjectID = tblObjectsBackup.ObjectID
WHERE  (tblObjects.ObjectID IS NULL)

In this document, you must encapsulate the SQL code within a view. In this procedure the view is named vw02. You can use the tools provided by SQL Server to create this 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.

If you run this view in SQL Server, it returns the ObjectID values for deleted objects. As the following table shows, the view should return the value 3 because this is the ID for the deleted user U1, according to the scenario tables in this document.

bd707f3c-55c6-4c37-82fc-48a0bc628dad

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.

After you have created the view, use SimpleViewer to confirm that the view returns the results you expect. For information about creating SimpleViewer, see Configuring the database viewers.

The following command-line output shows SimpleViewer results based on the data in the scenario tables.

C:\>cscript SimpleViewer.vbs vw02
Microsoft ® Windows Script Host Version 5.6
Copyright © Microsoft Corporation 1996-2001. All rights reserved.

Displaying vw02

ObjectID
========================
3
Command completed successfully!

To create a view that isolates deleted objects using SQL Server tools

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

To create a view that isolates deleted objects 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 TableViewer to confirm that the delta view returns the results you expect.

Isolating modified objects

Isolating modified object is the most complex operation you can perform in conjunction with a delta view because you must analyze the objects table and the references table.

Changed objects have records in the objects table and the objects snapshot table, but that have at least one column that differs from the columns in those tables. The columns can differ because of a group membership change. When group membership changes, MIIS 2003 applies group membership changes to the references table, but not to the objects table. This is why you must analyze the objects table and the objects snapshot table to generate a complete list of modified objects.

Detecting modified objects in the objects table

In the objects table, modified objects have a record in the objects table and in the objects snapshot table with at least one column value that differs.

One method to isolate modified objects in the objects table is to use the UNION operator on the objects and the objects snapshot table. If the UNION operator is applied to two tables, duplicated records are filtered. If two records have the same value for the ObjectID attribute, but at least one value that differs for any of the other columns, both records are returned. Then, to isolate objects with at least one column value that differs, the records with the same value for the ObjectID attribute that are returned multiple times are filtered. The following SQL statement shows an example of this.

SELECT ObjectID
FROM (SELECT * FROM tblObjects
      UNION
      SELECT * FROM tblObjectsSnapshot)DERIVEDTBL
GROUP BY ObjectID
HAVING (COUNT(*) > 1)

In this document, you must encapsulate the SQL code within a view. In this procedure the view is named vw03. You can use the tools provided by SQL Server to create this view or you can use the script in Appendix O to create the view. For more information about using the supplied scripts, see Running the scripts.

If you run this view in SQL Server, it returns the values for the ObjectID attribute for the modified records in the objects table. As the following table shows, the view should return the value 2 because this is the ID for the user U2 with the modified description attribute, according to the scenario tables in this document.

06bca057-7667-4979-9719-0cce10cbb28f

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.

After you have created the view, use SimpleViewer to confirm that the view returns the results you expect. For information about creating SimpleViewer, see Configuring the database viewers.

The following command-line output shows SimpleViewer results based on data in the scenario tables.

C:\>cscript SimpleViewer.vbs vw03
Microsoft ® Windows Script Host Version 5.6
Copyright © Microsoft Corporation 1996-2001. All rights reserved.

Displaying vw03

ObjectID
========================
2
Command completed successfully!

To create a view that detects modified objects in the objects table using SQL Server tools

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

To create a view that detects modified objects in the objects table using the script

  1. In Appendix O, 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:\AppendixO.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.

Detecting objects with changes in the references table

In the references table, modified objects have either a record in the references table but not in the references snapshot table or a record in the references snapshot table but not in the references table. This is because a modified group object has either an added or a removed group member. One method to isolate modified objects from the references table is to use the LEFT and RIGHT Join technique discussed in Isolating Added Objects and Isolating Deleted Objects. The following SQL statement shows an example of this.

SELECT DISTINCT tblReferences.ObjectID
FROM            tblReferences LEFT OUTER JOIN tblReferencesSnapshot ON 
tblReferences.ObjectID = tblReferencesSnapshot.ObjectID AND 
tblReferences.ReferenceID = tblReferencesSnapshot.ReferenceID
WHERE     (tblReferencesSnapshot.ObjectID IS NULL)
UNION
SELECT DISTINCT tblReferencesSnapshot.ObjectID
FROM            tblReferences RIGHT OUTER JOIN tblReferencesSnapshot ON 
tblReferences.ObjectID = tblReferencesSnapshot.ObjectID AND 
tblReferences.ReferenceID = dbo.tblReferencesSnapshot.ReferenceID
WHERE     (tblReferences.ObjectID IS NULL)

In this document, you must encapsulate the SQL code within a view. In this procedure the view is named vw04. You can use the tools provided by SQL Server to create this view or you can use the script in Appendix P to create the view. For more information about using the supplied scripts, see Running the scripts.

If you run this view in SQL Server, it returns the values for the ObjectID attribute for the modified records in the references table. As the following table shows, the view should return the values 5 and 6 because this are these IDs for the groups G1 and G2 that have a modified group members, according to the scenario tables in this document.

Not available

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.

After you have created the view, use SimpleViewer to confirm that the view returns the results you expect. For information about creating SimpleViewer, see Configuring the database viewers.

The following command-line output shows SimpleViewer results based on data in the scenario tables.

C:\>cscript SimpleViewer.vbs vw04
Microsoft ® Windows Script Host Version 5.6
Copyright © Microsoft Corporation 1996-2001. All rights reserved.

Displaying vw04

ObjectID
========================
5
6
Command completed successfully!

To create a view that detects objects with changes in the references table using SQL Server tools

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

To create a view that detects objects with changes in the references table using the script

  1. In Appendix P, 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:\AppendixP.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.

Isolating existing objects

Existing objects are objects that have neither been added nor deleted. These objects include objects without changes and objects with changes. Existing objects have a record in the objects and the objects snapshot table. One method to isolate existing objects is to use an Inner Join on the objects table and the objects snapshot table. Using an Inner Join returns only the ObjectID values that exits in both tables. The following SQL statement shows an example of this.

SELECT   tblObjects.ObjectID
FROM     tblObjects INNER JOIN
         tblObjectsSnapshot ON tblObjects.ObjectID = tblObjectsSnapshot.ObjectID

In this document, you must encapsulate the SQL code within a view. In this procedure the view is named vw05. You can use tools provided by SQL Server to create this view or you can use the script in Appendix R to create this view. For more information about using the supplied scripts, see Running the scripts.

If you run this view in SQL Server, it returns the ObjectID values for existing objects. As the following table shows, the view should return the values 1, 2, 4 and 5 because these are the IDs for objects that have neither been added nor deleted.

aa726d03-da2e-43ff-8049-89fe45dbcb7d

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.

After you have created the view, use SimpleViewer to confirm that the view returns the results you expect. For information about creating SimpleViewer, see Configuring the database viewers.

The following command-line output shows SimpleViewer results based on data in the scenario tables.

C:\>cscript SimpleViewer.vbs vw05
Microsoft ® Windows Script Host Version 5.6
Copyright © Microsoft Corporation 1996-2001. All rights reserved.

Displaying vw05

ObjectID
========================
1
2
4
5
Command completed successfully!

To create a view that detects existing objects in the objects table using SQL Server tools

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

To create a view that detects existing objects in the objects table using the script

  1. In Appendix R, 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:\AppendixR.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.

Detecting modified objects in the references table

In Detecting objects with changes in the references table, you learned how to extract the ObjectID values of objects with a changed reference attribute value. The list of IDs returned by the view discussed in this topic may include added and deleted groups. Modified existing objects in the references table are the ObjectID values of exiting groups with an updated member attribute. To isolate these objects from the list of modified references table objects, the records of existing group objects are filtered. The following SQL statement shows an example of this.

SELECT  vw04.ObjectID
FROM    vw04 INNER JOIN
vw05 ON vw04.ObjectID = vw05.ObjectID

In this document, you must encapsulate the SQL code within a view. In this procedure the view is named vw06. You can use the tools provided by SQL Server to create this view or you can use the script in Appendix S to create the view. For more information about using the supplied scripts, see Running the scripts.

If you run this view in SQL server, it returns the values for the ObjectID attribute for modified existing objects in the references table. As the following table shows, the view should return the value 5 because this is the ID of the existing group G1 that has a modified member attribute.

Patterns for storing object changes

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.

After you have created the view, use SimpleViewer to confirm that the view returns the results you expect. For information about creating SimpleViewer, see Configuring the database viewers.

The following command-line output shows SimpleViewer results based on the data in the scenario tables.

C:\>cscript SimpleViewer.vbs vw06
Microsoft ® Windows Script Host Version 5.6
Copyright © Microsoft Corporation 1996-2001. All rights reserved.

Displaying vw06

ObjectID
========================
5

Command completed successfully!

To create a view that detects modified objects in the references table using SQL Server tools

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

To create a view that detects modified objects in the references table using the script

  1. In Appendix S, 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:\AppendixS.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.

Isolating distinct modified objects in the scenario tables

At this point, you have two views—vw03 and vw06—with the ObjectID values of modified objects. Both lists must be merged into one list. This can be accomplished by the UNION operator, which will also filter duplicates. A duplicate can be a result of a group with a change to an attribute that is tracked in the objects table and an applied change to the group membership. The following SQL statement shows an example of this.

SELECT * from vw03
UNION
SELECT * from vw06

In this document, you must encapsulate the SQL code within a view. In this procedure the view is named vw07. You can use tools provided by SQL Server to create this view or you can use the script in Appendix T to create this view. For more information about using the supplied scripts, see Running the scripts.

If you run this view in SQL Server, it returns the distinct ObjectID values for modified objects. As the following table shows, the view should return the values 2 and 5 because these are the IDs for the use object with the updated description and the existing group with the updated group membership.

c1f36978-c1b5-485e-b199-ec0ca56abbeb

After you have created the view, use SimpleViewer to confirm that the view returns the results you expect. For information about creating SimpleViewer, see Configuring the database viewers.

The following command-line output shows SimpleViewer results based on data in the scenario tables.

C:\>cscript SimpleViewer.vbs vw07
Microsoft ® Windows Script Host Version 5.6
Copyright © Microsoft Corporation 1996-2001. All rights reserved.

Displaying vw07

ObjectID
========================
2
5
Command completed successfully!

To create a view that detects distinct modified objects in the scenario tables using SQL Server tools

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

To create a view that detects distinct modified objects in the scenario tables using the script

  1. In Appendix T, 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:\AppendixT.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.

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 sections explained how to extract the ObjectID values for each type of change. You use the INNER JOIN operator to combine each list of ObjectID values with one of the objects tables.

The following table shows the types of change with the corresponding views and the appropriate INNER JOIN partner.

Type of change View name Inner join partner

Add

vw01

tblObjects

Delete

vw02

tblObjectsSnapshot

Modify

vw07

tblObjects

MIIS 2003 uses the UNION operator to further combine the records for each type of change into one dataset. The following SQL statement shows an example of this.

SELECT  'Add' AS DeltaOperation, tblObjects.*
FROM    tblObjects INNER JOIN vw01 
ON      tblObjects.ObjectID = vw01.ObjectID
UNION
SELECT  'Delete' AS DeltaOperation, tblObjectsSnapshot.*
FROM    tblObjectsSnapshot INNER JOIN vw02 
ON      tblObjectsSnapshot.ObjectID = vw02.ObjectID
UNION
SELECT  'Modify' AS DeltaOperation, tblObjects.*
FROM    tblObjects INNER JOIN vw07 
ON      tblObjects.ObjectID = vw07.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.

11b1975c-2ef7-4ba0-9c9b-c364b0d35c30

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 U 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 U, 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:\AppendixU.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 Configure 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 delta view by running a delta import on the SQL management agent. After running the delta import operation, verify that the following results are reported in the synchronization statistics.

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.

Testing the delta view

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: Script to Populate Active Directory Objects

'Name       : Snapshot01.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 B: Script to Create a Database

'Name       : Snapshot02.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 C: Script to Create the Objects Table

'Name       : snapshot03.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 D: Script to Create the References Table

'Name       : Snapshot03.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the multi-value 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 E: Script to Create the Snapshot Tables

'Name       : Snapshot04.vbs
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the snapshot tables

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

'Add the list of SQL commands here:
Dim sqlCmdList
sqlCmdList = array("select * into tblObjectsSnapshot from tblObjects", _
                   "select * into tblReferencesSnapshot from tblReferences")

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 Snapshot Tables"

Appendix F: Script to Create the Schema Objects

'Name       : Snapshot05.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 G: 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 H: 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 I: Script to Clear the Objects Table

'Name       : ClearObjectsTable.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 J: HTA code to View the Content of the 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", _
                   "tblObjectsSnapshot", _
                   "tblReferences", _
                   "tblReferencesSnapshot")

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 K: Script to Apply Changes to the Active Directory Data

'Name       : ModifyADObjects.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the Active Directory objects required for the Active Directory to SQL Server walkthrough

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 L: Script to Take a Snapshot of a Master Table

'Name       : TakeSnapshot.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create backup tables

Option Explicit

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


'Add the list of SQL commands here:
Dim sqlCmdList
sqlCmdList = array("DROP TABLE tblObjectsSnapshot", _
                   "select * into tblObjectsSnapshot from tblObjects", _
                   "DROP TABLE tblReferencesSnapshot", _
                   "select * into tblReferencesSnapshot from tblReferences")

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, "TakeSnapshot"

Appendix M: Script to Display the First Column of a Table or View

'Name       : SimpleViewer.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to display the first column of a view or table

Option Explicit

Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"
Const AD_OPEN_STATIC = 3
Const AD_LOCK_OPTIMISTIC = 3
Const AD_USE_CLIENT = 3

If(WScript.Arguments.Length <> 1) Then
   WScript.Echo "Invalid number of arguments specified"
   WScript.Echo "You need to provide a table of view name"
   WScript.Quit 1
End If 

WScript.Echo "Displaying " & WScript.Arguments(0) & VbCrLf

Dim objConnection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & DATABASE_NAME & ";" 
Dim objRs
Set objRS = CreateObject("ADODB.Recordset")
objRs.CursorLocation = AD_USE_CLIENT
objRs.Open "SELECT * FROM " & WScript.Arguments(0), objConnection, AD_OPEN_STATIC, AD_LOCK_OPTIMISTIC


If Not objRs.eof Then
   objRs.MoveFirst
   WScript.Echo Trim(objRs.fields(0).Name)
   WScript.Echo
End If
Do While Not objRS.Eof
   WScript.Echo Trim(objRs.fields(0))
   objRs.MoveNext
Loop
objRs.Close
objConnection.Close

WScript.Echo VbCrLf & "Command completed successfully!"

Appendix N: Script to Create a View to Extract the IDs of Added Objects

'Name       : vw01.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create a view to extract the IDs of added objects

Option Explicit

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

Dim tableDefinition
tableDefinition  = "CREATE VIEW vw01 " & _
                   "AS " & _
                   "SELECT tblObjects.ObjectID " & _
                   "FROM tblObjects LEFT OUTER JOIN " & _
                   "tblObjectsSnapshot ON tblObjects.ObjectID = tblObjectsSnapshot.ObjectID " & _
                   "WHERE (tblObjectsSnapshot.ObjectID IS NULL)"

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 vw01"

Appendix O: Script to Create a View to Extract the IDs of Deleted Objects

'Name       : vw02.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create a view to extract the IDs of deleted objects

Option Explicit

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

Dim tableDefinition
tableDefinition  = "CREATE VIEW vw02 " & _
                   "AS " & _
                   "SELECT tblObjectsSnapshot.ObjectID " & _
                   "FROM tblObjects RIGHT OUTER JOIN " & _
                   "tblObjectsSnapshot ON tblObjects.ObjectID = tblObjectsSnapshot.ObjectID " & _
                   "WHERE (tblObjects.ObjectID IS NULL)"

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 vw02"

Appendix P: Script to create a view to extract the IDs of modified objects

'Name       : vw03.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create a view to extract the IDs of modified objects

Option Explicit

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

Dim tableDefinition
tableDefinition  = "CREATE VIEW vw03 " & _
                   "AS " & _
                   "SELECT ObjectID " & _
                   "FROM (SELECT * FROM tblObjects UNION " & _
                   "SELECT * FROM tblObjectsSnapshot)DERIVEDTBL " & _
                   "GROUP BY ObjectID " & _
                   "HAVING (COUNT(*) > 1)"

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 vw03"

Appendix Q: Script to Create a View to Extract the IDs of Modified Objects from the References Tables

'Name       : vw04.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create a view to extract the ObjectIDs of modified objects 
'             from the references tables

Option Explicit

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

Dim tableDefinition
tableDefinition  = "CREATE VIEW vw04 " & _
                   "AS " & _
                   "SELECT DISTINCT tblReferences.ObjectID " & _
                   "FROM tblReferences LEFT OUTER JOIN tblReferencesSnapshot ON " & _ 
                   "tblReferences.ObjectID = tblReferencesSnapshot.ObjectID AND " & _
                   "tblReferences.ReferenceID = tblReferencesSnapshot.ReferenceID " & _
                   "WHERE (tblReferencesSnapshot.ObjectID IS NULL) " & _
                   "UNION " & _
                   "SELECT DISTINCT tblReferencesSnapshot.ObjectID " & _
                   "FROM tblReferences RIGHT OUTER JOIN tblReferencesSnapshot ON " & _ 
                   "tblReferences.ObjectID = tblReferencesSnapshot.ObjectID AND " & _
                   "tblReferences.ReferenceID = dbo.tblReferencesSnapshot.ReferenceID " & _
                   "WHERE (tblReferences.ObjectID IS NULL)"

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 Vw04"

Appendix R: Script to Create a View to Extract the IDs of Objects that have not been Added or Deleted

'Name       : vw05.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create a view to extract the ObjectIDs of objects
'             that have neither been added nor deleted.

Option Explicit

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

Dim tableDefinition
tableDefinition     = "CREATE VIEW vw05 " & _
                      "AS " & _
                      "SELECT tblObjects.ObjectID " & _
                      "FROM       tblObjects INNER JOIN " & _
                      "tblObjectsSnapshot ON tblObjects.ObjectID = tblObjectsSnapshot.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 = tableDefinition  
objCmd.Execute
objConnection.Close

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

Appendix S: Script to Create a View to Extract the IDs of Modified Objects from the References Tables that have not been Added or Deleted

'Name       : vw06.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create a view to extract the ObjectIDs of modified objects 
'             from the references tables that have neither been added nor deleted.

Option Explicit

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

Dim tableDefinition
tableDefinition     = "CREATE VIEW vw06 " & _
                      "AS " & _
                      "SELECT  vw04.ObjectID " & _
                      "FROM    vw04 INNER JOIN " & _
                      "vw05 ON vw04.ObjectID = vw05.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 = tableDefinition  
objCmd.Execute
objConnection.Close

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

Appendix T: Script to Create a View to extract the IDs of all Modified Objects from the Objects and References Tables

'Name       : vw07.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create a view to extract the ObjectIDs of all modified objects 
'             from the objects and the references tables

Option Explicit

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

Dim tableDefinition
tableDefinition     = "CREATE VIEW vw07 " & _
                      "AS " & _
                      "SELECT * from vw03 " & _
                      "UNION " & _
                      "SELECT * from vw06"

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 vw07"

Appendix U: Script to Create the Delta View

'Name       : vw08.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the delta view

Option Explicit

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

Dim tableDefinition
tableDefinition     = "CREATE VIEW vwDelta " & _
                      "AS " & _
                      "SELECT 'Add' AS DeltaOperation, tblObjects.* " & _
                      "FROM tblObjects INNER JOIN " & _
                      "vw01 ON tblObjects.ObjectID = vw01.ObjectID " & _
                      "UNION " & _
                      "SELECT 'Delete' AS DeltaOperation, tblObjectsSnapshot.* " & _
                      "FROM         tblObjectsSnapshot INNER JOIN " & _
                      "vw02 ON tblObjectsSnapshot.ObjectID = vw02.ObjectID " & _
                      "UNION " & _
                      "SELECT     'Modify' AS DeltaOperation, tblObjects.* " & _
                      "FROM         tblObjects INNER JOIN " & _
                      "vw07 ON tblObjects.ObjectID = vw07.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 = tableDefinition  
objCmd.Execute
objConnection.Close

msgbox "Command completed successfully!", 64, "Create Delta View"

m 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 vw07"

Appendix V: HTA Code to View the Contents of the Delta View 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:

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")
      
   tableName = "vwDelta"
   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
   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>