Replicating SQL Server 2000 across Heterogeneous Databases

By Guest Authors on 24 August 2003 | Tags: Replication

---

This article was submitted by Sujoy Paul. He writes "The plethora of mission critical databases in most companies has made it imperative to harness the valuable data by integrating across various database vendors and on different platforms. The seamless integration of the complexities involved is made possible by using replication across heterogeneous sources. MSSQLServer 2000 enables the usage of both snapshot and merge replication to take place across heterogeneous databases by OLEDB and ODBC. This article explains the usage of replicating data from MSSQLServer 2000 database to Access database."

Introduction

Replication enables data and database objects to be copied and modified from one database to another across different networks and platforms. Yet, the process of synchronization maintains the consistency of the database. The physical separation of the databases and latency are the integral part of the design process in replication. These characteristics enhance, among other things, the performance of the application. Other benefits, as cited by Books Online, but not limited to, includes facilitating greater autonomy to users who can work with a local copy of the database and then transfer the changes to remote or mobile users across the network or over the Internet.

MSSQLServer 2000 permits 3 different kinds of replication. They are snapshot, transactional and merge. Snapshot makes a copy of the data and propagates the changes of the whole set of data rather than individual transactions, thereby making it a discontinuous process and entailing a higher degree of latency. Transaction replication allows incremental changes of data to be transferred either continuously or at specific time intervals. Merge replication permits a higher degree of autonomy and allows the subscribers to update changes and then propagates the changes to the publishers which in turn transfers to other subscribers. This article describes this elegant transfer of data across a different data source like Access 2002 using snapshot replication.

Configuration

MSSQLServer 2000 communicates with heterogeneous databases like Access 2002 either by ODBC or OLEDB. The use of a linked server facilitates the execution of queries on heterogeneous databases from SQLServer. However, this requires that the OLEDB driver for the Access database reside on the same server as the SQLServer.

Establishing a Linked Server

The linked server is set up using T-SQL. The steps needed in setting up a linked server are as follows: (a) create a linked server using sp_addlinkedserver, (b) setup the server options using sp_serveroption and (c) the login value of the linked server

(a) Firstly the linked server is created:

sp_addlinkedserver
@server ="ACCESSSERVERPATIENTGP",
@provider="Microsoft.Jet.OLEDB.4.0",
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\research\sqlserver\replication\GPPatient.mdb'
go

Since the heterogeneous database is Access 2002, the name of the provider as specified in the online documentation is Microsoft.Jet.OLEDB.4.0. The name of the server is ACCESSSERVERPATIENTGP while the datasrc parameter specifies the file directory of the database.

(b) The server options with their corresponding parameters for the linked server are then specified:

sp_serveroption 'ACCESSSERVERPATIENTGP','collation compatible', 'true'
go
sp_serveroption 'ACCESSSERVERPATIENTGP','rpc','true'
go
sp_serveroption 'ACCESSSERVERPATIENTGP','rpc out', 'true'
go

(c) Finally, login value is set to true so that SQLServer logins can be used to connect to the linked server:

sp_addlinkedsrvlogin 'ACCESSSERVERPATIENTGP', true
go

Creation of the Database

In this scenario a database is created for a patient tracking system whereby the patient’s health plan, the symptoms and the possible prescription drugs are stored in the SQLServer 2000 repository. The database, entitled GPPatient, can be created by using the Enterprise Manager and following the create database wizard. The E-R diagram for the database is shown in Figure 1.

Figure 1: E-R diagram of the GPPatient database

Establishing a Distributor

The distributor along with the publisher can reside on the same server or on a remote server. In this case, the distributor is set up on the same server as the publisher. Administrators can set it up using the wizard in the Enterprise Manager by selecting Wizards/Replication/Configuring Publication and Distribution Wizard and following the steps.

Establishing a Publisher

The publisher is set up on the database called GPPatient. The steps needed to set up a publisher are: (a) create a publication using sp_addpublication, (b) create an agent for the publication – in this case, snapshot—using sp_addpublication_snapshot, (c) grant publication access to the users using sp_grantpublication access and (d) create articles for publication using sp_addarticle. Alternatively, you can create the publication using the Tools/Replication/Create and Manage Publication wizard in Enterprise Manager and following the steps accordingly. Here, T-SQL has been used for the purpose.

(a) Create the snapshot publication called GPPatient on the publication database GPPatient. (The sp_replicationdboption can be used to set the database for replication)

sp_addpublication @publication = 'GPPatient',  
	@sync_method = 'character', 
	@repl_freq = 'snapshot',  
	@status = 'active',
	@allow_push = 'true', 
	@allow_pull = 'true', 
	@allow_anonymous = 'false', 
	@independent_agent = 'false', 
	@immediate_sync = 'false', 
	@allow_sync_tran = 'false', 
	@autogen_sync_procs = 'false', 
	@retention = 336, 
	@allow_queued_tran = 'false', 
	@snapshot_in_defaultfolder = 'true', 
	@compress_snapshot = 'false',
	@allow_dts = 'false', 
	@allow_subscription_copy = 'false',
	@add_to_active_directory = 'false'

