Schema changes in SQL Server 2005 Transactional Replication

Schema changes will be replicated by default. I am adding a column named street to the table Address using the below command,

ALTER TABLE dbo.address ADD street VARCHAR(50)

We can see that the above command will be replicated to the subscriber and hence the column street is now available in subscriber as well. The publisher database Infitest and the subscriber is testdb.

Please refer the screenshots of the table structure in publisher.

Please refer the screenshots of the table structure in subscriber.

But once we set the option ‘Replicate schema changes’ as false it will not be replicated. We can alter the option as shown in the below screenshot. Right click your publication – choose properties click subscription options the below screen will be visible. Change that option as False.

After setting that option no schema changes like DDL commands should be replicated. However ALTER TABLE DROP COLUMN is an exception. If you run that command it will still be replicated regardless of the option set as false.

Another exception is that we can still change the value of the data type in the publisher and it will still be replicated, regardless of the option set to false. I have tested this as well by modifying the value of the varchar from 50 to 100 in the publisher and it has been replicated to subscriber even if the option Replicate schema changes is set as false.

Please refer the screenshot of the same, where I run the following command in publisher,

USE Infitest
GO
ALTER TABLE dbo.address ALTER COLUMN street VARCHAR(100)

The below screenshot taken in subscriber shows that the change is reflected to subscriber as well,

The operations like Index creation (Create INDEX) will not be replicated also Alter INDEX commands will also not be replicated. So we have to perform all the maintenance tasks in the respective databases individually.

I have observed the following differences between log shipping and replication