Sql Cert - Log Shipping Failover

Front 1

prep

Back 1

primary and secondary db are typically unsynched, because the primary db continues to be updated after its latest backup job. In some cases recent transaction log backups have not been copied to the secondary instance, or some have not been applied.

Front 2

Fail over step 1.

Back 2

copy an uncopied backup files from the backup share to the copy destination folder of each secondary server

Front 3

Fail over step 2

Back 3

apply any unapplied transaction log backups in sequence to each secondary db.

Front 4

fail over step 3

Back 4

if the primary db is accessible, back up the active transation log and apply the log backup to the secondary db. if primary is not damaged, back up the tail of the transaction log of the primary db using WITH NORECOVERY. this leaves the db in a restoring state and unavailable to users.

Front 5

fail over step 4

Back 5

after the secondary servers are synched, you can fail over to the secondary server by recovering its secondary db and r3edirecting clients to that server instance. Recovering puts the db into a consistent state and brings it online.

Front 6

fail over step 5

Back 6

after recovery to a secondary db, you can reconfigure it to act as a primary db for other secondary dbs.

Front 7

Log shipping

Back 7

allows you to automatically send transaction log backups from a primary db on a primary server instance to one or more secondary dbs on separate secondary server instances. The trans log backups are applied to each of the secondary dbs individually.

Front 8

Monitor server

Back 8

an optional third server in log shipping setup that records the history and status of backup and restore operations and optionally raises alerts if these operations fail to occur as scheduled

Front 9

Benefits

Back 9

Provides a disaster recovery solution for a single primary db and 1 or more secondary dbs each on a separate instance of SQL server
supports limited read-only access to secondary dbs
allows a user specified delay between when the primary server backs up the log and when the secondary server must apply the log backup

Front 10

log shipping operations

Back 10

1. back up trans log at the primary server instance
2. copy the trans log file to the secondary server
3. restore the log backup on the secondary server

Front 11

backup job

Back 11

SQL server agent job that performs the backup operation, logs history to local and monitor, deletes old backup files and history info. When log shipping is enabled a "log shipping backup" job is created on the primary server instance.

Front 12

copy job

Back 12

sql server agent job that copies the backup files from the primary to the secondary and logs history on the secondary and monitory. When log shipping is enabled a "log shipping copy" job is created on the secondary server instance.

Front 13

restore job

Back 13

sql server agent job that restores teh copied backup files to the secondary db. Logs history on the local server and monitor and deletes old files and old history info. When log shipping is enabled a "log shipping restore" job is created on the secondary server instance.

Front 14

alert job

Back 14

sql server agent job that raises alerts for primary and secondary dbs when a backup or restor operation does not complete successfully w/in a specified threshold. When log shipping is enabled a "log shipping Alert" job is created on the monitor server instance.