SQL Server Transaction Log Shipping Replication

Prerequisite :

2 separate SQL database instances at 2 different physical boxes – T-B0399254ED744 & TVLX4686.
A share accessible to Primary and Secondary Server for log shipping

Here I am going to log replicate AdventureWorks database from T-B0399254ED744 to TVLX4686

Check if databases are in FULL /BULK recovery MODE Please make sure the database is running in FULL recovery mode else you will be presented error like one below

Create a job to ship Transaction Logs at Primary Server

Enable “Primary Database in Log Shipping Configuration” and the next ting you need to setup is the backup job for hipping logs from primary to Secondary Server.

I have created a share which would be made accessible to both servers file:///C|/tvlx4686/SQL_TRAN_LOG . Click on Backup and you will see a window below

Click on Schedule and you will be presented with window below. Alter any options as per your needs. I am going to set the copy job at Primary for every 5 minutes

Create a job to apply Transaction Logs at Secondary Server

Click an “ADD” option under Secondary databases.

Click on Connect and it will pop up a window to add credentials for Secondary Database

Select Secondary database you want the logs to be applied to , I chose “AdventureWorks” here.

Since there were no database existing at Secondary Server we would select “Generate Full backup of Primary Database” option which will backup primary and restore a point in time copy at Secondary.

Let’s define the area where these logs would be copied at Secondary server D:\SQL_TRAN_LOG

I am going to set the copy job at Secondary same as Primary for every 5 minutes

Finally I need to configure how frequently I want to run a Restore Transaction Log job. There are couple of options here “No Recovery Mode” or “Standby Mode”

“No Recovery Mode” : No one able to connect to the database same like Mirroring

“Standby Mode” : People will ave READ ONLY access to the database

Please set the schedule for every 5 minutes same like Primary job interval and we are all set.

Finally configure the Monitor Server Instance

I can see folllowing files in my shared drive for database and transaction log backup .

You will see a TUF file , so what does it do? : The .tuf file is the Transaction Undo File and is created when performing log shipping to a server in Standby mode.
In the standby mode, database recovery is done when the log is restored and this mode also creates a file with the extension .TUF (which is the transaction Undo file on the destination server). It is actually used while restoring the transaction logs with the STANDBY option. Generally when you do a transaction log restore, you can specify this file along with your RESTORE LOG statement

Once Successful you should be able to see the Database in Standby/read-Only mode.

I had some issues on domain authentication between 2 nodes so I have created both Primary (AdventureWorksDW) & Secondary databases (AdventureWorksDWSecondary) on the same machine now.

The procedure is same for even remote systems except you have to define a different path for restoring Secondary database than primary database/log files. Once the standby is in place we can can point applications to “Read Only” copy of database.

How would you monitor the Standby Database Transaction log shipping … It’s Simple

This would display information as below. Here Primary/Secondary databases on same instance otherwise you see Primary backup details at Primary Server and Secondary Restore details at Secondary Server.

Please notice the Scheduled Jobs at Primary/Secondary server. One can alter Replication Duration/Status if needed.

Let’s see the jobs and log replication in Action

I will create a table called users and add data into it at Primary Database. We will Wait for 5 minutes and in theory it should pop up at Read only secondary system.