Multiple Publisher Replication

  • Hi,

    I'm attempting replication for the first time and would like some guidance from the experts, please 

    We have 5 remote sites, each site has an application running on SQL Server 2000 with exactly the same structure.

    Example Structure (This is just a dummy schema):

    Meta Table:

    ReferenceTable (PK: Category & Code):

    - Category (E.g. Country)

    - Code (E.g. USA)

    - Descr (E.g. United States of America)

    Data Tables:

    Level1 (PK: Level1)

    - Level1 (E.g. SC)

    - Descr (E.g. Some Corporation)

    - Country (E.g. USA): Values provided from "ReferenceTable"

    Level2 (PK: Level1 & Level2, FK: Level2.Level1 = Level1.Level1)

    - Level1 (E.g. SC)

    - Level2 (E.g. CA123)

    - Descr (E.g. California Branch)

    - Size (E.g. SML): Values provided from "ReferenceTable"

    The idea is to replicate the information from each site to a central database that resides at head office. The replication should only run in one direction (site to head office). Data stored in the data tables (Level1 and Level2) at one site will not exist at another site, this is not the case with the meta table (ReferenceTable). Duplication between these will occur.

    How do I go about setting up replication that the data and meta tables are transferred correctly. Data table records are transfer as is and meta tables are transferred only if they do not exist at head office.

    Thank you,

    Hank

  • Type in

    replication, sql server

    in Google, you will get tons of them.

  • SQL ORACLE,

    You should be nicer to a new guy

    Hank,

    You can do this with transactional replication and with some trick . You should use nosync replication, that is, during the replication setup tell the wizard that the subscriber already has the data. It's your responsibility that the central database really has the records from the branch DBs. There's nothing to do with the 'fact' tables after it.

    Your 'dimension' tables (country, region, etc.) need some adjustment: you can modify the insert stored procedures (something similar to sp_ins_TableName_1 and so on) in the subscriber db created by the replication wizard to check the existence of the record before inserting a new one.

    However, this way you may break referential integrity when e.g. California will be deleted in one branch db, but remains in the others. So what I suggest is: use triggers. I really try to avoid them because they make your life overcomplicated sometimes, but this is a beautiful task for them. Use instead of triggers on insert and delete and think it through how about updates.

    Have fun!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply