Sign Up

SqlBak Blog

SQL Server Log Shipping

by
Last updated on August 31, 2024

What is log shipping in SQL Server?

Log shipping in Microsoft SQL Server is an automated solution for master-slave replication based on the transfer of transaction logs from primary to secondary servers. This process ensures continuous database cloning while minimizing the load on the primary server.

How does log shipping work?

Configured database log shipping involves a continual repetition of two steps:

  1. Backing up the transaction log on the primary server
  2. Restoring this transaction log on the secondary server

Transaction log backup is a backup method for storing database transaction logs, providing the ability to accurately restore data to any point in time (point-in-time recovery).

Is the secondary database accessible?

It depends on the chosen mode of log shipping. There are two possible states that the secondary database can be in:

  1. Unavailable — if the NO RECOVERY mode is selected
  2. Read-Only — if Stand-By/Read-Only mode is selected

Can the database be switched to read-write mode?

Yes, absolutely! This can be achieved using the following command:

RESTORE DATABASE [db-name] WITH RECOVERY;

But log shipping for this database will stop working.

Why is transaction log shipping needed?

Microsoft SQL Server log shipping is primarily a tool that can be utilized for various engineering tasks and can be combined with other tools.

There are two classic use cases:

  1. Standby server: A server that can be switched to in case of a failure
  2. Read-only database for reporting

However, this tool can also be used more creatively, such as for server migration, or to create a read-only server with a fixed delay based on the relevance of the data.

Advantages of log shipping

  1. Ease of setup — There is no requirement for the primary and secondary servers to have direct access to each other or be in the same domain. You can transmit transaction log backups even through Dropbox (using SQLBackupAndFTP).
  2. Ease of debugging — This stems from the first point.
  3. Integration with transaction log backups — If you are already performing transaction log backups, then everything is already configured for the primary server.
  4. Minimal impact on the primary server — Since log shipping operates asynchronously, it does not affect the performance of the primary server at all (unlike AG and replication).
  5. There can be as many secondary servers as desired.

Disadvantages of log shipping

Log shipping has its limitations, which may make it inferior to other SQL Server tools. Unlike AG, it cannot switch to a replica in case of failure. Restoring a transaction log backup breaks existing connections to the secondary database.

Requirements

To enable log shipping, the database must be in either the full or bulk-logged recovery model. You can set the required recovery model using the following command:

ALTER DATABASE [db-name] SET RECOVERY FULL WITH NO_WAIT

Alternatively, you can set this in SQL Server Management Studio by adjusting the database options.

How to set up log shipping via SQLBackupAndFTP

SQLBackupAndFTP is a program for creating backups of SQL Server. You can also set up a log shipping job in it to restore transaction log backups. One distinctive feature of this app is that there is no need to establish a connection between SQL Server instances; file exchange can be done via FTP, AWS S3, Azure Blob Storage, or other cloud storage services.

How to set up SQLBackupAndFTP for log shipping

Create a backup job on the primary server

  1. Specify the connection to the primary database; the database must be installed on the same server as SQLBackupAndFTP
  2. Choose the databases that will participate in log shipping
  3. Specify the destination where backups will be stored. When restoring, you will need to select the same storage.
  4. Schedule the job, and ensure to include transaction log backups in your backup plan
  5. To make sure the backup job runs smoothly, click on the “Run Now” button at the top of the app

 

Create a log shipping job on the secondary server

To create a new job, navigate to “Job” > “Add Log Shipping Job.”

  1. Select the destination that you use in the backup job on the non-primary server
  2. Choose the databases for which log shipping should be performed. The list of available databases for selection is generated based on scanning the destination
  3. Specify the connection details for the secondary server
  4. Specify the restore frequency; typically, you should enter the same value as in the backup job for transaction log backups
  5. Click “Initial Log Shipping” and a job progress log will be displayed

