What is SQL Server Log Shipping:

 Log Shipping allows replicating databases using the database transaction log. The transaction log contains all the transactions in the database. The main idea is to copy the transaction log to a secondary database in order to replicate the information. So, you need a Primary Server with a database, configure log shipping to have some transactional log backups, and then copy it to a secondary database to have a replica.

Log Shipping Involves below steps

Backup Job – Backup the transaction log file on the primary SQL Server Instance or any backup share.

Copy Job – Copy the transaction log backup file across the network to one or more Secondary SQL server instances

Restore Job – Restore the copied transaction log on the secondary SQL server instance.

Terms related to Log Shipping

Primary Server -The instance of SQL Server, which is your production server is called as the primary server.

Primary Database - The database on the primary server which you want to backup to another server is called as the primary database.

All Log Shipping administrative activities are performed from the primary database with the help of SQL Server management studio.

Secondary Database - The worm standby copy of the primary database is called as secondary database.

The secondary database can be in below two states

• Recovering
• Standby

Monitor server

Monitor Server tracks all the details of log shipping. It includes below things

• When the transaction log on the primary database was last backed up.
• When the secondary server last copied and restored the backup files.
• Information about any backup failure alert.

Monitor server is optional.

Note – We cannot make any changes in monitor server configuration without removing log shipping.

Operating Modes

There are two modes and they are based on the state in which the secondary database will be

• Standby mode – The database is available for querying and users can access it, but in read only mode. The database will not be available when restore process is running.
• Restore mode – The database is not accessible

TUF file

TUF stands for “Transaction Undo File”.

This file contains information regarding any changes that were made as part of incomplete transactions at the time the backup was performed.

It is required if a database is loaded in read state. In this state further transaction log backups may be applied.

It is created while performing log shipping to a server in standby mode.

If TUF file is corrupted or lost, log shipping will not work, and we need to setup it again.

WRK File

The WRK files are produced when the transaction log backups are copied from the backup location to a secondary server.

Once the copy operation completed successfully file renamed to “.trn” file.

It ensures that the files are not picked up by restore job until it successfully copied.

Permissions

The usage of sqllogship involves the use of Windows Authentication . The Windows Authentication account running the command must have access to the Windows directory and fulfill the requirements of SQL Server Permissions , which vary depending on whether the command includes the -backup, -copy, or -restore option.

Option	Directory access	Permissions
	-backup	Needs both read and write permissions for the backup folder.	The permissions needed are identical to those required for the BACKUP statement. Additional details can be found in the Transact-SQL documentation for BACKUP.
	-copy	Necessary permissions include the ability to read from the backup directory and the ability to write to the copy directory.	The permissions needed are identical to those required by the stored procedure sp_help_log_shipping_secondary_database.
	-restore	Access to both read and write functions is necessary for the copy directory.	The permissions required for this are identical to those needed for the RESTORE statement. Additional details can be found in the RESTORE (Transact-SQL) documentation.

 

sqllogship Application:

The sqllogship application performs a backup, copy, or restore operation and associated clean-up tasks for a log shipping configuration. The operation is performed on a specific instance of Microsoft SQL Server for a specific database.

 

sqllogship Application:

Primary Node: Log Shipping: SQL Jobs: sqllogship (Application Executable):

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Backup ...

Secondary Node: Log Shipping: SQL Jobs: sqllogship (Application Executable):

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Copy ...

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Restore ...

Failing Over:

To fail over to a secondary database:

1.      Copy any uncopied backup files from the backup share to the copy destination folder of each secondary server.

2.     Apply any unapplied transaction log backups in sequence to each secondary database. For more information, see Apply Transaction Log Backups (SQL Server).

3.     If the primary database is accessible back up the active transaction log and apply the log backup to the secondary databases. You may need to set the database to single-user mode to obtain exclusive access before issuing the restore command, and then switch it back to multi-user after the restore completes.

4.      If the original primary server instance is not damaged, back up the tail of the transaction log of the primary database using WITH NORECOVERY. This leaves the database in the restoring state and therefore unavailable to users. Eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database.

5.     For more information, see Transaction Log Backups (SQL Server).

6.      After the secondary servers are synchronized, you can fail over to whichever one you prefer by recovering its secondary database and redirecting clients to that server instance. Recovering puts the database into a consistent state and brings it online.

7.      After you have recovered a secondary database, you can reconfigure it to act as a primary database for other secondary databases.

8.     If no other secondary database is available, see Configure Log Shipping (SQL Server).

Different ways to monitor Log Shipping for SQL Server databases

Log Shipping is a basic SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover or reporting purposes.

You can use the below items to investigate if there are any issues with your databases that are setup for Log Shipping.  We will cover each of these items and how they can be used.

• SQL Server Error Log
• SSMS Built In Report
• System Stored Procedures
• Query the MSDB database
• Application/System EventViewer Log

 

I’ve gotten a lot of great questions about log shipping, so I’ve put together an FAQ.

• What editions of SQL Server is log shipping available in?
• 2012 – Enterprise, Business Intelligence, Standard, and Web
• 2008R2 – Datacenter, Enterprise, Standard, Web, and Workgroup
• 2008 – Enterprise, Standard, Web, and Workgroup
• 2005 – Enterprise, Standard, and Workgroup
• Does the secondary need to be licensed?
• I am not the licensing police, and I am not Microsoft – check with your licensing representative to clarify your exact situation. Generally, you can have one warm standby server. However, the second someone starts using it for reporting, testing, or anything else, you need to license it like any other server.
• Log shipping is compatible with backup compression. What edition of SQL Server do I need to take advantage of compression?
• 2012 – Enterprise, Business Intelligence, or Standard
• 2008R2 – Datacenter, Enterprise, or Standard
• 2008 – Enterprise
• 2005 – Not available
• When log shipping is set up, Agent jobs are created to alert me if a backup, copy, or restore fails. How do I get notified?
• You need to go into the Agent job, pull up Notifications, and choose your method – email an operator, or write to the event log, for example.
• Are my logins shipped from the primary to the secondary?
• No, they are not. You’ll need to set up a separate method to sync the logins.

• Does this replace, or can it be combined with, our existing daily full and log backups?
• TL; DR – no.
• You’ll still want to take regular full and/or differential backups. Log shipping only takes one full backup – at the beginning – and that’s only if you specify that it does so. It can also be initialized from an existing full backup.
• Taking two log backups in separate jobs will break the log chain, however. If you implement log shipping, it will replace your current transaction log backup job.
• What’s the difference between the secondary being in “Restoring” vs. “Standby”?

·  Restoring means the database is not accessible. Standby means it is read-only. You make this decision when you set up the log shipping.

• If the database is in Standby mode, users can query it – except when a log backup is being restored. You need to decide if a restore job will disconnect users, or if the restore is delayed until after the users are disconnected.