Step By Step Logshipping configuration

Log shipping provides a means to maintain a secondary server on an automated basis
using a chain of transaction log backups. This chapter explains the basic confi guration of
log shipping along with considerations for configuring the failover and failback within a log
shipping environment.

In this article, I will show you a step by step procedure to configure Log-shipping.

Primary Database
The primary database is accessible to applications and accepts transactions. Transaction
log backups are taken on a periodic basis and copied to the server hosting the secondary
database.

Secondary Database
The secondary database, also referred to as the standby, is normally inaccessible, and
transaction log backups from the primary database are restored on a continuous basis.
The secondary database can be in two different modes: Standby Mode or No Recovery
Mode. When the secondary database is in Standby Mode, users can connect to and issue
SELECT statements against the database. When the secondary database is in No Recovery
Mode, users cannot connect to the database. In either mode, you can restore transaction
logs to the secondary database. You cannot restore transaction logs when users are
connected to the database, so you should not use Standby Mode for high-availability
architectures.

Monitor Server
The monitor server, which is optional within a log shipping architecture, contains a set of jobs
that send alerts when the log shipping session is perceived to be out of sync.

How to enable Log-shipping between two SQL instances in DC and DR site.

Step 1:

Take full backup and log backup of LSDB database in primary instance (SQL1) and restore backups to secondary instance (SQL2) in standby mode.

Open Windows Explorer on the primary and create a share named LS_Primary
Grant Full Control permissions on this share to the SQL Server service account on
the primary as well as Read permissions to the SQL Server Agent service account on
the secondary





Step 2:

Open Windows Explorer on the secondary and create a share named LS_Secondary.
Grant Full Control permissions on this share to the SQL Server service account and the
SQL Server Agent service account on the secondary.

Step 3;

Verify that the database is configured for either the Full or Bulk-logged recovery model. If it is not, change the recovery model to Full. Start SSMS, connect to Primary instance (SQL1) within Object Explorer, right-click the database (LSDB) and choose Properties. Below Select A Page, select Transaction Log Shipping.

Step 4:

Select the Enable This As A Primary Database In A Log Shipping Configuration check
box and click Backup Settings. In the Network Path To Backup Folder text box, enter the Universal Naming Convention (UNC) path to the share you created in step 1. The Backup Folder Is Located On The Primary Server text box, enter the physical path to the directory in which your backups will be stored. Click OK to close the Transaction Log Backup Settings dialog box.

Select schedules

Delete files older than                                    72 hours

Alert if no backups occurs with in                  45 minutes



Step 5:

Click Add to add a new secondary. Click Connect and connect to SQL2; leave the name of the secondary database set to LSDB. You can select No, the secondary database is initialized as we have already restored full backup of database in standby mode in secondary instance.
selected.

Step 6:

Click the Copy Files tab. Set the destination folder that the transaction log backups will be copied to and change the copy interval as per your requirement

.

Step 7:

Click the Restore Transaction Log tab. Verify that the Standby mode is selected, set delay alert
as per your requirement. Click OK to close the Secondary Database Settings dialog box.
Click OK to generate the log shipping configuration.







Run back up job on primary SQL instance and copy, restore jobs in secondary instance. Create the log shipping jobs and alerts, and start log shipping. Verify that backups are going to the correct folder, are copied from LS_Primary to LS_Secondary correctly, and are restored to SQL2.

Step 8:

Right click Primary and Secondary instance, Select reports and click transaction log-shipping status.

This will show you Log-shipping sync status for database between two instances.