Setup SQL Server Transaction Logshipping

Logshiping :

Log shipping provides a way of keeping in step databases that reside on different servers, or on different instances on the same server.

·         You must have at least two SQL Server database engine servers or two database engine  instances in your log shipping implementation.

·         Note that you cannot ship transaction logs from a SQL Server 2005 database to a SQL Server 2000 database, because of the  differences in their structures (but you can ship from a SQL Server 2000 database to a  SQL Server 2005 database if you use No Recovery Mode .

·         It achieves this by automating the process of backing up data from a primary database on one server or instance and restoring the data to one or more secondary databases on another server or instance.

·         You can then use the secondary server as a ‘standby’ for when your primary server is unavailable.

·         Transaction log backups are performed on the database on the primary server (for example the production database).

·         The backup files are copied to a shared folder that can be accessed by both servers.

·         The backup files are restored to the secondary database on the standby server.

How to Configure Logshipping :

Step 1: Take the Full Backup of PRADIP DB (Primary DB)

Step 2: Create the DB with the name PRADIP_LS in the secondary server and restore the DB with the primary backup as below

restore database PRADIP_LS from disk = 'D:\Install_Software\PRADIP_LS\PRADIP.bak'

with move 'PRADIP' to 'D:\Install_Software\Instance\MSSQL11.DILIP\MSSQL\DATA\PRADIP_LS.mdf',

move 'PRADIP_log'  to 'D:\Install_Software\Instance\MSSQL11.DILIP\MSSQL\DATA\PRADIP_LS_log.ldf'

,replace

Step 3:

Configure the logshipping between Primary and secondary server by following the below steps:

Right Click on the PRADIP DBà Click on Properties à Go to Transaction Log shipping

Select the “Enable this as a primary ” Check box and Click on “Backup Settings”

Provide the backup location as above and Click on OK. Then Secondary Databases Tab will be enabled.
 

Click on Add button .

Make sure that you have restored you destination server (Secondary Server) with the Source Server (Primary Server) database backup.

 

Logshipping can be configured in two ways :

·         No recovery mode (will not available for users to query) .

·         Standby mode , available for read only purpose or else reporting purpose.

Logshipping Jobs :

Change the job ownership on the following jobs to SA

·         Transaction Log Backup Job.

·         Transaction Log Copy Job.

·         Transaction Log Restore Job.

·         Backup Alert Job.

·         Restore Alert Job.

Location of the jobs :

·         Tran log backup job lives on the Primary Server

·         Tran log copy job lives on the Secondary

·         Tran log restore job lives on the Secondary

·         The Backup Alert job lives on the Monitor

·         The Restore Alert job lives on the Monitor

·         The Log Shipping Monitor lives on the Monitor

Verify each job runs without failure before sending notification to the customer.

Please let me know if you have any other with logshipping and its issues.

Regards,
Dilip