Tip

Replicating data from SQL Server 2000 to Oracle

This tip is useful for understanding the replication options between SQL Server 2000 and Oracle 8i databases. It will help DBAs enable the sharing of data in a heterogeneous environment that is a common challenge in most IT departments.

If you want to replicate data from MS SQL Server 2000 (publisher) to Oracle (subscriber), you could use the SQL Server Replication Monitor (transactional, snapshot or merge) to configure the replication. This can be made possible by configuring a Linked Server to Oracle database from the publishing database and choosing it as the subscriber. The Linked server connection can be made either by using "Microsoft OLEDB Provider for Oracle" or "Oracle provider for OLEDB" for Oracle 8i and above. One can use ODBC drivers for other versions.

Configure a Transactional Replication if you need a data replicated continuously and with minimal latency. There are other alternatives that involve coding like using triggers or stored procedures. Microsoft DTS (a graphic data transformation tool) can also be a good option to selectively transfer data to and from Microsoft SQL Server and Oracle database objects. DTS Packages can be coded with SQL, VB Script, Java Script, XML and various other languages and can be scheduled as a job on SQL Server or run from a batch file with the frequency needed.

I have personally used some of these methods to share data between databases running on Oracle 8i and MS SQL Server 2000. The following is typical

    Requires Free Membership to View

T-SQL code for creating replication. You can use the SQL Server Replication Wizard to set up this too.
/****** Scripting the replication setup of server mySQL01. Script Date: 9/26/2002 3:00:18 PM ******/
/****** Please note: Any password parameter was scripted with NULL or empty string for security reason. ******/

/****** Begin: Script to be run at Distributor: mySQL01 ******/
/****** Installing the server mySQL01 as a Distributor. Script Date: 9/26/2002 3:00:18 PM ******/
use master
GO

exec sp_adddistributor  @distributor = N'mySQL01', @password = N''
GO

-- Updating the agent profile defaults
sp_MSupdate_agenttype_default @profile_id = 1
GO
sp_MSupdate_agenttype_default @profile_id = 2
GO
sp_MSupdate_agenttype_default @profile_id = 4
GO
sp_MSupdate_agenttype_default @profile_id = 6
GO
sp_MSupdate_agenttype_default @profile_id = 11
GO

-- Adding the distribution database
exec sp_adddistributiondb  
@database = N'distribution', 
@data_folder = N'D:\Program Files\Microsoft SQL Server\MSSQLData', 
@data_file = N'distribution.MDF', 
@data_file_size = 2, 
@log_folder = N'D:\Program Files\Microsoft SQL Server\MSSQLData', 
@log_file = N'distribution.LDF', 
@log_file_size = 0, 
@min_distretention = 0, 
@max_distretention = 72, 
@history_retention = 48, 
@security_mode = 1
GO

-- Adding the distribution publisher
exec sp_adddistpublisher  
@publisher = N'mySQL01', 
@distribution_db = N'distribution', 
@security_mode = 1, 
@working_directory = N'mySQL01D$Program Files\Microsoft SQL Server\MSSQLReplData', 
@trusted = N'false', 
@thirdparty_flag = 0
GO

/****** End: Script to be run at Distributor: mySQL01 ******/

/****** Begin: Script to be run at Publisher: mySQL01 ******/
-- Adding the registered subscriber
exec sp_addsubscriber 
@subscriber = N'ORACLEDB', 
@type = 3, 
@login = N'sales', 
@password = N'', 
@security_mode = 0, 
@frequency_type = 64, 
@frequency_interval = 1, 
@frequency_relative_interval = 2, 
@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 = 235900, 
@description = N''

exec sp_changesubscriber_schedule 
@subscriber = N'ORACLEDB', 
@agent_type = 1, 
@active_end_date = 0
GO

-- Enabling the replication database
use master
GO

exec sp_replicationdboption 
@dbname = N'Northwind', 
@optname = N'publish', 
@value = N'true'
GO

use [Northwind]
GO

