Does LogShipping Include the Structural Changes Made to the Database?

Recently a client asked me “
Does
LogShipping ship the Create Table, Alter Table, Drop Table commands onto the
Secondary Server?”.
Obviously the answer to his question was “Yes” but
I wanted to be 101% sure before committing something to him. In this article we are going to prove
that LogShipping not only propogates the Insert/Update and Delete commands onto
the Secondary Database but also propagates the
structural changes which are happening against the Primary Database onto the
Secondary Database.

Consider that we have a Database named ABC
which is the database on the Primary Server and the name of the Secondary
Database is ABC_DR which is the database on the DR Server. Now in order to test
the functionality whether LogShipping allows
Structural changes happening on
the Primary Server to be propagated onto the Secondary Server successfully
,
we will keep the Database named ABC_DR which is present on the Secondary
Server in the StandBy mode so that we can query out the same.

For demonstration purposes, I created a
Database named ABC on an instance named ABCSQL. The DR copy of
the database is named ABC_DR which is in Standby/Read-Only mode as
shown in the below screen capture. Or Demonstration purpose, both the Databases
named ABC and ABC_DR resides on the same Server.

Now let’s create a table named Student
inside the ABC Database. Against the database named ABC, execute
the below T-SQL.

create table student
(
student_id int identity(1,1),
student_first_name varchar(50),
student_last_name varchar(50)
)

As soon as the above T-SQL is executed a
table named Student is created inside the database named ABC. Now
let’s try to insert few records into this table using the below T-SQL.

insert student(student_first_name, student_last_name)
select 'Avinash','Lewis'
union
select 'Hemal','Joshi'
union
select 'Satnam','Singh'

To examine whether the above
changes have been properly reflected in the Secondary database named ABC_DR ,
let’s execute the 3 LogShipping jobs in a sequential manner as
follows:-

  • LS Backup
  • LS Copy
  • LS Restore

Once the above Jobs are executed
successfully, let’s query the Secondary Database named ABC_DR and the
output is as follows:-

select * from student

Now let’s try to alter the DataType
of a particular column in the student table named student_first_name
from varchar(50) to varchar(30). On the Primary Database named ABC,
execute the below T-SQL to alter the length of the column named student_first_name.

Alter table student
Alter column student_first_name varchar(30)

Now let’s execute the 3 LogShipping jobs in
a sequential manner i.e. they are as follows:-

  • LS Backup
  • LS Copy
  • LS Restore

Once the above jobs are executed
successfully we can query the Secondary Database named ABC_DR and the
output is as follows:-

Now let’s try to add a new Column to the
table named Student on the Primary Database named ABC. In order
to do so execute the below T-SQL against the primary database named ABC.

alter table student
add student_address varchar(500)

Once the above T-SQL is executed
successfully, execute the below 3 LogShipping jobs in a sequential manner (i.e. LS Backup, LS Copy, LS Restore)

Once the above jobs are executed
successfully, we can query the Secondary database named ABC_DR and the
O/P is as follows:-

Now let’s try to create a non-clustered
Index on the column named student_address present inside the student
table on the Primary Database named ABC involved in LogShipping.

       CREATE NONCLUSTERED INDEX [Student_Address] ON [dbo].[student]
(
       [student_address] 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) ON [PRIMARY]
GO

Once the above T-SQL is executed
against the Primary Database named ABC, execute the below 3
LogShipping jobs in a sequential manner.

Once the logShipping jobs
are executed , on the Secondary Database named ABC_DR, we
can see that the Non Clustered index named Student_Address has been
created successfully.

Thus we can conclude that LogShipping
propagates the Insert/Update and Delete operation which happens against a
particular table on the Primary Database successfully onto the Secondary
Database and also propagates the structural changes on a
Primary Database onto the Secondary
Database.




Array

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