sql server replication:
Hi,
I'm setting up a three-tier replication. The top tier is a corporate
server running SQL Server 2005 STD. That is setup as a publisher. The
second tier is many district servers running SQL Server 2005 STD. Those
are setup as subscriber/publisher. Within each district are many
offices running SQL Server 2005 Express. Those are setup as
subscribers.
The goal of the system is to push out control data from the corporate
server, to the district server and then to the office servers. The
control data is used to validate entry of data entered at the office
server. That data will be copied into district and corporate data
warehouses via a scheduled job. The data will sit for a while on the
office servers and will be deleted after a period of time by another
scheduled job. I don't want the data in the data collection tables to
be replicated up to the district server from the office server.
The exact same database structure is used on all three tiers. The
database contains PK, FK and RI for integrity. GUIDS are used as PK for
all tables. Any corporate, district, or office server could change the
control data with appropriate security.
Most of the tables in the database will be used for control. That data
is not very large. Maybe 10 MB. A handful of tables in the database
will be used for collecting data in the offices. I expect that data to
be several TB in size in the data warehouses.
I have setup merge replication between the corporate server and
district servers with no issues.
I have also setup the copy of data from the office servers to the data
warehouses with no issues.
I also setup merge replication between the district server and office
servers. On that publication I specified subscriber_upload_options = 1
(Changes are allowed at the Subscriber, but they are not uploaded to
the Publisher) on the data collection tables that I do not want
replicated back to the district publisher. When I checked the district
server the data from the office server was being replicated back to the
district server.
Any idea what I am doing wrong? Any ideas of how to do this better?
Thanks for your help. Ed
