FIX: SQL Server Replication - Cannot alter column because it is 'REPLICATED'

In SQL Server 2005, I really appreciated the addition of DDL replication.  Before that, I found that making schema changes to replicated tables was a pain in the neck. Almost every time, I was there dropping replication and later setting it up again. That’s easy but takes a long time, so sometimes, I’d resort to lots of manual hacking.

Using DDL replication, I could make a whole lot of different types of changes and the subscribers would be updated automatically, at least in most standard subscriptions.

The issue

That was a good story for me up to a few weeks ago. I was working with a client, and they needed to modify the same column in six different databases. Each separate database was replicating (via transactional replication) to Azure SQL Databases.

The client made the changes and they all worked, apart from one server. It failed with the error:

Cannot alter column ‘columnname’ because it is ‘REPLICATED’

I can’t say I love that error message. Why is REPLICATED in quotes? (Pretend replication??)

So I was wondering what on earth was different about the servers, or how they were configured. I did a lot of searching and I found many unhelpful posts and comments. I was surprised how many said that you can’t modify a column in transactional replication without dropping it and setting it up again. That’s not true, and was obvious from all the other servers where it worked. I checked all their subscribers, and sure enough, the change was applied just like we expected.

The fix

I eventually found a useful comment from Steve Fenton.

He mentioned that he’d seen that error coming up if you happened to try to change a column while the Snapshot Agent is running.

I knew the Snapshot Agent shouldn’t have been running but I checked with the client. Turns out the Agent had failed when it ran on the previous Sunday. It had a deadlock error, and stopped. So some part of SQL Server thought the Snapshot Agent was still running.

Sure enough, after the client did another execution of the Snapshot Agent, the column could be altered as expected.

I hope that error message gets fixed, as it’s really, really misleading.

2020-04-30