Log Shipping

LOG SHIPPING
  • It is simple method of keeping entire database in different server.
  • It works with backup, copy and restores jobs.
  • We need 3 servers
    • Primary server
    • Secondary server
    • Monitor server (optional)
  • In primary server database is in online state where the appls or users are connected.
  • In secondary server database is in standby mode where we can read data.
  • In secondary server T.Log backups are applied either with
    • No recovery or
    • standby
  • We can have multiple secondary servers.
Advantages
  • To reduce downtime for the applications or users
  • To implement load balancing
 i.e we can allow reading from secondary server and manipulations in primary server.


Note: Both committed and uncommitted transactions are transferred to the secondary      
          database.
          Supports FILESTREAM.


Warm/ Hot Standby Solution: It provides a warm standby solution that has multiple copies of a database and require a manual failover.

Points to Remember

We can ship all databases except master, model, read-only and the databases whose recovery model is SIMPLE.
We should have sysadmin privileges on both servers.
Changing recovery model from FULL or BULK LOGGED to SIMPLE will break log shipping process.
We cannot take ad-hoc Transaction Log backups from primary server as it breaks log shipping log backup chain.

Architecture





Log Shipping Jobs

Log shipping supports 4 jobs
          -Backup Job
          -Copy Job
          -Restore Job
          -Alert Job

1. Backup Job

         -  It is created in primary server for every log shipping configuration.
- It takes backup of T.Log file periodically and deletes old backups and old history information.
- We have to grant read write permissions on Backup folder to primary server service account and read permissions to secondary server account.
2. Copy Job

          - Created in secondary server for every log shipping configuration.
          - Copy the backup files from backup folder into copy folder.
          - It deletes old files and old history from copy folder.
- On backup folder we have to grant read permission to secondary server account and read write permissions on copy folder.
3. Restore Job

          - It is created in secondary server for every log shipping configuration.
          - It restores the files from Copy folder into secondary server in standby mode.

4. Alert Job

          - It is created in Monitor server.
          - If monitor server is not used it is created in primary and secondary servers.
          - Only one instance of Alert Job is created.

Requirements
  •  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.
Configuration Steps

1. Go to SSMS take 2 instances
2. Note down their service accounts
          BHANU-PC\TEST       
          BHANU-PC\SECOND 
3. Create the following folders
          - In Primary Server d:\LSBackup_Sales
          - Grant read write permissions on this folder to Primar server account
          - Grant read permission to secondary server account
          - In Secondary server d:\LSCopy_Sales
          - Grant read write permissions to secondary server account
4. Test sharing features as
           In Primary server
                   * Start --> Run --> \\systemname\LSBackup_Sales
           In Secondary Server
                   * Start --> Run --> \\systemname\LSCopy_Sales
5. In Primary server
          1. Create database with the name: Sales
                   CREATE DATABASE Sales
          2. Create sample table
                   CREATE TABLE Bills(BillNo int, BillName varchar(40),
                                         BillDate Date)
                   GO
                   Insert Bills VALUES(1,'Purchase',getdate())
          3. Taking Full backup
          BACKUP DATABASE Sales TO DISK='\\systemname\LSBackup_Sales\Sales.bak'
Note:
          We have to configure on existing database.
6. Go to Secondary Server
           Create folder with the name d:\SalesFiles and grant read write permissions to service account.
           R.C on databases --> Restore database
             To Database:        Sales
             Select From Device:
             Click on browse button --> Add--> select sales.bak file--OK
           Select checkbox under Restore option.
               * Go to options --> select recovery state : with standby
           Under Restore As mention the following paths
                   d:\SalesFiles\Sales.mdf
                   d:\SalesFiles\Sales_log.ldf
          -->OK
