SQL MCM Training – Day 8

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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

3 Comments

  • Did you came across any scenarios for -Q ?

  • Hello Yusuf,

    Short answer: no 😉

    Thanks

    -Klaus

  • Definately it would be great help for preparation in MCM.
    I have a question on you blogs

    "While a log backup is running you can’t do a minimally logged operation in the bulk logged recover model"

    We have many datbase with bulk_logg recovery model. Would you please more about this?

    Thank You

SQLpassion

Copyright © 2017 by SQLpassion e.U. · Imprint · Offerings · Academy · Contact · Go to Top