[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Database transactional replication for very normalized database

Posted on 2013-05-13
4
Medium Priority
?
301 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

649 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