Central Subscriber with updates to subscriber possible?

I am trying to set up a central subscriber topology (seven small databases
replicating into one large database). The requirement I am having trouble
with is that updates need to be made to the central subscriber and get
replicated back to the publisher DB they belong to.
This is why this topology is needed: The company sells training courses,
both online and through regional sales offices. The seven publisher
databases are located in the regional sales offices, and are updated through
a local application by the staff taking course registrations in their
office. The central subscriber is located in the corporate headquarters, and
drives the website where customers can register online and update their
contact info. Contact info updates and registrations made online need to be
replicated back to one or more of the regional office databases. The rules
for which regional DB to update as basically this:
1. Customer records need to get replicated to the regional database that
manages the geographical area containing the customer's address (can be
determined by State and Country) AND to any other regional databases where
the customer has registered for a course (the courses are held at the
regional offices).
2. Registration records need to get replicated to the regional databases
where the course registered for is held.
My biggest problem is that only merge replication seems to be able to handle
the logic about passing updates back to the regional databases, however
merge replication does not support the central subscriber topology, only
central publisher. I am very wary of trying to create a central publisher
topology out of the current setup (i.e. create a central merged DB, drop the
regional DBs and recreate them) - 90% of business activity happens in the
regional offices and it seems too risky to put the regional DBs at the
effect of the central one.
Is there any solution to this?
I think you will find that merge replication will work well for this. It is
a common deployment setup. Look at dynamic snapshots to redeploy the branch
office data, or separate publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:efsiLA0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> I am trying to set up a central subscriber topology (seven small databases
> replicating into one large database). The requirement I am having trouble
> with is that updates need to be made to the central subscriber and get
> replicated back to the publisher DB they belong to.
> This is why this topology is needed: The company sells training courses,
> both online and through regional sales offices. The seven publisher
> databases are located in the regional sales offices, and are updated
through
> a local application by the staff taking course registrations in their
> office. The central subscriber is located in the corporate headquarters,
and
> drives the website where customers can register online and update their
> contact info. Contact info updates and registrations made online need to
be
> replicated back to one or more of the regional office databases. The rules
> for which regional DB to update as basically this:
> 1. Customer records need to get replicated to the regional database that
> manages the geographical area containing the customer's address (can be
> determined by State and Country) AND to any other regional databases where
> the customer has registered for a course (the courses are held at the
> regional offices).
> 2. Registration records need to get replicated to the regional databases
> where the course registered for is held.
> My biggest problem is that only merge replication seems to be able to
handle
> the logic about passing updates back to the regional databases, however
> merge replication does not support the central subscriber topology, only
> central publisher. I am very wary of trying to create a central publisher
> topology out of the current setup (i.e. create a central merged DB, drop
the
> regional DBs and recreate them) - 90% of business activity happens in the
> regional offices and it seems too risky to put the regional DBs at the
> effect of the central one.
> Is there any solution to this?
>
|||Thanks for your reply. When you say "Look at dynamic snapshots to redeploy
the branch office data, or separate publications." it sounds like I have to
stop using the regional databases as the publishers, drop them and recreate
them as subscribers to the central database. Is that right and is there any
way to avoid dropping and recreating the regional databases? I am trying to
avoid any impact to the regional databases.
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eHh36D4pFHA.248@.TK2MSFTNGP14.phx.gbl...
>I think you will find that merge replication will work well for this. It is
> a common deployment setup. Look at dynamic snapshots to redeploy the
> branch
> office data, or separate publications.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:efsiLA0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> through
> and
> be
> handle
> the
>