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.




Array

No comments yet... Be the first to leave a reply!