Initializing Transactional Replication From two Different Database Backup

Question

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.

Answer 1

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.

Answer 2

But my problem is I have to restore two different backups to a single database. Is that possible at all?

You will loose the data from the first restore when you restore second backup. You have to find an intermediate way.

You can restore both backups as individual databases and then use tools like Redgate schema and data compare to generate scripts to merge the data and then take a full backup of the merged database and then replicate. This will be time consuming + your business will have to do testing on the new data to stamp it accurate.

The database size will be huge due to the data volume.

You can follow : SQL Server Many-to-One replication approach that I pointed out and Kendal Van Dyke's approach from Reduce Transactional Replication Synchronization Time By Applying Snapshots Manually

Answer 3

Another possibility is that you replicate both databases independently to your CityA server. Then each initialization of the database and the subsequent replications remain as simple as replication can be.

Then in either one of those databases or in a third database that you create, use stored procedures and/or views that combine the results from the two servers into result sets used for analysis.

Stored procedures could leverage temporary table (or some other work tables) to collect the needed data for a report or query covering both databases. Stored procedures would require some development,but it may be that you already have procs that just need to be copied and modified to include two databases.

Views that UNION ALL (or UNION) data from two databases may not perform quite as well, but it will work. (UNION ALL performs better, if that fits your data patterns.) And it simplifies your replication landscape and preserves the idea of giving the boss data from both locations.

Note: Perhaps in that case you have a CityBdatabase and CityCdatabase database replicated to your CityA server, but a separate CityAdatabase contains the objects that pull the results together for the combined report.