Log shipping is a feature of SQL Server that allows you to use one server as the primary database from which data is replicated to secondary servers. This is very similar to master-slave replication. The secondary server can be used as a read-only server or as a standby server to which applications can be manually switched in case of a failure.
About secondary databases
The secondary database can be either in read-only mode or completely inaccessible. However, the secondary database can be switched online at any time simply by executing the following command:
RESTORE DATABASE [db-name] WITH RECOVERY;
After the database is switched online, continuing log shipping is not possible. It will be necessary to restart it by pressing the Initial Log Shipping button.
How log shipping works in SQLBackupAndFTP
Log shipping operates by continuously creating transaction log backups on the primary server and restoring these backups on the secondary server.
Firstly, you need to create a backup job on the primary server that will generate transaction log backups and export them to a location accessible on the secondary server (for example, to the cloud).
- Define the connection to the primary database, which should be located on the same server as SQLBackupAndFTP
- Select the databases that will be involved in log shipping
- Specify the destination for storing backups, ensuring that the same storage is selected for restoration
- Schedule the job and ensure that transaction log backups are included in your backup plan
- To verify smooth operation of the backup job, click the Run Now button at the top of the application
After at least one transaction log backup has been created in the backup job, you can proceed with the log shipping job.
Setting up log shipping jobs
To create a log shipping kob, click on Job – Add Log Shipping Job or click on the plus icon and select Add Log Shipping Job.
- Choose the destination used in the backup job on the non-primary server. SQLBackupAndFTP will automatically scan the selected destination for backups that can be restored.
- Choose the databases that you want to configure for log shipping. The list of available databases is populated by scanning the destination. By clicking the gear icon, a new dialog will open where you can specify the database to which the backups should be restored.
- Provide the connection details for the secondary server
- Specify the restore frequency, typically matching the frequency set in the backup job for transaction log backups
The Advanced Schedule settings can be found by clicking the gear icon. - Click Initial Log Shipping to initiate the process and view the job progress log
- The above settings are the primary ones, but you can also configure email notifications to receive emails in case the job fails or succeeds. Additionally, you have the option to provide a description if the backups are encrypted, and specify a temporary folder. Please note that the temporary folder setting applies to all jobs; if it is changed, the change will affect all existing jobs as well.
- Log shipping options. During log shipping operation, the database on the secondary server can be in two possible states:
- Restoring — the database is not available for connection. It can be switched online using the command
RESTORE [db-name] WITH RECOVERY
. This mode is typically used for standby servers that need to be activated in case of a primary server failure. - Standby/Read-Only — the database is available for reading but not for writing. However, with each restore operation, active connections to the database will be terminated. If you plan to use this mode for load balancing, your application logic should include retries for database access. This mode is also often used to create databases with data frozen at the end of the day (for reporting purposes).
- Restoring — the database is not available for connection. It can be switched online using the command