7. Configuring Log Shipping
          - Go to Primary Server
          - Right click on Sales db
          - Tasks --> Ship Transaction Log
          - Select checkbox "Enable this as primary database...."
          - Click on "Backup Settings" --> Enter backup folder path
                   \\systemName\LSBackup_Sales
          - Click on "Schedule" Change the time to 5minutes
                   Occurs Every: 5 minutes
          - OK
          - Under "Secondary Databases" click on Add button
          - Secondary server instance = Click on "Connect" button --> Select secondary server instance
          - Select the option "No, the secondary database is initialized
          - Click on Copy Files tab --> enter Destination folder as
                   \\systemName\LSCopy_Sales
          - Click on schedule Change the time to 5minutes
                   Occurs Every: 5 minutes
          - Click on Restore transaction log tab -->
          - Select "Standby Mode" option and checkbox "Disconnect ..."
          - Click on schedule Change the time to 5minutes
                   Occurs Every: 5 minutes
          - OK
          - OK
          - OK

Configuration in GUI MODE:

Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover.
Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.
Permissions
To setup a log-shipping you must have sysadmin rights on the server.

Minimum Requirements
  1. SQL Server 2005 or later
  2. Standard, Work-group or Enterprise editions must be installed on all server instances involved in log shipping.
  3. The servers involved in log shipping should have the same case sensitivity settings.
  4. The database must use the full recovery or bulk-logged recovery model
  5. The SQL Services in both the primary and secondary server should be the same with same password.Preferably a domain account.
  6. A shared folder for copying T-Log backup files 
    * Read/Write permission required —>For SQL service account of Primary for the tran log backup to be successful
    * Read/Write permission required —>For SQL Agent account of Secondary for the copy job to be successful
    * Read/Write permission required —>For SQL Service Account of Secondary for the restore job to be successful
  7. SQL Server Agent Service must be configured properly
In addition, you should use the same version of SQL Server on both ends. It is possible to Log Ship from SQL 2005 to SQL 2008, but you can not do it the opposite way. Also, since Log Shipping will be primarly used for failover if you have the same versions on each end and there is a need to failover you at least know you are running the same version of SQL Server.


Steps to Configure Log-Shipping:
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 = 'jugal'

USE [master]
GO
ALTER DATABASE [jugal] SET RECOVERY FULL WITH NO_WAIT
GO
2. 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.
Figure-1
3. The next step is to configure and schedule a transaction log backup. Click on Backup Settings… to do this.
Figure-2
If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. 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.
Figure-3
4. 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.
Figure-4
When you click the Add… button it will take you to the below screen where you have to configure the Secondary Server and database. Click on the Connect… button to connect to the secondary server. Once you connect to the secondary server you can access the three tabs as shown below.
Initialize Secondary Database tab
In this step you can specify how to create the data on the secondary server. You have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.
Figure-5

Copy Files Tab
In this tab you have to specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.
Figure-6
Restore Transaction Log Tab
Here you have to specify the database restoring state information and restore schedule. This will create the restore job on the secondary server.
Figure-7
5. In this step we will configure Log Shipping Monitoring which will notify us in case of any failure. Please note Log Shipping monitoring configuration is optional.
Figure-8
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.
Figure-9
6. Click on the OK button to finish the Log Shipping configuration and it will show you the below screen.
Figure-10
Observations
1. Go to primary server --> SQL Server agent --> Jobs -->View the backup job with the name 
                    LSBackup_Sales
2. If no monitor server is used, check 2 alerts are created in Alerts folder.
    Log shipping Primary Server Alert
    Log shipping Secondary Server Alert
3. In Secondary server verify that 2 jobs are created
    * Copy       (LSCopy_Sales)
    * Restore  (LSRestore_Sales)
4. All the above (B,C,R) jobs uses "sqllogship.exe" file.
5. Alert job calls the following SP
          sys.sp_check_log_shipping_monitor_alert
6. sqlmaint.exe is responsible for updating backup, copy and restore information in the monitor    
   server/p/s servers.
7. Linked Servers are created in primary and secondary server related to monitor as
    LOGSHIPLINK_<MonitorServerName>

8. The following SPs are used to configure Log Shipping
    1.master.dbo.sp_add_log_shipping_secondary_primary
    2.master.dbo.sp_add_log_shipping_secondary_database

Configuring Alerts
- Once we configure Log shipping 2 alerts are created automatically.
- We have to configure reponse for the alerts.

Steps
          - go to primary server --> SQL Server Agent --> Alerts
          --> R.C on Log shipping primary server alert --> Properties --> response -->Notify Operators --> select existing operator and select Email
          - Go to secondary server configure "Log shipping secondary alert.."


