SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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
  • 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
    • 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
    • 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!!!
    • 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 Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.


No comments.

Leave a Comment

Please register or log in to leave a comment.