SQL Server Transactional Replication – Unable to Enable Publishing on Restored Database

sql serversql-server-2008-r2sql-server-2016transactional-replication

We have a production database (2008 R2) that is enabled for replication as a publisher and subscriber with a remote distributor (distributorA).

We restored a copy of this database onto a new 2016 server. Now we want to configure replication on the restored copy as a publisher to our new distributor (distributorB).

We configured distributorB with a new distribution database and configured the new 2016 server as a publisher to distributorB.

Back on the new 2016 server, we ran sp_adddistributor to point it to distributorB. Everything works as expected so far.

Now we run the following command on the restored db to enable replication sp_replicationdboption @dbname = 'restoreddb', @optname = 'publish', @value = 'true'. That completed successfully.

This is where the problem starts to manifest itself. When I tried to create a new publication now, at the end of the publishing wizard, it errored out saying it can't find dbo.syspublications.

I check the system tables on the restoredb and find that some of the tables required for replication have not been created (syspublications, sysarticles, syssubscriptions and etc), but some have (sysreplservers and MSPeer*). I can, however, see that this database has 'transactional' checked in publication databases under publisher properties. When I try to uncheck it and hit OK, it gives me the following error 

Invalid object name 'dbo.syssubscriptions'.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

I tried using sp_removedbreplication to cleanup any remnants of replication settings from the database to start afresh, but that didn't seem to help at all.

I think the problem here is somehow the remnant replication settings from the database's 'former self' is interfering with setting up replication again with a different distributor.

So my question is why some of the replication system tables were not created? How do we remedy this?

Best Answer

After some further investigation through profiling, we found the issue.

We traced the whole process of enabling publishing on the database by running 

sp_replicationdboption @dbname = 'restoreddb', @optname = 'publish', @value = 'true'

and zeroed in on the part that creates the sys tables:

EXEC @retcode = sys.sp_MScreate_pub_tables

This is where it gets interesting:

EventClass  TextData 
44  IF not exists (select * from sys.objects where name = 'sysarticles')
45  IF not exists (select * from sys.objects where name = 'sysarticles')
44  IF not exists (select * from sys.objects where name = 'sysarticlecolumns')
45  IF not exists (select * from sys.objects where name = 'sysarticlecolumns')
44  IF not exists (select * from sys.objects where name = 'sysschemaarticles')
45  IF not exists (select * from sys.objects where name = 'sysschemaarticles')
44  IF not exists (select * from sys.objects where name = 'sysextendedarticlesview')
45  IF not exists (select * from sys.objects where name = 'sysextendedarticlesview')
44  IF NOT EXISTS (select * from sys.objects where name = 'syspublications')
45  IF NOT EXISTS (select * from sys.objects where name = 'syspublications')
44  IF not exists (select * from sys.objects where name = 'syssubscriptions')
45  IF not exists (select * from sys.objects where name = 'syssubscriptions')
44  IF not exists (select * from sys.objects where name = 'sysarticleupdates')
45  IF not exists (select * from sys.objects where name = 'sysarticleupdates')
44  IF not exists (select * from sys.objects where name = 'MSpub_identity_range')
45  IF not exists (select * from sys.objects where name = 'MSpub_identity_range')
44  IF not exists (select * from sys.objects where name = 'systranschemas' and schema_id = 1)
45  IF not exists (select * from sys.objects where name = 'systranschemas' and schema_id = 1)
44  CREATE TABLE dbo.systranschemas
            (
                tabid int not null,
                startlsn binary(10) not null,
                endlsn binary(10) not null,             typeid int not null default 52
            )     

45  CREATE TABLE dbo.systranschemas
            (
                tabid int not null,
                startlsn binary(10) not null,
                endlsn binary(10) not null,             typeid int not null default 52
            )

With eventclass 44 = SPStmtStarting and 45 = SPStmtCompleted.

So what's happening here? It looks like the 'IF NOT EXISTS' statements were returning false for all the sys tables EXCEPT systranschemas, but we checked that these tables don't exists at all under System Tables.

So we picked a select from there and ran it

select * from sys.objects where name = 'sysarticles'

We found that there is a SYNONYM with the same object name but under a different schema in the database. This is a remnant of a migration project we did a few months ago where we moved all the tables from another database to this one under a different schema. The synonyms were created as the first step - instead of just creating them for user tables, we created them for the system tables as well.

Regardless, I am a little appalled at how lazy the coding is in this system procedure - it does not check for the 'is_ms_shipped' flag in the sys.objects, nor does it check for the schema - strangly except for 'systranschemas' where it does check for the schema and thus was able to create the table without any issues.

Related Question