Microsoft SQL Server 7.0 System Administration Training Kit
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.
- Run the SQL Server 7 Subscriber server using trace flag -T3685
or
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
- In the console tree, click your server.
- On the Tools menu, point to Replication, and then click Configuring Publishing, Subscribers, And Distribution.
- On the Subscribers tab, click New Subscriber.
- Select Microsoft Jet 4.0 database (Microsoft Access). Click OK.
- Click Add to register a Jet database as a new linked server.
- In Linked Server Name, type REPLICATION.
- In Database File And Path Name, type C:\Sqladmin\Exercise\Ch17\Repl.mdb. Click OK.
- In the list of linked servers, select the REPLICATION entry.
- In Login Name, type Admin, and then click OK.
- 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
- In the console tree, click your server.
- On the Tools menu, point to Replication and click Create And Manage Publications.
- Click StudyNwind and then click Create Publication.
- Use the Create Publication Wizard and the information in the following table to create your publication. Accept defaults for options not specified.
- 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.
- Use the information in the next table to create the push subscription.
- Close the dialog boxes to return to SQL Server Enterprise Manager.
| 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 |
| 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 |
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
- In the console tree, expand Replication Monitor, then expand Agents and click Distribution Agents.
- In the details pane, right-click the entry that has a value of REPLICATION:DSN in the Subscription column, and then click Start.
- Wait for the Status and Last Action columns to indicate that the agent has successfully applied the snapshot to the Subscriber.
- 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.
- 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.
- Configure the FTP address and login details using the Pull Subscription Properties dialog box.
Ensure that this working folder is available to Subscribers.
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.