Log Shipping Article-II

Log Shipping Article-II

List of Tables & Stored Procedures

Primary server tables

Table	Description
log_shipping_monitor_alert	Stores alert job ID. This table is only used on the primary server if a remote monitor server has not been configured.
log_shipping_monitor_error_detail	Stores error detail for log shipping jobs associated with this primary server.
log_shipping_monitor_history_detail	Stores history detail for log shipping jobs associated with this primary server.
log_shipping_monitor_primary	Stores one monitor record for this primary database.
log_shipping_primary_databases	Contains configuration information for primary databases on a given server. Stores one row per primary database.
log_shipping_primary_secondaries	Maps primary databases to secondary databases.

Primary Server Stored Procedures

Stored Procedure	Description
sp_add_log_shipping_primary_database	Sets up the primary database for a log shipping configuration, including the backup job, local monitor record, and remote monitor record.
sp_add_log_shipping_primary_secondary	Adds a secondary database name to an existing primary database.
sp_change_log_shipping_primary_database	Changes primary database settings including local and remote monitor record.
sp_cleanup_log_shipping_history	Cleans up history locally and on the monitor based on retention period.
sp_delete_log_shipping_primary_database	Removes log shipping of primary database including backup job as well as local and remote history.
sp_delete_log_shipping_primary_secondary	Removes a secondary database name from a primary database.
sp_help_log_shipping_primary_database	Retrieves primary database settings and displays the values from the log_shipping_primary_databases and log_shipping_monitor_primary tables.
sp_help_log_shipping_primary_secondary	Retrieves secondary database names for a primary database.
sp_help_log_shipping_primary_secondary	Retrieves secondary database names for a primary database.
sp_refresh_log_shipping_monitor	Refreshes the monitor with the latest information for the specified log shipping agent.

Secondary Server Tables

Table	Description
log_shipping_monitor_alert	Stores alert job ID. This table is only used on the secondary server if a remote monitor server has not been configured.
log_shipping_monitor_error_detail	Stores error detail for log shipping jobs associated with this secondary server.
log_shipping_monitor_history_detail	Stores history detail for log shipping jobs associated with this secondary server.
log_shipping_monitor_secondary	Stores one monitor record per secondary database associated with this secondary server.
log_shipping_secondary	Contains configuration information for the secondary databases on a given server. Stores one row per secondary ID.
log_shipping_secondary_databases	Stores configuration information for a given secondary database. Stores one row per secondary database.

Note: Secondary databases on the same secondary server for a given primary database share the settings in the log_shipping_secondary table. If a shared setting is altered for one secondary database, the setting is altered for all of them.

Secondary Server Stored Procedures

Stored Procedures	Description
sp_add_log_shipping_secondary_database	Sets up a secondary database for log shipping.
sp_add_log_shipping_secondary_primary	Sets up the primary information, adds local and remote monitor links, and creates copy and restore jobs on the secondary server for the specified primary database.
sp_change_log_shipping_secondary_database	Changes secondary database settings including local and remote monitor records.
sp_change_log_shipping_secondary_primary	Changes secondary database settings such as source and destination directory, and file retention period.
sp_cleanup_log_shipping_history	Cleans up history locally and on the monitor based on retention period.
sp_delete_log_shipping_secondary_database	Removes a secondary database and the local history and remote history.
sp_delete_log_shipping_secondary_primary	Removes the information about the specified primary server from the secondary server.
sp_help_log_shipping_secondary_database	Retrieves secondary database settings from the log_shipping_secondary, log_shipping_secondary_databases, and log_shipping_monitor_secondary tables.
sp_help_log_shipping_secondary_primary	This stored procedure retrieves the settings for a given primary database on the secondary server.
sp_refresh_log_shipping_monitor	Refreshes the monitor with the latest information for the specified log shipping agent.

Monitor Server Tables

Table	Description
log_shipping_monitor_alert	Stores alert job ID.
log_shipping_monitor_error_detail	Stores error detail for log shipping jobs.
log_shipping_monitor_history_detail	Stores history detail for log shipping jobs.
log_shipping_monitor_primary	Stores one monitor record per primary database associated with this monitor server.
log_shipping_monitor_secondary	Stores one monitor record per secondary database associated with this monitor server.

Monitor Server Stored Procedures

Stored Procedures	Description
sp_add_log_shipping_alert_job	Creates a log shipping alert job if one has not already been created.
sp_delete_log_shipping_alert_job	Removes a log shipping alert job if there are no associated primary databases.
sp_help_log_shipping_alert_job	Returns the job ID of the alert job.
sp_help_log_shipping_monitor_primary	Returns monitor records for the specified primary database from the log_shipping_monitor_primary table.
sp_help_log_shipping_monitor_secondary	Returns monitor records for the specified secondary database from the log_shipping_monitor_secondary table.

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.