Microsoft SQL Server

Introduction

Oracle Data Integrator (ODI) seamlessly integrates data in Microsoft SQL Server. Oracle Data Integrator features are designed to work best with Microsoft SQL Server, including reverse-engineering, changed data capture, data integrity check, and mappings.

Concepts

The Microsoft SQL Server concepts map the Oracle Data Integrator concepts as follows: A Microsoft SQL Server server corresponds to a data server in Oracle Data Integrator. Within this server, a database/owner pair maps to an Oracle Data Integrator physical schema. A set of related objects within one database corresponds to a data model, and each table, view or synonym will appear as an ODI datastore, with its attributes, columns and constraints.

Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to Microsoft SQL Server.

Knowledge Modules

Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 9-1 for handling Microsoft SQL Server data. In addition to these specific Microsoft SQL Server Knowledge Modules, it is also possible to use the generic SQL KMs with Microsoft SQL Server. See Generic SQL for more information.

Table 9-1 MSSQL KMs

Knowledge Module	Description
IKM MSSQL Incremental Update	Integrates data in a Microsoft SQL Server target table in incremental update mode.
IKM MSSQL Slowly Changing Dimension	Integrates data in a Microsoft SQL Server target table used as a Type II Slowly Changing Dimension in your Data Warehouse.
JKM MSSQL Consistent	Creates the journalizing infrastructure for consistent journalizing on Microsoft SQL Server tables using triggers.
JKM MSSQL Simple	Creates the journalizing infrastructure for simple journalizing on Microsoft SQL Server tables using triggers.
LKM File to MSSQL (BULK)	Loads data from a File to a Microsoft SQL Server staging area database using the BULK INSERT SQL command.
LKM MSSQL to MSSQL (BCP)	Loads data from a Microsoft SQL Server source database to a Microsoft SQL Server staging area database using the native BCP out/BCP in commands.
LKM MSSQL to MSSQL (LINKED SERVERS)	Loads data from a Microsoft SQL Server source database to a Microsoft SQL Server staging area database using the native linked servers feature.
LKM MSSQL to ORACLE (BCP SQLLDR)	Loads data from a Microsoft SQL Server to an Oracle database (staging area) using the BCP and SQLLDR utilities.
LKM SQL to MSSQL (BULK)	Loads data from any ANSI SQL-92 source database to a Microsoft SQL Server staging area database using the native BULK INSERT SQL command.
LKM SQL to MSSQL	Loads data from any ANSI SQL-92 source database to a Microsoft SQL Server staging area. This LKM is similar to the standard LKM SQL to SQL described in Generic SQL except that you can specify some additional specific Microsoft SQL Server parameters.
RKM MSSQL	Retrieves metadata for Microsoft SQL Server objects: tables, views and synonyms, as well as columns and constraints.

Installation and Configuration

Make sure you have read the information in this section before you start working with the Microsoft SQL Server technology:

System Requirements and Certifications

Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.

The list of supported platforms and versions is available on Oracle Technical Network (OTN):

http://www.oracle.com/technetwork/middleware/data-integrator/documentation/index.html.

Technology Specific Requirements

Some of the Knowledge Modules for Microsoft SQL Server use specific features of this database. The following restrictions apply when using these Knowledge Modules. See the Microsoft SQL Server documentation for additional information on these topics.

Using the BULK INSERT Command

This section describes the requirements that must be met before using the BULK INSERT command with Microsoft SQL Server:

The file to be loaded by the BULK INSERT command needs to be accessible from the Microsoft SQL Server instance machine. It could be located on the file system of the server or reachable from a UNC (Unique Naming Convention) path.
UNC file paths are supported but not recommended as they may decrease performance.
For performance reasons, it is often recommended to install Oracle Data Integrator Agent on the target server machine.

Using the BCP Command

This section describes the requirements that must be met before using the BCP command with Microsoft SQL Server:

The BCP utility as well as the Microsoft SQL Server Client Network Utility must be installed on the machine running the Oracle Data Integrator Agent.
The server names defined in the Topology must match the Microsoft SQL Server Client connect strings used for these servers.
White spaces in server names defined in the Client Utility are not supported.
UNC file paths are supported but not recommended as they may decrease performance.
The target staging area database must have the option select into/bulk copy.
Execution can remain pending if the file generated by the BCP program is empty.
For performance reasons, it is often recommended to install Oracle Data Integrator Agent on the target server machine.

Using Linked Servers

This section describes the requirements that must be met before using linked servers with Microsoft SQL Server:

The user defined in the Topology to connect to the Microsoft SQL Server instances must have the following privileges:
- The user must be the db_owner of the staging area databases
- The user must have db_ddladmin role
- For automatic link server creation, the user must have sysdamin privileges
The MSDTC Service must be started on both SQL Server instances (source and target). The following hints may help you configure this service:
- The Log On As account for the MSDTC Service is a Network Service account (and not the 'LocalSystem' account).
- MSDTC should be enabled for network transactions.
- Windows Firewall should be configured to allow the MSDTC service on the network. By default, the Windows Firewall blocks the MSDTC program.
- The Microsoft SQL Server must be started after MSDTC has completed its startup.
See the following links for more information about configuring the MSDTC Service:
- http://support.microsoft.com/?kbid=816701
- http://support.microsoft.com/?kbid=839279

