Replication Dilemma - Need a Solution/Ideas

Hi all,
Here's a situation I have been challenged with....
I will have up to 6 remote databases and I want them all to update a
single master database in a company with multiple production plants.
The remote databases will just have active work in a plant and only the
work that is in that particular plant. I would like the master
database to have all information from the plants. Ideally, the remote
plants will update the master database once an hour (consisting of new
inserts of orders coming into the plant and updated records as work
moves through the plant). Once work ships from a plant, I want to purge
it off the remote plant database, yet keep it on the master.
It seems with replication, if I delete records at a plant, it will
delete on the master, which is not what I want. The master is
basically the database customer service will use and needs active
status on work in the plants and would contain a couple of years of
information. The remote plant databases have just the data they need
and our optimized. A remote database has 20 tables, with multiple
tables with 1 to 20 million records actively inserted/udated each day.
The schema of the master is identical to the plants and all keys at
each plant are guaranteed unique.
Any ideas would be greatly appreciated, as I am currently contemplating
using bcp in an automated fashion and anticipating a nightmare of
keeping referential integrity.
Regards
What I think you need to do is the following.
1) create a filter on your publications which filter by plant id. Hopefully
you have a column to do this with. Then when you create you publication and
get to the specify articles dialog click on the browse button to the right
of your tables. Select the snapshot tab, and select the option to delete the
data which matches the row filter.
2) In the table article properties dialog, click on the commands tab and
replace the delete command with the word NONE - this will prevent deletes
from being replicated
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
<davidkrainess@.yahoo.com> wrote in message
news:1131135473.722037.278030@.g44g2000cwa.googlegr oups.com...
> Hi all,
> Here's a situation I have been challenged with....
>
> I will have up to 6 remote databases and I want them all to update a
> single master database in a company with multiple production plants.
> The remote databases will just have active work in a plant and only the
> work that is in that particular plant. I would like the master
> database to have all information from the plants. Ideally, the remote
> plants will update the master database once an hour (consisting of new
> inserts of orders coming into the plant and updated records as work
> moves through the plant). Once work ships from a plant, I want to purge
> it off the remote plant database, yet keep it on the master.
> It seems with replication, if I delete records at a plant, it will
> delete on the master, which is not what I want. The master is
> basically the database customer service will use and needs active
> status on work in the plants and would contain a couple of years of
> information. The remote plant databases have just the data they need
> and our optimized. A remote database has 20 tables, with multiple
> tables with 1 to 20 million records actively inserted/udated each day.
> The schema of the master is identical to the plants and all keys at
> each plant are guaranteed unique.
> Any ideas would be greatly appreciated, as I am currently contemplating
> using bcp in an automated fashion and anticipating a nightmare of
> keeping referential integrity.
> Regards
>
|||Thanks...
My initial thoughts were a bcp dump, zipping the data, sending it over
the WAN, and unzipping then loading it with update/insert SPs. bcp is
incredibly fast, but this strategy requires a lot of management
applications written to ensure the data gets to the master and there
might be loss of some referential integrity as each bcp command would
dump a table at a different time interval (that would be fixed in the
next hourly update). I am intrigued by the replication strategy
because of it's ease of use.
Assuming I have the distributon on the master, what is the performance
impact on the plant databases, assuming a publishing every hour. I am
concerned that the overhead of a replication scheme is vastly greater
than a bcp dump.
What is going on with the replication and how do people set this up. I
guess I was thinking of each plant's database dumping it's repl data on
a remote share over a WAN on the distribution server. I was thinking
the Master would reside on the same server as the distribution (why not
take advantage of loading the data off of a local disk). bw, I would
rather take slowness on the master than in a production facility.
Also...what's the overhead of the replication dump, is it similar to a
log? Given a WAN is down, would sql recover and recopy the replication
data when the WAN came backup?
Also, is this information in any of the replication books out there
(I.E. enterprise strategies for moving data) with pros and cons of
different methods.