Advantages of SQLBackupAndFTP for log shipping:
  • Very simple setup
  • No need for a direct connection between primary and secondary servers
  • Backups can be transferred via the cloud
Disadvantages of SQLBackupAndFTP for log shipping:
  • Transaction log backups are not available in the free edition.
  • Users are always disconnected when using STANDBY or read-only mode.

How to set up log shipping via SQL Server Management Studio (SSMS)

SQL Server Agent can be configured to perform log shipping. Setting up SQL Server Agent for log shipping is done through SSMS (SQL Server Management Studio).

How to set up SQL Server Management Studio for log shipping

Make sure that the SQL Server Agent is configured to start automatically

  1. Press Win+R, and type services.msc
  2. Find SQL Server Agent, right-click it, and choose Properties
  3. Set the Startup Type to Automatic

Enable log shipping for the database

  1. Select the database, then click Properties
  2. Select the “Transaction Log Shipping” section, choose the option “Enable this as a primary database in a log shipping configuration” and select “Backup Settings”
  3. Specify the path to the network directory through which backups will be transmitted. If this path is accessible on your local disk, indicate it in the box below. The backup job that creates transaction log backups will do this every 15 minutes. You can override this by clicking the Schedule button.

Connect secondary server

  1. Go back to the options tab and click the Add button.
  2. Click on the Connect button and specify the data required to establish a connection with the secondary database.
  3. On the “Copy Files” tab, specify the directory where to temporarily store transaction log backups. The job responsible for copying the transaction logs from the network directory will do this every 15 minutes, which can be overridden by clicking the Schedule button.
  4. Specify the type of log shipping job, and optionally override the frequency of execution (default is every 15 minutes).

Save and test

  1. After saving, a job creation log for the backup agent will be displayed on both the primary and secondary servers.
  2. On the primary server, a job should appear in SQL Server Agent to create transaction log backups.
  3. On the secondary server, two jobs should appear, one that copies backups and one that restores.
  4. By clicking on “View History” on any of these jobs you can see the results of job executions.

 

Advantages of SSMS for log shipping:
  • SSMS and SQL Server Agent are already installed.
  • No limitations in capabilities
Disadvantages of SSMS for log shipping:
  • Requires a connection between the primary and secondary servers for setup
  • Transfer of T-LOG backups is only available through a network share.

Custom log shipping (via T-SQL)

In essence, log shipping operates based on two T-SQL commands: BACKUP LOG and RESTORE LOG. Neither SQL Server Backup Agent nor SQLBackupAndFTP do any magic: they simply manage the execution of these commands and transfer files between servers.

Below is an example of a demonstration script that performs log shipping within a single DBMS instance.

Powershell script implementing log shipping
  1. Creates a full backup with the BACKUP DATABASE command
  2. Copies the full backup to a separate directory using Copy-Item
  3. Restores the full backup with RESTORE DATABASE WITH STANDBY command
  4. Runs an endless loop in which
    • Creates log backups with the BACKUP LOG command
    • Copies the log backup to a separate directory using Copy-Item
    • Restores the log backup with RESTORE LOGcommand
This script is not a ready-made solution, it is a base for customization. For example, if you want it to work with a secondary server, specify the network path in destinationPath. Alternatively, instead of Copy-Item, use an FTP client to transfer backups.
Also in this script, the transaction log backup is constantly overwritten. To avoid losing backups, you should copy the transaction log backups created to a storage location.
$primaryServerInstance = "."
$databaseName = "db2"
$restored_databaseName = "db2_restored"
$backupPath = "c:\temp\6\"
$fullBackupFile = "$backupPath\$databaseName`_Full.bak"
$logBackupFile = "$backupPath\$databaseName`_Log.bak"

$secondaryServerInstance = "."
$destinationPath = "c:\temp\7\"

# New variables for file paths
$mdfFilePath = "c:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db2_.mdf"
$ldfFilePath = "c:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db2_.ldf"
$undoFilePath = "$destinationPath$databaseName`_undo.ldf"

