Logshipping in SQL Server
Logshipping in SQL Server
Logshipping:
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. The key thing in the logshipping is the Backup/Restore process is totally automated.
Minimum Requirement to Setup Log shipping
Log Shipping Requirements
SQL Server Editions: SQL Server 2005 Standard, SQL Server 2005 Workgroup, SQL Server 2005 Enterprise Edition
Case-Sensitivity
The servers involved in log shipping should have the same case-sensitivity settings.
Recovery Model
Full recovery model or bulk-logged recovery model
Permissions
you must be a sysadmin on each server instance to enable log shipping.
Backup/Restore Directories Requirements(Read/Write rights).
1) The SQL Server service account on the primary server instance.
2) The proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.
3) For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account
4) Agent account on the secondary server instance must have the below rights
-> The SQL Server service account on the secondary server instance.
-> The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.
Jobs Included in Log shipping
1 Backup Job (Primary Server)
2 Copy Job (Secondary Server)
3 Restore Job (Secondary Server)
4 Alert Job (Optional)
Log shipping out of sync reasons
1. Directory Permission/Rights issue
2. Network Issue
3. Transaction Log files become very large
4. If transaction log backup taken manually on the production server with truncate option