The retention time is 336 hr, which is the default for subscription activity. If set to 0 then it is set to infinity.

(b) Create the snapshot agent for the publication GPPatient

sp_addpublication_snapshot @publication = 'GPPatient',
	@frequency_type = 4, 
	@frequency_interval = 1, 
	@frequency_relative_interval = 1, 
	@frequency_recurrence_factor = 0, 
	@frequency_subday = 8, 
	@frequency_subday_interval = 1,
	@active_start_date = 0, 
	@active_end_date = 0, 
	@active_start_time_of_day = 0, 
	@active_end_time_of_day = 235959, 
	@snapshot_job_name = 'GPPatientAccess'

(c) Grant publication access to the users

exec sp_grant_publication_access @publication = 'GPPatient',
	@login = 'distributor_admin'
GO
exec sp_grant_publication_access @publication = 'GPPatient', @login = 'sa'
GO

(d) Next we create the articles

-- Adding the snapshot articles
sp_addarticle @publication = 'GPPatient', 
	@article = 'Patient', 
	@source_owner = 'dbo', 
	@source_object = 'Patient', 
	@destination_table = 'Patient', 
	@type = 'logbased', 
	@creation_script = null, 
	@description = null,
	@pre_creation_cmd = 'drop', 
	@schema_option = 0x00000000000000F1, 
	@status = 0, 
	@vertical_partition = 'false', 
	@ins_cmd ='SQL', 
	@del_cmd = 'SQL', 
	@upd_cmd = 'SQL', 
	@filter = null, 
	@sync_object = null, 
	@auto_identity_range = 'false'

We use the same command as above for the rest of the tables in the database. The parameters ins_cmd, del_cmd and upd_cmd when set to SQL allows INSERT, DELETE and UPDATE commands to be replicated.

Establishing the subscriber

The linked server needs to be added as a subscriber. This is achieved by selecting Tools/Replication/Configure Publishers, Distributors and Subscribers from the Enterprise manager and then selecting Subscribers as shown below

Figure 2: Publisher and Distributor Properties

We then select New and choose Microsoft Jet 4.0 database (Microsoft Access)

Figure 3: Enabling the New Subscriber

This then brings up the new window as shown below. Enter the login and the password for Access.

Figure 4: Setting up the subscriber for the linked server ACCESSSERVERPATIENTGP

We now need to select the subscription. Select Tools/Replication/Push Subscriptions to others

Figure 5: Selecting the publication for creating the new Subscription

Highlight the publication that you want to subscribe and then select Push New Subscription

Figure 6: Selecting the subscriber for the subscription

After selecting the subscriber, select the destination database which happens to be GPPatient

Figure 7: Selecting the database for the subscription

Set the agent as a continuous process for this process

Figure 8: Set the schedule for the subscription

Then initialize the subscription as shown below

Figure 9: Set the initialization of subscription

We will then insert some values in the Patient table to verify that they are replicated:

Insert into patient (firstname, lastname, dateofbirth, gender) 
Values('Smith','Joe','1/12/1950','M')
Go
Insert into patient (firstname, lastname, dateofbirth, gender) 
Values('West','S','1/12/1975','F')
Go
Insert into patient (firstname, lastname, dateofbirth, gender) 
Values('East','A','7/1/1955','M')
Go
Insert into patient (firstname, lastname, dateofbirth, gender) 
Values('Cheng','Bill','12/17/1959','M')

Next we run the snapshot agent from Replication Monitor/Agents/Snapshot Agents from the Enterprise Manager for the data to be replicated.

Using the OPENQUERY function we can then check to ensure that the data has been replicated from MSSQLServer 2000 to MSAccess 2002:

select * 
from openquery( ACCESSSERVERPATIENTGP,
    'select firstname, lastname, dateofbirth, gender from patient')

You can also check the data in MSAccess2002 to verify that the data has been replicated.

Related Articles Horizontal and Vertical Partitioning in Replication (30 January 2003) Database Journal - SQL Server section (18 December 2002) Transactional Replication Issues (14 September 2000) Error message While Exporting Data and Replication (23 August 2000) Replicating Triggers (14 August 2000) Choosing a replication type (26 July 2000)

Other Recent Forum Posts Troubleshooting Deadlocks in SQL Server (6d) Last Login date and time (7d) Negative effects of High VLF counts (7d) Need to return a value that indicates that a record has been added, but not when a record is modified (8d) Indexex on low cardinality fields (8d) Error in stored procedure (8d) Spam post flagging (9d) Update Microsoft SQL Server (RTM) 12.0.2000.8 to latest v14 (12.0.6449.1) (9d)