Replication Distributor

I was wondering if you could set up multiple distributors for one database?
For example:
Server A needs to publish data to server B so I set up server A's distributor as server B.
Later Server A needs to publish data to server C so I want to set up the distributor for this replication to use server C.
I don't think this is possible as you need to set up the distributor for the entire DB when its created not for each individual replication job.
Any help is appreciated.
NickTry the link below for some replication info. Hope this helps.
http://www.replicationanswers.com/

Replication Deadlocks

Hello,
I am running multiple replication publication on a SQL 2k database, with
a separate server as distribution agent, then finally push subscriptions
to multiple databases across 5 servers.
All publications use row and column filtering in them.
My question is, can I use locking hints on the selection criteria for
the row filtering?
ie. Can I specify WITH (NOLOCK) on the row filtering so that it
eliminates the majority of the blocking / deadlocks?
Thanks
Dave
*** Sent via Developersdex http://www.codecomments.com ***
You could if it was a subquery. Do you have indexes on the join
condition/filter key in your filter?
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
"Dave" <Anonymous@.devdex.com> wrote in message
news:eqm8zv1THHA.5060@.TK2MSFTNGP02.phx.gbl...
>
> Hello,
> I am running multiple replication publication on a SQL 2k database, with
> a separate server as distribution agent, then finally push subscriptions
> to multiple databases across 5 servers.
> All publications use row and column filtering in them.
> My question is, can I use locking hints on the selection criteria for
> the row filtering?
> ie. Can I specify WITH (NOLOCK) on the row filtering so that it
> eliminates the majority of the blocking / deadlocks?
> Thanks
> Dave
> *** Sent via Developersdex http://www.codecomments.com ***
|||There is an index on the originating table/column, but the deadlocking
appears to be happening on the distribution database.
David
*** Sent via Developersdex http://www.codecomments.com ***

replication

Guys,
I am new to MS SQL server replication. As of now, I am trying to spec thngs
out. What is the difference between Multiple Publisher/Multiple subscriber
scenario and Updating Subscriber scenario? Both seem to me as a Sybase DBA
as Multiple master scenario as both have the same data and replication
happens both ways(two way replication). Also, In the Updating subscriber
scenario, server A is sending data to server B via the distribution server
but server B is sending data to server A via a SQL server Queue(Queue Reader
Agent). Why is the subscriber not sending data back to the publisher via the
distribution server? Also, what is the difference between immediate
updates and queued updates? As far as I can understand everything has to
be queued in order to send them to the other side.
Can someone please answer my questions?
Regards
Jaideep
Jaideep,
as a bit of background...
In normal transactional replication, an inserted record on the subscriber
will be replicated to the subscriber via the distribution database
(MSrepl_commands: use sp_browsereplcmds to view them). This is unidirectional
only. For queued updating subscribers the data flow is bi-directional, and
data changes are stored in a table on the subscriber (use sp_replqueuemonitor
to read this MSreplication_queue table). It has to be this way, because the
queue is available for offline access - ie there is the posibility that the
subscriber won't be able to access the publisher\distributer. In the case of
immediate updating subscribers the flow is again bidirectional, but there is
no need to save the command anywhere, because the subscriber enters a
distributed transaction.
I hope this clears up any confusion.
Regards,
Paul Ibison, SQL Server MVP

Replicating Multiple Tables

I can't seem to find the answer to this in the documentation.

I have several tables that I want to replicate via transactional replication. These tables do have some foreign keys between them.

What are the advantages/disadvantages of "one table per publication" versus "one publication with all of these tables listed as articles"?

If I do not define these in the same publication, will I get into trouble with the order that data is loaded to satisfy the foreign key constraints?

THe big disadvantage is management overhead of multiple publications. But for your scenario, you can mark FKs as not for replication at the subscriber if you want. However with tran, it will maintain and replicate in the exact order it was applied at the publisher.

Replicating Multiple Databases

I am currently using snapshot replication to backup a single SQL database to
a server at home. What technique(s) would be best if I wanted to backup say
6 databases? These would all be one-way as no changes are made to the data
from the backup server. Also, the data is not SO critical so as not to be
able to afford the loss of up to 24 hours worth of changes from one to the
other. I've considered doing 6 Publications-Subscriptions etc, but I
thought there must be a better way. Is there?
Thanks
Carlos, the publications are on a per-database basis, so there isn't a
simpler method than the one you're considering. Potential alternatives (not
simpler) include log shipping and transactional replication. If you're
concerned about bandwidth issues then these would be more suitable.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If you have big pipes between your publisher and subscribers I would
recommend log shipping. Otherwise I would recommend transactional
replication as only the changes will be replicated.
For transactional replication you will need a pk on each table; although
there are ways around this.
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
"Carlos Y. Maldonado, Ph.D." <cmaldonado@.earthlink.net> wrote in message
news:U5i4f.14372$vw6.9184@.newsread1.news.atl.earth link.net...
>I am currently using snapshot replication to backup a single SQL database
>to a server at home. What technique(s) would be best if I wanted to backup
>say 6 databases? These would all be one-way as no changes are made to the
>data from the backup server. Also, the data is not SO critical so as not
>to be able to afford the loss of up to 24 hours worth of changes from one
>to the other. I've considered doing 6 Publications-Subscriptions etc, but
>I thought there must be a better way. Is there?
> Thanks
>