Who Left Those Old Components in My New Database???

Recently a client requested some new transactional replication publications on a relatively new pre-production server.  The client has plenty of transactional and snapshot replication in their environment, including two other publications on this instance already, so this was just adding more to the existing pile.

Piece of cake, right?

https://s-media-cache-ak0.pinimg.com/564x/f2/de/6d/f2de6d3610642b866edcf76f7f86129a.jpg

I went through the new Publication Wizard (Shoot me - it is often the easiest way to handle a new "normal" publication) but this time I was greeted with a pair of errors:

Synonym 'dbo.MSreplication_subscriptions' refers to an invalid object.(Microsoft SQL Server, Error: 5313)

Object Reference not set to an instance of an object, (ConfigureDistWizard)

Um...OK...

My first instinct was that something was broken with the tools on the existing server - I RDP'ed to a different client server, and received the same errors when trying to create a publication for this server.

Hmm...

I have had experiences before where the REPL wizards just don't work (such as creating subscriptions to a higher version publication) so I said, "FINE, I'll just use the code!"

...but the sp_addpublication query threw me the same problem.

https://cdn.meme.am/cache/instances/folder61/250x250/71896061.jpg

At this point I figured that something must have been wrong with the replication components themselves - the server was new and not *really* production yet so maybe something had been fouled at setup.

***NOTE*** - I did *not* take the one step I should have at this point - since there were existing publications on a different user database, I should have paused and considered what was wrong with *this particular database* rather than with the instance at large - it would have saved us roughly 24 hours.

I advised the client I would script out the two existing publications and then completely disable and re-enable publishing and distribution on the instance and try again.

No good...

At this point the client suggested blowing away the VM and setting it up again - I could take backups of the existing user databases and restore them after they recreated the VM from scratch.  I told them  I could continue to investigate but they decided to go ahead with the rebuild.

Fast forward to the next day...

http://fandompodcast.com/wp-content/uploads/2016/01/tune-in-tomorrow.jpg

Even after the server rebuild, I found that the problem with the Publication Wizard still existed!  More than a little research found that it was due to the particular database still having some old replication components in it – older than the current version and therefore not cleaned up when I tried running sp_removedbreplication across the user databases to try to restart replication setup from scratch.  Since the problem was in one of the user databases it still existed after the rebuild since we just restored the old databases onto the new server!

I found the source of the problem (the old components) via the methodology described here http://willtonic.blogspot.com/2012/03/sql-2005-sp4-and-msreplicationsubscript.html and then mitigated it by renaming the two offending objects:

--

USE <dbname>

GO

EXEC sp_rename 'MSreplication_subscriptions', 'MSreplication_subscriptions_old'

GO

EXEC sp_rename 'MSsubscription_agents', 'MSsubscription_agents_old'

GO

--

This resolved the issue and allowed the New Publication Wizard to move forward. 

In the post-mortem, we found that the impacted database had originally been provided by a vendor and that it almost certainly had been migrated from an older version of SQL Server, hence the old components.

--

I want to draw attention to the fact that the blog post I referenced above doesn't have anything to do with errors in the replication New Publication wizard.  It discusses a "bug" in SQL Server 2005 the author encountered while trying to apply a 2005 service pack.  I found this post simply by spreading my search criteria from my specific error of "Synonym 'dbo.MSreplication_subscriptions' refers to an invalid object" down to simply "dbo.MSreplication_subscriptions error"

When troubleshooting (and searching for information online in general), never discount something simply because it doesn't match your specific situation.  You may find as in this case that there is some shared underlying problem that caused a certain set of symptoms for you but had manifest as a different yet similar set of symptoms for someone else.  If you are looking for a specific error or message and not finding anything, cast your net a little wider by reducing your criteria - always start with your specific message, but if you don't get anything useful try again, and instead of searching for "Really Long Error Message about this Object and Some Other Stuff" look instead for "<object> <other stuff> error/warning/whatever"

As always, proceed with caution, but this is true even when you *do* find a blog post/newsgroup post/Connect item/etc. that *does* exactly match your situation - just because renaming an index fixed Error 123456 for someone else doesn't necessarily mean it will for you - always consider the potential impact of any course of action *before* deploying it anywhere, even to Dev/Test.

https://imgflip.com/i/1mvtnu

Hope this helps!