Today Paul was speaking about Database Mirroring and Jonathan was talking about Failover Clustering – all in all a very intensive day J

Module 6: Database Mirroring

• Split Brain is possible
  
• When you loose the witness, Database Mirroring goes into the asynchronous mode
  
  • No automatic failover possible
    
• Witness should be also put in the same data center as the principal
  
• Mirror can’t be backed-up, because it’s in the Restoring state
  
• You also have to think of your maintenance workload
  
  • Like Index Rebuilds, they have to be send also across the wire
    
  • Solution
    
    • Partition your large Index (like 1 TB)
      
    • ALTER INDEX REBUILD on a single partition
      
    • Switch maybe to asynchronous mirroring during the REBUILD phase
      
• Synchronous Mirring
  
  • Transaction Log Record must be flushed out to the disk on the mirror, before the transaction is comitted
    
  • The transaction must not be replayed, before the transaction is comitted on the Principal
    
    • Therefore we have the REDO queue on the Mirror
      
  • Redoing is run constantly on the mirror asynchronously in the background
    
  • Undone is never done on the Mirroring, only when the Mirror becomes the Principal
    
• You monitor the
  
  • SEND queue – should be also monitored in synchronous mode (SEND queue > 0 KB)
    
  • REDO queue
    
    • Mirror can be only goes online, when the REDO queue is 0
      
    • Can impact the time how long it takes until the Mirror becomes the Principal in a Failover
      
• Pausing/Suspended
  
  • When you have really big transactions like Bulk Loadings
    
• Local Cluster Failover
  
  • Partner Timeout must be set higher than 10 pings
    
  • Partner Timeout must be higher than the Local Cluster Failover time needed
    
  • http://technet.microsoft.com/en-us/library/ms191309.aspx
    
    
• Failure Detection
  
  • Inside of SQL Server
    
  • Outside of SQL Server
    
• Mirroring a large number of databases
  
  • http://sqlcat.com/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx
    
    
• Rolling Upgrades with Database Mirroring
  
  • http://blogs.msdn.com/b/sqlcat/archive/2009/02/09/minimize-downtime-with-db-mirroring.aspx
    
    
• Database Mirroring & Log Shipping
  
  • http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/DBMandLogShipping.docx
    
• Database Mirroring & Replication
  
  • http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/replicationanddbm.docx
    
• Trace Flag 1462 disables Log Stream Compression
  
  • Turn it on, when you don’t get any benefit from Log Stream Compression
    
  • Log Stream Compression is on by default
    
  • Log Compression is also available on Standard Edition of SQL Server
    
• Use Alerts to enable/disable Log Backup Jobs on Old Principal/New Principal
  
  • http://technet.microsoft.com/en-us/library/cc966392.aspx
    
• Automatic Page Repair is available on SQL Server 2008 R2 Standard Edition
  

Module 7: Failover Clustering

• Preferred owner configuration defines on which node a SQL Server can run
  
• Cluster Validation Tests offline the shared cluster disks
  
  • You have to plan offline time
    
• You need a current Cluster Validation Test Report for Microsoft Product Support Services to get help
  
  • Rerun Validation Tests, as soon as you add a new node to the cluster
    
• Only the active nodes have to be licensed
  
  • The node with the most CPUs defines how many CPUs you have to license when you use a CPU based license
    
• You need on odd numbe of votes to form a Quorum
  
• Not enable Large Pages, when 2 instances can be run on one node
  
  • The whole buffer pool must be allocated during startup phase of SQL Server
    
• SCSI-3 Persistent Reserveration must be enabled on the LUN level in the SAN
  
  • It’s normally not enabled by default
    
  • It’s needed for the shared disks in the cluster
    
• TempDb is currently not supported on a local disk in a cluster
  
  • SQL Server Denali will support it
    
  • You can’t use currently SSDs als local disk for TempDb
    
• Network Binding Order must be set with multiple NICs
  
• MSDTC is only needed, when you need distributed transactions
  
  • MSDTC is installed as a cluster resource
    
    • MSDTC disk could be mounted on another cluster node
      
  • MSDTC should be in the same resource group als SQL Server
    
    • MSDTC can fail together with SQL Server
      
    • Otherwise MSDTC could get a bottleneck
      
• You can have a separate MSDTC instance per SQL Server instance
  
• Slipstreaming
  
  • http://blogs.msdn.com/b/petersad/archive/2009/04/24/sql-server-2008-slipstream-frequently-asked-questions.aspx
    
  • KB955392
    
• Failback
  
  • Should be only configured, when the new failover node has less resources as the old one
    
  • Otherwise a failback occurs automatically as soon as the old node gets online
    
• You need Disk Dependencies if you’re using Mount Points
  
• Virtualization doesn’t provide you High Availability during Patching
  
• Rolling Patches/Upgrades
  
  • Go to the SQL Server Instance Properties inside Cluster Manager (for each SQL Server Instance)
    
  • Advanced Policies Tab
    
  • Remove the Possible Owner who you are currently patching
    
  • The Instance is taken offline/online during Patching, therefore those steps are needed to be done
    
    • Patching node must be removed from the possible owners
      
  • After one instance is patched
    
    • Fail over to the patched instance, after allow it as a possible owner
      
    • Remove possible owner from the unpatched instance
      
    • Patch the unpatched instance
      

Thanks for reading

-Klaus