Post reply

Data Replication - Central Subscriber

  • June 4, 2009 at 2:11 am

    #201162

    Hello guys,

    I need some help with my replication architecture. I really do not know which strategy to apply here. I am new to Replication, I have tried to google for scnarios

    Let me draw you the picture. I want to create a “network” of databases. There are N client databases and one server database.

    So let’s say I have

    -Local1 DB

    -Local2 DB

    -Server DB

    I am in the situation where I need to build consolidated reports, where information from all the local databases are gathered and uploaded to the Server database. I cannot just copy all the data from local databases (using replication) to the server database, due to ID conflicts. Example: CountryID=1 in Local1 correspond to France, while CountryID=1 in Local2 corresponds to US. So it makes no sense to replicate the exact data (keep them always in sync). Instead, I am going to create separate tables on the Server database where I will upload all the information needed for reporting. The best scenario for me would be to:

    -Create views that will pull out all the data from the client databases and will insert it in the Server database. Example create view vw_Server_Financial will return main information about existing invoices (Invoice Number, Invoice Amount…)

    -Data extracted from local views will be inserted into a Server database table and used in Reporting

    -Use replication (or any other existing tool/technology) to minimize the programming efforts for this

    Other network requirements:

    -The replication procedure should handle the changes on the Local databases (Update/Insert/Delete)

    -The server cannot modify data. It is the readonly snapshot of the local databases

    -The amount of data to sync is quite big, so “delete all and then overwrite” will not work.

    Any help would be very appreciated!

  • June 7, 2009 at 11:33 pm

    #1005911

    Hi,

    I would approach this by having a single server DB and set up an ETL process which removes the ID conflicts as the data is loaded into the central DB.

    HTH

    Mat

Viewing 2 posts - 1 through 1 (of 1 total)

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