Write-Host "Removing previous full backup if it exists..."
Remove-Item $fullBackupFile -ErrorAction SilentlyContinue

Write-Host "Creating full backup of the database $databaseName..."
Invoke-Sqlcmd -Query "BACKUP DATABASE [$databaseName] TO DISK = N'$fullBackupFile' WITH INIT, NAME = N'$databaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;" -ServerInstance $primaryServerInstance

Write-Host "Copying full backup to the secondary server..."
Copy-Item -Path $fullBackupFile -Destination $destinationPath

Write-Host "Dropping previous version of the restored database $restored_databaseName if it exists..."
Invoke-Sqlcmd -Query "DROP DATABASE IF EXISTS [$restored_databaseName]" -ServerInstance $secondaryServerInstance

Write-Host "Restoring database from the full backup..."
Invoke-Sqlcmd -Query "RESTORE DATABASE [$restored_databaseName] FROM DISK = N'$destinationPath$databaseName`_Full.bak' WITH FILE = 1, MOVE N'db1' TO N'$mdfFilePath', MOVE N'db1_log' TO N'$ldfFilePath', STANDBY = N'$undoFilePath', NOUNLOAD, STATS = 5;" -ServerInstance $secondaryServerInstance

while ($true) {
    Write-Host "Creating transaction log backup..."
    Invoke-Sqlcmd -Query "BACKUP LOG [$databaseName] TO DISK = N'$logBackupFile' WITH NOFORMAT, INIT, NAME = N'$databaseName-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;" -ServerInstance $primaryServerInstance

    Write-Host "Copying log backup to the secondary server..."
    Copy-Item -Path $logBackupFile -Destination $destinationPath

    Write-Host "Restoring transaction log in $restored_databaseName..."
    Invoke-Sqlcmd -Query "RESTORE LOG [$restored_databaseName] FROM DISK = N'$destinationPath$databaseName`_Log.bak' WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'$undoFilePath';" -ServerInstance $secondaryServerInstance

    Write-Host "Waiting for 15 min before the next iteration..."
    Start-Sleep -Seconds 900
}

 

Advantages of custom log shipping:

  • You can customize the process to fit your specific needs.
Disadvantages of custom log shipping:
  • Achieving a good implementation will require writing a lot of PowerShell code.
  • You will need to maintain and support this solution.

Frequently asked questions

Do full or differential backups on the primary server affect a running SQL Server Log Shipping?

No, it doesn’t. Each subsequent transaction log backup contains information from the last transaction log backup, not from Full or Differential backup (the exception is the very first Full backup).

How to stop log shipping?

Simply stop the execution of the restore job on the secondary server.

How to restart log shipping?

For SSMS

  1. Disable log shipping on both servers.
  2. Perform a backup of the primary database.
  3. Restore the database on the secondary server with the NORECOVERYor STANDBY \ READ-ONLY option on Server B.
  4. Re-enable the log shipping jobs on both servers.

For SQLBackupAndFTP

Press the “Initial Log Shipping” button in the job settings.

What is better, availability groups or log shipping?

Availability groups (AG) is a more specialized tool designed to ensure high availability in case of failures. The main advantage of AG is the automatic switching to a running server in case of a failure.

The advantage of log shipping is that it does not have the limitations of AG. It is easier to configure, and the servers do not necessarily need to be aware of each other. Additionally, there are no overhead costs on the primary server (since backups are necessary regardless). But in case of failure on the primary server, you will need to manually switch the database connection string to the backup server.

To enable automatic failover with log shipping, you need to implement logic that changes the connection string at the application level. This is possible but not trivial to accomplish.
Is it possible to combine log backups and log shipping?

This is important and needs to be combined, and under no circumstances should they be parallel processes.

The same .trn files used for log shipping should be used for backups. If you set up these processes separately, your log shipping will not work, and backups will not be restored.

Leave a Comment