7. Log Shipping

Topics In This Module.

• Log Shipping 
• Working with Log Shipping 
• Terminology 
• Pre-requisites to implement Log Shipping 
• Different modes of Log Shipping 
• Configuring Log Shipping 
• Monitoring 
• Manually Performing Failover 
• Log Shipping Tables 
• Some Real Time Scenarios  

 ***============================================================================***

Log Shipping

What is Log Shipping? 

Log Shipping is a technique where logs or log backups from primary server(database) are transferred or shipped to secondary server(database). 
In this technique the log backup is taken in primary server databases to a network path, and then the file is copied to the available location where secondary server database is accessible, and then the backup is restored to secondary server database. 

All the above communication is done using jobs; Hence we can say this as Agent based communication (SQL Server Agent) 

The log shipping operations/steps are done by jobs which are scheduled accordingly, those jobs are: 

1. Backup Job – Job is created on primary instance; This job takes the backup of database on primary server to a network path.
2. Copy Job – Job is created on secondary instance; This job copies all the backup files present in network path to a path in secondary server 
3. Restore Job – Job is created on secondary instance; This job restores the database on secondary server with all the backup files present in secondary path. 
4. Alert Job – If monitor server is configured a alert job is created in monitor server else alert jobs are created in each instances (Primary, Secondary); This job checks and send alerts to Dba if any delay in backup or restore.

============================================================================

Log Shipping Configuration

Check List: 

1. Three Servers  
1. Primary Server/Instance 
2. Secondary Server/Instance. 
3. Monitor Server/Instance (Optional for alerting) 
2. Recovery Model: 
1.  Either Full (or) 
2.  Bulk logged. 
3. Recovery State: 
1.  a. Standby mode – Read only. 
2. b. Restoring mode. 
4. Two folders 
1. a. Primary/Network path 
2. b. Secondary path 
5. Account used to run SQL Server agent should have below access 
1. a. User used for SQL server agent in primary instance need to have write access on primary folder. 
2. b. User used for SQL server agent in secondary instance need to have read access on primary folder, read and write access on secondary paths. 
6. Sysadmin privileges for log shipping configuration user. 
7. Editions, Versions, compatibility, collation needed to be same – recommended. 

Note: Lower to higher versions can be configured but higher to lower version log shipping cannot be configured when failover it gets difficult to configure, so recommended to use the same edition, version, compatibility, collation of instances. 

• But Log shipping cannot be configured between 2000 and 2005 instances as the log architecture is different between these versions. 

Log Shipping Monitoring

Log shipping can be monitored using three ways they are: 

1. Using Stored procedures. 
2. Using Job Activity Monitor. 
3. Using Transactional log Monitor 

Using Stored procedure

1. Run the below in primary server to get the log shipping details about primary server.
    sp_help_log_shipping_primary_database ‘<Database Name>’ 
2. Run the below in secondary server to get the log shipping details about 2nd server.
    sp_help_log_shipping_secondary_database ‘<Database Name>’ 
3. Run the below in primary server to get the secondary server details. sp_help_log_shipping_primary_secondary ‘<Database Name>’ 

Using Job Activity Monitor: 

            We can check the log shipping sync using the job activity monitor using the jobs, when they have been last run and next run whether they are successfully runned are not. 

Using Transactional log Monitor: 

            Right click on instance – navigate to reports – navigate to standard reports – navigate to transactional log monitor and click to open, 

1. Transactional log monitor on primary instance shows the information regarding jobs on primary like the last run backup job status, name, time, file created by job.
2. Transactional log monitor on secondary instance shows the information regarding jobs on secondary server log shipping status and also like the last runned copy job, restore job, name, time, file copied and restored by jobs. 

Important Points in Log shipping

•  This is a Database level HA or DR technique, and minimum requires 2 servers or databases to configure, usually we use 2 servers to configure, only then it gives sense of configuring HA/DR.
•  And we can also use 3 servers in this configuration, these servers are known as Primary Server, Secondary Server, Monitor Server. 
• This technique works only when databases in primary server is in FULL/Bulk Logged recovery model.
•  We can say log sipping as a Disaster Recovery – DR techniques. 

We can keep the database state on secondary server in two ways they are: 

 o Restoring mode, 

 o stand by (read – only) mode

<DB Name>_<Log>_<Date>_<LSN>.trn --- transaction log backup file name. 

. WRK File: 

• This is a temporary file, when copy job is initiated the backup file is copied from primary path to secondary path when the copy/transfer is in progress the file will be with wrk extension. 
• Once the file is copied successfully to destination then this wrk file is turned into file with extension of trn file. 

. TUF File: 

• When the database in secondary server is kept in read only mode then a tuf file will be created in secondary server where mdf files are present, which contains all the un-committed transaction present in the log backup, 
• Usually, a log backup contains all the logs, which may be committed or un-committed transactions, when this backup file is restored at secondary server database all committed transaction will/can be restored but the uncommitted transaction cannot be restored hence they will be noted in a file called as tuf file, TUF – Transaction undo files 
• whenever next backup file is about to restore then it will check this tuf file for uncommitted transaction whether they are completed or not, if committed/ completed they will be restored, and any uncommitted transactions in present file then they are noted to tough file again. 
• This TUF file is simply performing the operations of undo file, all un-committed transaction is roll forwarded 

SqlLogShip.exe: 

• This file is mandatory to configure the log shipping is present in location. C:\Program Files\Microsoft SQL Server\150\Tools\Binn 

Remember Points (Note): 

We can take normal full backups or normal Differential backup’s but we cannot take a normal transactional log backup’s instead of it we need to take copy-only transactional log backup if required on databases on which log shipping is configured. 

No Performance delay will be there at application level when compared to mirroring Asynchronous mode. 

Case Study Points: 

Patching: 
When patching disable all jobs and proceeding with any patching (Windows or SQL) and restart the instance or server once server or instance is online starting the job and run the jobs manually and check the sync of logs between primary and secondary. 

Log shipping Not in Sync: 
When the primary and secondary is not in sync verify when the last restore happened, and check whether any files are missing or not if any missing files restore the missed file first and then run the restore job to make it sync. 

If the missed tlog file is not present then we can say that log shipping is breaked hence we need to reconfigure the log shipping again. 

File adding: 

File Shrinking: 

 File name changing: 

• Changing Logical name:
• Changing Physical name: 
• Changing Physical path: 

User adding/granting roles: 

Login Adding: 

Fail over or switch over: 

Changing the secondary databases state: 

•  From Standby to restoring and vice versa: 
•  From standby to online or restoring to online: 

 Any Objects creation at primary database: 

 Log shipping rebuild: