how to configure from local distributor to remote

Hi,
I already have a merge replication setup successfully.
However I now need to change the network from a central publisher to a
central publisher with remote distributor
See http://www.databasejournal.com/features/mssql/article.php/1458491 and
Ctrl+F for the words central publisher.
I tried to redo another publication but the wizard no longer asks me whether
I want my distributor to be on a remote machine.
Then I tried to follow this article
http://technet.microsoft.com/en-us/library/ms151192.aspx
and looked for a Configure distribution which I cannot find.
Then I tried this article
http://msdn2.microsoft.com/en-us/library/ms147363.aspx
, but realise that the value of installed in the result set is 1, so I am
not sure what I should do next. Furthermore I am unfamiliar with the T-SQL
syntax necessary to make the changes.
FYI, the result set from using the sp_get_distribution was
installed = 1
distribution server = machineName
distribution db installed = 1
is distribution publisher = 1
has remote distribution = 0
Please advise. I am running SQL server 2005 Trial edition on 2 separate
win2003 server with one of them already set up as a successful central
publisher.
If I really have no choice but to use T-SQL please give me more explicit
details. Thank you.
This is pretty much a one-off choice. To move to a remote distributor I'd
script out the publications, remove the subscriptionas and publications,
remove the distributor entirely, readd the distributor remotely then add the
publications again.
HTH,
Paul Ibison

How to configure distributor thread for being up while replicated database is down

Hi
I often deal with a problem when replicated database is down due to electric outage or any other scheduled type of downtime. Primary database is up but distribution agent is going down 'after 10 attempts to connect to replicated database'. That's the problem I think because not only distribution agent goes in undetermined state and I have to restart sqlagent service, but I need to go through whole process of snapshot and applaying it to replicated db. I would like it to accumulate transactions in distribution database and once replicated db is up - distribute them.
I wonder if anybody can tell me how to configure distribution thread so it does not go down after 10 unsuccessfull attempts but stays for day or two.
Thank you, Gene.

Gene, why do you need to re-apply the snapshot, is your retention period very small? By default it's 72 hours, so if distribution agent can get back up within that time period, it should just pick up where it left off.

Unfortunately if your distribution agent is running in Continuous mode (which you mention since it goes down after 10 unsuccessful attempts), the workaround would be change it so that the agent runs on a scheduled interval, like every minute. This way in the case the machine gets knocked off the network, the agent will always run every N minutes, pass or fail. As soon as the machine gets back on the network, it should pick up where it left off, provide the oldest txn that hasn't been replicated is within the retention period.

|||

The other possible solution is to increate the number of retry attempts for your distribution agent job, when you view job steps of an agent job, there is an advanced tab, on that screen, you can specify a big value for "Retry Attempts" and how often you want the retry to happen "Retry Interval (minutes)". For example, if you want the retry lasts for 2 days, you could put 2880 for Retry Attempts and 1 for Retry Interval.

Hope that helps,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||I don't think this exists for SQL 2000, but if it does, then this is also a good solution.

How to configure bidirectional replication

In a MSCS SQL 2000 SP3 cluster environment I plan on implementing
transactional replication. Distributor and publisher are on the same server
(quad processor machine). I Intent to publish a relatively small db (@. 60
articles) to a subscriber in a remote SQL Server cluster and would like to
have updates made to subscribers also replicated back to publisher. I
understand bidirectional replication is not supported by the replication
wizard in enterprise manager. How can that be achieved?
For starters, this article may help:
http://support.microsoft.com/default...75&Product=sql
There's also an article on this subject in SQL Server Magazine
(www.sqlmag.com) by Bren Newman, ArticleID 42858.
"Cesar" wrote:

> In a MSCS SQL 2000 SP3 cluster environment I plan on implementing
> transactional replication. Distributor and publisher are on the same server
> (quad processor machine). I Intent to publish a relatively small db (@. 60
> articles) to a subscriber in a remote SQL Server cluster and would like to
> have updates made to subscribers also replicated back to publisher. I
> understand bidirectional replication is not supported by the replication
> wizard in enterprise manager. How can that be achieved?
>
>

how to configure a server as distributor

When configuring my SQL Server as a distributor I get the following
error:
Error 14113:Could not execute 'copy "
C:\Program Files\Microsoft SQL Server\MSSQL\DATA\DISTMDL.MDF"
"C:\Program Files\Microsoft SQL Server\MSSQL\DATA\distribution.MDF"'.
Check
'instdist.out' in the install directory
I could not find a file named instdist.out in the install directory.
Please Help!!
Thanks.
The setup will require you to be a system administrator - are you logged on
as one?
Also has the distribution database been created?
Also, check that you have sufficient room for a copy of the distribution
database.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I've logged in as system administrator.
distmodel database is also there and have sufficient space on my disk.
but still the problem has not been solved.
please show me a way.
thanx
jeff
Paul Ibison wrote:
> The setup will require you to be a system administrator - are you logged on
> as one?
> Also has the distribution database been created?
> Also, check that you have sufficient room for a copy of the distribution
> database.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I'd set it up manually and see where the issue is:
sp_adddistributor
sp_adddistributiondb
sp_adddistpublisher
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||First disable distribution from the Replication menu
Then try below commands in query analyser
sp_helpserver ( to see server name)
sp_dropserver 'SERVERNAME','DROPLOGINS'
sp_addserver 'SERVERNAME','LOCAL'
Then try creating Distribution again
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u7Y6pBCBHHA.3540@.TK2MSFTNGP03.phx.gbl...
> I'd set it up manually and see where the issue is:
> sp_adddistributor
> sp_adddistributiondb
> sp_adddistpublisher
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Take the database distmodel to Offline
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

how to config server as a Distributor

how to config server as a Distributor?
thnx
Message posted via http://www.webservertalk.comBook online has details on this:
http://msdn.microsoft.com/library/e...pl_sph_2so9.asp
-oj
"zizi via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:dea0c44760714273b90e28953aeac4c1@.SQ
webservertalk.com...
> how to config server as a Distributor?
> thnx
> --
> Message posted via http://www.webservertalk.com

How to clean out Replication on DBRepaired DB

Hi all,

I had a suspect database that I had to kill.. it was non-recoverable.
The DB was a publisher and distributor for replication.
The Publications are still listed Enterprise Manager, the distribution database is still poopulated with publication info for this database.

Anyone know an easy way to clean out the replication info for the Dropped DB?

Thanks,
RyanHave you tried setting status with SP_RESETSTATUS and may use sp_helpreplicationdboption to get info. on replication.|||Yes, Thank you for replying.

I used SP_RESETSTATUS to set the status on the DB so it is not suspect.
I tried to kill replication at this point but it could not access the DB becasue the MDF file was corrupted. Nothing I could do would get the database recognizable...the recover function on start-up would consistantly fail because of a bad FCB on the file.

So I used the DBCC DBREPAIR command to kill the database...but in the replication monitor in Enterprise manager, the publications still exist for the non-existant DB. The Distribution database still contains info about those publications. Is there a Stored Procedure that anyone knows of to cleanup the distribution database?

I am also planning on applying SP4 to this SQL7 server..but have been waiting to clean up the databases first. Any thoughts?

Thank you so much to the DBFORUM community!

Ryan Jones

Originally posted by Satya
Have you tried setting status with SP_RESETSTATUS and may use sp_helpreplicationdboption to get info. on replication.|||Use SP_REMOVEDBREPLICATION and then drop the database using DROP DATABASE statement. Then go for SP4.