Re: Host Name Change and Replication
From: Anthony Thomas (ALThomas_at_kc.rr.com)
Date: 01/24/05
- Next message: Anthony Thomas: "Re: float point error"
- Previous message: Aaron [SQL Server MVP]: "Re: float point error"
- In reply to: Shaker: "Re: Host Name Change and Replication"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 23 Jan 2005 21:27:41 -0600
Same thing. That data is on the distributor in the msdb that lists current
subscriptions. Those are orphaned to of course and only a hack or a
restoration can fix it.
There is also information in the registry for the Windows Synchronization
system that can catalogue information like this. Just scan the registry for
the PDB01 server to find the keys.
Sincerely,
Anthony Thomas
--
"Shaker" <Shaker@discussions.microsoft.com> wrote in message
news:89A95B6E-A09F-4165-BBE5-2BCB4086FBC0@microsoft.com...
Yes, the msdb.sysjobs did still contain PDB01 as the server originator and
this caused the 14274 error about the MSX. I tried the following code
DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('PDB02') AS sysname)
UPDATE sysjobs SET originating_server = @srv
under the MSDB database and I got could not insert NULL value error. So I
just opened up the table and edited it manually. After I did this and
updated
the Master db I was able to disable replication and then re-enable it
under
PDB02.
I fixed the account error. First off it wouldn't let me add the user
PDB02\accountName in the Security folder. I figured this was due to SIDs
or
something. So I removed any occurence of PDB01\accountName on the server
and
deleted the Login information. I then stopped the mssqlserver and
sqlagentservice services and reenabled them as local system. I then
deleted
the user from Local Users group and added him back in with same password
and
privs. I also went into the registry and changed the default domain
registry
key in the HKLM\Software\Microsoft\MSSQL\blah folder. I'm not sure if this
was necessary or not. I then stopped the mssql and sqlagentservice
services
and reenabled them as the local accountName user again. I then added
accountName user to the Security folder again, reenabled replication, and
then made sure each agent was using the accountName and tested the
replication. It worked perfectly!
The only problem now is that on my push subscribers they list two
subscriptions, one from PDB01 and one from PDB02. The PDB01 one is
orphaned
of course, how is it possible to remove that? Some registry setting?
Thanks,
Andrew Niemann
"Anthony Thomas" wrote:
> Because you have unresolved orphans in both your Publisher and
Distributer
> as to who owns the subscription. Also, the similar issue on your renmed
> PDB01 server, the msdb replication tables still contain references to
PDB01
> that need to be dropped and/or modified to PDB02.
>
> As far as the login is concerned, have you mapped the PDB02\AccountName
as
> an authorized grant to log into SQL Server? You can't rename owners of
> anything that aren't mapped users in the databases.
>
> Sincerely,
>
>
> Anthony Thomas
>
>
> --
>
> "Shaker" <Shaker@discussions.microsoft.com> wrote in message
> news:B9117BF2-A698-4D7A-8E6F-C3F7F5ED99DB@microsoft.com...
> No, I haven't uninstalled and reinstalled yet.
>
> The MSX stuff was resolved by changing the servername in the database
from
> PDB01 to PDB02 and modifying the jobs table in MSDB.
>
> When I fixed the MSX stuff I was able to disable publication on PDB02.
The
> replication monitor folder was removed and under Replication ->
Publication
> there was nothing listed. Unfortunately on DGP09 and TDB10 in their
> subscription folder each had a push subscription pointing to PDB01 which
no
> longer exsists. The only options I have when I right click on the
> subscription is the help option, all others (like two others) are greyed
> out.
> So I can't get rid of them because they don't show up on PDB02 and there
is
> no option to delete them on the subscription servers.
>
> I haven't enabled multi-server administration support, as far as I know
> anyways.
>
> Do you know why it is not recognizing the PDB02\accountNameHere? The
strange
> thing is when I try to reassign the owner of the jobs on PDB02 the
accounts
> PDB01\accountNameHere and PDB02\accountNameHere both show up. When I
select
> PDB02\accountNameHere, click Apply, then Ok and then re-open the
properties
> it still says PDB01\accountNameHere.
>
> "Anthony Thomas" wrote:
>
> > I hate to be a fatalist but if you did uninstall and reinstall, you'd
be
> > sure to get everything clean...well, almost. The registry keys have a
> nasty
> > way of remaining.
> >
> > Before you go to that extreme, try to remove all publications and
> > subscriptions on all servers and then reinitialize. If this fails,
then
> try
> > to remove replication support on all three servers and then reinstall.
If
> > this fails, then you're pretty much stuck with what you originally
feared.
> >
> > As far as MSX errors, you should be only getting those when you've
enabled
> > Multi-server administration support. You might want to revoke target
> > servers and then re-enlist them.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Shaker" <Shaker@discussions.microsoft.com> wrote in message
> > news:7B229BDD-FB5B-433D-91FA-A342BCC8D546@microsoft.com...
> > So I've got a problem with Replication and name changes to my host SQL
> 2000
> > server.
> >
> > Here is the setup before name change.
> >
> > Three Windows 2003 servers with Active Directory.
> >
> > PDB01 was the master and set up as a distributor and publisher.
> > DGP09 and TDB10 were setup with push subscriptions from PDB01.
> >
> > All have MSSQLSERVER and SQLSERVERAGENT running under the same local
> account
> > with proper privledges. All are running SQL 2000 SP3.
> >
> > The problem is I forgot to disable replication before changing PDB01
to
> > PDB02. When I tried modifying the replication after the name change it
> gave
> > me the MSX error message. I tried changing the name back to PDB01 but
due
> to
> > an Active Directory error I could not do so (and I don't have the
> authority
> > to fix). So after reading some stuff I used the sp_dropserver and
> > sp_addserver to get the proper servername in the master and msdb
tables. I
> > manually changed the name of the server in the msdb.jobstatus (I think
> that
> > is the name) and restarted the SQL service. I was then allowed to
modify
> the
> > replication on PDB02 (by modify I mean disable). I then looked at the
push
> > subscriptions on DGP09 and TDB10 and both were not updated when I
disabled
> > publishing and I can't do anything about them client side (when I
right
> > click
> > on the subscriptions the option available is the Help option) and they
no
> > longer show up on PDB02. I've tried re-enabling replication on PDB02
and
> set
> > it up so DGP09 and TDB10 have push subscriptions again. I follow the
> wizards
> > and no error messages are generated. I tried to test the replication
by
> > manually starting the agents but all three refuse to start and do not
give
> > an
> > error message explaining why. Upon closer inspection it was discovered
> that
> > they were failing to start because the account PDB01\accountNameHere
no
> > longer existed on the computer (it is now PDB02\accountNameHere). I
tried
> to
> > reset the ownership of the jobs to PDB02\accountNameHere but in the
> Security
> > -> Logins -> New User Login wizard it says the account
> PDB02\accountNameHere
> > doesn't exist!
> >
> > Is there anything I can do short of un-installing re-installing SQL
2000
> on
> > all three servers?
> >
> >
> >
>
>
>
- Next message: Anthony Thomas: "Re: float point error"
- Previous message: Aaron [SQL Server MVP]: "Re: float point error"
- In reply to: Shaker: "Re: Host Name Change and Replication"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
