SQL SERVER DBA LOG SHIPPING CONCEPT

 

LOG SHIPPING:

• Log shipping SQL Server allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances.
• Backups of the transaction log are applied individually to each of secondary databases.
• It is a continuous process in the form of batches.

Logshipping requires 3 servers:

1.primary server:It contains source database for sending the transaction logs.

2.Secondary server:It contains destination database receiving the transaction logs.

3.Monitor server:

      An optional instance of SQL Server that tracks all of the details of log shipping, including:

• When the transaction log on the primary database was last backed up.
• When the secondary servers last copied and restored the backup files.
• Information about any backup failure alerts.
• Once the monitor server has been configured, it cannot be changed without removing log shipping first.

Log shipping mainly depend on 4 jobs:

1.Backup job:

• For backing up transaction logs on primary server database.
• this job 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.

4.Alert job:

• A SQL Server Agent job that raises alerts for primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold.
• When log shipping is enabled on a database, job category "Log Shipping Alert" is created on the monitor server instance.

iphone 6s

Logshipping steps:

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 job on monitor server to indicate a failure if the databases are out of synchronization beyond the configure threshold.

Failover steps:

Planned failover:

planned failover situations:

1. upgrade from 2008R2 to SQL Server2012
2. Hardware failure

In these situations keep primary server in offline and make secondary server database online.

1. Disable all log shipping jobs on both servers.
2. Make sure copy all backups copied to secondary server.
3. Restore all T-log backup in same order.
4. Change connection to secondary server and start jobs.

Unplanned/Disaster:

Need to identify crash time,when last backup,copy and restoration happened on server using 'dbo.logshipping_monitor'tables of MSDB databases.

1. Disable all log shipping jobs.
2. Perform Tail-log backups if log files available.
3. Manually copy last backup's from network share to secondary server.
4. Restore all T-log backups in same sequence.
5. Now secondary database will come online.
6. Sync all logins,jobs then find orphaned user and fix them.

Log shipping Prerequisites:

1. SQL Server 2005 or later.
2. Standard, Workgroup or Enterprise editions must be installed on all server instances involved in log shipping.
3. To setup a log-shipping you must have sysadmin rights on the server.
4. Make sure recovery model should be full.
5. Maintain same hardware between both server to avoid performance issue.
6. Services account running on both servers must have access to network share where log backups store.
7. Keep secondary server always in no recovery.
8. Make sure number of drives and name of drives should be same on both servers.

TUF file in log shipping:

• 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 the TUF file and starts restoring incomplete transactions

Log shipping Advantages:

1. Only logshipping allow to keep secondary database in stand by mode.
2. We can maintain multiple secondary databases.
3. No additional hardware or software required.

Logshipping Disadvantages:

More downtime possiable,We can not bring secondary server immediately.

Data loss possiable,as jobs run every 15 minutes.

We have to perform manual failover.

Need to perform several steps to bring secondary server online which we call warm stand by.

pendrive