Lesson 2: Replicating in Heterogeneous Environments

[Previous] [Next]

With SQL Server, it is possible to replicate data to heterogeneous Subscribers by using Open Database Connectivity (ODBC) and OLE DB. It is also possible to replicate data from sources other than SQL Server. In this lesson you will learn how to configure replication in these heterogeneous environments and also how to make publications available for subscription on the Internet.

After this lesson, you will be able to

• Describe how to replicate to and from heterogeneous databases
• Publish to Internet Subscribers

Estimated lesson time: 45 minutes

Replicating Data with ODBC

SQL Server supports replication to heterogeneous databases (databases that are not running on SQL Server) on Windows NT, Windows 95, and Windows 98. You can also replicate to other platforms, provided you have the appropriate ODBC or OLE DB driver and necessary communication software.

Data Sources Other Than SQL Server

Heterogeneous databases that are supported by SQL Server replication include

• Microsoft Access databases
• Oracle databases
• IBM DB2/MVS and IBM DB2/AS400
• Other databases that comply with SQL Server ODBC subscriber requirements

Replicating from a SQL Server 6.5 Publisher/Distributor to a SQL Server 7 Subscriber

To perform replication from a SQL Server 6.5 Publisher/Distributor to a SQL Server 7 Subscriber, you must perform one of these actions to ensure that the SQL Server 7 Subscriber behaves in a backward-compatible fashion:

• Add the SQL Server Authentication login, repl_publisher, with no password at the SQL Server 7 Subscriber.

or

• Run the SQL Server 7 Subscriber server using trace flag -T3685

ODBC Driver Requirements

SQL Server comes with Microsoft ODBC drivers for Oracle, Access, and the IBM data protocol Distributed Relational Database Architecture (DRDA). Drivers for other ODBC subscriber types must conform to SQL Server replication requirements for generic ODBC subscribers. The ODBC driver must

• Allow updates
• Conform to ODBC Level 1
• Support transactions
• Support the Transact-SQL data definition language (DDL) statements
• Be 32-bit and thread-safe

Publishing Data to Heterogeneous Subscribers

You can publish data to heterogeneous Subscribers by using replication wizards in SQL Server Enterprise Manager.

Creating a Subscription

You can create a push subscription from the Publisher to the heterogeneous Subscriber by using the Push Subscription Wizard. Creating a heterogeneous pull subscription is possible by programming the replication ActiveX control.

Restrictions Involving Heterogeneous Subscriber Types

The following table lists the restrictions that apply to replication with heterogeneous Subscriber types that use ODBC.

Restriction	Explanation
Data types	SQL Server data types are mapped to the closest data type on the target database.
Snapshots	Snapshots must use the bcp character format, not the native SQL Server format.
Using the publication option to truncate the destination table before synchronization	Heterogeneous subscriptions to publications that have this option selected are not supported.
Batched statements	Batched statements are not supported to ODBC subscribers.
ODBC configuration issues	The ODBC data source name (DSN) must conform to SQL Server naming conventions. The quoted identifier setting on the target server, as reported by the ODBC driver, is used.

Using System Stored Procedures

The following table lists the system stored procedures that support replication to ODBC subscribers.

System stored procedure	Description
sp_enumdsn	Returns a list of ODBC and OLE DB data sources available to the SQL Server
sp_dsninfo	Retrieves ODBC or OLE DB data source information from the Distributor associated with the current server, including whether the data source can be a Subscriber

Replicating from Heterogeneous Databases

SQL Server enables third-party replication products to become Publishers within the SQL Server replication framework. This allows these applications to leverage a full set of the replication features provided by SQL Server 7. Figure 17.2 illustrates how a third-party product can be used in a SQL Server replication framework. A SQL Server Publisher and a third-party Publisher are both using the same remote Distributor, and the Subscriber has subscribed to publications on both of the Publishers.

Figure 17.2 Replication from heterogeneous databases

To integrate heterogeneous data sources with SQL Server replication, a developer can create a SQL-DMO program written in Microsoft Visual Basic, C, or C++ that defines a publication, articles, and subscriptions. A second program using the Replication Distributor Interface must be written in C or C++; this program stores the replication transactions in the Distributor. After the publication, articles, and subscriptions are created and the transactions are stored in the Distributor, the transactions are forwarded by the SQL Server Distribution Agent and can be monitored using the Replication Monitor in SQL Server Enterprise Manager.

Exercise: Enabling a Microsoft Access Jet 4 Subscriber

In this exercise, you will enable a new Microsoft Access Jet 4 Subscriber. You will specify a Jet database that does not exist; it will be created automatically when a subscription is initialized in a later exercise.

• To enable a Microsoft Access Jet 4 Subscriber

