Which SQL table holds the ARS management history partner SQL database name for ARS 7.3

We have an orphaned entry in ARS SQL config for a management history database subscriber that was deleted.

it was deleted using the ARS console - right-click 'delete'

it disappears - only to re-appear in the console on re-connect.  I found reference on how t resolve for a 7.4* database in my forum search - below, but tat doesn't apply to 7.3.1

What is that doggone table in ARS configuration DB - or MH DB on the publisher SQL host …hosting the record that identifies the remembered SQL replication partner ?

This link shows the table I believe for the newer DB. <Crizzapp> can't copy/paste - but it references 7.4.3 but no love in my forum search for 7.3.1

Parents
  • for anyone that one day wanders into this thread - We opened an SR with Quest and Microsoft to figure out why - with  MS$ SQL Management Studio showing no existing replication partners, WHY would  ActiveRoles server console still show the replication partner was present.  Deleting it at ARS console appeared to have worked, until  we  perform a 're-connect'  and it came back.  After weeks of troubleshooting and major finger pointing ...  and a couple wags ...  
    I ran a very low level query in PS to see if there was anything it could see in SQL config that we were missing in the GUI's

    $SQLARS = 'SQLARS01','SQLARS02','SQLARS03','SQLARS04'
    
    FOREACH($SQLSERVER IN $SQLARS)
      {invoke-command -computername $SQLSERVER -ScriptBlock {[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Rmo') | Out-Null
       $script:SQLInstance = $env:computername
       $subscriberServer = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $SQLInstance
       $subscriberServer
      }
    }

    of course - change $SQLARS values to reflect your own SQL boxes, and call the script from PS (I used 5.1) with an account that has permission on the SQL hosts to read this data.  This can be run remotely - from your own workstation without logging in to or remoting to the SQL box itself.

    You end up seeing results like below - for each of your SQL boxes. 

    PSComputerName         : SQLARS01

    RunspaceId             : 375b6a48-8af7-491e-b001-e5e5de209aca

    Name                   : SQLARS01

    DistributorInstalled   : True

    DistributorAvailable   : True

    IsDistributor          : True

    IsPublisher            : True

    HasRemotePublisher     : False

    DistributionServer     : SQLARS01

    DistributionDatabase   : AelitaDistributionDB

    WorkingDirectory       : \\SQLARS01\S$\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData

    AgentCheckupInterval   : 10

    DistributionPublishers : {SQLARS01}

    ReplicationDatabases   : {ActiveRoles73, ActiveRoles73_MH, ActiveRolessync73...}

    RegisteredSubscribers  : {SQLARS03, SQLARS02, SQLARS04}

    SqlServerName          : SQLARS01

    ConnectionContext      : Data Source=SQLARS01;Integrated Security=True;MultipleActiveResultSets=False

    IsExistingObject       : True

    CachePropertyChanges   : False

    UserData               :

    And it revealed that the ghost instance was presenting in ARS because the original publisher SQL instance  still had the distributor role and a subscriber/partner listed. This remnant was left over from our attempt to upgrade from ARS 7.3.1 to 7.4.3 in Jan 2021.   At that time, we broke replication by removing partners and demoting the publisher.  only ... despite the ARS console indicating success - apparently SQL didn't complete the job of removing the distributorship.  Above, having the distributionDatabase and Registered Subscribers is valid - as this is the publisher.  If you find yourself with a replication ghost of your own - save yourself some time.  Check these two values.    

    the things we learn.

  • Thanks for sharing your findings.  'Bit of an edge case, but nevertheless, this might make for a good Support KB article.

  • to boldly go where no one has gone before.

    thanks JohnnyQuest

Reply Children
No Data