September 1, 2011 at 10:11 am
Hi
We have three servers so I wanted to enable replication on these servers so background for this installtion is Server A is Publisher, ServerB is Distributor and Server C is Subscriber. I had started setting up replication in our environment with remote distributor on Server B and I was struck here and the thing that I did here is created distributor on Server B and dropeed it and I want to clean up and start settin up from scratch.
Here are the steps I did it after the distributor database has been created on server B.
use master
go
alter database distribution set offline;
drop database distribution;
USE MASTER
GO
create database distribution on
(filename=''),
(filename='')
FOR Attach
go
----------------------------------------------------------------------------------------------------
Then I tried disabling publishing and distribution using SSMS this is the information I get
-------------------------------------------------------------------------------------------------------
TITLE: Microsoft.SqlServer.ConnectionInfo
------------------------------
SQL Server could not disable publishing and distribution on 'XXXXXXX'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
sp_MSdistpublisher_cleanup can only be executed in the "distribution" database.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21482)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=21482&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Thanks,
Ravi
September 1, 2011 at 11:24 am
The error message is because you dropped the distribution database before you were done disabling replication. Any particular reason you did that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 1, 2011 at 12:38 pm
I didnot know how to move forward with enabling replication so, I dropped it. can you please let me know how to move forward.
Thanks,
Ravi
September 1, 2011 at 12:42 pm
You're in kind of uncharted waters on this one.
I'd try just setting up Replication again, as if it were never there. You'll end up with some orphan artifacts on some of the servers, but that shouldn't have any functional impact. Can't guarantee that'll work, but it's what I'd try next.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 1, 2011 at 12:49 pm
HI
The one thing I did is created a distribution on Server B and dropped the database. I did not even enabled the replication or created any publisher/subscribers.
and when I tried this step
USE MASTER
GO
create database distribution on
(filename=''),
(filename='')
FOR Attach
go
The distribution database was up but this db was not created under system databases instead it was created in user databases.
Thanks,
Ravi
September 1, 2011 at 1:02 pm
Try this: http://msdn.microsoft.com/en-us/library/ms151192.aspx The idea here is that you cannot just create a distribution database the way that you did. This page will show you how to do it from SSMS, but there are also links on the left on how to script it.
Thanks,
Jared
Jared
CE - Microsoft
September 1, 2011 at 1:06 pm
Also, for what reasons are you using a second server as the distributor? There are reasons for this, but there may be better reasons to keep the publisher the distributor and use the extra server for something else like a mirrored backup or something...
Thanks,
Jared
Jared
CE - Microsoft
September 1, 2011 at 1:20 pm
Hi Jared.
The reason here is we wanted all the publisher, distributor, subscriber to be independent to each other the database is used constantly by application and we did not wanted to have any performance issues that is the reason why we did that and part of it I started Server B and configured distributor and later I dropped it and again I created distributor with the above steps that I mentioned in my previous post and Im unable to remove the database distribution and now I want to create the replication from the scratch and some orphaned infomation sitting on server B and I want to create distribution on server B and it is not allowing me to do it. can you please let me know how can I clean up this entire mess that I had done.
Thanks,
Ravi
September 1, 2011 at 1:31 pm
Ok, this will not work:
USE MASTER
GO
create database distribution on
(filename=''),
(filename='')
FOR Attach
go
This creates a user database named [distribution].
I don't understand what the current issue is... You want to remove distribution in the system databases? Or you want to create it?
Thanks,
Jared
Jared
CE - Microsoft
February 6, 2012 at 10:36 am
If you got here as I did because of this error:
sp_MSdistpublisher_cleanup can only be executed in the database.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21482)
And the issue is you can't setup or alter Publishing/Distribution on your server because someone deleted your distribution database and all you want to do is remove that reference to the Distribution database (mine was not actually called 'distribution'), then go to this post[/url]:
The part that worked for me (I wanted publishing/distribution/replication gone and had *nothing* to lose, use at your own risk...refer back to the post above before doing anything):
[font="Courier New"]exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply