SQLMCM Training – Day 10

(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 was speaking about Database Mirroring and Jonathan was talking about Failover Clustering – all in all a very intensive day J

Module 6: Database Mirroring

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

SQLpassion

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