Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently rearchitecting an application so that each of seven businesses will have there own "live" SQL Server 2008 database and at scheduled times during the data the data from all seven databases will be replicated to a master reporting database.

The question I have is do I need to do anything within the replication to maintain the Foriegn Key relationships from the source database tables or will replication take care of it for me?

share|improve this question
    
Does all the data have to go into the master database from the get-go? Or can it make a first stop into a replicated copy on the master server and then get transferred in via another method? –  Valkyrie Feb 9 '12 at 15:10
    
To be honest I am open to any architecture that fits. I can either have a staging database in the middle and failing that I will write a .Net app to transfer the data. Preference is to let SQL handle it through. –  Phil Murray Feb 10 '12 at 8:38
add comment

migrated from stackoverflow.com Feb 9 '12 at 14:37

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 3 down vote accepted

SQL Replication isn't going to handle that for you. You'll have to setup a very custom replication type system, possibly using SQL Service Broker to handle the merging of data into a single system for reporting.

share|improve this answer
add comment

I would recommend you keep staging databases for the data that's coming over, and then use an ETL process (SSIS or just stored procedures) to migrate that data into the reporting database. That way you'd be able to preserve all existing relationships without worrying about bits of data stepping all over each other.

share|improve this answer
add comment

This can be done using the Central Subscriber model by horizontally partitioning the data.

Foreign key constraints would be handled by replication. You need to specify the schema option to replicate foreign key constraints when adding articles to your publication.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.