Share via

Oracle and SQL Server Interoperability options

  • Article

SQL Server provides various options for interoperating with Oracle

1) SSIS - Microsoft SQL Server 2005 Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.

Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, for executing SQL statements, or for sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service, the Integration Services service, for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model. Integration Services replaces Data Transformation Services (DTS), which was first introduced as a component of SQL Server 7.0.

The typical uses of Integration Services are: a) Merging data from heterogeneous data sources like Oracle, Teradata, Excel etc., b) Populating data warehouse and data marts, c) cleaning and standardizing data, d) building business intelligence into a data transformation process and d) automating administrative functions and data loading.

SSIS provides a modular architecture to create a workflow and to connect to various data sources including Oracle. SSIS can connect to the following sources out of the box: a) DataReader, b) Excel, c) Flat file, d)OLE DB, e) Raw file, f) Script Component, g) XML and to the following destinations – a)Data mining model, b) DataReader, c)Dimension processing, d) Excel , e) Flat file, f) OLE DB, g) Partition processing, h) Raw file, i) Recordset, j) Script component, k) SQL Server compact and k) SQL Server

There are various ODBC/OLEDB/.NET providers to connect to Oracle that are provided both by Microsoft and Oracle.

2) Replication - Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet. Replication uses a publishing industry metaphor to represent the components in a replication topology, which include Publisher, Distributor, Subscribers, publications, articles, and subscriptions

Many businesses and organizations have data stored in databases from multiple vendors. Integrating this data is often a key component in allowing systems to work together in an organization. Replication allows you to integrate heterogeneous data in two ways:

a. Use Oracle as a source for data that can be replicated to Microsoft SQL Server, IBM, and Oracle databases: With Microsoft SQL Server 2005, you can include Oracle Publishers in your replication topology, starting with Oracle version 8.0.5. Publishing servers can be deployed on any Oracle supported hardware and operating system. The feature is built on the well-established foundation of SQL Server snapshot replication and transactional replication, providing similar performance and usability.

b. Use SQL Server as a source for data that can be replicated to IBM and Oracle databases: Subscriptions are supported for the two most recent versions of Oracle using the most recent version of the OLE DB provider.

3) Linked Servers - A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages: Remote server access, The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise,  The ability to address diverse data sources similarly .

Typically, linked servers are used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server parses the command and sends requests to OLE DB. The rowset request may be in the form of executing a query against the provider or opening a base table from the provider. For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server.

4) Data drivers for use in custom applications – You can use the set of data drivers (OLEDB, ODBC, .NET) that are available to create a custom application for interoperating SQL Server and Oracle to give you the most flexibility at the cost of additional development if none of the above options suit your needs.

Click Here for a good discussion on this topic

Comments