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.

Replication and Enterprise Manager

Hi
I have a very interesting situation with my replication. Replication works
great if I leave opened Enterprise Manager, but when I close it,
replication and SQL server does not work any more.
If I made a "mistake" and close EM, that the next morning SQL server does
not respond even if I reboot server. I must reinstall SQL service pack and
than it works. This was happened 5 time and now I am positivly shure that
this is the problem.
And yes ... this is SQL 2000 with SP3, Windows 2000 Server with Terminal
services installed.
Did anyone knows about this funny problem?
Thank you
what security context are your replication jobs running under? It sounds
like they are not running under a system administrator account.
To fix this right click on your agents and look at your agent properties.
Change the owner account to an account which is in the system administrator
role.
<dklancar@.nkbm.si> wrote in message
news:uxK3yvvFEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a very interesting situation with my replication. Replication works
> great if I leave opened Enterprise Manager, but when I close it,
> replication and SQL server does not work any more.
> If I made a "mistake" and close EM, that the next morning SQL server does
> not respond even if I reboot server. I must reinstall SQL service pack and
> than it works. This was happened 5 time and now I am positivly shure that
> this is the problem.
> And yes ... this is SQL 2000 with SP3, Windows 2000 Server with Terminal
> services installed.
> Did anyone knows about this funny problem?
> Thank you
|||Thank you for response, but I must tell you, that all agent are running as
user listed in the system admin role.
Thank you again.
|||If SQL Server won't start, is there anything listed in hte Windows Application Log or the SQL Server log that sheds any light?
Regards,
Paul Ibison

Replicating over VPN to non trusted workgroup

I have a situation where a client connects to our network via VPN has set up
a SQL Server on a small workgroup that wishes to subscribe (via
transactional replication) to publications on my SQL Publisher.
My stumbling block is permissions to the \\myserver\repldata folder on our
domain. The client machine being a non-trusted domain (in fact not even a
domain) means I can't set up read and list files & folders permissions.
My plan is that I set up the pull subscriptions, create the snapshots and
then copy the entire \\myserver\repldata folder to the client machine and
point the pull subscriptions there.
Is this the best solution in the circumstances? After the initial snapshot,
does transactional replication ever need to access \\myserver\repladat
again?
Cheers!
Simon
Simon,
you can use the alternative snapshot location option - I do it this way for
large snapshots which I zip up before sending over.
If FTP is available this is probably an easier option though to set up the
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Many thanks.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OzVonAjYHHA.208@.TK2MSFTNGP05.phx.gbl...
> Simon,
> you can use the alternative snapshot location option - I do it this way
> for large snapshots which I zip up before sending over.
> If FTP is available this is probably an easier option though to set up the
> publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Try to use pass-through authentication. This is where you have two identical
local machine accounts with the same name and the same password.
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
"Simon" <simon.gladwell@.gmailNotThisBit.com> wrote in message
news:%23W9Y9siYHHA.448@.TK2MSFTNGP04.phx.gbl...
>I have a situation where a client connects to our network via VPN has set
>up a SQL Server on a small workgroup that wishes to subscribe (via
>transactional replication) to publications on my SQL Publisher.
> My stumbling block is permissions to the \\myserver\repldata folder on our
> domain. The client machine being a non-trusted domain (in fact not even a
> domain) means I can't set up read and list files & folders permissions.
> My plan is that I set up the pull subscriptions, create the snapshots and
> then copy the entire \\myserver\repldata folder to the client machine and
> point the pull subscriptions there.
> Is this the best solution in the circumstances? After the initial
> snapshot, does transactional replication ever need to access
> \\myserver\repladat again?
> Cheers!
> Simon
>
|||That works when I log on that account and browse to the folder. Should I
then run SQL Server and Agent under that local account?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OiI0V2jYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> Try to use pass-through authentication. This is where you have two
> identical local machine accounts with the same name and the same password.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
|||Yes!
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
"Simon" <simon.gladwell@.gmailNotThisBit.com> wrote in message
news:eQmS6QlYHHA.4692@.TK2MSFTNGP04.phx.gbl...
> That works when I log on that account and browse to the folder. Should I
> then run SQL Server and Agent under that local account?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OiI0V2jYHHA.4308@.TK2MSFTNGP05.phx.gbl...
>