Log Shipping

Log Shipping In sql server Article-1

            Microsoft offers a new feature in sql server 2000, i.e. known as Log shipping to make Database maximum available for the client.

How It Will Works:

Log shipping will back up the transaction log file of the primary server and restore in one or more secondary servers.

1.       Full backup of the database is taken on the primary server and copied to the standby server.

2.       Standby server maintains a copy of the database.

3.       The database is not operational; it can stay in either read-only mode or no-recovery mode.

4.       Transaction log for the "log-shipped" database is backed up on the primary server periodically. Note:  The  databases that are in FULL or Bulk-log recovery mode can be log-shipped.

Transaction log backups are placed on a shared drive; standby server's SQL Server Agent account must have access to this shared drive.

5.       Transaction log backups are copied to the standby server.

6.       Transaction log backups are applied to the database on the standby server in the order that they were taken on the primary server.

Prerequisites:

• The primary database must be in the Full or Bulk-logged recovery model

• Before we configure log shipping, create a share Folder  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.

              

Security:

Permissions:

The log-shipping stored procedures require membership in the sysadmin fixed server role.

Server Editions:

 Minimum 2 servers are required.

             ·         Database must be in FULL or BULK LOGGED recovery model.

             ·         Any of the editions

                * Enterprise Edition

                * Standard Edition

                * Workgroup Edition

·         Both the servers should have same collation settings and same service Account.

·         If you need Monitor server, it may be any Edition. i.e. need not to be same as primary and secondary.

About Servers and Databases:

Primary server:

            

              Primary server is your Production server and the Primary Database is nothing but, the Database on the Production server for which you want to configure Log-sipping .

Secondary server:

            

              Secondary server is your Server where you want to keep a warm standby copy of your primary Database and Secondary Database contain backup copies of databases from several different primary servers.

Monitor server:

              This is an optional monitor server tracks all of the details of log shipping, including:

·         When the transaction log on the primary database was last backed up.

·         When the secondary servers last copied and restored the backup files.

·         Information about any backup failure alerts.

The monitor server should be on a server separate from the primary or secondary servers to avoid losing critical information and disrupting monitoring if the primary or secondary server is lost.

 A single monitor server can monitor multiple log shipping configurations. In such a case, all of the log shipping configurations that use that monitor server would share a single alert job.

Note: Once the monitor server has been configured it cannot be changed without removing log shipping first.

Configuring Log Shipping using SSMS:

1.       Make sure your database is in full or bulk-logged recovery model. You can change the database recovery model using the below query. You can check the database recovery model by querying sys.databases.

 SELECT name, recovery_model_desc FROM sys.databases

         WHERE name = 'Database_Name'

 USE [master]

GO

 ALTER DATABASE Database_Name SET RECOVERY FULL WITH NO_WAIT

 GO

2.       Create 3-Folders.

i)                    One is on the Primary server and It shoud have READ-WRITE permissions on the Folder.

ii)                   Second Folder on the Secondy server and It shoud have READ-WRITE permissions on the Folder.

iii)                 Third Folder on the Secondy server and It shoud have READ permissions on the Folder.

3.    On the primary server, right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the "Enable this as primary database in a log shipping configuration" check box.

  4.    Click on the Backup settings to configure the Transaction log backup.

If your backup folder location is on the network share or Local machine specify the appropriate path as shown below:

The backup compression feature was introduced in SQL Server 2008 Enterprise edition. While configuring log shipping, we can control the backup compression behavior of log backups by specifying the compression option. When this step is completed it will create the backup job on the Primary Server.

5.       In this step we will configure the secondary instance and database. Click on the Add... button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping. After adding secondary server and secondary sever Database click on Connect…  button to connect the secondary server Database.

a)      Initialize secondary Database.

i)                    Yes, generate a full backup of primary Database and Restore on Secondary Database.

ii)                   Yes, Restore an existing backup of Primary Database into Secondary server Database.

iii)                 No, Secondary Database is initialized. i.e. if u manually Restore the backup of primary Database into Secondary Database.

Select the any one of the above (i),(ii),(iii) According to your requirement.

b)      Copy Files tab, Provide Destination Folder path (Provide Second Folder path) where Transaction log Backups should be copied.

Note: the copy schedule listed in the Schedule box under Copy job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.

c)        Restore Transaction Log tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

i)                     Standby (Read-Only) Mode:                                                                                                           If u wants to Access your Secondary server Database for Reporting purpose etc. Select Standby mode.

ii)                   No recovery mode:                                                                                                                           If u Don’t wants to Access your Secondary server Database for Reporting purpose etc. Select No recovery mode. It is also called as non operational mode.

6.       If you chose the Standby mode option, choose if you want to disconnect users from the secondary database before the restore operation is started.

7.       If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.

8.       Choose an alert threshold under Alert if no restore occurs within.

Note: the restore schedule listed in the Schedule box under Restore job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.

9.       Click OK to Complete the configuring log Shipping.

 If you are Using Monitor server(Optional)  for Monitoring the log shipping, Check on Use Monitor server.

11.        Click on Settings... button which will take you to the "Log Shipping Monitor Settings" screen. Click on Connect ...button to setup a monitor server. Monitoring can be done from the source server, target server or a separate SQL Server instance. We can configure alerts on source / destination server if respective jobs fail. Lastly we can also configure how long job history records are retained in the MSDB database. Please note that you cannot add a monitor instance once log shipping is configured.

1.       Click OK to finish Configuring Loshipping.

POINTS TO REMEMBER:

• As Log Shipping does not support automatic failover, plan for some down time and a manual failover
• Once you failover, check for Orphan Users and fix as needed

Using Transact-SQL

To configure log shipping:

 

1.       Initialize the secondary database by restoring a full backup of the primary database on the secondary server.

2.       On the primary server, execute sp_add_log_shipping_primary_database to add a primary database. The stored procedure returns the backup job ID and primary ID.

3.       On the primary server, execute sp_add_jobschedule to add a schedule for the backup job.

4.       On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job.

5.       On the primary server, enable the backup job.

6.       On the secondary server, execute sp_add_log_shipping_secondary_primary supplying the details of the primary server and database. This stored procedure returns the secondary ID and the copy and restore job IDs.

7.       On the secondary server, execute sp_add_jobschedule to set the schedule for the copy and restore jobs.

8.       On the secondary server, execute sp_add_log_shipping_secondary_database to add a secondary database.

9.       On the primary server, execute sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server.

10.   On the secondary server, enable the copy and restore jobs. For more information, see Disable or Enable a Job.

To View the Transaction Log Shipping Status report on a server instance

1. Connect to a monitor server, primary server, or secondary server.
2. Right-click the server instance in Object Explorer, point to Reports, and point to Standard Reports.
3. Click Transaction Log Shipping Status.