• Software failures
• Hardware component failures
• Network failure
• Power failure and natual disaster

• Hardware component redundancy
• Network redundancy
• RAID
• Server and database redundancy

• Virtual Server
  • Appears on the network as a normal server
• Editions
  • Enterprise Edition
  • Developer Edition
  • Standard Edition

Check configuration and compatibility of:

• Hardware
• Windows cluster configuration
• Shared disk

• Virtual Server
  • Specify a virtual server name in SQL Server Setup
• Instances
  • Only one instance of SQL Server in each resource group

• Alternative to failover clustering
• Mirror Server
  • Provides a hot standby
• Witness Server
  • Monitors the primary and mirror database servers
• Client redirection
  • Automatic client redirection

• TCP endpoint that enables network communication between servers

  CREATE ENDPOINT endpointName
      STATE=STARTED
      AS TCP ( LISTENER_PORT = listenerPortList )
      FOR DATABASE_MIRRORING
      (
          [ AUTHENTICATION = WINDOWS [ authorizationMethod ]
          ]
          [ [,] ENCRYPTION = REQUIRED
                  [ ALGORITHM { algorithm } ]
          ]
          [,] ROLE = ROLE
      )

1. Create mirroring endpoints and logins
2. Set recovery model to FULL
3. Back up database and restore on mirror server
4. Copy server-level resources

1. Set the pricipal server on the mirror server
2. Set the server on the principal server
3. Change the session mode from High Protection
   • Add a witness server for High Availability mode
   • Set partner safety off for High Performance mode

• Automatic failover
  • Mirror and witness form quorum
  • Role changes automatically
• Manual failover

  ALTER DATABASE AWData
  SET PARTNER FAILOVER

• Forced service

  ALTER DATABASE AWData SET PARTNER
  FORCE_SERVICE_ALLOW_DATA_LOSS

• A high-availability technique that applies log backups to a standby server

• Implement log shipping in either:
  • SQL Server Management Studio
  • Transact-SQL

    sp_add_log_shipping_primary_database
    @DATABASE = N'AdventureWorks'
    ,@backup_directory = N'c:\lsbackup'
    ,@backup_share = N'\\tribeca\lsbackup'
    ,@backup_job_name = N'LSBackup_AdventureWorks'

1. Copy transaction log backups
2. Back up primary database with NORECOVERY

   BACKUP LOG AdventureWorks TO AWLogBackup
   WITH NORECOVERY

3. Restore primary database on secondary server

   RESTORE LOG AdventureWorks FROM AWLogBackup
   WITH RECOVERY

4. Disable log shipping jobs
5. Configure log shipping on new primary server

• Effects of transactions and cnoflicts
• Improved performance and scalability
• Control over network traffic
• Suitability over intermittent network connections

• Update and read load balancing
• Read load balancing

• Synchronizing nodes
• Peer-to-peer replication topologies
• Implementing a peer-to-peer replication topology