Need a Serious Help in SQL Replication...

[Sundarrajan Ganesan]

Hi,

Hi everyone, I have been working on SQL Replication since a week ago but still i was not able to find a solution for my
Database to be replicated in Real-Time have tried with all 3 Replication types, so please help me to find a solution for my
replication.

Below I will give a brief description of my database which i am trying to replicate and the problem which I am facing,

I have a Third Party Software Database which we use for our application. this database has 840 tables, most of these tables are the master tables for our application which may have about Lakhs of rows in it. This database will be
Updated (Inserts/Updates/Deletes) many times in a day, so the replication should be Real Time.

The problem is,

1. About 70 to 80% of the tables do not have a Primary Key in it, so it basically does not qualify for TRANSACTIONAL REPLICATION because it needs a primary key in a table to replicate. We can't include the IDENTITY Column and set it as Primary Key in it since it is third party database.

2. Then if i go for MERGE REPLICATION, in Merge Replication the insert statements should specify the Column List in their
Insert Command. As it is a third party software database we could not make any changes in the database. So the basic
qualification for merge replication also fails.

3. Finally I tried with SNAPSHOT REPLICATION. As the Database is too large it takes about 20 to 30 minutes to Generate a new Snapshot from the Publisher and to synchronize the data in the Subscriber. So when a snapshot is restarted after particular intervals then the tables in the Subscriber normally is dropped and recreated in the synchronization process. So until the
synchronization process is over the tables do not have data in it,at that time if the application fetches data there will be
no data available for it. This raises a big problem.


This is where I am struck and I don’t know how to proceed or what to do for a Real-Time synchronization and Replication.
So please help me about what to do and provide any solution for me.


Thanking You...

[marsh_1]

hi,

can you give some detail around the os/database/storage/servers types and versions involved here. split sites , distance?

hth

[TTr]

> I have a Third Party Software Database
What is so secretive about this? It is OK to mention product names here.

> the replication should be Real Time.
Then you should be looking at database replication (transactional and find a soluiton to your "primary key issue") or storage based replication in real time not snapshots (snapshots are not realtime).