What is Log Shipping?
Log Shipping is one of the solutions for High Availability for business critical application. Log Shipping is first introduced in SQL Server 7.0. Log Shipping; as name defines does the shipping of Transaction Log to another server; it does exact replica of the database; it does backup of Transaction Log and restores it on a Secondary /Stand by Server which keeps database on secondary server in sync with the database on Primary Server. In case of failure work loss happens only for the time being copy of T-Log backup and restoring back to Secondary/Stand by Server. Stand by / Secondary server which can act as a primary server in case of failure. SQL Server DBA has to perform some manual tasks to bring it online for business.
Why to do Log Shipping?
There are other options like Clustering and Replication for High Availability of Business Critical Application, so why do we go for Log Shipping?
Well, there are reasons for choosing Log Shipping against Cluster and Replications:
Setting up: It is very easy to create a Log Shipping, only thing you need to identify is you must have two servers with SQL Server Enterprise Edition and a database(s) which is critical to your business environment.
Manageability: It is easy to manage Log Shipping comparing to Replication and Cluster Server, generally Log Shipping works well and you don't feel panic with its monitoring or maintenance work.
Act as a Reporting Server: Stand by or Secondary Server can act as a Reporting Server in your organization, your database which is being log shipped can be used for Read Only queries or I would rather say it can act as a Reporting Server. The thing need to be consider here is it will be unavailable at the time of Restoration of Transaction Log e.g. if your Transaction Log backup takes 10 minutes of Restoration Time and you does Log Shipping every 60 minutes then your Reporting/Secondary/Stand by Server will be un-available for 10 minutes.
Can use multiple database(s)/server(s): You can use multiple server(s) as a Stand by or Secondary Server for different purpose, e.g. you can create two Stand by server(s) one for Reporting and another for High Availability
Cheaper then cluster: If you go for a Cluster Server you will have to meet software and Hardware requirement; I mean you need identical hardware to setup clustering where as in case of Log Shipping you don't need to have exactly the same set of hardware for Primary and Secondary or Stand by server.
Best solution for physical dispersed location: Data Center: In your environment you are having a cluster server configured at your Data Center for high availability so that if in case of any failure or damaged you can recover your data back, imagine what if your data center itself got damaged!!!!!! In this type of scenario Log Shipping does good job you can configure Log Shipping to Dr Server which is at different physically location and if your local Data Center / Server got damaged you may still recover your data.
You can do this thing using Replication too; in case of replication again you have to select Snapshot/Transactional/Snapshot Replication, now as you may use Log Shipping you would rather go for Snapshot Replication, you have to monitoring it regularly whether it is running or not!!! Where as with Log Shipping it generally runs smoothly and even if it caught an error it will tell you exact where it breaks so fixing a Log Shipping is not difficult.
How to do Log Shipping?
Here, I've tried describing Prerequisites and steps for setting up Log Shipping.
Database must be in Full or Bulk-Logged Recovery Model.
It is required that Database has to be in Full or Bulk-Logged Recovery Model,
One can perform T-Log backup only when Database Recovery Model is set to Full or Bulk-Logged, here is a brief on recovery model.
| Recovery Model | Benefit | Work Loss | Type of Recovery |
| Simple | Keeps T-Log in small size, can perform bulk copy operation | If needed you have to restore Full backup or Differential backup | Can done up to the last backup time |
| Bulk-Logged | Minimal log space occupation, can perform bulk operation | If the log is damaged or bulk operation is done after last T-Log backup | Can done up to the last backup time |
| Full | No work lost due to damaged data file, Point-In-Time recovery | Normally none, only if you lost your most recent T-Log backup | Point-In-Time |
Windows Account used to setup Log Shipping must have SQL Server System Admin rights on both servers.
It will not break Log Shipping if you the password of Account has changed, because it needs to change at the same time on both the servers else Log Shipping will fails.
Create a share on Primary Server and Secondary Server with change and write permissions for Windows Account User which is used for SQL Server Agent.
You may Restore Full Database on Secondary Server manually or it can be done via Log Shipping installation itself at the time of setup process (Ensure this database on Secondary Server must be restored with NORECOVERY or STANDBY option).
It is required to Restore Database with NORECOVERT | STANDBY because if the database restored in recovered/write enabled mode you can not apply another T-Log.
| Optional Component : Monitoring / Witness Server |
Identify your Monitor Server which will look after for your Primary Server and Secondary Server's Activity.
It is better to use separate server which monitors the activity of Primary and Secondary Server so that you can get the alert even if server itself won't works.
- Create a Share with read/write permission for domain user which we'll be using to startup SQLServerAgent
Create a Full Database backup for
LogShDB
Backed up T-Log on the folder you have created earlier to store the log backup
You can create a maintenance plan to take a T-Log backup for regular interval and then add it as a Step in job to automate this process
Please ensure that the duration must enough to copy the log backup file to Secondary Server.
Suppose we are taking T-Log backup each 1/2 hour and if the backup file is big enough which takes more then 1/2 to copy over the network share, it will again creates a new T-Log backup which will then occupies recourse unnecessarily.
Copy T-Log backup file to Secondary Server's shared folder
- Delete the T-Log backup file after it copied to the Secondary Server's shared folder.
Ensure you have selected Allow Database to assume primary role option so that if in future requirement of role reversal can be possible easily.
If in case we need to change the role of primary server or secondary server to act as Primary or Secondary this option should be used while setting up Log Shipping.
Ensure that you have set up proper threshold values for Destination Database for Out of Sync, Load Time Delay, File Retention Period and History Retention Period option.
These options are self explanatory: Out of Sync=used by witness server to send alert if the specified time has been exceeded between Last T-Log backup on primary and secondary server, Load Time Delay= Destination Database waits before it restores the T-Log, File Retention Period= Time elapse till file gets deleted and History Retention Period= Time till we retain History details
- Check if the Database is already exists on Secondary Server, if it is already their drop it.
- Restore a Full Database Backup for LogShDB
- Restore a T-Log for LogShDB on the secondary server which is copied on the shared folder of Secondary Server;
- Ensure you have given STANDBY or NORECOVERY option while restoring T-Log backup for LogShDB.
Summary:
You may find log shipping is a very use full yet simple to configure, use and manage, it is cheaper and easily manageable solution for High Availability of your data. It is a good solution over clustering (because it is costly; requires identical hardware to setup cluster) and easily manageable, easy troubleshooting against replication as it clearly says where it breaks. Generally Log Shipping works very smooth once it is configured. Even if you have to re-configure it from the scratch it is easy job to do.
