August 6, 2014 at 2:35 pm
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
August 7, 2014 at 2:32 pm
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.
September 16, 2014 at 1:27 pm
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.
September 16, 2014 at 1:34 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply