Julius Bichage (1/13/2014)
I have the following scenario:
1. I have live databases installed for my clients
2. I want to replicate the data from each live client into a single central database
3. This central database has an identical schema for all the tables on live databases which I will host on our on-premise server
4. I have added a ClientId column to each and every table on the central database to identify the clients. So essentially this ClientId plus the each tables primary key will comprise the new primary key in the central database
I want to design a replication model which will enable me achieve the following
1. Replicate the data from each live client to the central database in real time or close to real time
2. Replication has to be done over HTTP to our on-premise Sql Server, so I will need some security
3. Ensure the ClientId is inserted into the central database as part of the replication from live database.
I have read this article and I feel this closely captures my requirement. I look forward to your second article. I am happy if you can spare time to advise how I can implement my solution as well. I have asked this question here as well http://ask.sqlservercentral.com/questions/109357/sql-server-2012-replication.html%5B/quote%5D
Hi. PPTR does not sound like the solution, since every node is a publisher and subscriber to all other nodes. Thus, you would be forced to interconnect all of your clients, and the data from each one would end up everywhere.
One-subscriber-multiple-publisher articles aren't truly within the scope of SQL Server PPTR. Merge replication might work with web synchronization. To me, provided that you can arrange the replication, I would replicate or otherwise transmit the data into your server, with a discrete article in the central database for each client, and combine that with a job to ETL that into your central database on a schedule.