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,
MS DBA / SQL Developer - Cerulean - UK