Categories
High Availability

Ten Steps to configure Log Shipping

I have used 3 instances to configure log shipping and they are as follows,

Deepak—>Primary

DeepakSansu—>Secondary

DeepakTest—>Monitor

The database for which I am configuring Log shipping is named as “sansu” and it is present in primary server.The following are the steps I followed,

Step 1 :Take a full backup of the DB to be log shipped in primary server(i.e sansu) and restore it in secondary server WITH STANDBY option

Step 2 :Right Click the DB to be log shipped in primary server,select Task and Ship Transaction logs option and ensure that you enable the option “enable this as a primary database in log shipping

configure_log_shipping_1

Step 3 :Click Backup settings and give the primary server name\tran log older(shared folder) and also mention the local hard drive in the primary server where the shared folder resides in the subsequent box

configure_log_shipping_2

Step 4 :There are options “Delete files older than” it helps to remove the old transaction log backup files from the shared folder in the primary and “Alert if no backup occurs within” it sends an alert if a tran log backup has not happened for a stipulated time mentioned there.

Step 5 :Click the schedule option for the tran log backup and schedule as per your desire

configure_log_shipping_3

Step 6 :As mentioned in Step 2,below the enable option select “Add” option to add the secondary server and connect to it via the connect option.

Step 7 :In the “Initialize secondary database” option choose the 3rd option “No, secondary database is initialized” because we have manually taken a full backup and restored as in Step1.If we havent done Step1,we can choose options 1 which will take a fresh full backup and restore it in secondary server or option 2 which will restore from an existing backup rather than a fresh backup.In that case you need to point the location where the backup file resides and also the name of the data and log files in the restore options.

configure_log_shipping_4

Step 8 :Choose the Copy files tab and specify the path where the tran log is copied and put in the secondary and schedule the job and fill the delete old files option.

configure_log_shipping_5

configure_log_shipping_6

Step 9 :In the Restore transaction log tab choose STANDBY mode and “disconnect users in db when restoring backup, because if the users are accessing the db restoration will fail. In “Delay Restoring Backups” if it is 0 minute(s) as soon as copy job completes, restore job will start immediately without any delay. In “Alert if no restore occurs within” option if restore does not happen for a stipulated amount of time mentioned here an alert is issued. Also schedule the restore job as per your desire.

configure_log_shipping_7

configure_log_shipping_8

Step 10 :Choose the page as in Step2 and enable the “Use a monitor server instance” and connect to a new sql instance(DeepakTest)which will monitor the log shippings backup,copy and restore jobs and will help in troubleshooting.We can also configure the secondary server instance(Deepaksansu) as monitor.Both are correct it all depends on your requirements.Click Ok and the log shipping is configured with all 3 jobs and status will be shown as success.

configure_log_shipping_9

If you see the below screenshot which shows the secondary db sansu which is in read only mode, further 3 jobs will created 1 in the primary server which is the backup job and other 2 in secondary server which is the copy and restore job.In the monitor server an alert job will be created to generate alerts if the copy or restore job fails for a stipulated amount of time.You can view the jobs created by navigating to SQL server agent and then beneath it you have a folder called Jobs, which displays the jobs present in that server.

configure_log_shipping_10

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*