Timeline for Looking for advice on how to integrate data from 100+ client DB's into a centralized reporting database
Current License: CC BY-SA 3.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 14, 2017 at 7:57 | comment | added | Gulrez Khan | add additional column like SERVER_ID and make it composite primary key then set default value 1 for publisher-1 and set default value 2 for publisher-2 after that when create publisher set article properties "Delete data.if article has row filter".therefore central subscriber database will have row with server_id 1 and 2.so you can differentiate you individual publisher. | |
| Aug 7, 2017 at 14:32 | comment | added | bperry | The two links explain methods to avoid primary key collisions when combining data from multiple sources. But they do not provide any additional insight on how to handle schema differences between two publishers with the same central subscriber. Our primary keys are unlikely to change over time, so I am confident we can handle those at setup. My concern is as we update our SaaS application, new database columns will be added. If one client upgrades before another, that one client needs to be able to replicate the new column(s) and everyone else still needs to replicate using the old schema. | |
| Aug 5, 2017 at 6:02 | comment | added | Gulrez Khan | also see this..dba.stackexchange.com/questions/146070/… | |
| Aug 5, 2017 at 6:02 | comment | added | Gulrez Khan | see below link..dba.stackexchange.com/questions/142449/… | |
| Aug 3, 2017 at 15:06 | comment | added | bperry | Let me make sure I understand your solution. Let's say Publisher A is on version 1 and Publisher B is on version 2. 1) You have turned off the replication of schema changes on both publishers (using replicate_ddl = 0 at setup). 2) Version 2 includes a new column, so you would manually add it at the central subscriber. 3) At Publisher B (upgraded) you would then manually add the new column into replication (using sp_articlecolumn). No changes are made at Publisher A. Would this allow both publishers to continue replicating to the central subscriber without breaking replication? | |
| Aug 3, 2017 at 6:04 | comment | added | Gulrez Khan | I am not using 'Update Publisher'.I divided database in two part like(two type of replication) some of table in detailed publisher(Publisher to centralised subscriber) and some is master publisher(centralised subscriber to all publisher).....My centralised subscriber is also part of AlwaysOn avaibality group therefor my secondary replica work as report server. | |
| Aug 2, 2017 at 17:16 | comment | added | bperry | thanks for the article. Using the central subscriber model, have you worked out how you will handle updates to your publishers' schema (for example, with version upgrades)? Will you force all publisher databases to be updated simultaneously? In my environment, we do not always have this option and that was my primary hesitation in pursuing a central subscriber replication model. If there is a way around this barrier, I would love to know! | |
| Jul 31, 2017 at 7:16 | history | answered | Gulrez Khan | CC BY-SA 3.0 |