4

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.

  • Can you clarify restore two different backups to a single database do you have striped backup files ? – Kin Shah Oct 3 '13 at 11:59
  • @Kin I have two different back up from two different database which their schema are the same but they have different data. according to the link in my question, I have to restore the backup of the database at first and then create the subscription from the backup. the problem is that I have two different database hence two different back up files. how can I restore them both to a central database? – Mehrdad Kamelzadeh Oct 3 '13 at 12:08
  • You cannot do a 'merge restore' of two databases into a single database. Each database is independent. – RLF Oct 3 '13 at 12:19
  • @RLF so what do you suggest for my scenario? I have to replicate two database one in cityB and one in cityC to a central database in cityA. Both are with the same schema and they are about 200GB – Mehrdad Kamelzadeh Oct 3 '13 at 12:50
  • @MehrdadKamelzadeh So you want to do a many-to-one replication. Refer to my answer here on how you can do it (dba.stackexchange.com/a/43995/8783). It would be tricky as your database is huge ? Why are you replicating your entire database ? Also due to your huge database size, have a look at (dba.stackexchange.com/a/50227/8783). Both links should help you. – Kin Shah Oct 3 '13 at 13:07
2

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.

0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.