Connectivity Requirements

This section lists the requirements for connecting to a Microsoft SQL Server database.

JDBC Driver

Oracle Data Integrator is installed with a default Microsoft SQL Server Datadirect Driver. This drivers directly uses the TCP/IP network layer and requires no other installed component or configuration. You can alternatively use the drivers provided by Microsoft for SQL Server.

Setting up the Topology

Setting up the Topology consists of:

Creating a Microsoft SQL Server Data Server

A Microsoft SQL Server data server corresponds to a Microsoft SQL Server server connected with a specific user account. This user will have access to several databases in this server, corresponding to the physical schemas in Oracle Data Integrator created under the data server.

Creation of the Data Server

Create a data server for the Microsoft SQL Server technology using the standard procedure, as described in Creating a Data Server of Administering Oracle Data Integrator.

This section details only the fields required or specific for defining a Microsoft SQL data server:

In the Definition tab:
- Name: Name of the data server that will appear in Oracle Data Integrator
- Server: Physical name of the data server
- User/Password: Microsoft SQLServer user with its password
In the JDBC tab:
- JDBC Driver: weblogic.jdbc.sqlserver.SQLServerDriver
- JDBC URL: jdbc:weblogic:sqlserver://hostname:port[;property=value[;...]]

Creating a Microsoft SQL Server Physical Schema

Create a Microsoft SQL Server physical schema using the standard procedure, as described in Creating a Physical Schema in Administering Oracle Data Integrator.

The work schema and data schema in this physical schema correspond each to a database/owner pair. The work schema should point to a temporary database and the data schema should point to the database hosting the data to integrate.

Create for this physical schema a logical schema using the standard procedure, as described in Creating a Logical Schema in Administering Oracle Data Integrator and associate it in a given context.

Setting Up an Integration Project

Setting up a project using the Microsoft SQL Server database follows the standard procedure. See Creating an Integration Project of Developing Integration Projects with Oracle Data Integrator.

It is recommended to import the following knowledge modules into your project for getting started with Microsoft SQL Server:

IKM MSSQL Incremental Update
IKM MSSQL Slowly Changing Dimension
JKM MSSQL Consistent
JKM MSSQL Simple
LKM File to MSSQL (BULK)
LKM MSSQL to MSSQL (BCP)
LKM MSSQL to MSSQL (LINKED SERVERS)
LKM MSSQL to ORACLE (BCP SQLLDR)
LKM SQL to MSSQL (BULK)
LKM SQL to MSSQL
CKM SQL. This generic KM is used for performing integrity check for SQL Server.
RKM MSSQL

Creating and Reverse-Engineering a Microsoft SQL Server Model

This section contains the following topics:

Create a Microsoft SQL Server Model

Create a Microsoft SQL Server Model using the standard procedure, as described in Creating a Model of the Developing Integration Projects with Oracle Data Integrator.

Reverse-engineer a Microsoft SQL Server Model

Microsoft SQL Server supports both Standard reverse-engineering - which uses only the abilities of the JDBC driver - and Customized reverse-engineering, which uses a RKM to retrieve the metadata.

In most of the cases, consider using the standard JDBC reverse engineering for starting. Standard reverse-engineering with Microsoft SQL Server retrieves tables, views, and columns.

Consider switching to customized reverse-engineering for retrieving more metadata. Microsoft SQL Server customized reverse-engineering retrieves the tables, views, and synonyms. The RKM MSSQL also reverse-engineers columns that have a user defined data type and translates the user defined data type to the native data type.

Standard Reverse-Engineering

To perform a Standard Reverse-Engineering on Microsoft SQL Server use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator.

Customized Reverse-Engineering

To perform a Customized Reverse-Engineering on Microsoft SQL Server with a RKM, use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator . This section details only the fields specific to the Microsoft SQL Server technology:

In the Reverse Engineer tab of the Microsoft SQL Server Model, select the KM: RKM MSSQL.<project name>.
In the COMPATIBLE option, enter the Microsoft SQL Server version. This option decides whether to enable reverse synonyms. Note that only Microsoft SQLServer version 2005 and above support synonyms.

Note the following information when using this RKM:

The connection user must have SELECT privileges on any INFORMATION_SCHEMA views.
Only native data type will be saved for the attribute with user defined data type in the repository and model.
User defined data types implemented through a class of assembly in the Microsoft .NET Framework common language runtime (CLR) will not be reversed.

Setting up Changed Data Capture

The ODI Microsoft SQL Server Knowledge Modules support the Changed Data Capture feature. See Working with Changed Data Capture of Developing Integration Projects with Oracle Data Integrator, for details on how to set up journalizing and how to use captured changes.

