DBA > Articles

Does Transactional Replication Include Structural Changes Made to a Database

By: Satnam Singh
To read more DBA articles, visit http://dba.fyicenter.com/article/

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:

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/