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