SQL Server复制中tinyint改int后订阅端未同步变更的解决指引

Hey there, let's work through this issue step by step—changing a column's data type from tinyint to int on the publisher but not seeing it sync to the subscriber is a common replication gotcha. I’ll walk you through the most likely fixes, assuming you’re using SQL Server replication (the most common setup for this scenario):

By default, some replication configurations don’t automatically replicate schema changes. Let’s check if your publication permits this:

• Run this query on the publisher database to verify:

  sp_helppublication @publication = 'YourPublicationName'
  

  Look for the allow_schema_changes value—if it’s 0, schema changes won’t replicate.
• If it’s disabled, enable it with:

  sp_changepublication 
    @publication = 'YourPublicationName',
    @property = 'allow_schema_changes',
    @value = 'true'
  

• After enabling, re-run your ALTER TABLE statement to ensure it’s marked for replication.

More often than not, the schema change failed to sync because of an unreported error in the replication agent. Here’s how to check:

• Open SQL Server Management Studio (SSMS), go to Replication > Local Publications > [Your Publication] > Subscriptions.
• Right-click your subscription and select View Synchronization Status.
• Look through the agent history for errors like:
  • Permission issues (the agent account doesn’t have ALTER permissions on the subscriber table)
  • Data type conversion conflicts (though tinyint to int is a safe widening conversion, there might be edge cases)
  • Schema change not being captured (if the agent wasn’t running when you made the change)

The fix varies a bit depending on what replication you’re using:

• Transaction Replication: If allow_schema_changes is enabled, the ALTER TABLE should sync automatically once the distribution agent runs. If it didn’t, restart the distribution agent and check logs again.
• Snapshot Replication: Schema changes require a new snapshot to be generated. Right-click your publication, select Generate Snapshot, then reinitialize the subscription (note: this will overwrite the subscriber table with the publisher’s current schema).
• Merge Replication: Merge replication handles schema changes differently—ensure the merge agent is running, and check if the schema change was added to the replication metadata. You might need to reinitialize the subscription if the change wasn’t captured.

If automatic sync still isn’t working, you can manually apply the ALTER TABLE statement to the subscriber, or use replication’s built-in tool to push the change:

• Create a SQL script with your ALTER TABLE command (e.g., ALTER TABLE YourTable ALTER COLUMN YourColumn int;) and save it to a file (like C:\AlterColumn.sql).
• Run this on the publisher to push the script to all subscribers:

  sp_addscriptexec 
    @publication = 'YourPublicationName',
    @scriptfile = 'C:\AlterColumn.sql'
  

• This ensures the change is applied consistently across all subscribers tied to the publication.

Once you’ve tried the above steps, verify the change took effect on the subscriber:

• Run this query on the subscriber database:

  SELECT DATA_TYPE 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'YourTableName' 
    AND COLUMN_NAME = 'YourColumnName'
  

• Confirm the result is int, and check that the replication agent is running without errors.

A quick side note: Always test schema changes in a non-production environment first—even safe widening conversions can cause unexpected issues if applications depend on the original data type.

内容的提问来源于stack exchange，提问作者user8829086