Post-it notes: Chapter 6 – Managing High Availability

March 3, 2013

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

RAID Levels


  • Clustering, log shipping, replication, mirroring
  • Hot-add CPU & memory support
  • Fault tolerant disk sub-systems (RAID)

RAID (Redundant Array of Independent Disks)

  • All RAID levels require at least 2 disks
    • Very fast, very good performance
    • No redundancy
    • Writes to many disks
    • If any disk fails, RAID array is lost
    • No performance benefit
    • Provides redundancy
    • Data written to first disk then mirrored to other
    • Expensive solution, two hard drives only really using one
  • RAID 5
    • Most common
    • Best of RAID  0 and 1
    • Requires at least 3 disks
    • Writes data to all disks except 1 (last disk is for parity)
    • Writes are slower (additional parity recording) read is ok
  • RAID 10 (1+0)
    • Highest performing
    • High redundancy
    • Most expensive
    • At least 4 drives
    • Basically 2 RAID (performance) and then mirrored

SQL High Availability Options

  • Log shipping (licence all servers)
  • Replication (licence all servers)
  • Mirroring (licence all servers)
  • Clustering (licence active node only)
  • AlwaysOn (new to 2012 – if using the passive node, then license)

SQL Recovery models and HA Options

  • Log shipping – Full
  • Mirroring – Full
  • Replication – Simple or Full (depends on recovery)
  • Clustering – Simple or Full

Log Shipping

  • Since 2000
  • Uses t-logs, send to nodes, which restore the log at intervals
  • Included in all editions that you pay for, big change from 2000 – only avail to Ent edition
  • Must use Full/Bulk-log recovery model
  • Share drive location required for logs to be backed up to and read from
  • Can configure compression – usually not required unless WAN
  • Backup node has option of ‘no recovery’ or ‘standby’ mode
    • Standby – allow user access. Configure restore job to kill users
    • No Recovery – does not allow any access
  • Can ship to multiple nodes
  • No quick and easy way to failover
  • Bring backup server online [ RESTORE LOG xxx WITH RECOVERY]


  • Introduced in 2005 (SP1 Production ready)
  • DB by DB HA solution
  • Only user databases can be mirrored
  • Login, SQL jobs, SSIS not mirrored
  • Port 5022 – default port. If mirrored instances are on same server; need different ports
  • CREATE ENDPOINT ‘xxx’ state = started as tcp (listener port=5022) for dbxxx
  • Principal databases show word ‘principal, synchronized’
  • Mirrored databases show word ‘restoring’
  • You can failover using SSMS or [ALTER database xxx set partner failover]
  • If you setup mirroring using t-sql (full safety is set automatically)
  • Synchronous Mode
    • Manual or Automatic failover (witness server)
    • High Safety mode
    • Writes are hardened on both servers before ACK sent back to Client
    • Automatic failure (can configure failover partner=backupserver in connection string)
  • Asynchronous Mode
    • Performance mode
    • Command first written to server
    • ACK sent back to client
    • Command sent to mirror server
    • Mirror sends ACK back to Primary


  • Uses MSCS – Microsoft Cluster Service
  • Failover entire server from one node to another
  • Single set of hard disks
  • SQL Service only runs on one node at a time. On the passive node it’s installed but stopped
  • On failover, services are started on the new active node
  • Requires a clustered quorum drive for cluster settings
  • Concept of resources (service, drive, netshare, ip, hostname)
  • Failovers entire resource groups
  • Server nodes all poll each other, quorum decides. If quorum is down other vote between      themselves
  • Use the SQL Server Cluster Configuration tool
  • Windows cluster service does not have to run under domain account – uses local sys.
  •  The SQL Services should use domain account (except browser)
  • Public network (clients connect to instance)
  • Private network (communication between nodes)
  • Can use Filestream – KB937444
  • Install on Primary node and then add node wizard for all other nodes
  • Patches, apply to primary test then apply to others
  • SQL 2000 – enterprise edition only
  • SQL 2005 – Standard and Enterprise
  • SQL 2008  – Standard and Enterprise
  • SSIS – further configuration [MsDTSSrvr.ini.xml] in DTS\BIN folder
  • Install, configure and cluster MSDTC for distributed transactions
  • See my previous clustering related post for further details and examples


  • See chapter 9 – Click Here – lots of detail – to be completed.


  • Hot Add CPU
  • Hot Add Memory

Thanks for reading.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: