?
Solved

Database transactional replication for very normalized database

Posted on 2013-05-13
4
Medium Priority
?
300 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:Faiga Diegel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 75

Assisted Solution

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

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:Faiga Diegel
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 27

Accepted Solution

by:
Zberteoc earned 750 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

765 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