Solved

Database transactional replication for very normalized database

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

18 Experts available now in Live!

Get 1:1 Help Now