Hello,
I have two SQL Servers, the production and the new server
I want to transfer the production database. I tried to be clever and thought it would be possible to restore the production from the backup to the new server, then create a pull subscription and wait for the actual migration date.
The production is already configured as a publisher with Allow initialization from backup files = true
I didn't choose initialize when creating the subscription, I don't want to use snapshot but the recent backup
Now my errors are:
Error messages:
The process could not execute 'sp_repldone/sp_replcounters' on '<the new server instance>'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. (Source: MSSQLServer, Error number: 18757)
Get help: http://help/18757
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 '<the new server instance>'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
Any help? Can this be done using pull subscription or should it be push?
March 5, 2020 at 12:00 am
When you created the subscription, you said you "didn't choose initialize when creating the subscription". If you mean you cleared the check in the checkbox for initializing then that is the same as setting the sync_type to Replication Support Only and there are some differences with that sync option. As the documentation for sp_addsubscription explains, it is assumed that the subscriber already has the schema and data from the publication. Replication Support Only is used more for rebuilding the subscribers. If you read this blog post on that option, one of the key points listed is that the publisher and subscriber must have identical data and no new data changes are occurring on publisher until replication is re-enabled. Push or pull would be the same.
Reset topology using “Replication Support Only”
Sue
March 5, 2020 at 7:05 am
thanks,
did this again, now with initialize
Replication Monitor -> My Publishers -> newdb -> pub_proddb -> Agents:
Error Log Reader Agent
Error messages:
The process could not execute 'sp_repldone/sp_replcounters' on 'MUSTERPRODYKL\YKLSQL'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. (Source: MSSQLServer, Error number: 18757)
Get help: http://help/18757
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 'MUSTERPRODYKL\YKLSQL'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
March 5, 2020 at 7:12 am
Sorry... this error was because I had configured this newdb as a distributor and a publisher earlier for another tests. Removed those and it seems my subscription is running.
March 5, 2020 at 8:25 am
I did some further study on this. Data is not synched.
newdb Local Subscriptions - view synchronization status: "Job xxx started succesfully"
proddb Local Publications - Replication Monitor - Subscription Watch List: no errors, PErformance excellent, last syncronization is current time
If I double click my Subscription Publisher to Distributor History, I can see some transactions delivered there
Distributor to Subscriber History says Distribution Agent is running, Action message "The initial snapshot for publication proddb is not yet available with current timestamp
Undistributed Command: none
If I query proddb table (which is in published articles)
SELECT [Id]
[Timestamp]
FROM [baja].[dbo].[Log]
where timestamp>'2020-03-05 08:00:00.0000000'
order by timestamp desc
returns 106 rows
but the same query in newdb does not return anything
How to sync newdb with the proddb? Restart log reader agent? Don't want to use snapshot because I've already restored full backup to the newdb from the proddb...
Take a look at this documentation.
Because you're initializing from a backup, there's a few things you have to do (listed in the article). If you search for "sql server initialize replication from backup", there are several other articles that come up. I didn't read through any of the others, but I imagine they would be helpful as well.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 5, 2020 at 5:51 pm
This was removed by the editor as SPAM
March 6, 2020 at 5:28 am
Thank you,
it looks like you cannot initialize from a backup using SSMS GUI so this looks promising also:
March 6, 2020 at 10:21 am
Looks like this is working although there's problem with one table, I try to investigate this further.
Error messages:
Explicit value must be specified for identity column in table 'HistoryRows' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
the table HistoryRows has ID column with Not For Replicaton = True
as it should be in replication. Strange.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply