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.