-- Adding the transactional publication
exec sp_addpublication 
@publication = N'MSSQL_2_Oracle_Repl', 
@restricted = N'false', 
@sync_method = N'character', 
@repl_freq = N'continuous', 
@description = N'Transactional publication of Northwind database from Publisher mySQL01.', 
@status = N'active', 
@allow_push = N'true', 
@allow_pull = N'true', 
@allow_anonymous = N'false', 
@enabled_for_internet = N'false', 
@independent_agent = N'false', 
@immediate_sync = N'false', 
@allow_sync_tran = N'false', 
@autogen_sync_procs = N'false', 
@retention = 336, 
@allow_queued_tran = N'false', 
@snapshot_in_defaultfolder = N'true', 
@compress_snapshot = N'false', 
@ftp_port = 21, 
@ftp_login = N'anonymous', 
@allow_dts = N'false', 
@allow_subscription_copy = N'false', 
@add_to_active_directory = N'false', 
@logreader_job_name = N'mySQL01-Northwind-1'

exec sp_addpublication_snapshot 
@publication = N'MSSQL_2_Oracle_Repl',
@frequency_type = 4, 
@frequency_interval = 1, 
@frequency_relative_interval = 0, 
@frequency_recurrence_factor = 1, 
@frequency_subday = 1, 
@frequency_subday_interval = 0, 
@active_start_date = 0, 
@active_end_date = 0, 
@active_start_time_of_day = 223200, 
@active_end_time_of_day = 0, 
@snapshot_job_name = N'mySQL01-Northwind-MSSQL_2_Oracle_Repl-1'
GO

exec sp_grant_publication_access 
@publication = N'MSSQL_2_Oracle_Repl', 
@login = N'distributor_admin'
GO

exec sp_grant_publication_access 
@publication = N'MSSQL_2_Oracle_Repl', 
@login = N'sa'
GO

-- Adding the transactional articles
exec sp_addarticle 
@publication = N'MSSQL_2_Oracle_Repl', 
@article = N'Customers', 
@source_owner = N'dbo', 
@source_object = N'Customers', 
@destination_table = N'Customers', 
@type = N'logbased', 
@creation_script = null, 
@description = null, 
@pre_creation_cmd = N'drop', 
@schema_option = 0x00000000000000F1, 
@status = 0, 
@vertical_partition = N'false', 
@ins_cmd = N'SQL', 
@del_cmd = N'SQL', 
@upd_cmd = N'SQL', 
@filter = null, 
@sync_object = null, 
@auto_identity_range = N'false'
GO

exec sp_addarticle 
@publication = N'MSSQL_2_Oracle_Repl', 
@article = N'Employees', 
@source_owner = N'dbo', 
@source_object = N'Employees', 
@destination_table = N'Employees', 
@type = N'logbased', 
@creation_script = null, 
@description = null, 
@pre_creation_cmd = N'drop', 
@schema_option = 0x00000000000000F1, 
@status = 0, 
@vertical_partition = N'false', 
@ins_cmd = N'SQL', 
@del_cmd = N'SQL', 
@upd_cmd = N'SQL', 
@filter = null, 
@sync_object = null, 
@auto_identity_range = N'false'
GO

exec sp_addarticle 
@publication = N'MSSQL_2_Oracle_Repl', 
@article = N'Order Details', 
@source_owner = N'dbo', 
@source_object = N'Order Details', 
@destination_table = N'Order Details', 
@type = N'logbased', 
@creation_script = null, 
@description = null, 
@pre_creation_cmd = N'drop', 
@schema_option = 0x00000000000000F1, 
@status = 0, 
@vertical_partition = N'false', 
@ins_cmd = N'SQL', 
@del_cmd = N'SQL', 
@upd_cmd = N'SQL', 
@filter = null, 
@sync_object = null, 
@auto_identity_range = N'false'
GO

exec sp_addarticle 
@publication = N'MSSQL_2_Oracle_Repl', 
@article = N'Products', 
@source_owner = N'dbo', 
@source_object = N'Products', 
@destination_table = N'Products', 
@type = N'logbased', 
@creation_script = null, 
@description = null, 
@pre_creation_cmd = N'drop', 
@schema_option = 0x00000000000000F1, 
@status = 0, 
@vertical_partition = N'false', 
@ins_cmd = N'SQL', 
@del_cmd = N'SQL', 
@upd_cmd = N'SQL', 
@filter = null, 
@sync_object = null, 
@auto_identity_range = N'false'
GO

-- Adding the transactional subscription
exec sp_addsubscription 
@publication = N'MSSQL_2_Oracle_Repl', 
@article = N'all', 
@subscriber = N'ORACLEDB', 
@destination_db = N'ORDERS', 
@sync_type = N'none', 
@update_mode = N'read only', 
@offloadagent = 0, 
@dts_package_location = N'distributor'
GO

/****** End: Script to be run at Publisher: mySQL01 ******/

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

This was first published in October 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.