SQL Server 2000

Log-Shipping
Sal Cincotta
Senior Technology Specialist - MCSD
Microsoft

Agenda

What is log shipping?

When should it be used?
Prerequisites.
Architecture.
Advantages / Disadvantages.
Demo

What is Log-Shipping?
Log-Shipping is one of several pieces
to an underlying high availability
solution.
Enables corporations to protect
against natural disasters, regional
power failures, terrorism, etc.
Uses the transaction log to track
changes.

When to use.
Designed as an inexpensive
alternative to clustering.
Use to protect against site level
disasters.
Use to offload query processing to
another server. (Some dos and don'ts here)

Prerequisites
VERY IMPORTANT!!!
This is one piece of the puzzle. Do not
underestimate the importance of a sound plan
around availability and fault tolerance.
Its about preventing downtime what does that
downtime cost?
Database Backups. (recover from application or user errors)
SLAs: How long will it take to recover? Backup
to disk or tape? How often? Are you using a run
book?
Continued

Prerequisites
Redundant Components

Power supplies
Fans
Controller Cards
STORAGE SUBSYSTEM

Architecture
EE contains built in support for LogShipping, whereas SE requires a
more complex and custom
implementation.
The architecture consists of:
Primary Server
Standby Servers
Monitoring Server

Architecture

(cont.)

Primary Server:
contains your primary database.
SS makes periodic transaction log
backups to capture changes.

Architecture

(cont.)

Standby Server:
Are servers running a licensed copy of
SQL Server.
Contain an unrecovered copy of the
production database.
The standby server can be recovered and
replace the primary server if it fails or
must be taken offline for maintenance.
One standby server can contain standby
databases from multiple primary servers.

Architecture

(cont.)

Monitoring Server:
Is a server running a licensed copy of SQL
Server.
Monitors the status of the log-shipping jobs on
the primary and each standby server.
Should use a server other than the primary or
the standby to detect problems on either server.
One monitoring server can monitor multiple
primary-standby server pairs.

Architecture

(cont.)

Once a copy of the database has

been restored log-shipping uses
several SQL Server Agent jobs and
alerts to:
1. Maintain an up-to-date copy of the
production db on one or more standby
servers.
2. Report the success or failure of these
jobs to the monitoring server and to
administrators.

Architecture

(cont.)

Jobs include:

Transaction log backup job

Transaction log backup copy job
Transaction log restore job
Backup alert
Out of sync alert

Architecture

(cont.)

Transaction Log Backup Job:

Runs on the primary server according to a
schedule.
Records the results of each execution in a status
table on the monitoring server.
This backup should be placed on a fault tolerant
set of disks.
The more frequent the backup, the smaller the
size of the backup file and the more closely you
can sync the standby server

Architecture

(cont.)

Transaction Log Backup Copy Job:

Runs on the standby server and copies
each backup from the share to the
standby server according to a schedule.
Records the results of each execution in
a status table on the monitoring server.
Job should run with the same frequency
as the backup job to ensure maximum
sync in the event of failure.

Architecture

(cont.)

Transaction Log Restore Job:

Runs on the standby server and restores the
backups to the standby server according to a
schedule.
Restore the logs without recovery to allow for
additional logs to be restored.
Should run with the same frequency as the copy
job. This keeps the standby synced within
minutes of the production db.
Special Note: you must chose between
maximum sync and additional functionality.
Continued

Architecture

(cont.)

If you want to use the standby for reporting

you will not be able to restore as frequently
because SS requires exclusive access.
The restore would have to kick all users off
every time a file is restored.
If not, it will take longer to recover in the
event of failure and increases the risk of
errors or corruption.
Possible Solution: create two standby
servers 1) to be used for disaster and
max sync and 2) to be used for reporting

Architecture

(cont.)

Backup Alert:
Runs on the monitoring server and
monitors the success or failure of the
backup jobs on the primary.
Set the threshold for alerting an
administrator to allow for large backups.

Architecture

(cont.)

Out of Sync Alert:

Runs on the monitoring server and
monitors the level of synchronicity
between the production db and the
standby db.
Threshold can be set to determine the
level of sync that needs to be exceeded
before an alert is sent.

Architecture

(cont.)

Other important notes:

Log-shipping only copies db objects in
the production db.
Objects that are stored in master and
msdb or in the file system must be
manually copied or recreated on the
standby before or when a role change is
initiated.
Continued

Architecture

(cont.)

Objects to copy:
Server Logins
Use dts and the transfer login task
Backup the sysxlogins table using the BCP
utility to capture server-level logins.
Store the backup file on each of the
standby servers.
Be sure to update these whenever a change
has been made.
Continued

Architecture

(cont.)

Other DB Objects:
Use t-sql to recreate all jobs, alerts, and
user-defined error messages. Run these
scripts on each of the standby servers.
Update these as objects change.

DTS Packages:
Store a copy of each dts package on
each of the standby servers.
Update the copy of each package as
changes are made.

Architecture

(cont.)

If one standby server will act as a

repository for multiple standby
databases keep these things in mind:
SQL logins from different instances may
have identical names with different
permissions, which can create a conflict.
DB objects from different instances must
have different names to avoid conflicts.
DTS packages from different instances
must have different names.

Architecture

(cont.)

Ports:
The following ports must remain open if
going through a firewall:
Port 1433 must remain open for SQL to
communicate back and forth.
Port 135 required for SQL Agent to copy
backup files to the shared folder on the
standby server.
Ports 137, 138, and 139 or 445 required
to use UNC shares.

Architecture

(cont.)

Initiating a Role Change:

This is a manual process that must be initiated
on the standby server.
Back up the transaction log with no recovery.
Use sp_change_primary_role to accomplish this.
If the primary fails, completed transactions that
are not backed up and copied to the standby are
lost.
Copy backup files to the standby server. Use
sp_change_secondary_role to accomplish this.
Continued

Architecture

(cont.)

Restore transaction log backups, with

recovery on the standby server.
Sp_change_secondary_role.
Resolve SQL logins. Use
sp_resolve_logins to load the saved
bcp file. This sp resolves the SID
values between the production and
the standby database.

Architecture

(cont.)

Once these tasks are complete the

standby server is ready to accept
client connections.
However, client apps have to point to
the new server name and ip address.
This can be resolved in several ways
COM+, DNS, or via NLB.

Advantages
Hardware requirement does not
require certified hardware like MSCS.
Software requirement can use
Standard Edition for manual setup.
Less network traffic than replication.
Site Protection protect against
catastrophic site disaster.
Offload query processing.

Disadvantages / Limitations
Manual detection and role change.
Required objects and files must be
kept in sync.
Dual purposes vs. minimal latency.
Potential transaction loss.
Lack of transparency to clients.

Log-Shipping
Demo:
Setting Up and Monitoring
Log-Shipping

Final Steps
Create and execute a sync login job
Must sync syslogins table when initial log
ship is complete and when changes are
made

Script and copy jobs, alerts and

operators from msdb
Copy DTS packages

More Information
Support Web Site:
support.microsoft.com
search on log-shipping

Books:
Patterns and Practices: SQL Server 2000
High Availability Volume 1 and 2