Does Transactional Replication Include Structural Changes Made to a Database

We typically use Transactional Replication for Reporting purposes. In Transactional Replication, the idea is to replicate the changes done on the Publisher Database (OLTP) onto the Subscriber Database (Reporting).  The main challenge in Replication is whether it will replicate the structural changes made on the Publisher database successfully onto the Subscriber database.
For Demonstration purposes, I created a database named Student which acts as a Publisher and the Subscriber database is named Student_Reporting. Both these databases reside on the same SQL Server 2014 instance.

The publisher and the subscriber Database named Student and Student_Reporting respectively have a table named student whose structure is as follows:

create table student
(
student_id int primary key,
student_first_name varchar(30),
student_last_name varchar(30)
)
It contains the following Data:-
insert student values(1,'Satnam','Singh')
insert student values(2,'Bhupendra','Valsangkar')
insert student values(3,'Avinash','Lewis')
insert student values(4,'Swati','Singhal')
insert student values(5,'Kinjal','Patel')

Now let’s consider a case where we try to change the data type of the column named student_first_name on the Publisher database student from varchar(30) to varchar(40) using the below T-SQL.

alter table student
alter column student_first_name varchar(40)

After the above T-SQL gets executed on the publisher database named Student we examine the Student_Reporting table on the Subscriber database and we can see the Data Type has been changed successfully on the Subscriber Database:

Now let’s consider a case where we try to create a Non-Clustered Index on the column named student_first_name present in the Student database (ie on the Publisher Database) using the below T-SQL.

USE [Student]
GO
/****** Object:  Index [Student_First_Name]    Script Date: 01-02-2015 11:19:38 ******/
CREATE NONCLUSTERED INDEX [Student_First_Name] ON [dbo].[student]
(
       [student_first_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

After we execute the above T-SQL when we view the Student table on the Subscriber database (i.e. Student_Reporting) we can see that the Index hasn’t got replicated, please refer the screen capture below:-

From the above screen capture, we conclude that if we create a Non Clustered index on a Database involved in Replication, then we need to create this manually on the Subscriber database.

The next case would be to Replicate a new table created on the publisher database named Student. One important point to be considered here is that Replication was originally created using the Snapshot Agent. We will create a new table named College against a publisher database named Student :

create table college
(
college_id int primary key,
college_name varchar(100),
college_address varchar(200),
college_city varchar(30),
college_state varchar(30)
)

In order to ensure that if a new Article is added without generating the Snapshot of all the articles, execute the below T-SQL against the Student publisher database:

exec sp_changepublication
@publication = 'Student',
@property = 'allow_anonymous', @value = 'False'
exec sp_changepublication
@publication = 'Student',
@property = 'immediate_sync', @value = 'False'

EXEC sp_addarticle
@publication = Student,
@article = College,
@source_object = College,
@force_invalidate_snapshot=1

Now let’s add the new article (table) named college using the below T-SQL. Please execute it against the publisher database named Student.

EXEC sp_addarticle
      @publication = Student,
      @article = College,
      @source_object = College,
      @force_invalidate_snapshot=1

Now let’s create a subscriber to the existing publisher for the new table named College :

EXEC sp_addsubscription
@publication = 'Student',
@subscriber = 'INTEL-PC',
@destination_db = 'Student_Reporting',
@reserved='Internal'

By specifying @reserve = ‘internal’ when you add the subscription for the new article the snapshot agent generates snapshot for the new article after that.

Next, I executed the Snapshot Agent manually and found that the Snapshot of only the Single table named College was generated successfully:

Now let’s query the Reporting database (Subscriber) named Student_Reporting and examine whether the College table has been replicated successfully :

Now let’s insert few set of records into the college table on the publisher and examine whether the Data is flowing properly onto the subscriber database :

insert college values(1,'VJTI','Matunga','Mumbai','Maharashtra')
insert college values(2,'SSJCOE','Sonarpada','Dombivli','Maharashtra')

Now let’s query the Subscriber Database named Student_Reporting and examine whether the changes have been replicated properly.

Now let’s consider a case wherein we try to add a column named Sex to the student table present on the Publisher database named Student using the below T-SQL.

alter table student
add sex char(1)

As soon as the above T-SQL is executed, we queried the Subscriber Database named Student_Reporting and received the below output:-

Thus we can see that the New Column has been properly added onto the Student table on the Subscriber database named Student_Reporting.

]]>

Leave a comment

Your email address will not be published.