Multiple Publishers, Single Subscriber (Data Warehouse)

I'm trying to figure out the best way to populated the staging database in a
data warehouse. I will have a number of databases (identical schema, SQL
Server 2005 Standard). Ideally I'd set up each as a publisher, with the
same publication. There will be a single subscriber database (SQL Server
2005 Enterprise) that will subscribe to each of the publisher databases.
Data in the publisher databases will be modified. The subscriber database
will only be updated by its subscriptions, and therefore does not need to
send changes back to any of the publishers. Publisher databases don't need
to update each other. Replication will be occurring over the internet
(although VPN could be used).

A given table will have 'system' data that's identical in all publisher
databases, and also user-entered data that will be unique to a given
publisher database. I'd like all of this data to be merged into the
subscriber database so that it ends up with a single copy of each record of
system data, and a single copy of of each record of user-entered data. I'm
not clear on what kind of replication I should be using for this.

Hi cpnet,
Thank you for using Microsoft MSDN Managed Newsgroup. This is Charles. I am glad to work with you on this issue.

From your description, I understand that you would like to establish a replication with a many-to-one relation on publisher and subscriber.
You want all the data from the tables in your publications are merged into one table in your subscription.
If I have misunderstood, please let me know.

You can establish transactional replication for this requirement, however since you need to replicate many tables from different
publications to one single table in a subscriber, you need to ensure that all the publications and the subscription initially have the same
status before synchronization. Considering duplicate records may exist, I do not recommend that you apply any constraint on your
subscription table. You may consider to timely move the data from the subscription table to another table in your subscription on which you
can apply constraints and create indexes.

To configure a replication, you can refer to the following articles:
How to: Configure Distribution (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms151192(SQL.90).aspx
How to: Enable a Remote Publisher at a Distributor (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms151781(SQL.90).aspx
Creating Publications and Defining Articles (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms152542(SQL.90).aspx
Creating Subscriptions (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms152503(SQL.90).aspx
Securing a Replication Topology (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms152554(SQL.90).aspx
Synchronizing Subscriptions (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms152550(SQL.90).aspx

Hope this helps. If you have any other questions or concerns, please feel free to let me know.

Have a nice day!


Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg.DeleteThis@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx...tificat

Note: MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft
Support Engineer within 2 business day is acceptable. Please note that each follow up response may take approximately 2 business days as
the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not
appropriate for situations that require urgent, real-time or phone-based interactions. Issues of this nature are best handled working with a
dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/en-
us/subscriptions/aa948874.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================