Replication Support For Dropping Tables

Drew Furgiuele recovers from a case of the vapors after discovering a replication codebase update:

To enable this change though, there’s more to it than just “you need to be running this service pack level on your publisher.” It’s far, far more than that:

  • Your publisher needs to be running SQL Server 2016 SP1 or 2014 SP2. We’ve already seen that is a requirement based on the content of the KB article. Big whoop.
  • In addition, if you have the same article in more than one publication, you have to enable this feature on all of them or you’ll still get the error that a table is in replication and can’t be dropped.
  • Your subscribers don’t care about the SQL Server version (except when you have a data type that exists in the publisher that doesn’t at the subscriber. Then it cares. But for this change, it doesn’t).
  • Your distributor also needs to be running a version that supports this functionality.

I can’t make that last point any bolder; that’s not documented anywhere that I could find. I’ll explain why later, but for now, roll with it. In fact, let’s see what happens if we don’t upgrade our distributor as well. Time to go to the lab!

Read the whole thing, particularly the warning about how dropping a table on the publisher will not drop tables on the subscriber.

Related Posts

Merge Replication on Linux

Jignesh Raiyani shows how you can set up merge replication on SQL Server on Linux: In this article, we will discuss to deploy SQL Server Merge Replication on Linux environment. Many SQL Server features are not available on Linux by Microsoft but the Replication feature exists for Linux Environment Edition. Before starting anything, let’s address […]

Read More

Tracking Transactional Replication Status

Pamela Mooney has a script to validate that transactional replication is up to date: You may sometimes have reports or other processes that are dependent on transactional replication being current.  If that is the case, you will probably need a mechanism to check and see if, in fact, replication is caught up.  Here is my solution to […]

Read More

Categories

January 2017
MTWTFSS
« Dec Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031