1. Using MSDB tables and Views
          Go to primary server --> MSDB
            1. log_shipping_primary_databases
                   - Consists of information related to
                             - Log Bakcup folder path
                             - Last Log backup file name
          2. log_shipping_primary_secondaries
                   - Consists of details of secondary server name and  database name
          Go to Secondary Server --> MSDB
          1. log_shipping_secondary
                   * Details of copy folder and last copied file
          2. log_shipping_secondary_databases
                   * Details of last restore file

2. Using Log Shipping Status Report

- Go to Primary Server --> R.C on Server name --> Reports --> Standard Reports -->    Transaction Log Shipping Status

3. Using Agent Job History

- We can monitor the jobs history and if any issues are there we can resolve it.
You can execute the below Log Shipping System Stored Procedure to monitor log shipping and get detailed information about log shipping.
  • sp_help_log_shipping_monitor
    • This is the how SQL Server generates the Log Shipping Status report by executing sys.sp_help_log_shipping_monitor procedure. This procedure returns the log shipping status (whether it is healthy or not) as well as metadata such as primary and secondary database names, time since last backup, last backup file, last restore file, etc...
  • sp_help_log_shipping_monitor_primary
    • returns all columns from the log_shipping_monitor_primary table for the specified primary log shipping database. It returns server name, database name, time of last backup, backup threshold, threshold alert and history retention period.
  • sp_help_log_shipping_monitor_secondary
    • returns all columns from log_shipping_monitor_secondary table for the specified secondary log shipping database. It will return database name, server name, restore threshold, last copied file, time of last copy / restore and history retention period.
Points to Remember
- As part of Log shipping only database users are transferred to secondary server   (db).
- Logins are not transferred as part of log shipping. We have to transfer manually or by creating job.
- Log backup schedule = 15min
      Copy   ,,       = 15Min
      Restore ,,     = 15Min

  If the changes are made in primary server @8am. The max time to reach at
  Secondary server 8:45am (15+15+15)

- If the table is truncated at primary server then at the secondary server database
  also it is truncated, because the truncate command is minimal logged operation.

- Log shipping is stopped if we change the db recovery model from FULL or BULK_LOGGED to SIMPLE.

To fail over to a secondary log shipping server, follow this 10-step process:
Disable all backup jobs that might back up the database on both log shipping partners.
Disable the log shipping jobs.
Run each log shipping job in order (i.e., backup, copy, and restore).
Drop log shipping.
Manually back up the log of the primary database using the NORECOVERY option. Use the command
        BACKUP LOG [DatabaseName]
        TO DISK = 'BackupFilePathname'
        WITH NORECOVERY;
        where DatabaseName is the name of the database whose log you want to back up and    
        BackupFilePathname is the backup file’s pathname (e.g., Z:\SQLServerBackups\TLog.bck).
Restore the log backup on the secondary database using the RECOVERY option, and bring the secondary database online. The primary and secondary databases have now switched positions.
Back up the log of the new primary database (optional).
Restore the log on the new secondary database using the NORECOVERY option (optional).
Reconfigure log shipping.
Re-enable any backup jobs that were disabled.

Changing Roles Between Primary and Secondary Servers
SQL Server 2008 R2 Other Versions 2 out of 2 rated this helpful - Rate this topic
After you have failed over a SQL Server log shipping configuration to a secondary server, you can configure your secondary database to act as a primary database. Then, you will be able to swap primary and secondary databases as needed.

Performing the Initial Role Change
The first time you want to fail over to the secondary database and make it your new primary database, there is a series of steps you must take. After you have followed these initial steps, you will be able to swap roles between the primary database and the secondary database easily.

Manually fail over from the primary database to a secondary database. Be sure to back up the active transaction log on your primary server with NORECOVERY. 

Disable the log shipping backup job on the original primary server, and the copy and restore jobs on the original secondary server.

On your secondary database (the database you want to be the new primary), configure log shipping using SQL Server Management Studio.
Enable Log Shipping (SQL Server Management Studio). Include the following steps:
Use the same share for creating backups that you created for the original primary server.
When adding the secondary database, in the Secondary Database Settings dialog box, enter the name of the original primary database in the Secondary database box.

