Solved

Database transactional replication for very normalized database

Posted on 2013-05-13
4
290 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now