What is Disaster?
Disater is an event that cause data loss and any sql server / database disruption.The main causes of disaster are power failure,hardware failure,virus attack,human errors etc.
What is DR Drill?
DR Drill is an activity which involves actions which are taken to overcome data loss after disaster for example if primary server down due to some reason so we can make secondary instance to primary instance is known as disaster drill .
What is Always On feature of SQL Server 2012?
Always on feature is advanced option of high availability
LOG SHIPPING IS HIGH AVAILABILITY OPTION WHICH COULD BE CONFIGURED FOR UNPREDICTED MANNER
DEFINITION OF LOG SHIPPING:
It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.
There are four jobs created during the log shipping operation:
1.Backup job-Sql server agent perform backup operation.when log shipping enabled,log shipping backup created on primary server.
2.Copy job-sql server agent copies the backup file from primary server (instance) to configurable destination on secondary server (instance) as well as log history on secondary server.
3.Restore transaction log-Sql server agent restores the copied backup file to secondary database.
4.Alert-sql server agent raises alert for primary and secondary database when backup and restore operation does not complete successfully. If Monitor server is configured then alerts are created on moniter server.
ARCHITECTURE OF LOG SHIPPING
- The backup job runs on the primary server sql agent, in that job log backup is performed as it is scheduled at the time of configuration.
- The backup file is stored in the shared backup folder.
Note: create share folder on primary server which have read write permission.
- The primary database is online on the primary server.
- The copy job which is running on the secondary server’s agent copies the backup file from backup folder to the copy folder(we can set one folder for backup and copy job,it does not make copy only restores from it)
- The restores job on the secondary server restores log backups keeping secondary database in standby\read only state or restoring state.
- While monitor server keeps status of all of this activity with alert job.It sends mail for particular events eg.job failure,job complete.
- If the primary server is crashed, the secondary database on the secondary server should be restored with recovery and connect the front-end application to the port of the secondary server to bring database online.
PREREQUISITES FOR CONFIGURING LOG SHIPPING
1)Both SQL & OS Editions and Versions should be at same level on all participating servers ( This applies to SQL Server version 2005 onwards)
2)Express Edition doesn’t supports SQL server Logshipping
3) The user who configures the Logshipping must be member of sysadmin server role
4)SQL Server and SQL Server agent services must be configured under windows authentication account with sysadmin permissions on all participating server
5)Recommended to have unique logon account for both SQL Server and SQL Server agent services
6)The Logon account must have at least local administrator privileges to carry out the administrative tasks
7)SQL Server port (1433) must be opened at network level for communication between both the servers
8)Sharing ports must be enabled on both production & DR servers for log shipping
9)Create shared network share on both Primary or secondary servers
10)The backup and restore directories in your log shipping configuration must accomplish the following requirements.
For the backup job, read/write permissions to the backup directory are required on the following:
· The SQL Server service account on the primary server instance.
· The SQL Server Agent account on the primary server instance.
· For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the service account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
For the restore job, read/write permission to the copy directory are required by the following:
· The SQL Server service account on the secondary server instance.
· The SQL Server Agent account on the secondary server instance.
11)SQL service log on accounts must be same on both Environments.
12)Database being log shipped must be in Full recovery model or Bulk logged recovery model. The simple recovery model is not supported
13)Any maintenance plan or backup job against log shipped databases should not be planned since that would break Logshipping chain
14)Logshipping configuration can be done within trusted domain computers or work group computers
15)Recommended to have the same Drive Space capacity in fact more space of secondary server considering the data growth in future