Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

recreating replication without reinitializing missing sp_MSsetupnosyncsubwithlsnatdist_helper Expand / Collapse
Author
Message
Posted Wednesday, August 6, 2014 2:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:45 PM
Points: 2,449, Visits: 419
Hi,

We have a database replicated with some customization that makes re-initializing a prospect we would rather avoid. We have also reached a point where we need to move our distributor to a different server.

The GUI to set up replication has an option to not initialize, so we decided to try that option.

We are working in a test environment that had an up to date replication scenario matching production. We took the application offline and allowed replication to catch up completely before ensuring no connections remained to the publication database. dropped the subscribers and publishers and then rebuilt the publishers to the new distribution database. We then tried to create the subscription. Everything looked good until we finished the wizard at which point it spun for a short while before returning an error indicating that it couldn't find the procedure 'Dev_distribution.dbo.sp_MSsetupnosyncsubwithlsnatdist_helper'

After a couple hours of trying to figure out why the system couldn't access the stored procedure I thought I saw in the database (there is one without the _helper) I realized the procedure doesn't exist anywhere on the distributor. I went through all of the servers that were part of replication and managed to find a copy of the system proc in the publisher's master database but not on any of the other servers.

I'm guessing I could manually create the proc in the location the wizard is expecting it, but I'm thinking that I'm probably missing something more fundamental that would be easier then copying MS supplied procs around.

Anyone have any ideas what could be misconfigured, or is there a more basic bug that I haven't found any documentation on in my searching?

Thanks,
Kevin
Post #1600394
Posted Thursday, August 7, 2014 2:32 PM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:45 PM
Points: 2,449, Visits: 419
Hi,

I found the solution. It turns out that our publisher was running the most recent SP2 update for SQL 2008 R2 but the distributor and subscriber were only running 2008 R2 SP2. when I installed the cumulative update package the procedures were created in the master database and I was able to create the subscriptions.

Thanks, and hopefully this helps if someone else runs into the same issue.
Post #1600890
Posted Tuesday, September 16, 2014 1:27 PM This worked for the OP Answer marked as solution
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 724, Visits: 298
I faced same issue when Publisher was on SQL Server 2012 SP1 and distributor was on SQL server 2008 SP3 and is resolved by updating distributor to SQL Server 2012 SP1.
Post #1614234
Posted Tuesday, September 16, 2014 1:34 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:45 PM
Points: 2,449, Visits: 419
From what I saw it looks like Microsoft updated all supported versions of SQL server at the same time, so the real secret was just getting to either a service pack that was released after the change was made or to a cumulative update that was created after the change was made. I believe I saw a couple occurrences where you could have stayed at a lower service pack and picked up the most recent cumulative update if necessary (as long as the service pack was still supported) or probably simply installed the correct hot fix.
Post #1614238
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse