Recently I got a project to implement log shipping in SQL Server. The project was to implement log shipping for multiple databases. I’ve configured log shipping before but that was years ago and I bet stuff has changed since then to make it easier to implement.
Log shipping existed partially in SQL 6.5 and 7 and was officially released in SQL Server 2000. Log shipping today is still being used pretty often as a warm standby or for readable secondary databases being used for reporting.
One of the concerns was that in case of a schema change like, adding a table to the database or altering a column in an existing table, would affect the continuity or even break the log shipping.
If you look at replication for instance, it’s reliant on the schema to work and if the schema changes you’ve got to make sure the schema is the same on the secondary server.
I was pretty sure that schema changes would be applied in log shipping but I didn’t want to assume and tested it out to make sure it wouldn’t cause any problems.
I wanted the following cases to get tested:
- Create a table
- Adding a user
- Add a stored procedure
- Change the data type of a column
I created a database called “LogshippingTest” and set up log shipping to a database called “LogshippingTest_DR” on the same machine. For this test it was fine that both databases resided on the same machine but in the real world you’ll probably want to have different machines.
I created a table called “Test”:
There are three columns and I want to see what happens when I change the column with the VARCHAR data type from 30 characters long to 130.
Keep in mind that there are caveats to changing the schema with log shipping. When changing a column from NULL to NOT NULL will result in a change where you have to recreate the table and import the data.
The DR database looks like this after implementing log shipping:
It all looks good now let’s execute the jobs for the log shipping and make the changes.
To change the table I executed the following script on the primary database:
ALTER TABLE Test ALTER COLUMN column2 VARCHAR(130)
Let’s see if the changes are in the transaction log:
SELECT [Current LSN] ,
[Transaction ID] ,
[Parent Transaction ID] ,
[Begin Time] ,
[Transaction Name] ,
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_BEGIN_XACT'
This gave the following result:
That looks pretty good let’s check the DR database to see what happened after log shipping.
As expected, after the log shipping was executed, the change was transferred to the secondary database.
The same results could be seen when creating the other objects like the users and procedures.
We can safely say that schema changes are applied on the secondary database in log shipping.
Happy log shipping!