Thursday, November 22, 2012

Database Experts:Log Shipping for SQL Server 2005 disaster recovery solution

    SQL Server 2005 Log Shipping For Disaster Recovery. 



Read this for more information about database log shipping for disaster recovery.

http://msdn.microsoft.com/en-us/library/ms187103%28v=sql.90%29.aspx

There are three SQL Server instance for Log shipping operation.

1.Primary Server-Source database which logs transfer to multiple secondary server.

2.Secondary Server.Destination server for log shipping where logs apply for recovery.

3.Monitor Server :Monitor Log shipping on this server.

**Note:There is one shared drive where transaction logs backup moved to secondary server.

Follow this steps for configuring log shipping.

1.Create Shared Drive.

This share drive can be accessible by both servers.

2.Create Shared folder to copy transaction logs on secondary server.

Give Full Permission to SQL Server Service Account.



Here this folder is Shared in Secondary Server.




3.Go to Primary Server for Log Shipping Configuration.

Right Click on Database which need to start log shipping.

Go to Task-->Ship Transaction Logs.







Click on Enable this as a primary... to activate log shipping...



Configure Backup Settings in primary server SQL Server Instance.

Set Shared Drive Path.



Schedule backup.






Configure Secondary Database.Click on Add and connect to Secondary Server SQL Server Instance.









Select Path of Secondary Server Data files.




Select Copy folder Path that shown in steps -2.



Schedule time for copy folder.



Choose Restore Transaction Logs Schedule.

Choose Standby Mode as it useful in Reporting and Read only mode to Query on Database.

It is Much more important to disconnect when recovery on Database.








Schedule time for Restore Job.






4.Configure Monitor Server Instance.



Click on Use a monitor server instance.

Connect to SQL Server Instance,.




Click to Script to Configuration to Script all this steps.

Click Ok to Start Log Shipping.




Check Success status.






Check Secondary Server Database in Standby Mode.







5.Jobs in Secondary Server for Copy and Restore Operation.



View History for Successful run of Job.

Check Job Activity Monitor.

Check Shared folder and Local folder for Transaction logs backup copy.

6.Check Standard report for Log Transaction status.