Blog Post

SQL MCM Training – Day 8

,

Today Paul talked the whole day about HA DR Strategies and Backup/Restore operations.

I've already known a lot about Backup/Restores, but Paul showed some cool things that

were even new to me J. Here are my study

notes for the 3 modules of today:

Module 1: HA DR Strategies

  • Zero downtime is not possible
    • Even not mit Live Migration
  • Failover Clustering
    • Server is protected
    • Data is not protected
  • RPO - Recovery Point Objective
    • Maximum allowable data-loss
    • Zero data-loss is possible through synchronous Database Mirroring or synchronous SAN

      replication

  • What is the transaction log generation rate of your workload?
    • Impacts
      • Database Mirroring
      • Log Shipping
      • Replication
      • Log File Size
  • Recovery Model
    • Database Mirroring needs FULL
    • Log shipping works with FULL and BULKLOGGED

Module 2: Backups

  • Don't plan a backup strategy, plan a restore strategy!!!
  • When a Full Database Backup starts, a CHECKPOINT occurs
    • CHECKPOINT generates log records
  • Concurrent Full Backups and Tx Log Backups are possible
    • Log Clearing from the Tx Log Backup is deferred until the concurrent Full Backup is

      completed

  • DBCC SQLPERF (LOGSPACE)
  • Only a log backup clears the transaction log in the FULL recovery model
  • Reverting from a database snapshot breaks the backup chain
  • While a log backup is running you can't do a minimally logged operation in the

    bulk logged recover model

    • The ML bitmap mask can't be changed in the mean time
  • Tail Log backup in the bulk logged recovery model also needs the data files (needs

    to backup the changed extents)

    • They must be accessible, not only the transaction log
    • Tail Log backup can be performed, but it leads to a corrupt database
  • Backup chain is not broken, when you switch between full <> bulk logged <>

    full

  • Backup chain is only broken when you switch to the SIMPLE recovery model
  • Database Snapshot can't be created on a database in the RESTORING state because Backup/Restores

    aren't going through the Buffer Pool

  • SQL Server can't rebuild a partition, when they are spread across filegroups,

    where some filegroups are readonly

    • Therefore you can use Partitioned Views to union the Read/Write and the ReadOnly filegroups
  • When you do a Full backup of a readonly filegroup, nothing else (diff, log backup)

    has to be done

    • Just back it up once, and you're done
  • COPY_ONLY
    • Also possible with transaction log backups - will not clear the transaction log
  • For each backup device a writer thread will be created
    • For the restore a seperate reader thread will be created for each backup device
  • Mirrored Backups
    • When a mirror disappears, the entire operation fails
  • CONTINUE_AFTER_ERROR
    • Forces Backups & Restores of corrupt databases
  • Backup Compression new on SQL Server 2008 Enterprise Edition
    • SQL Server 2008 R2 also includes it in Standard Edition

Module 3: Restores

  • Files can be only set OFFLINE, but you have to restore from backup to get it ONLINE
  • Prevent that Backup Compression preallocates space
    • Trace Flag 3042
    • kb2001026
  • STOPAT does nothing on a full or diff backup restore
    • It's just syntetical reason
  • After STOPAT a full database backup should be taken to create a new well-known

    recovery starting point

    • Otherwise the restore sequence begins at the initial full backup and all subsequent

      restores must use STOPAT, which is a way more complicated

  • fn_dump_dblog
    • Look into a backup
    • 64 parameters!!!
  • STOPBEFOREMARK
    • Stops the restore process on a specific LSN
  • Piecemeal Restore
    • PRIMARY filegroup must be restored with the PARTIAL option
    • E.g. Single Page Restore when a page is corrupt
    • Only possible in the FULL or BULK LOGGED recovery model
    • Must start with the PRIMARY file group
  • sp_delete_backuphistory
    • Backup history tables in msdb must be cleaned up manually
  • Startup parameter -Q
    • Starts SQL Server without creating TempDb
    • Can be used when the model database is damaged, and SQL Server can't create a copy

      of it for TempDb

  • xp_readerrorlog
  • Option RESTART
    • Skips steps during restore, when they are already done
    • Writes a .CKP file that stores the different phases
    • Stores in the default specified Backup folder
    • TF 3004: Instant File Initialization
    • TF 3014: What's going during restores
    • TF 3605: Print out what caused on TF 3004 and TF 3014

Thanks for reading

-Klaus

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating