Simple replication question… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Simple replication question…

<br /> I hope someone can help me here ( Satya maybe <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> )…<br /><br />I have 3 servers, Transactional/Push subscription…<br /><br />P1: is publisher .<br />S1: Distributor/Subscriber.<br />S2: New Server. I created a distribution server on it.<br /><br /><b> This is what I have now: </b><br /><br />P1 is pushing data to S1. When I right click on "replciation" info for P1, I see S1 being designated as it’s Distributor. Everything is fine.<br /><br /><b> This is what I want to do </b><br /><br />Start using S2 ( the new server) as the distributor for P1 and disable S1.<br /><br /><b> Question </b><br /><br />1. How can I disable S1 ( the current distributor) and point P1 to S2 ( the new distributor)?
Hi Camilla
(surprised to see you here) BOL specifies :
To disable a Distributor (Transact-SQL)
Execute sp_dropdistpublisher to drop a Publisher that is also the Distributor.
Execute sp_dropdistributiondb to delete the distribution database.
Execute sp_dropdistributor to remove the Distributor designation from the server.

And use distrib – Replication Distribution Agent Utility (command line). To enable publishing – Click the properties button (…) next to a Publisher to set the security options, login information, and snapshot folder. The servers you select as Publishers will use the distribution database created by this wizard. If you want to use a different distribution database than the one created in this wizard, do not enable the Publisher at this time. Instead, use the Publisher and Distributor Properties dialog box to add Publishers after you complete the Configure Publishing and Distribution Wizard.
Also refer to BOL for -Remote Agent Activation topic.
Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
You are wonderful! thanks!