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.
]]>