Shipping of transaction log files from the primary server database to second server database to get both database in continuous synchronization. It is a continuous process in the form of batches.
Log shipping requires 3 servers
1. Primary: Primary server contains source database for sending the transaction logs.
2. Secondary: Secondary server contains destination database receiving the transaction logs.
3. Monitor: Monitor server tracks information related to log shipping jobs and sends alerts in case of failure.
Log shipping mainly depend on 3 jobs:
1. Backup job: For Backup transaction logs on primary server database. Always run on primary server.
2. Copy job: Copy job runs from secondary server, copies transaction log backup from primary server to secondary server.
3. Restore job: Restore run on secondary server to restore backups.
1. Before log shipping configuration, take full backup of primary database and restore it on secondary server. This is initial synchronization.
2. Create backup job on primary server to backup the transaction logs of primary database.
3. Create copy job on secondary server to copy the transaction logs.
4. Create restore job on secondary server to restore the transaction logs.
5. Create an alert jobs on monitor server to indicate a failure if the database are out of synchronization beyond the configuration threshold.
1. Upgrade from 2008 R2 to sql server 2012
2. Hardware failure
In these situations keep primary server in offline and make secondary server database online.
Need to identify crash time, when last backup, copy and restoration happened on server using ‘db0.log shipping-monitor’ tables of MSDB databases.
For configuring log shipping we require 3 servers and 3 jobs and we need backup destination, there are 2 types
In local share, backup copies into primary server share whenever primary goes down data loss is more.
In remote share, backup copies to network share copy job of secondary server goes to this share and copies backup to local share of secondary server. We have to grant proper permissions to sql agent services for this network share folder on both servers.
1. Select database → Go to properties →Select options
From recovery model drop down choose full recovery model
By using T-sql
Alter database databasename set RECOVERY FULL;
2. From properties → select transaction log shipping
Click Backup setting button
3. Transaction log backup settings
Provide network path to backup folder and local path if folder is local to primary server.
4. Using schedule option we can change time interval by default it will be 15 minutes (Latency)
5. Delete files older than (Retention period) – 72 hours
Alert if no backup occurs with in – 1 hours
6. Keep compression as default.
Based on retention period old backup files will be deleted. Whenever there is no backup for particular period we need to get alert.
Backup setting completed, secondary server setting enabled.
Secondary database settings
Select secondary server name:
Initialize secondary database
We have to create database on secondary server, if not present.
Select one option from the 3 options.
create secondary database if not available by using above option
In case backup already exist we use this option (It is not recommended, LSN mismatch may happen).
In this we specify location of MDF and LDF files.
Destination folder for copied files: \\win2KS13\secondary
Delete copied after (retention period) : 24 hours
Provide name to copy job
Schedule setting → specify setting
Restore Transaction Log
No recovery mode will not allow user to access secondary db standby mode, allows users to run select command till next restoration.
Disconnect users when restoring, kills all user connections.
No recovery → To keep database in restoring mode
Stand by → we can execute select commands
Disconnect users → It skills user who connect to server while restoring
Delay restoring → If we want , wantedly delaying restoring.
Log shipping configuration completed.
If log files available then only we can perform tail log backup.
TUF file stands for Transaction undo file (TUF)
TUF file contains the modifications that were not committed on primary database. When transaction log backup was in progress and when log was restoring on secondary database. When next transaction restoring in secondary server, sql server uses that TUF file and starts restoring incomplete transactions.
Select * from msdb.. Sysjobhistory where message
Like ‘% operating system error%’