I have two different large Database (200GB for each) which I want to replicate their data to a central database (3rd DB). I followed this link and it was very useful for a single database.
But my problem is I have to restore two different backups to a single database. Is that possible at all?
I am working for a company which it is located in CityA. it has two branches in two different cities CityB and CityC. I have developed a software for them to insert the the customers' information and scan their documents. and I have developed another software for their boss to monitor the data inserted in CityB and CityC for the sake of doing some reports and analysis. until now I could do it by replicating the data form CityB and CityC to CityA with central subscriber topology and it was fine. But now the problem is the Databases in CityB and CityC have become very big (about 200GB for each). Now something happened and I had to establish the replication again. and it is now very cumbersome and time consuming and sometimes it gives strange and wired errors. the last problem I had was the snapshot agent is very very slow and it stops at the following message The process is running and is waiting for a response from the server which I asked here
I can replicate CityB to CityA a with Initialize using Backup but I don't know how to do the same for CityC?
another question is that do I have any better choice instead of replication at all?
please give me the practical advice or links as I am not a professional DB Admin and I am actually a programmer.
Instances are all sql server 2008R2, windows Servers are 2008R2 the connection between the cities are VPN over Internet.
Best Answer
The replication topology that you are describing is known as the Central Subscriber Model which is comprised of two or more Publishers replicating to one Subscriber. The dilemma you are having is how to go about initializing the Central Subscriber, which is a common conundrum.
I have talked about this in detail in my post Central Subscriber Model Explained and have found through experience that the best way to initialize a Central Subscriber is with a snapshot, utilizing horizontal partitioning, static row filters, and setting the Action if name is in use article properties to Delete data. If article has a row filter, delete only data that matches the filter.
If you have anymore questions please let me know. I hope this helps.