Skip to content

SQL Log Shipping

July 10, 2015

What is 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.

 graph


 Benefits

  • Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.
  • Supports limited read-only access to secondary databases (during the interval between restore jobs).
  • Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore (apply) the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.

Configuring SQL Log Shipping

 

Before SQL Log shipping can be configured, there are some preparations to be done.

1: Ensure the database is at Full Recovery Mode.

full_recovery_mode

2: Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server. This is a share of the directory where the transaction log backups will be generated. For example, if you back up your transaction logs to the directory r:\pri, you could create the \\primaryserver\pri share of that directory. If you are running clustering, you might want to create this on shared storage. Make sure the unc name is pointing to the cluster name.

source_shared_permission

3: Also make sure sql server agent is configure with the correct credential.

sql_server_agent

Now we can begin the configuring of SQL Log Shipping.

4: Right click the database you want to use as your primary database in the log shipping configuration, and then click Properties.

5: Tick the checkbox “Enable this as a primary database….”. Then click on Backup Setting.

enable_log_shipping

6: Enter the path(shared folder) you created earlier. Then click on schedule.

source_path

7: You might want to change the occurrence to 10 mins or less. Click Ok twice to continue.

occurs_every

8: You will be brought back to the main screen. Click Add to configure the secondary database.

sec_db

9: Click on Connect and provide the credentials for the secondary DB.

connect_to_sec_db

10: On the Initialize Secondary Database tab, click on restore options and specific the path for the logs and data files to be created (this will be located on source server).

initialize_sec_db

11: On the Copy Files tab, specific the destination path.

copy_files_destination_folder

12: Click on the schedule and change the occurrence to 2 mins.

occurs_every

13: On the Restore Transaction Log, tick the Standby Mode radio button. Also, change the occurrence schedule to 2 mins. Click Ok to continue.

restore_trans_log

14: Finalize settings and click ok to finish the wizard.

finalize_settings

15: The job will run and result will show.

save_log_config_result

Failed Over to Secondary Database

Before we start, we have to disable the log shipping job on primary server.

16: Check the log shipping is in sync by executing following query on Secondary Server .

select secondary_database,last_copied_file,last_restored_file from msdb..log_shipping_monitor_secondary

last_copied

select * from msdb.dbo.log_shipping_monitor_secondary

last_restore

17:  On the primary server, backup the tail of the DB logs. Use GUI is advisable, if db is in use, run the below query.

use master

ALTER DATABASE staff_record

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

backup log staff_record to disk =’r:\backup\logs\staffrecord_tail.trn’ with norecovery

18: Restore the tail log to DR server and bring it out of standby mode.

alter database staff_record

set single_user with rollback immediate

restore database staff_record from disk =’R:\sql\in\staffrecord_tail.trn’

with recovery

19: Check and the bring it back to multi-user mode

alter database <dbname>

set multi_user

go

Now, we would want to setup log shipping on secondary server. Please follow step 4 till 8. Since we are reversing, kindly note of the path and folder.

20: Connect to Primary server this time.

conenct back to pri database

21: Since the primary database is available, we would want to select “no, secondary database is initialized”

sec_db_initialized

Finally, follow step 11-15.

The database on the primary server should turns to gray.

yellow_gray

To failback, simply reverse the steps from 16.

To transfer logins, follow this link :

https://support.microsoft.com/en-us/kb/918992

Advertisements

From → SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: