Categories
High Availability

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.

Repl_01

Please refer the screenshots of the table structure in subscriber.

Repl_02

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.

Repl_03

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)

Repl_04

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

Repl_05

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

 

Log Shipping
Replication
The recovery model of the primary and secondary databases should be Full or Bulk logged
The recovery model of the publisher and subscriber can be anything either, full or bulk logged or simple
The usage of backup log with truncate only command will break the log chain and hence we shouldnt use it for a log shipped database
In Replication we can still use the backup log with truncate only command without any issues. The objects will still be replicated

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*