What is Log shipping in SQL Server?
Log shipping means Shipping transaction log files from the Primary Server database to one more secondary servers to get both databases in continuous synchronization.
SQL Server log shipping is a database level high availability feature, whenever primary database down due to any reason like natural calamity or software or hardware or human errors then we make the secondary database as primary database. In primary Server transaction log backups are taken and copied to network share from there these backups are copied to secondary server local share from there the backups are copied within specified time by the user. These all steps are being done by the backup job, copy job, restore job and alert job.
Transaction log contains every action is done by the users, this file is more important when ever any disaster happen, by using this only we can recover the database.
In SQL Server log shipping, Primary server database backup are being taken by backup job, in secondary server these backups are being copies and restored by copy job. Before configuring log shipping make sure the database is in full recovery or bulk recovery model.
How to check the Recovery Model:
Right click on Database -> go to properties -> select on options -> Here you can find recovery model of database.
If your database is not in full recovery model use below script:
use master go alter database database-name set recovery full
Configuring SQL Server log shipping step by step:
First we need to enable the primary server database. Right click on database -> go to properties -> select transactional log shipping -> check the check box enable this as a primary database in log shipping configuration
Next click on Backup settings -> Mention where to take the backup either network share or local path, In my scenario I am using local share
In the below image delete files older than 72 hours means the older files will be deleted and if no backup occurs within 1 hour we will get the alert for not taking backup
click on the schedule button -> Now select when should this backup job has to run -> if you want you can change the backup name
click on ok. Next add secondary instance database
Add Secondary instance database then fill all the details like Initialize secondary database, copy files and restore setting tabs.
Active Directory: It maintains all servers, users information
Primary Server: Primary server contains a source database for sending the transaction logs.
Secondary Server: Secondary Server contains the destination database receiving the transaction logs.
Monitor Server: Monitor Server tracks information related to log shipping jobs and sends alerts in case of failure.
It depends mainly on 4 jobs:
Backup job: For Backup transaction logs on the primary server database, always run on the primary server.
Copy Job: It runs on the secondary server, and copies transaction logs from the primary server to the secondary server.
Restore job: Restore job run on secondary server to restore backups.
Alert job: It runs on the monitor server, if the monitor server is not configured then it stores in primary and secondary servers.
For Configuring log shipping, we require 4 servers and need backup share. There are 2 types
- Local share
- Remote or Network share
- In local share, backup copies into primary server share whenever primary goes down data loss is more.
- In network share, Backup copies to the network share, copy job of the secondary server goes to this share and copies backup to local share of the secondary server. We must grant proper permissions to service accounts to this network share folder on both servers.
- Make sure the recovery model should be full.
- Maintain the same hardware between both servers to avoid performance issues.
- Service accounts running on both servers must have access to the network share where log backups are stored.
- Keep secondary server always in no recovery.
- Make sure the number of drives and name of drives should be the same on both servers.
How to configure log shipping?
- Check the recovery model of the database.
- Take full and transactional log backup of the primary server database and restore it to a standby server with standby or no recovery.
- Configure jobs.
- Configure alerts.
- Configure linked servers.
Configuring log shipping :
- Go to SSMS (SQL Server Management Studio) and take 3 instances
- Note down the service account names
- Create the folder in monitor server D Drive BakcupShare and grant read/write permissions to the service account.
- In the primary server create one test database for instance bank and create one table
- Take full backup
- Go to the secondary server, create a local copy in some drive and grant read/write permissions to the service account and restore the database from the primary server to the secondary server with a standby option.
- Configuring log shipping
Go to the primary server
Right-click on the bank database Tasks ship transaction log
Select checkbox “Enable this as primary database”
Click on backup settings and give the backup folder path Click on schedule, leave the default values
Select the secondary database.
- Only log shipping allows for keeping the secondary database in standby mode.
- We can maintain multiple secondary databases.
- No additional hardware or software is required.
- More downtime is possible, we can not bring a secondary server immediately.
- Data loss is possible, as jobs run every 15 minutes.
- We must perform manual failover.
- Need to perform several steps to bring a secondary server online which we call warm stand by.
Interview questions on log shipping in SQL Server:
- What is a TUF file?
TUF stands for Transaction Undo file contains the modifications that were not committed on the primary database when transaction log backup was in progress and when a log was restored on the secondary database. When the next transaction restoring in the secondary server, SQL Server uses the TUF file and starts restoring incomplete transactions.
2. Can we take full backup?
A full backup can not truncate transactional log files so we can take a full backup.
3.What is the difference between log shipping and replication?
Log shipping is a database level high availability feature whereas replication is an object level high availability feature.
4.What is the difference between log shipping and mirroring?
There is no automatic failover in log shipping, but automatic failure is there in mirroring. There might be downtime and data loss is in log shipping but in mirroring synchronous commit mode no downtime and no data loss.
How do you failover in log shipping?
Disable all log shipping jobs on the primary and secondary servers.
Check whether we can take a tail log backup or not, if log files are available then only, we can perform a tail log backup.
Manually copy last backup and tail log backup from network share to secondary server.
Restore all available log backups with no recovery and the last log backup with recovery
Now the secondary database will become online.
Sync all logins and jobs
Find orphaned users and fix them.
Monitoring Log Shipping in SQL Server:
Right-click on SQL Server in SSMS, go to reports, select standard reports, and transaction log shipping status.