Understanding Log Shipping: SQL Server Log shipping allows
you to automatically send transaction log backups from a primary
database on a primary
server instance to one
or more secondary databases on separate secondary
server instances. The
transaction log backups are applied to each of the secondary databases
individually. An optional third server instance, known as the monitor
server, records the history and status of backup and restore
operations and, optionally, raises alerts if these operations fail to occur as
scheduled.
Terms and Definitions:
primary server
The
instance of SQL Server that is your production server.
primary database
The
database on the primary server that you want to back up to another server. All
administration of the log shipping configuration through SQL Server Management
Studio is performed from the primary database.
secondary server
The
instance of SQL Server where you want to keep a warm standby copy of your
primary database.
secondary database
The
warm standby copy of the primary database. The secondary database may be in
either the RECOVERING state or the STANDBY state, which leaves the database
available for limited read-only access.
monitor server
An
optional instance of SQL Server that tracks all of the details of log shipping,
including:
- When the transaction log on the
primary database was last backed up.
- ·
When the secondary servers last
copied and restored the backup files.
- ·
Information about any backup failure
alerts.
Jobs in Log shipping :
· Backup Job
A SQL
Server Agent job that performs the backup operation, logs history to the local
server and the monitor server, and deletes old backup files and history
information. When log shipping is enabled, the job category "Log Shipping
Backup" is created on the primary server instance.
· Copy job
A SQL
Server Agent job that copies the backup files from the primary server to a
configurable destination on the secondary server and logs history on the
secondary server and the monitor server. When log shipping is enabled on a
database, the job category "Log Shipping Copy" is created on each
secondary server in a log shipping configuration.
· Restore job
A SQL
Server Agent job that restores the copied backup files to the secondary
databases. It logs history on the local server and the monitor server, and
deletes old files and old history information. When log shipping is enabled on
a database, the job category "Log Shipping Restore" is created on the
secondary server instance.
· Alert job
A SQL
Server Agent job that raises alerts for primary and secondary databases when a
backup or restore operation does not complete successfully within a specified
threshold. When log shipping is enabled on a database, job category "Log
Shipping Alert" is created on the monitor server instance.
Important: Once the monitor server has been configured, it cannot be
changed without removing log shipping first.
Minimum
Requirements:
- SQL Server 2005 or later
- Standard, Workgroup or Enterprise editions must be installed on all
server instances involved in log shipping.
- The servers involved in log shipping should have the same case
sensitivity settings.
- The database must use the full recovery or bulk-logged recovery
model
- A shared folder for copying T-Log backup files
- SQL Server Agent Service must be configured properly
A
Typical Log Shipping Configuration:
The following figure shows a log shipping configuration with the primary
server instance, three secondary server instances, and a monitor server
instance. The figure illustrates the steps performed by backup, copy, and
restore jobs, as follows:
- The primary server instance runs the backup job to back up the
transaction log on the primary database. This server instance then places
the log backup into a primary log-backup file, which it sends to the
backup folder. In this figure, the backup folder is on a shared
directory—the backup share.
- Each of the three secondary server instances runs its own copy job
to copy the primary log-backup file to its own local destination folder.
- Each secondary server instance runs its own restore job to restore
the log backup from the local destination folder onto the local secondary
database.
Steps to Configure Log-Shipping:
1. Make sure your
database is in full or bulk-logged recovery model. You can change the database
recovery model using the below query. You can check the database recovery model
by querying sys.databases
SELECT name, recovery_model_desc FROM sys.databases WHERE name =
'jugal'
USE [master]
GO
ALTER DATABASE [jugal] SET RECOVERY FULL WITH NO_WAIT
GO
2. On the primary server,
right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the "Enable this as primary
database in a log shipping configuration" check box.
3. The next step is to configure and schedule a
transaction log backup. Click on Backup
Settings… to do this.
If you are creating backups on a network share enter the network
path or for the local machine you can specify the local folder path. The backup
compression feature was introduced in SQL Server 2008 Enterprise edition. While
configuring log shipping, we can control the backup compression behavior of log
backups by specifying the compression option. When this step is completed it
will create the backup job on the Primary Server.
4. In this
step we will configure the secondary instance and database. Click on the Add… button to configure the Secondary
Server instance and database. You can add multiple servers want to setup
one to many server log-shipping.
When you click the Add… button it will take you to the below
screen where you have to configure the Secondary Server and database. Click on
the Connect… button to connect to the secondary
server. Once you connect to the secondary server you can access the three tabs
as shown below.
Initialize Secondary Database tab
In this step you can specify how to create the data on the
secondary server. You have three options: create a backup and restore it, use
an existing backup and restore or do nothing because you have manually restored
the database and have put it into the correct state to receive additional
backups.
Copy Files Tab
In this tab you have to specify the path of the Destination Shared
Folder where the Log Shipping Copy job will copy the T-Log backup files. This
step will create the Copy job on the secondary server.
Restore Transaction Log Tab
Here you have to specify the database restoring state information
and restore schedule. This will create the restore job on the secondary server.
5. In this step we will configure Log Shipping Monitoring which
will notify us in case of any failure. Please note Log Shipping monitoring
configuration is optional.
Click on Settings… button which will take you to the “Log Shipping Monitor Settings” screen. Click on Connect …button to setup a
monitor server. Monitoring can be done from the source server, target server or
a separate SQL Server instance. We can configure alerts on source / destination
server if respective jobs fail. Lastly we can also configure how long job
history records are retained in the MSDB database. Please note that you cannot
add a monitor instance once log shipping is configured.
6. Click on the OK button to finish the Log Shipping
configuration and it will show you the below screen.