In the Secondary Database Settings dialog box, select No, the secondary database is initialized.
If log shipping monitoring was enabled on your former log shipping configuration, reconfigure log shipping monitoring to monitor the new log shipping configuration. Execute the following commands, replacing database_name with the name of your database:
On the new primary server
Execute the following Transact-SQL statements:
-- Statement to execute on the new primary server
USE msdb
GO
EXEC master.dbo.sp_change_log_shipping_secondary_database @secondary_database = N'database_name', @threshold_alert_enabled = 0;
GO

On the new secondary server
Execute the following Transact-SQL statements:
-- Statement to execute on the new secondary server
USE msdb
GO
EXEC master.dbo.sp_change_log_shipping_primary_database   @database=N'database_name', @threshold_alert_enabled = 0;
GO
Swapping Roles
After you have completed the steps above for the initial role change, you can change roles between the primary database and the secondary database by following the steps in this section. To perform a role change, follow these general steps:
Bring the secondary database online, backing up the transaction log on the primary server with NORECOVERY.
Disable the log shipping backup job on the original primary server, and the copy and restore jobs on the original secondary server.
Enable the log shipping backup job on the secondary server (the new primary server), and the copy and restore jobs on the primary server (the new secondary server).
Important
When you change a secondary database to the primary database, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins and jobs, on the new primary server instance. For more information, see Managing Metadata When Making a Database Available on Another Server Instance.

FAQ:- If we take FULL backup at primary server then is log shipping continues?
Ans:

Full backup cannot break log backups LSN number. Hence Log shipping process continues.


1. Out of sync

          - It is raised when the T.Log backups are not applied at secondary server with the error number 14421
          - If there is no disk space in the secondary server for restoring backups.
          - If there is no respective path to create the files in secondary server.
          - If there is ad-hoc T.Log backup was taken in the primary server.
                             9:00    T.Log  1000
                             9:10    T.Log 1050 (Ad-hoc)
                             9:15    T.Log   1100
Solution
          - Restore first ad-hoc log backup which was taken at 9:10 manually in secondary server                with no-recovery.
          - Restore next log backup which was generated by Log shipping process i.e at 9:15
          - If the Agent service or msdb is not working in secondary server.
          - If any T-Log backups are missing/corrupted before restoring.
          - Disable Log shipping jobs.
          - Take differential/Full backup and apply in secondary server.
          - Enable jobs

2. Copy and Restore jobs are not running

          - Check secondary server date and time. i.e secondary server date and time is less than primary server.
          - Change the date of start for the above jobs equal to secondary server date.

FAQ:- If the secondary server is down or Agent service in secondary server is not running or msdb is not online copy and restore jobs fail. Once the Agent service is started how the backup files are copied.
Ans:
          - All the backup files are copied from last copied file at once by the copy job.

FAQ:- In secondary server restore job was failing and there is error message "it is too early to apply the logs". What may be the possible reasons?

Ans:
          - Try to restore if there is any previous backup file.

Performing fail over

- Process of making secondary database available to the appls or  users is called fail over when primary server/db failed.
- Log Shipping Fail over process is not automatic.

Steps

1. Perform one last T.Log backup in primary server if possible
2. disable Log shipping jobs
3. Restore if any backups are not applied at secondary server with NORECOVERY.
4. Restore tail log backup with RECOVERY.
5. Transfer the Logins related to that db.
6. Make the secondary server available to the users.
7. Configure Log shipping.

