Administering and
Managing Log Shipping
Log shipping is one of four SQL Server 2005 High-availability solutions.
Other SQL Server 2005 high-availability alternatives
include
Database Mirroring
Fail-over Clustering
Peer-to-Peer Replication.
Note: -
Database mirroring and Peer-to-Peer Replication are new technologies introduced
with SQL Server 2005
Log shipping offers increased database availability and
database protection by maintaining a warm standby database on another instance
of SQL Server 2005.
Unlike database mirroring, a Log Shipping fail-over is not
done automatically but its manual fail-over.
Factors affecting
Log Shipping
The size of the transaction log backups, speed of the
network, and length of time the restoration takes all play a significant role
in planning and scheduling the log shipping solution.
Log Shipping
Design Considerations
* SQL
Server Version: SQL Server 2005 Standard, Work group and Enterprise Editions
can be used for log shipping. All servers must be configured with the same
case-sensitivity settings.
* Recovery
Mode: The recovery mode of the source database on the primary server must
be configured as full or
bulk-logged. Because the transaction log is a key part of log shipping, the
simple recovery model cannot be used.
* Monitor Server: The monitor server is optional. This server is
important because it provides a central location for the log shipping status
and alert messages.
* Security: Sysadmin role is required on each SQL Server that will
take part in the Log Shipping. The agent account used to run the backup job on
the primary server must be able to read and write to the backup location.
* Backup
& Restore T Log Location: The backup storage location is used to store
the transaction log backups created by the backup job. It is highly recommended
to host the transaction log backups on a fault-tolerant server independent of
the log shipping primary or secondary servers having enough disk space. Similarly
on backup server, enough drives should be available.
* Log shipping reports can be viewed from the primary,
secondary, and monitor servers. However, viewing the reports from the monitor
server is most effective because the monitor server contains records from both
the primary and secondary servers. Viewing the log shipping report from the
primary and secondary servers shows only half the data.
Log Shipping
Tables
|
Table Name
|
Description
|
|
|
|
|
log_shipping_monitor_alert
|
Stores alert job ID.
|
|
log_shipping_monitor_error_detail
|
Stores error details for log shipping jobs.
|
|
log_shipping_monitor_history_detail
|
Contains history details for log shipping agents.
|
|
log_shipping_monitor_primary
|
Stores one monitor record for the primary database in
each Log shipping configuration, including information about the last backup
file and last restored file that is useful for monitoring.
|
|
log_shipping_monitor_secondary
|
Stores one monitor record for each secondary database,
including information about the last backup file and last restored file that
is useful for monitoring.
|
Reasons for Log shipping Failure
§
Network Failure
§
No Disk space on Secondary Server
§
Read/Write permission removed from Log folder
§
Password Expired of SS Agent Service
§
Copy / Restore job is not running
Prerequisite to configure Log Shipping
§
2 Servers (Primary & Secondary)
§
Recovery model should be FULL
§
SQL 2005 Enterprise / Standard edition
§
Same collation on both the Servers
§
Agent should be in Automatic Mode
§
One shared folder on primary server to keep log
backups
-------------------------------------------------------------------
Script to check Log shipping
Status - SQL 2000
select p.primary_Server ,p.primary_database
,s.secondary_server, p.last_backup_file,s.last_copied_file,
s.last_restored_file
from msdb..Log_shipping_monitor_primary p ,
msdb..Log_shipping_monitor_secondary S
where p.primary_database = S.primary_database
What is .TUF file
in Log Shipping?
TUF file is a Microsoft SQL Server Transaction Undo file.
.TUF File contains the information regarding any modifications that were made
as part of incomplete transactions at the time the backup was performed.
A transaction undo(.TUF) file is required if a database
is loaded in read-only state. In this state, further transaction log backups
may be applied.
.TUF File in Log
Shipping
The transaction undo file contains modifications that
were not committed on the source database but were in progress when the transaction
log was backed up AND when the log was restored to another
database, you left the database in a state that allowed addition transaction log backups to be restored to it (at some point in the future. When another transaction log is restored, SQL Server uses data
from the undo file and the transaction log to continue restoring the incomplete transactions (assuming that they are completed in the next transaction log file). Following the restore, the undo file will be re-
written with any transactions that, at that point, are incomplete.
Hope its not too geeky.
database, you left the database in a state that allowed addition transaction log backups to be restored to it (at some point in the future. When another transaction log is restored, SQL Server uses data
from the undo file and the transaction log to continue restoring the incomplete transactions (assuming that they are completed in the next transaction log file). Following the restore, the undo file will be re-
written with any transactions that, at that point, are incomplete.
Hope its not too geeky.
Question: In
my environment there is an issue with Log shipping destination file path, I've
to change the file path on the destination, I've changed and LS copy is working
fine and LS restore is failing because it is trying find the .tuf file on the
old path which is not exists on the destination.
I don't want to do full restore for 30+ databases, so I'm
trying to update the .tuf path on msdb on destination server but I couldn't
find out the path details on any of the log shipping system tables. I knew the
last restored file path details can be found on
dbo.log_shipping_monitor_secondary ,dbo.log_shipping_secondary_databases
tables, updating these tables not helping to resolve my issue.
dbo.log_shipping_monitor_secondary ,dbo.log_shipping_secondary_databases
tables, updating these tables not helping to resolve my issue.
Where is the .tuf
file path details on msdb?
Ans: The tuf file path is none other
than the column backup_destination_directory in log_shipping_secondary on the
primary server. And this will be automatically updated when you change the folder
name in the LS setup page . But TUF should be available in the old directory
when the next restore happens.
SELECT backup_destination_directory FROM dbo.log_shipping_secondary
If you are changing the path for this directory what SQL server does is , when the next restore happens it first tries to copy the TUF file from the old directory to new directory and then only go ahead with the restore operation . If SQL server cannot find the .tuf file in the old directory or the old directory is itself lost – then there is no other way than reconfiguring your LS setup from scratch.
What is Undo File? Why it is required?
Undo file is needed in standby state because while restoring the log backup, uncommitted transactions will be recorded to the undo file and only committed transactions will be written to disk there by making users to read the database. When you restore next tlog backup SQL server will fetch the uncommitted transactions from undo file and check with the new tlog backup whether the same is committed or not. If its committed the transactions will be written to disk else it will be stored in undo file until it gets committed or rolled back.
SELECT backup_destination_directory FROM dbo.log_shipping_secondary
If you are changing the path for this directory what SQL server does is , when the next restore happens it first tries to copy the TUF file from the old directory to new directory and then only go ahead with the restore operation . If SQL server cannot find the .tuf file in the old directory or the old directory is itself lost – then there is no other way than reconfiguring your LS setup from scratch.
What is Undo File? Why it is required?
Undo file is needed in standby state because while restoring the log backup, uncommitted transactions will be recorded to the undo file and only committed transactions will be written to disk there by making users to read the database. When you restore next tlog backup SQL server will fetch the uncommitted transactions from undo file and check with the new tlog backup whether the same is committed or not. If its committed the transactions will be written to disk else it will be stored in undo file until it gets committed or rolled back.
No comments:
Post a Comment