What is SQL Server log shipping? March 16, 2014 by Ivan Stankovic What is SQL Server log shipping? SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations A log shipping session involves the following steps: Backing up the transaction log file on the primary SQL Server instance Copying the transaction log backup file across the network to one or more secondary SQL Server instances Restoring the transaction log backup file on the secondary SQL Server instances Implementation examples One of the common log shipping scenarios is the environment with two servers (SQLServer-1 – primary and SQLServer-2 – secondary), two SQL Server instances (SQLInstance-1 and SQLInstance-2), and one SQL Server database named SQLDB-1 with log shipping running on it Another common configuration is the environment with three (or more) servers (SQLServer-1 – primary, SQLServer-2 – secondary, and SQLServer-3 – secondary), three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3), and one SQL Server database named SQLDB-1 with log shipping running on it Operating modes There are two available modes and they are related to the state in which the secondary, log shipped, SQL Server database will be: Standby mode – the database is available for querying and users can access it, but in read-only mode The database is not available only while the restore process is running Users can be forced to disconnect when the restore job commence The restore job can be delayed until all users disconnect themselves Restore mode – the database is not accessible Advantages and disadvantages of using SQL Server log shipping SQL Server log shipping is primarily used as a disaster recovery solution. Using SQL Server log shipping has multiple benefits: it’s reliable and tested in details, it’s relatively easy to set up and maintain, there is a possibility for failover between SQL Servers, data can be copied on more than one location etc. Log shipping can be combined with other disaster recovery options such as AlwaysOn Availability Groups, database mirroring, and database replication. Also, SQL Server log shipping has low cost in human and server resources The main disadvantages in the SQL Server log shipping technique are: need to manage all the databases separately, there isn’t possibility for an automatic failover, and secondary database isn’t fully readable while the restore process is running Setting up the database log shipping environment SQL Server log shipping is based on execution of predefined SQL Server jobs. The SQL Server log shipping feature is available in all SQL Server editions except the Express edition. All the databases intended to be used for log shipping must be in the Full or Bulk logged recovery model Another important prerequisite is running SQL Server Agent on both servers. Security policies must be defined in order for SQL Server Agent to have permission to read and write in the backup folder. Note that SQL Server agent on the secondary server must be able to read from the primary server’s backup folder The database backups can be compressed, but that requires additional CPU time. Most common configurations use network locations for storing the backups The database log shipping setup needs to be initiated from the principal server using the SQL Server Management Studio wizard. The first step defines transaction log backup settings: A network path to the backup How long backup files should be kept before deleting An alert if no backup is taken The backup job itself Schedule of the job Schedule type Frequency Duration The next step defines secondary databases which involve choosing the secondary SQL Server instance and secondary database. The full database backup, from the primary database, must be restored on the secondary server before log shipping commences After initializing the secondary database you must define the copy folder where the transaction log backups from the primary server will be stored The final step involves choosing from two available modes: The No recovery – Restore mode and Standby mode. You can also delay the restoring process and set up an alert if no restore occurs within the specified time Once the log shipping is ready for use, it will run in the background, and if the problem occurs the alert will signalize the problem About Latest Posts Ivan StankovicIvan is a SQL Server professional and computer geek with years of IT and SQL Server experience. He has startedwith playing computer games, continued with computer programming and system administration. His areas of expertise are SQL Server disaster recovery, auditing, and complianceView all posts by Ivan Stankovic Latest posts by Ivan Stankovic (see all) Using Extended Events to review SQL Server failed logins - August 5, 2014 SQL Server backup – models and types - May 26, 2014 SQL Server Policy Based Management – Categories and Database Subscriptions - May 21, 2014 Related posts: How to create SQL Server Log Shipping How to set up SQL Server Log Shipping on Linux What is backup and restore in SQL Server disaster recovery? What is a SQL Server disaster recovery? What is SQL Server database mirroring?