Completely resetting replication? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Completely resetting replication?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2001-11-26 : 16:50:03

I've got a production SQL server that has never had replication configured on it. Setting it up for the first time, it failed with the message "Error 18484: Could not connect to server XXXXX because 'distrubutor_admin' is not defined as a remote login."

Since then I've played with it a bit, tried setting up a DB using the instrepl.sql script manually (works, but the server is still not happy).

Is there any way, short of uninstalling and reinstalling SQL server, to completely clear out replication on this server so I can start over?

It is 24/7 production server, so I'm really not looking forward to the uninstall/reinstall option. We have no second server at the moment (That's what replication is going to be for).

Thanks
-Brooks

SKIBUM
Starting Member

32 Posts

Posted - 2001-11-26 : 18:31:33

Here's a link to an article on mssqlserver.com:

http://www.mssqlserver.com/replication/bp_manual_replication_cleanup.asp

I've had to do this a few times and it's not fun to disable; however, it is possible to get it done.

aiken
Aged Yak Warrior

525 Posts

Posted - 2001-11-27 : 00:41:32

Thanks for the link... I tried the steps the article mentions with no luck. Same error is still happening.

The problem seems to revolve around setting up the distributor. Enterprise manager doesn't even successfully create the distribution database, though I can do that manually. If I do that, I can get as far as defining publications and getting a merge agent set up, but it always fails with various weird errors. Something is just messed up on this server.

I've got a second SQL server that's also in production, configured identically (set up at the same time, same service packs, you name it) and of course it's happy to set itself up as a distributor. Unfortunately, I need things to go the other way.

Any other ideas, anyone?

Thanks
-Brooks

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2001-11-27 : 21:43:45

Did you try this:

1. Right-click tbe Replication folder in EM, and select "Configure publishing,.etc."

2. Click next, and select "Make <servername> its own distributor". Click next again.

3. Enter a snapshot folder path

4. Select any other appropriate options

aiken
Aged Yak Warrior

525 Posts

Posted - 2001-11-28 : 00:24:03

Ohhh, yeah. Many times. That way doesn't get me far at all; it looks like EM fails to create the distribution database for some reason, then errors out.

The farthest I've gotten is by manually creating and populating the distribution database (with instrepl.sql) and using the various sp's to tell the SQL server to use it. That way, I can get as far as setting up publications... but the merge agent is never created for some reason.

I'm definitely in need of a system table-level approach for stripping out replication altogether, then reconfiguring it. Otherwise it's the dreaded uninstall/reinstall of SQL server, for optimistically an hour of downtime on a 24/7 production server. Ugh.

-b

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2001-11-28 : 01:35:51

Sorry -- that was kind of a lame question

If you haven't run all these procs already, try this. It's not particularly pretty and has no error handling, but it might be worth a try before doing a re-install.

==============================

CREATE PROCEDURE sp_removerepl
AS
DECLARE @dbnames TABLE 
  ( 
  dbid INT IDENTITY, 
  dbname SYSNAME
  ) 
INSERT INTO @dbnames (dbname) 
SELECT name FROM sysdatabases WHERE dbid > 4 -- skip master,tempdb,model,msdb
DECLARE @i INT 
DECLARE @dbname SYSNAME 
SET @i = 1 
WHILE (@i <= (SELECT MAX(dbid) FROM @dbnames))     
  BEGIN     
    SELECT @dbname = dbname     
    FROM @dbnames     
    WHERE dbid = @i     

    EXEC ('sp_replicationdboption N''' + @dbname + ''', N''publish'', false')
    EXEC ('sp_removedbreplication ' + @dbname)
    EXEC (@dbname + '..sp_droppublication @publication = ''all''')
    EXEC (@dbname + '..sp_dropmergepublication @publication = ''all''')
    EXEC (@dbname + '..sp_dropsubscription @publication = ''all'', @article = ''all'', @subscriber = ''all''')

    SET @i = @i + 1     
  END

EXEC master..sp_dropsubscriber @subscriber = @@SERVERNAME
EXEC master..sp_dropdistpublisher @publisher = @@SERVERNAME
EXEC master..sp_dropdistributiondb @database= 'distribution'
EXEC master..sp_dropdistributor

aiken
Aged Yak Warrior

525 Posts

Posted - 2001-11-29 : 01:14:37

Thanks for the help there, PoE. I tried it, and it produced the errors you'd expect (for databases not published and such). Definitely a handy script to have around.

However, still no luck. Basically the same problem: enterprise manage doesn't create the distribution database, and if I do it myself I get as far as configuring table for replication, but the agents refuse to start.

Thanks, though! And I will keep the script for future reference.

Cheers
-Brooks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources