Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication between different databases Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 4:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:28 AM
Points: 15, Visits: 217
Hi all,

I was wondering if anyone has had any experience using replication in the description text below:

Detailed below is a description of the problem domain, the questions (and assumptions I have made) , and a small visual representation (not really that visual though) of the requirement.

Description:
For site A, I have various clients scattered around a site on one network. My customer has an Oracle central server.
For site B, I have various clients scattered around a site on one network. My customer has an SQL 2005 central server.
For site C, I have various clients scattered around a site on one network. My customer has an SQL 2008 central server.
* Clients utilise both Access and SQL 2008 Express

The problem domain (with some assumptions and an implied design descision):
I need to be able to setup replication between these clients and the server. I have had experience with Merge Replication technologies using SQL Server 2008 R2. But what I am a little worried about is how to replicate between the clients (which we could assume will always be SQL for this example) and a server.
So what I want to do is to replicate the data from all clients from a replication server to either the customers Oracle or SQL server. So essentially, the SQL server that performs the replication will be the master, and it will have changes pushed to it by the clients. It will then in turn push those changes to the customers servers (be them SQL or Oracle), and the customers clients.

The Questions:
Is this possible? Can you replicate from SQL 2008 R2 (or even 2012) to an Oracle server. Obviously with Merge replication you want updates propigated both ways.
But if I said that the SQL server in the middle was in charge of maintaining all the changes; and that the Oracle (or SQL) server was only designed as a storage server for the customer (storing every possible configuration for all of the clients), and I changed the replication type to transactional from the SQL Replication server to the Oracle (or SQL) server.
I could then leave the replication type as merge from the Clients to the SQL Replication server.

1) Would this be a viable solution?
2) Can SQL Replication talk directly to Oracle?
3) Has anyone had any experience with this?
4) Can anyone make any suggestions, or has had any experience with this?

Client Replication server Customers server to hold

SQL Server .......... (<2 way>) ......... SQL Server 2008 ......... (1 way>) ......... Oracle / SQL 2005 / 2008
(or possibly Access) ....................... (Replication server)

Many thanks for any advice offered,

Laurence Proctor
MS DBA / SQL Developer - Cerulean - UK
Post #1356652
Posted Monday, September 10, 2012 5:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 3,928, Visits: 5,117
Start reading about NON-SQL Server Subscribers here

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1356659
Posted Tuesday, September 11, 2012 12:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:28 AM
Points: 15, Visits: 217
I really appreciate your help on this matter.

This is great; so I can setup a merge replication between clients and one SQL replication server, and then setup a transaction or snapshot replication between the SQL replication server and the customers Oracle / SQL server.

I will create a Publication for a Non-SQL Server Subscriber from the SQL Replication Server and the customers server will subscribe to this publication.

Thanks for the input.

Has anyone had any experience in trying anything like this out before?

Many thanks,

Laurence Proctor
Post #1357155
Posted Tuesday, September 11, 2012 2:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 3,928, Visits: 5,117
I have been involved in replication from a DB2 database to SQL server only.
However, the way you have described it appears to cover the gist thereof.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1357183
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse