Distributed Replication

Posted on 2008-11-13
Last Modified: 2012-05-05
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.

Question by:moodi_ali
    LVL 5

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

    Author Comment

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

    Expert Comment


    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now