Distributed Replication

I have the following scenario :

A cluster hosting a SQL 2005 CENTRAL Database ,6 servers residing in different sites are supposed to push data into 6 tables in the central database ,the tables structure (6 tables) in ALL servers is identical.
,data should be moved in TWO WAY ,from remote sites to the central database ,and vise versa.

We require to replicate 8 tables from the six sites to the central database , so that the central database has data from ALL sites ,the six sites have different data with uniquq flags (primary keys) in the tables.

1- do we use transactional or merge replication? and why?
2- de we need to apply any filters at the publications?
3- how do we solve the problem of a publication initializing the data at the time we create a new subscribtion? this is earasing all data that has been replicated by a previous subscription.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I think:

1. if your applications have critical access to data (your tables) then I suggest to you use merge replication. Why? this method is more robust and failsafe. If you use a transaction method then many process would be blocked a long time (due replication process over distributed transaction). I think merge is less transactional (you can not be sure all data are synchronized) but more robust.
2. yes, if each client synchronize only certain data, then you must apply filters to each suscriptor.
3. not, no new suscriptor is needed. you can initialize the suscriptions to refresh all data available.

NOTE: if you use merge replication, YOUR DATA IS NOT TRANSACTIONAL it would be a problem if your proccess (with synchronized data) is transactional (or must be transactional).

If you need a transactional replication, I suggest to you close your data acces logic (to tables) into a lot of stores procedures, you can set a transactional replication over stored procedures (when one store procedure is executed then the system send execution over suscriptors). If use it, ¡not modify tables out of this store procedures!.
Good luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moodi_aliAuthor Commented:
fine lets suppose that i am going to use one way transactional replication as a first step ,my developers told me that no need to have 2 way at the moment.
i want to create a publication on each of the 6 sites ,then i will go to the central database and create 6 subscription to each of the 6 sites.
1- my data is beaing earased from the central database after i create the first subscription ,how to avoid that?
2- do i need to apply any filters in this scenario?
remember my data is already logically partitioned (with primary kets unique accross the six sites)
thanks for the fast reply!

the general schema not change if publications are on central or client machines (but I think that a unique publication on central and 6 subscriptions is better than 6 publications and 6 subscriptions).

to solve 1, only backup your data before create subscriptions, after it, insert data on table (this insert will up from central to clients automatically). Really I don't known if it is needed (if data will be initialited or not).

yes, obviously you must set filters for each machine (and subscription).

A complete (and specifically) configuration not is trivial, I suggest to you read and make test over it. A good start point should be


Good luck!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.