Solved

Database transactional replication for very normalized database

Posted on 2013-05-13
4
294 Views
Last Modified: 2013-05-30
We have almost 120 host that we want to replicate our database to our centralize data center. These 120 hosts are located all over the county. The database is pretty small (less than 5 GB, and will not be more than that in the next 5 years or so. I just anticipate it to be 10 GB in 10 years), however the challenge we are facing is how to fully replicate this very normalize database without the hassle of foreign keys and/or parent-child relationship.

The MS SQL Transactional Replication is not gonna work. Please don't bother to suggest this. We tested and a lot of issues and work around that is not worth the effort of having this setup.

If no tools available around, any ideas how to cope up with this requirement? I believe somebody out there had done this before and we are not the only one needing this.

Thank you!
0
Comment
Question by:faiga16
  • 2
4 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 39162173
Have you thought of logshipping  ?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39163035
Define "without the hassle of foreign keys and/or parent-child relationship". You don't want to use them in a transactional replication context or you think they won't work?

Transactional replication works fine if you want to replicate all the objects, including procs, functions, views, etc and foreign keys and indexes. including spatial in SQL 2008.

However log shipping is probably the simplest solution as it was suggested. The only problem you have is that at the central location server you will have to create 120 databases. With transaction replication you can practically replicate to the same table from multiple sources if the structure for all the databases is the same. The only problem would be the values for PKs and unique indexes if they can have the same value over multiple servers, i.e. if you uses identity columns. That would be the real hassle.

Multiple databases though with log sipping would work.
0
 
LVL 15

Author Comment

by:faiga16
ID: 39166693
Define "without the hassle of foreign keys and/or parent-child relationship" -->> we do use them in our database. If we have a 20 normalize tables, each of them linked together on a foreign key to primary key, does this mean each of these 20 tables will have an identifier so that when merge to the centralize DB it will be unique using an identifier for each host DB and it's primary key? And then in the centralized DB, when we need to query/report we will have to do the join on both the unique identifier and the table's original primary key on all of the tables. Isn't?

Oh, and overtime our table structure from the 120 host databases will not be on the same version at one time, for the reason that we can't upgrade all at the same time due to regulatory process/steps, etc.  So that is also one of the reason why transactional replication is not going to work for us. The replication idea we are trying to come up with should support old version of the database structure.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 250 total points
ID: 39168021
"And then in the centralized DB, when we need to query/report we will have to do the join on both the unique identifier and the table's original primary key on all of the tables. Isn't?"

And why would that be? You will still use the original columns to join if, as I said, the values are overall unique, but probably that would not be the case.

Anyway, it seems that log shipping or a custom process is what would work here.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question