Log shipping is a DR solution that provides recovery at the database level. Log shipping includes one primary server, and one or more secondary servers. You don't have automatic fail-over, but if the primary becomes unavailable, the secondary can be brought online by restoring any remaining shipped logs to the database. In short, it's the automated transfer and restore of log backups from primary to standby server(s). In this post I am just going to show you a very basic implementation of Log Shipping.
- To set it up, you must be a sysadmin on the server
- Usable with SQL Server v2005 or later
- The same edition - Workgroup, Standard or Enterprise - must be installed on each instance within the log shipping solution.
- Your databases must be either Full or Bulk-logged recovery model
- You need a shared folder for the transaction log backup files
- Be sure the case sensitivity is the same across each server
Steps:
1. Create a shared folder for primary and standby servers to access the transaction logs
2. Make sure your recovery model is FULL or BULK_LOGGED -
USE msdb;
SELECT recovery_model_desc
FROM sys.databases
WHERE name = 'dbname'
3. Backup the database on the primary instance -
USE master;
BACKUP DATABASE dbname TO DISK='C:\Bkups\dbname_Full_Backup.bak'
WITH INIT,FORMAT;
4. Restore the database on the secondary instance -
USE master;
RESTORE DATABASE dbname
FROM DISK '\\Srv\Bkups\dbname_Full_Backup.bak'
WITH REPLACE, NORECOVERY;
USE master;
BACKUP DATABASE dbname TO DISK='C:\Bkups\dbname_Full_Backup.bak'
WITH INIT,FORMAT;
4. Restore the database on the secondary instance -
USE master;
RESTORE DATABASE dbname
FROM DISK '\\Srv\Bkups\dbname_Full_Backup.bak'
WITH REPLACE, NORECOVERY;
5. Configure log shipping at the primary with the following statements -
DECLARE @BackupJobId AS UNIQUEIDENTIFIER
DECLARE @PrimaryId AS UNIQUEIDENTIFIER
DECLARE @RetCode AS INT
EXEC @RetCode = master..sp_add_log_shipping_primary_database
@database = N'dbname',
@backup_directory = N'C:\Bkups',
@backup_share = N'\\Srv\Bkups',
@backup_job_name = N'Backup_dbname',
@backup_retention_period = 4320,
@backup_threshold = 5,
@threshold_alert_enabled = 1,
@history_retention_period = 5760,
@backup_job_id = @BackupJobId OUTPUT,
@primary_id = @PrimaryId OUTPUT,
@overwrite = 1
IF (@@ERROR = 0 AND @RetCode = 0)
BEGIN
DECLARE @BackUpScheduleUID AS UNIQUEIDENTIFIER
DECLARE @BackUpScheduleID AS INT
EXEC msdb.dbo.sp_add_schedule
@schedule_name = N'Every5Min',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 2,
@freq_recurrence_factor = 0,
@active_start_date = 20140905,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time = 235900,
@schedule_uid = @BackUpScheduleUID OUTPUT,
@schedule_id = @BackUpScheduleID OUTPUT
EXEC msdb..sp_attach_schedule
@job_id = @BackupJobId, @schedule_id = @BackUpScheduleID
EXEC msdb..sp_update_job @job_id = @BackupJobId, @enabled = 1
END
EXEC master..sp_add_log_shipping_alert_job
DECLARE @CopyJobID UNIQUEIDENTIFIER
DECLARE @RestoreJobID UNIQUEIDENTIFIER
DECLARE @SecondaryID UNIQUEIDENTIFIER
DECLARE @RetCode INT
BEGIN
@primary_database = N'dbname',
@secondary_server = N'SecondarySrvName',
DECLARE @RestoreJobID UNIQUEIDENTIFIER
DECLARE @SecondaryID UNIQUEIDENTIFIER
DECLARE @RetCode INT
EXEC @RetCode = master..sp_add_log_shipping_secondary_primary
@primary_server = N'PrimarySrvName',
@primary_database = N'dbname',
@backup_source_directory = N'\\PrimarySrvName\Logs',
@backup_destination_directory = N'C:\Bkups',
@copy_job_name = N'Copy_dbname',
@restore_job_name = N'Restore_PrimarySrv_dbname',
@file_retention_period = 300,
@overwrite = 1,
@copy_job_id = @CopyJobID OUTPUT,
@restore_job_id = @RestoreJobID OUTPUT,
@secondary_id = @SecondaryID OUTPUT
IF(@@ERROR = 0 AND @RetCode = 0)BEGIN
DECLARE @SecondaryCopyJobScheduleUID UNIQUEIDENTIFIER DECLARE @SecondaryCopyJobScheduleID INT EXEC msdb..sp_add_schedule
@schedule_name = N'Every5MinCopy',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 2,
@freq_recurrence_factor = 0,
@active_start_date = 20140905,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time = 235900,
@schedule_uid = @SecondaryCopyJobScheduleUID OUTPUT, @schedule_id = @SecondaryCopyJobScheduleID OUTPUT
EXEC msdb..sp_attach_schedule
@job_id = @CopyJobID,
@schedule_id = @SecondaryCopyJobScheduleID
DECLARE @SecondaryRestoreJobScheduleUID UNIQUEIDENTIFIER
DECLARE @SecondaryRestoreJobScheduleID INT
EXEC msdb..sp_add_schedule
@schedule_name = N'Every5MinRestore',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 2,
@freq_recurrence_factor = 0,
@active_start_date = 20140905,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time = 235900,
@schedule_uid = @SecondaryRestoreJobScheduleUID OUTPUT,
@schedule_id = @SecondaryRestoreJobScheduleID OUTPUT
EXEC msdb..sp_attach_schedule
@job_id = @RestoreJobID,
@schedule_id = @SecondaryRestoreJobScheduleID
END
DECLARE @RetCode2 INT
IF(@@ERROR = 0 AND @RetCode = 0)
BEGIN
EXEC @RetCode2 = master..sp_add_log_shipping_secondary_database
@secondary_database = N'dbname',
@primary_server = N'PrimarySrvName',
@primary_database = N'dbname',
@restore_delay = 0,
@restore_mode = 0,
@disconnect_users = 0,
@restore_threshold = 5,
@threshold_alert_enabled = 1,
@history_retention_period = 5760,
@overwrite = 1
END
IF (@@ERROR = 0 AND @RetCode = 0)
BEGIN
EXEC msdb..sp_update_job @job_id = @CopyJobID, @enabled = 1
EXEC msdb..sp_update_job @job_id = @RestoreJobID, @enabled = 1 END
7. Lastly, execute this statement on the primary, to configure the secondary database
EXEC master..sp_add_log_shipping_primary_secondary @primary_database = N'dbname',
@secondary_server = N'SecondarySrvName',
@secondary_database = N'dbname',
@overwrite = 1
USE msdb;
SELECT * FROM log_shipping_monitor_primary;
Now take a look at each of these queries to gather general information regarding the status of the log shipping solution:
How many databases are being targeted with the log shipping? USE msdb;
SELECT * FROM log_shipping_monitor_primary;
Where are the secondary, or standby servers? USE msdb; SELECT * FROM log_shipping_primary_secondaries; General status for the Log Shipping? USE msdb; SELECT * FROM log_shipping_monitor_history_detail; Any errors? USE msdb; SELECT * FROM log_shipping_monitor_error_detail;
That's about it. Just a very basic method for using Log Shipping as your DR solution. I encourage you to research and read a bit more. See each of these for more detailed information.
About Log Shipping - http://msdn.microsoft.com/en-us/library/ms187103.aspx
Log Shipping FAQs - http://www.brentozar.com/archive/2013/03/log-shipping-faq/
Here's a great tip that I just found this evening, for catching up your secondary faster:
http://www.brentozar.com/archive/2014/09/log-shipping-magic-using-differential-backup-delayed-secondary/
No comments:
Post a Comment