Sql-server – SQL Server Replication: “ALTER TABLE ALTER COLUMN” is not propagated to subscribers

ddlmerge-replicationreplicationsql server

We are running SQL Server 2008 R2 SP1 as publisher & distributor, and SQL Server 2005 SP3 as subscriber. The replication of schema changes is activated, and the replication has been running for years, including frequent schema changes (new column, new constraints, etc).

The following instruction was sent on the publisher:

use myDatabase
alter table Tbl_TypeLignePaye
      alter column natureTypeLignePaye nvarchar(12)
go

where field natureTypeLignePaye was originally nvarchar(3) null

The query ran without errors on the main database. The result is the following:

The field natureTypeLignePaye still appears as nvarchar(3) in the object browser
The column properties screen indicates a nvarchar type with a length of 12
The change was not propagated to the subscribers

Any idea on what is going on with this database?

Publisher: object browser window vs property window give incoherent data

field type and length

Best Answer

If anyone wants to increase the column width of the replicated table in SQL Server 2008, then no need to change the property of replicate_ddl=1. Simply follow the steps as given.

Open SSMS
Connect to Publisher database

run command

ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] varchar(22)

It will increase the column width from varchar(x) to varchar(22) and same change you can see on subscriber (transaction got replicated). So no need to re-initialize the replication

Hope this will help all who are looking for it.

Related Solutions

Sql-server – SQL Server 2014 Transactional Replication Partition Switching

We ended up coming up with a solution for this. Basically, we had to manage the partition creation for both the publisher and subscriber databases/tables when we made a change. We were loading data using SSIS and there were mechanics that were built in to create new partitions on the publisher, so we implemented the same logic to do the work on the subscriber too.

Of course, this can be difficult if you get into having multiple subscribers. I suppose one solution would be to query the subscription details and dynamically perform the same ALTER PARTITION statement to any subscriber database.

SQL Server Replication – Convert Existing Subscriber Database to Publisher

Best option for you will be to reestablish replication with reverse role but avoid a full snapshot and re-sync to minimize downtime. I will outline high level steps here and if you need further details let me know.

Once downtime start (confirm app servers are not making any new connection/writing to publishing articles):

Continue running the distribution agent jobs till undelivered command count is zero.
Stop the distribution agent job/jobs.
Remove replication (publisher and subscriber only) but keep the distributor as is.

Add remote distributor to new publisher if it is a separate instance (old subscriber).

USE [Master]
GO
exec sp_adddistributor @distributor = N'instance name where distribution database reside', @password = 
N'xxxxxxxxx'
GO

Reinstall replication with 'replication support only' when running sp_addsubscription. See details here.
Run snapshot jobs. It will not resync your table schema+data, rather create system objects if any missing.

replication support only Provides automatic generation at the Subscriber of article custom stored procedures and triggers that support updating subscriptions, if appropriate. Assumes that the Subscriber already has the schema and initial data for published tables. When configuring a peer-to-peer transactional replication topology, ensure that the data at all nodes in the topology is identical. For more information, see Peer-to-Peer Transactional Replication.

Not supported for subscriptions to non-SQL Server publications.

You can use this query to find undelivered commands in distribution database.

SELECT ss.srvname AS publisher, 
       [name]     AS Agent, 
       publication, 
       a.publisher_db, 
       subscriber_db, 
       art.article, 
       art.destination_object, 
       art.source_object, 
       undelivcmdsindistdb, 
       delivcmdsindistdb, 
       s.article_id, 
       agent_id, 
       a.publisher_id, 
       subscriber_id 
FROM   distribution.dbo.msdistribution_status AS s WITH (nolock) 
       FULL OUTER JOIN distribution.dbo.msdistribution_agents AS a WITH (nolock) 
                    ON s.agent_id = a.id 
       JOIN master.dbo.sysservers AS ss WITH (nolock) 
         ON a.publisher_id = ss.srvid 
       JOIN distribution.dbo.msarticles AS art WITH (nolock) 
         ON s.article_id = art.article_id 
WHERE  subscriber_db <> 'virtual' 
       AND subscriber_db = 'SubscriberDBName' 
ORDER  BY undelivcmdsindistdb DESC 
COMPUTE sum(undelivcmdsindistdb)