Database: Log Shipping
Log shipping is a technique used in database management to automatically send transaction log backups from one database (the primary database) to another database (the secondary database) on a separate server. This process helps in maintaining a standby copy of the primary database and can be used for disaster recovery, reporting, and read-only operations.
How does Log Shipping work?
Log shipping involves three main components: the primary database, the secondary database, and a monitor server. The primary database generates transaction log backups, which are then copied to the secondary database and restored. The monitor server tracks the status of the log shipping process and alerts administrators in case of any issues.
Code Snippet: Setting up Log Shipping
```sql -- Enable log shipping on the primary database EXEC sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE; BACKUP DATABASE [PrimaryDatabase] TO DISK = 'C:\Backup\PrimaryDatabase_backup.bak'; -- Copy the backup file to the secondary server RESTORE DATABASE [SecondaryDatabase] FROM DISK = 'C:\Backup\PrimaryDatabase_backup.bak' WITH NORECOVERY; -- Set up log shipping jobs on the primary and secondary servers EXEC sp_add_log_shipping_primary_database @database = 'PrimaryDatabase', @backup_share = '\\PrimaryServer\BackupShare', @backup_directory = 'C:\Backup', @backup_job_name = 'BackupJob', @backup_schedule_description = 'Every 15 minutes'; EXEC sp_add_log_shipping_secondary_database @secondary_database = 'SecondaryDatabase', @primary_server = 'PrimaryServer', @primary_database = 'PrimaryDatabase', @restore_share = '\\SecondaryServer\RestoreShare', @restore_directory = 'C:\Restore', @restore_job_name = 'RestoreJob', @monitor_server = 'MonitorServer', @monitor_server_security_mode = 1; ```Example: Monitoring Log Shipping
Once log shipping is set up, you can monitor the status of the process by querying the log_shipping_monitor_secondary view on the secondary server. This view provides information about the last backup, last copied, last restored, and the latency of the log shipping process.
```sql SELECT * FROM msdb.dbo.log_shipping_monitor_secondary; ```The output of this query will show details such as the time of the last backup, last copy, last restore, and the delay between the primary and secondary databases.
Common Use Cases
Log shipping is commonly used for disaster recovery purposes, where a standby copy of the primary database is maintained on a separate server to minimize downtime in case of a failure. It can also be used for reporting purposes, where the secondary database is used for running read-only queries without impacting the performance of the primary database.
Importance in Interviews
Log shipping is a fundamental concept in database management and is often asked about in interviews for database administrator or developer roles. Understanding how log shipping works, its benefits, and common use cases can help you stand out in technical interviews and demonstrate your expertise in database management.
Log shipping is a valuable technique in database management that helps in maintaining a standby copy of the primary database for disaster recovery and reporting purposes. By setting up log shipping, you can ensure data availability and minimize downtime in case of a failure. Understanding the process of log shipping and its practical applications can enhance your skills as a database professional.