1. In the console tree, click your server.
2. On the Tools menu, point to Replication, and then click Configuring Publishing, Subscribers, And Distribution.
3. On the Subscribers tab, click New Subscriber.
4. Select Microsoft Jet 4.0 database (Microsoft Access). Click OK.
5. Click Add to register a Jet database as a new linked server.
6. In Linked Server Name, type REPLICATION.
7. In Database File And Path Name, type C:\Sqladmin\Exercise\Ch17\Repl.mdb. Click OK.
8. In the list of linked servers, select the REPLICATION entry.
9. In Login Name, type Admin, and then click OK.
10. Click OK to close the Publisher and Distributor Properties dialog box.

Exercise: Creating a Publication and a Heterogeneous Push Subscription

In this exercise, you will create a publication and a heterogeneous push subscription.

• To create a publication and a heterogeneous push subscription

1. In the console tree, click your server.
2. On the Tools menu, point to Replication and click Create And Manage Publications.
3. Click StudyNwind and then click Create Publication.
4. Use the Create Publication Wizard and the information in the following table to create your publication. Accept defaults for options not specified.

Option	Value
No, I will define the articles and properties	Selected
Publication Type	Snapshot publication
No, do not allow immediate-updating subscriptions	Selected
One or more Subscribers will not be a server running SQL Server	Selected
Specify articles	Check dbo.Customers
If prompted to add an indexed uniqueidentifier column	Click OK
Publication name	StudyNwind_Access_Publication
No, create a publication without data filters and with the following properties	Selected

5. When the wizard has created the new publication, in the Create and Manage Publications dialog box, select the StudyNwind_Access_Publication listed below the StudyNwind database, and click Push New Subscription.
6. Use the information in the next table to create the push subscription.

Option	Value
Choose Subscribers	Select REPLICATION (Microsoft Jet 4.0)
When should the Distribution Agent update the subscription?:	Using the following schedule Occurs every 1 day(s), every 1 hour(s) between 12:00:00 AM and 11:59:00 PM.
Yes, initialize the schema and data at the Subscriber	Selected
Start the Snapshot Agent to begin the initialization process immediately.	Checked

7. Close the dialog boxes to return to SQL Server Enterprise Manager.

Exercise: Running the Distribution Agent for the New Subscription

In this exercise, you will manually run the Distribution Agent for the new heterogeneous subscription and verify that the Jet database was created.

• To run the Distribution Agent for the new subscription

1. In the console tree, expand Replication Monitor, then expand Agents and click Distribution Agents.
2. In the details pane, right-click the entry that has a value of REPLICATION:DSN in the Subscription column, and then click Start.
3. Wait for the Status and Last Action columns to indicate that the agent has successfully applied the snapshot to the Subscriber.
4. In Windows Explorer, navigate to the C:\Sqladmin\Exercise\Ch17 folder. Note that a new Jet database file called Repl.mdb is now present in the folder. The file is a new Jet 4 database, which you cannot open in Microsoft Access 97. If you push a subscription to an existing Jet database, created in Microsoft Access 97, you will be able to use the file in Microsoft Access 97, as you normally would.
5. Switch to SQL Server Query Analyzer and execute the following query to confirm that the data from the Customers table has been replicated to the Jet 4 database:

 SELECT * FROM [REPLICATION]...Customers 

Making a Publication Available on the Internet

You can use the TCP/IP network library to connect servers on the Internet. For pull and anonymous subscriptions, you can use File Transfer Protocol (FTP) to transfer snapshots from the Distributor to the Subscriber.

Consider the following requirements when you want to make publications available on the Internet:

• Push subscriptions cannot use FTP to transfer snapshots.
• Use FTP only when applying a snapshot to a Subscriber; all other replication data exchanges must occur using a network library connection.
• If you are using a firewall, ensure that the Publisher and the Distributor are both on the same side of the firewall.
• Make sure that the Publisher and the Distributor have a direct network connection with each other and are not connected by the Internet alone.
• Enable the TCP/IP protocol on each Subscriber where the Distribution Agent and Merge Agent execute and on the computers to which these agents connect.
• Ensure that the Distributor is installed on the same server as Microsoft Internet Information Server (IIS).
• Set the FTP home directory on IIS to the distribution working folder. The default is \\computer_name\C$\Mssql7\Repldata.

Ensure that this working folder is available to Subscribers.

• Configure the FTP address and login details using the Pull Subscription Properties dialog box.

Lesson Summary

SQL Server supports replication with non_SQL Server databases. A SQL Server Publisher can publish data to any ODBC or OLE DB Subscriber. Third-party vendors can create agents that allow their databases to be Publishers and to publish data into the SQL Server replication environment. You can control replication using the replication ActiveX control from within applications you write. You can allow Subscribers to receive snapshots on the Internet by enabling a publication to use FTP.