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