Microsoft SQL Server Journalizing Knowledge Modules support Simple Journalizing and Consistent Set Journalizing. The Microsoft SQL Server JKMs use triggers to capture data changes on the source tables.

Oracle Data Integrator provides the Knowledge Modules listed in Table 9-2 for journalizing Microsoft SQL Server tables.

Table 9-2 Microsoft SQL Server Journalizing Knowledge Modules

KM	Notes
JKM MSSQL Consistent	Creates the journalizing infrastructure for consistent journalizing on Microsoft SQL Server tables using triggers.
JKM MSSQL Simple	Creates the journalizing infrastructure for simple journalizing on Microsoft SQL Server tables using triggers.

Log-based changed data capture is possible with Microsoft SQL Server using the Oracle GoldenGate. See Oracle GoldenGate for more information.

Setting up Data Quality

Oracle Data Integrator provides the generic CKM SQL for checking data integrity against constraints defined on a Microsoft SQL Server table. See Flow Control and Static Control in Developing Integration Projects with Oracle Data Integrator for details.

See Generic SQL for more information.

Designing a Mapping

You can use Microsoft SQL Server as a source, staging area or a target of a mapping.

The KM choice for a mapping or a check determines the abilities and performance of this mapping or check. The recommendations in this section help in the selection of the KM for different situations concerning a Microsoft SQL Server data server.

Loading Data from and to Microsoft SQL Server

Microsoft SQL Server can be used as a source, target or staging area of a mapping. The LKM choice in the Loading Knowledge Module tab to load data between Microsoft SQL Server and another type of data server is essential for the performance of a mapping.

Loading Data from Microsoft SQL Server

Oracle Data Integrator provides Knowledge Modules that implement optimized methods for loading data from Microsoft SQL Server to a target or staging area database. These optimized Microsoft SQL Server KMs are listed in Table 9-3.

In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved to load data from Microsoft SQL Server to a target or staging area database.

Table 9-3 KMs for loading data from Microsoft SQL Server

Source or Staging Area Technology	KM	Notes
Microsoft SQL Server	LKM MSSQL to MSSQL (BCP)	Loads data from a Microsoft SQL Server source database to a Microsoft SQL Server staging area database using the native BCP out/BCP in commands.
Microsoft SQL Server	LKM MSSQL to MSSQL (LINKED SERVERS)	Loads data from a Microsoft SQL Server source database to a Microsoft SQL Server staging area database using the native linked servers feature.
Oracle	LKM MSSQL to ORACLE (BCP SQLLDR)	Loads data from a Microsoft SQL Server to an Oracle database (staging area) using the BCP and SQLLDR utilities.

Loading Data to Microsoft SQL Server

Oracle Data Integrator provides Knowledge Modules that implement optimized methods for loading data from a source or staging area into a Microsoft SQL Server database. These optimized Microsoft SQL Server KMs are listed in Table 9-4.

In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved.

Table 9-4 KMs for loading data to Microsoft SQL Server

Source or Staging Area Technology	KM	Notes
File	LKM File to MSSQL (BULK)	Loads data from a File to a Microsoft SQL Server staging area database using the BULK INSERT SQL command.
Microsoft SQL Server	LKM MSSQL to MSSQL (BCP)	Loads data from a Microsoft SQL Server source database to a Microsoft SQL Server staging area database using the native BCP out/BCP in commands.
Microsoft SQL Server	LKM MSSQL to MSSQL (LINKED SERVERS)	Loads data from a Microsoft SQL Server source database to a Microsoft SQL Server staging area database using the native linked servers feature.
SQL	LKM SQL to MSSQL (BULK)	Loads data from any ANSI SQL-92 source database to a Microsoft SQL Server staging area database using the native BULK INSERT SQL command.
SQL	LKM SQL to MSSQL	Loads data from any ANSI SQL-92 source database to a Microsoft SQL Server staging area.

Integrating Data in Microsoft SQL Server

Oracle Data Integrator provides Knowledge Modules that implement optimized data integration strategies for Microsoft SQL Server. These optimized Microsoft SQL Server KMs are listed in Table 9-5.

In addition to these KMs, you can also use the Generic SQL KMs.

The IKM choice in the Integration Knowledge Module tab determines the performances and possibilities for integrating.

Table 9-5 KMs for integrating data to Microsoft SQL Server

KM	Notes
IKM MSSQL Incremental Update	Integrates data in a Microsoft SQL Server target table in incremental update mode.
IKM MSSQL Slowly Changing Dimension	Integrates data in a Microsoft SQL Server target table used as a Type II Slowly Changing Dimension in your Data Warehouse

Using Slowly Changing Dimensions

For using slowly changing dimensions, make sure to set the Slowly Changing Dimension value for each column of the target datastore. This value is used by the IKM MSSQL Slowly Changing Dimension to identify the Surrogate Key, Natural Key, Overwrite or Insert Column, Current Record Flag and Start/End Timestamps columns.