mssql_log_shipping

Overview

Log-shipping allows the databases to be kept in active/passive state. Thus we can assure passive standby in case of a failure and the loos will be minimal. We can configure a log shipping each 1 hour or each 5 minutes. Thus the maximum we can loose is 5 minutes, of course this depends on the network and distance between the servers. Unlike mirroring, log-shipping is transparent to a SQL version, so we can have SQL server 2008 log shipping to SQL Server 2016 without any problems. In fact, this is strongly used for upgrades operations.

There are couple things which has to be configured:

On Primary

  • Disable Current Transaction Log Backup
  • Create a schedule
  • Create the necessary jobs and attach them Schedule
  • Do a backup to a shared location

On Secondary

  • Create a schedules (1 for copy and 1 for restore)
  • Create jobs for both: copy and restore
  • Restore the backup done on the primary
  • Start the log-shipping

Switchover

  • Perform Tail-log Backup (On Primary)
  • Restore with RECOVERY (On Standby)
  • mssql_log_shipping.txt
  • Last modified: 2020/03/10 23:18
  • by andonovj