SQL Server 7.0 Data Interoperability FAQ

  • [アーティクル]
 

Data interoperability is the sharing of data between unrelated data sources and multiple applications. Creating enterprise data warehouses or commerce Web sites from heterogeneous data sources are two of the most popular scenarios for using Microsoft SQL Server 7.0 as an interoperability platform. Customers choose SQL Server because it preserves their investments in existing systems through easy data interoperability, while providing additional functionality and cost-effectiveness that their existing database systems do not provide. The following are frequently asked questions about SQL Server 7.0 interoperability.

Q. What kind of data interoperability does Microsoft provide?

A.

Microsoft provides data interoperability through the implementation of software solutions adhering to published specifications, such as the Open Database Connectivity (ODBC) and Microsoft Object Linking and Embedding Database (OLE DB) standards. Microsoft supports both Microsoft Windows and non-Windows data sources in the form of ODBC drivers and OLE DB data providers.

The Microsoft Data Access Components (MDAC), which ship as a standard feature of popular Microsoft products such as BackOffice, SQL Server, Microsoft Office, and Microsoft Visual Studio, also include

  • Microsoft ODBC drivers for accessing SQL Server, Microsoft Access, Microsoft Excel, Microsoft Visual FoxPro, dBase, Paradox, Oracle, and text file data sources.

  • Microsoft OLE DB providers for accessing SQL Server, Access, and Oracle data sources.


Microsoft SNA Server, which ships both separately and in the BackOffice and Enterprise editions of Visual Studio, includes OLE DB providers for accessing mainframe Virtual Storage Access Method (VSAM) data sets, native AS/400 files, and IBM DB2. Recently, Microsoft also announced an interoperability-related project, code-named "Babylon," which will provide enterprise integration of Windows with mainframes, AS/400, Unix, and other non-Windows systems, plus a Microsoft OLE DB provider and a Microsoft ODBC Driver for Sybase systems.

Q. Can I query other databases from a SQL Server application?

A. Yes. Within a Transact-SQL statement, you can access any database for which you have an OLE DB provider or ODBC driver. Additionally, you can create a linked server. A linked server is a virtual server that has been defined to SQL Server with all the information needed to access an OLE DB data source. After a linked server is defined, it can be used in Transact-SQL statements to reference data objects in that linked server for greater location transparency. If an application requires data from a mainframe using the Systems Network Architecture (SNA) protocol, you will also need Microsoft SNA Server.

Q. How can I move heterogeneous data to a SQL Server–based data warehouse or Web-based application?

A. SQL Server includes Data Transformation Services (DTS), a graphical and easy-to-use feature that moves data from any data source to a data warehouse or Web-based SQL Server application. By using SQL Server as a destination for applications or data warehouses, organizations can benefit from other unique SQL Server features, including OLAP Services, Office 2000 integration, and English Query.

Q. Can I use SQL Server data in my Unix-based database application or data warehouse?

A. Yes. Any Unix database vendor that supports OLE DB connectivity can use a Unix OLE DB driver for SQL Server.

Q. How can I replicate data between SQL Server and IBM DB2, Oracle, and other databases?

A.

SQL Server supports heterogeneous data source replication, provided organizations have an ODBC driver or OLE DB provider for that data source. There are two types of replication available today for heterogeneous databases: snapshot and transactional.

As its name implies, snapshot replication takes a picture of the published data in the database at a moment in time. Transactional replication, on the other hand, uses snapshot replication as a starting point, and then maintains the consistency between the databases by sending database modifications to the other database on a regular basis. Transactional replication uses the transaction log to capture changes that were made to the data in an article. SQL Server monitors INSERT, UPDATE, DELETE statements, or other modifications made to the data in one server, and stores those changes in the distribution database, which acts as a reliable queue. Changes are then sent to the other database and applied in the same order.

SQL Server supports transactional replication from SQL Server to heterogeneous databases, while third-party solutions are available for automating transactional replication from heterogeneous databases to SQL Server. Below are just a few examples:

  • Replication with Oracle

    SQL Server enables bi-directional snapshot replication with Oracle, as well as transactional replication from SQL Server to Oracle.


  • Replication with IBM DB2, VSAM data sets, and native AS/400 files

    Using SQL Server and SNA Server, applications can perform bi-directional snapshot replication with IBM DB2 and perform transactional replication from SQL Server to IBM DB2. For more information about third-party products implementing transactional replication from IBM DB2, VSAM data sets, and native AS/400 data files to SQL Server.


  • Replication with Sybase and Informix

    With a compatible OLE DB provider or ODBC driver, SQL Server can perform bi-directional snapshot replication and transactional replication to Sybase or Informix. For more information about products providing transactional replication from Sybase to SQL Server.


  • Replication with Access (Microsoft Jet Database Engine 4.0)

    SQL Server provides both bi-directional snapshot and transactional replication with Jet 4.0.


Q. How can I develop my own solution for transactional replication from other data sources to SQL Server?

A. SQL Server provides a programming framework enabling heterogeneous data sources to update a SQL Server database through transactional replication. Several third parties, such as Open Universal and Vision Solutions, have implemented this framework to create products that help develop in-house solutions.
Top of page Top of page