TUF file (Transaction Undo File)
Consists all modifications.
Note: While Creating a Secondary file as a read-only system will create TUF file(user can read this database.

Note:
You cannot set the log shipping job to do copy only log backups


Failover Reasons:

1. Recent modifications (permissions removed) on the shared folders(Might be on Primary shared folder or on at the secondary side).
2. Human error ->either someone used the option of truncate only or switched the recovery model.
3. date/time for the windows servers unmatching due to any DST activities.
4. As usual ->Datafile added on Primary on different drives ->then you need to apply that on secondary with move until that your log shipping restore job will fail
5. Any I/O, Memory, N/w bottleneck–Please look at the error logs & event logs
6. Corruption.
7. Your tuf file is missing.
8. sqllogship.exe –hahahaha some one deleted..
9. Drives full
10. Improper SP/hot fix applied.
11. You may have set the incorrect value for the Out of Sync Alert threshold.
12. Might be you have scheduled the jobs in same time
13. Your MSDB database is full.

Query to check log shipping job errors using the MSDB log shipping system tables
--List of Log Shipping jobs
SELECT * from dbo.sysjobs WHERE category_id = 6
SELECT * FROM [msdb].[dbo].[sysjobhistory]
where [message] like '%Operating system error%'
order by [run_date] , [run_time]
 
SELECT * FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
where [message] like '%Operating system error%'
 
SELECT * FROM [msdb].[dbo].[restorehistory] 
 

Monitor Log Shipping

Log Shipping is a basic SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover or reporting purposes.
You can use the below items to investigate if there are any issues with your databases that are setup for Log Shipping.  We will cover each of these items and how they can be used.
  • SQL Server Error Log
  • SSMS Built In Report
  • System Stored Procedures
  • Query the MSDB database
  • Application/System EventViewer Log

SQL Server Error Log

Check the SQL Server error log for error messages related to log shipping such as database backup and restore failures using these commands.
-Query to check the Log Shipping related error messages
select * from sys.sysmessages where description like '%shipping%' and msglangid = 1033
--Execute it on Primary/Secondary server
EXEC xp_readerrorlog 0,1,"Error",Null
--Execute it on Primary/Secondary server
EXEC xp_readerrorlog 0,1,"Shipping",Null
--Execute it on Primary server
EXEC xp_readerrorlog 0,1,"Backup",Null
--Execute it on secondary server
EXEC xp_readerrorlog 0,1,"Restore",Null

Reversing Log Shipping

Reversing log shipping is an often overlooked practice. When DBAs need to fail over to a secondary log shipping server, they tend to worry about getting log shipping back up later. This is especially true in the case of very large databases. If you're using log shipping as your primary disaster recovery solution and you need to fail over to the secondary log shipping server, you should get log shipping running as quickly as possible. With no disaster recovery failover in place, you might be running exposed.
Reversing log shipping is simple. It doesn’t require reinitializing the database with a full backup if performed carefully. However, it’s crucial that you remember the following:
  • You need to preserve the log sequence number (LSN) chain.
  • You need to perform the final log backup using the NORECOVERY option. Backing up the log with this option puts the database in a state that allows log backups to be restored and ensures that the database’s LSN chain doesn’t deviate.
  • The primary log shipping server must still be accessible to use this technique.
·         To fail over to a secondary log shipping server, follow this 10-step process:
1.    Disable all backup jobs that might back up the database on both log shipping partners.
2.    Disable the log shipping jobs.
3.    Run each log shipping job in order (i.e., backup, copy, and restore).
4.    Drop log shipping.
5.    Manually back up the log of the primary database using the NORECOVERY option. Use the command
BACKUP LOG [DatabaseName]
  TO DISK = 'BackupFilePathname'
  WITH NORECOVERY;
where DatabaseName is the name of the database whose log you want to back up and BackupFilePathname is the backup file’s pathname (e.g., Z:\SQLServerBackups\TLog.bck).
6.    Restore the log backup on the secondary database using the RECOVERY option, and bring the secondary database online. The primary and secondary databases have now switched positions.
7.    Back up the log of the new primary database (optional).
8.    Restore the log on the new secondary database using the NORECOVERY option (optional).
9.    Reconfigure log shipping.
10. Re-enable any backup jobs that were disabled.
Note that step 7 and step 8 are listed as optional because they’re not required for establishing log shipping. However, I recommend performing these steps to ensure that the log shipping configuration will proceed without any problems.
With a few minor adjustments, this 10-step process works with multiple secondary log shipping databases. You perform the same basic steps, keeping in mind that the other secondary databases will still be secondary databases after the failover. After you back up the log on the new primary database, you should use the NORECOVERY option to restore that backup on all the planned secondary databases. You can then add them as secondary databases to the new primary database.



No comments:

Post a Comment

Popular Posts