asked
Sql Server 2012 Replication
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][1]. 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. [1]:
http://technet.microsoft.com/en-us/library/ms152746.aspx
@Grant Having read the Technet article you linked above, I prefer your second option. Suppose I define the Primary Keys on the central database as a combination of ClientId and the PK on each table - then this will sort out the primary keys. (of course I will turn off auto generation of PK seed values on these columns too). Do you have any further details on how option two can be implemented?
It's not something I've built myself, so I can't directly advise you in that area. But, the approach of having a compound key that combines the client-side key with a client identifier should work just fine. But, remember that you'll need that on all tables, not just at the top of the chain. Other than that, it's going to primarily building a data migration mechanism. I'd have a bias towards using SSIS, but you have other options there too.
15 People are following this question.
