Re: Host Name Change and Replication

From: Shaker (Shaker_at_discussions.microsoft.com)
Date: 01/24/05 

Date: Sun, 23 Jan 2005 18:25:01 -0800

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?
> >
> >
> >
>
>
>

Relevant Pages

  • Re: Host Name Change and Replication
    ... the msdb.sysjobs did still contain PDB01 as the server originator and this caused the 14274 error about the MSX. ... 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. ... Unfortunately on DGP09 and TDB10 in their> subscription folder each had a push subscription pointing to PDB01 which no> longer exsists. ...
    (microsoft.public.sqlserver.server)
  • Re: Is this merge replica model possible?
    ... Download Only) to only the transactions which occurred on the publisher flow ... Looking for a SQL Server replication book? ... > A user subscribes and downloads subscription X. ... > Is this model a total variant from the normal Merge Replica model? ...
    (microsoft.public.sqlserver.replication)
  • Re: sync over the Internet
    ... Looking for a SQL Server replication book? ... how do I connect to Publisher? ... Once this is done they are ready to set up their subscription. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL Compact Merge replication no longer works after VS SP1 is
    ... All i had done is installed the Server Tools 3.5 sp1 and re ran the ... subscription, ... case after installing VS 2008 SP1 the merge replication gives the following ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL Compact Merge replication no longer works after VS SP1 is
    ... subscription, ... The merge process could not connect to the message file from Subscriber ... Check to ensure that the server is running. ... Initializing SQL Server Reconciler has failed. ...
    (microsoft.public.sqlserver.replication)
Loading