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 central database 3. A central database which has an identical schema for all the tables on live databases which I host on our on-premise server 4. In addition, I have added a ClientID column to each and every table to identify the clients 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 over HTTP so I will need some security 3. Ensure the ClientId is inserted into the central database as part of the replication I think this sounds like a common problem out there which may already have a robust solution to implement. Kindly advise the best way I can implement the replication. I really need heads up and I am happy to ready any reference material if I am signposted.
Since you have more than one client bringing data in, you'll need to use [Merge Replication]. This means you have to modify your structures to use globally unique identifiers (GUID) as primary keys in order to prevent collision of data in the central database. For many people, that immediately makes replication a non-starter. Go to the link, read through it and understand what it implies. If you do want to use the replication technology, that's what you need to do. Otherwise, I know people have built that process manually, having the central database have a different structure that allows for identifying which client the data came from as well as mechanisms for generating primary key values. That's a lot of work, but it does mean you won't have to redesign and rebuild your client databases. :