• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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.

  • 2
1 Solution

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!
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!

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now