April 2, 2017 at 5:21 pm
We are running SQL Server 2008 R2 Standard Edition (SP3)
Today we successfully migrated all of our Databases (in Dev) from one Server to another.
The Method we used was:
- script out users on the Source server,
- install sql server 2008 r2 standard SP3 on another Server
- stop the SQL Services on the source server and copy all User Database data and log files to the destination server and attach them,
- power down the source Server
- renamed the destination server to be the same name as the source server, ran sp_dropserver and sp_addserver to rename the internal SQL Instance name and restarted the Services on the destination server.
There were a few orphaned users - but other than that, everything was fine - except for replication.
We had Transactional (push) Replication from one Database (five tables only) and it is not working.
Somehow, our DIstribution Database ended up as a 'user' database instead of a System Database in our new SQL Server.
When I execute sp_helpdistributor it returns all NULLS.
Also, when I execute :
exec sp_replicationdboption @dbname = N'MYDB, @optname = N'publish', @value = N'true'
I get this error:
Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 56
The Distributor has not been installed correctly. Could not enable database for publishing.
The replication option 'publish' of database 'MYDB' has been set to false.
In hindsight, I'm sure there were steps we should have followed to disable replication on the source server before we moved to the new SQL Server.
With that said, Can someone assist in getting Replication re-enabled on this new SQL Server Instance?
Is this a simple restore of the Distribution Database taken before the cutover? Or are there other steps that need to be taken to get our Distribution Database back.
April 2, 2017 at 11:07 pm
This was removed by the editor as SPAM
April 6, 2017 at 9:40 am
Still encountering problems.
We managed to get our Distribution Database recreated using the Configure Distribution Wizard in SQL Server Management Studio which created the Distribution Database. We then Recreated the Publication and subscription - when we start the log reader we are getting the following errors:
Error messages:
The process could not execute 'sp_repldone/sp_replcounters' on 'SQLServer'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {00036669:0000016e:0023}. (Source: MSSQLServer, Error number: 18768)
Get help: http://help/18768
The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
Get help: http://help/MSSQL_REPL22017
The process could not execute 'sp_repldone/sp_replcounters' on 'SQLServer'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
April 6, 2017 at 10:37 am
Jpotucek - Thursday, April 6, 2017 9:40 AMStill encountering problems.We managed to get our Distribution Database recreated using the Configure Distribution Wizard in SQL Server Management Studio which created the Distribution Database. We then Recreated the Publication and subscription - when we start the log reader we are getting the following errors:
Error messages:
The process could not execute 'sp_repldone/sp_replcounters' on 'SQLServer'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {00036669:0000016e:0023}. (Source: MSSQLServer, Error number: 18768)
Get help: http://help/18768
The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
Get help: http://help/MSSQL_REPL22017
The process could not execute 'sp_repldone/sp_replcounters' on 'SQLServer'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
I'd check to see if you have more than one log reader. You could have already has a log reader job and when you recreated the Distributor, it added a second one.
Sue
April 6, 2017 at 10:46 am
I have the following:
REPL - History Cleanup
REPL - Distribution Cleanup
REPL - Subscription Cleanup
REPL - Log reader <---------- only one
REPL _ Snapshot
REPL _ Distribution
REPL - Alert Response (Subscription)
REPL - Checkup
REPL - Alert Response (Distribution)
April 6, 2017 at 10:54 am
Sue
April 6, 2017 at 11:09 am
currently I have the log reader stopped because of the errors it is generating. so I should start the log reader and then re-initialize the subscription?
April 6, 2017 at 11:18 am
Jpotucek - Thursday, April 6, 2017 11:09 AMcurrently I have the log reader stopped because of the errors it is generating. so I should start the log reader and then re-initialize the subscription?
You could leave it off for now. Make sure the distribution agent is running. And do a new snapshot for the reinitialization. It just looks like it got out of sync with all of those things that happened and recreating the distribution database. Probably the worse case scenario would be to drop the sub, then the publication and recreate them. I would guess you may end up needing to do that but I'd try a reinitialization first and see if that clears things up.
Sue
April 6, 2017 at 1:42 pm
That did not work. Not sure how to go about disabling and then recreating the Publication and Subscription. simply delete and recreate?
April 6, 2017 at 3:08 pm
Jpotucek - Thursday, April 6, 2017 1:42 PMThat did not work. Not sure how to go about disabling and then recreating the Publication and Subscription. simply delete and recreate?
Yup...delete the subscription then delete the publication then recreate the publication, recreate the subscription. Not sure if you'll get errors from the publisher having been recreate but from what I remember it would probably say something like couldn't find xxx at publisher and allow you to proceed.
You may want to clean it all out though if you don't have any other pieces of replication involved - I probably would if that's the only piece of replication you have. It will just clean out any other pieces and remove the distributor database. So it's just adding a step to right click on the replication folder and remove distribution, publication. Then just recreate everything again. Start with the distributor - right click on the replication folder, configure distributor, the wizard starts up just like it did before, recreates the distribution database. Then you create the publication and then the subscription. As I said, may not be a bad idea with things getting out of whack with everything if you can afford to do it.
Sue
April 7, 2017 at 10:39 am
sorry again.....
I am at the point where I am adding the Subscription - I am using what was generated when I scripted out the Publication before I dropped it..
exec sp_addsubscription @publication = N'CMQTSStage', @subscriber = N'VMWSQL', @destination_db = N'CM', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'CMQTSStage', @subscriber = N'VMWSQL', @subscriber_db = N'CM', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'sa', @subscriber_password = null, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
Am I supposed to plug in the subscriber 'sa' password ? it reads null in the generated script. Also do I need to plug in a password for these:
@job_login = null, @job_password = null ?????
April 7, 2017 at 10:48 am
Yes, you are supposed to plug those in. This is why I always use the wizard instead, so that I know exactly what it needs right as it needs it. It's actually a lot less risky that way. Using a script leaves room for typos and various other human failings.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 7, 2017 at 11:38 am
Thank you All!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply