LOG SHIPPING
· Restoring data from backups takes more time i.e., application down time is more. So to reduce this down time Microsoft had developed new features. They are a) Log Shipping, b) Database Mirroring and c) Clustering.
· Based on the database criticality we go for these features. In Log Shipping, down time is minimal.
· Log Shipping is a high availability feature where data is sent from one server to another server through T-log file backups. This feature is available from SQL Server 2000 onwards.
· The server which is configured for log shipping is called Primary Server and the database is called Primary Database. The server which receives data is called Secondary Server or Stand-by Server, and the database is called Secondary Database or Stand-by Database.
· The secondary database will be either in restoration mode or stand-by mode.
· This technique is useful for database availability by maintaining the second warm database that fill in certain intervals.
· Whenever the Primary Server gets unavailable, then Secondary server can be used as Primary server to get the database online.
· There will be some downtime to change the roles of the server at fail-over.
· In Log shipping 3 jobs to be done. They are a) Back-up job b) Copy job and c) Restore job
· Back-up job is performed at Primary Server. Copy and Restore jobs are performed at Secondary Server.
How Log Shipping works?
· Take Full backup and t-log backup of the primary database is copied to the stand-by server.
· Restore the backup into Stand-by database as either in read-only or no-recovery mode.
· When Stand-by database is in read-only mode then we can even only access the data but cannot modify it. Whereas in no-recovery mode we cannot even access the data.
· Log shipping is done only when the Primary database is either in Full or Bulk-logged recovery mode.
· When performing log shipping to a server in stand-by mode a TUF (Transaction Undo File) file is created. This file contains information on all the modifications performed at the time backup is taken. This file is very important in stand-by mode because when restoring the log, uncommitted transactions will be stored in this TUF file and the committed transaction log will be restored in stand-by database making the database available to read-only. When the next restoration is done this uncommitted transaction in TUF file checks with the T-log backup whether the transactions are committed or not. If they are committed they will be written to disk, else they are stored in TUF file until it gets committed or rolled back.
· Backup job runs on Primary database and the default scheduling time is 15 minutes.
· Copy job copies the backup files to the Stand-by server, this job runs on Stand-by server and the default scheduling time is 15 minutes.
· Restore job restores the copied files into the Stand-by database, this job runs on Stand-by server and the default scheduling time is 15 minutes.
Pre-requisites:
· The Primary database must be either in Full or bulk-logged recovery model.
· There is no naming convention for the log shipping database.
· Log Shipping is a Manual fail-over. Automatic fail-over is not there in Log shipping.
· Log shipping information stored in MSDB.
· Here we may configure one more server called Monitor server, which monitors the primary server and secondary server.
· Minimum down time is required.
CONFIGURING LOG SHIPPING
Let’s our database is “test” in “WINDC” server.
Right click on the database for which we are going to configure log shipping and select tasksà Ship Transaction Logs
Select “Enable this as a primary database in a log shipping configuration” option.
Click on “Backup Setting” for setting for backup process. Here we have to provide the network path and the local path of the backup folder and click on “Schedule” for scheduling the backup time interval.
By default, the schedule is for every 15 minutes. For our example we are reducing it to 5 minutes.
Click ok.
Click “Add” to configure the secondary server.
Click “connect” to connect to the secondary server and to the secondary database.
Provide the name for the secondary database.
Under “copy files” tab we provide the information where the files are copied, usually this folder will be on secondary server location. So here we provide the network path and click on “Schedule” for scheduling the copy job.
By default, it will be for 15 minutes, for our convenience it is reduced to 5 minutes.
Under “Restore Transaction Log” tab we provide the state of the secondary server, when the alert to be raised if restore does not occur, and schedule of the restore.
By default the schedule for restore is for 15 minutes, for our convenience it is reduced for 5 minutes.
Click ok.
Check on for Monitor server instance for configuring a monitor server and click on settings.
Click on Connect and connect to the monitor server and click ok.
Click ok.
Save Log Shipping Configuration window appears and here if any error occur we have to fix them. Click on close.
In “Primary server” “Job Activity Monitor” we can observe the job success (backup taken success)
ERRORS
Scenario: accidentally, if the user had deleted the jobs and databases in both Primary server and Secondary Server. The user have created another database and configured log shipping then the alert job will be failed and it still points to the previous log shipping database even though it is not present. (It is not recommended process to delete the jobs and databases without breaking log shipping)
Using primary_id, we have to delete the table in primary server.
First we deleted the data from “log_shipping_monitor_primary” using “primary_id”.
Now we have deleted the data from “log_shipping_monitor_secondary” using “secondary_id”
In secondary server, when we check “log_shipping_monitor_secondary” still alert is working.
We have deleted the old data from the table “log_shipping_monitor_secondary” using “secondary_id”
Now we can see the Transaction log shipping status working fine.
If Log Shipping is out of synch or LSN of the log files are disturbed?
If log shipping is out of synch then we have to take
No comments:
Post a Comment