UTS Short Course SQL Server 2005 for Developers

							UTS Short Course




SQL Server 2005
 for Developers
Course Website




   http://www.ssw.com.au/ssw/events/2007SQL/

 • Course Timetable
 • Course Materials
About Justin

 •   Senior Software Architect for www.ssw.com.au –
 •   C# Developer working in ASP.NET and Winforms.
 •   Java background using Swing with Hibernate
 •   Working with new technologies WPF, WCF and
     Visual Studio Team System 2008

 • Email: JustinKing@ssw.com.au
 • Blog: http://kingjustin.com
Session 3:

SQL 2005 High Availability Features
What is high availability?


 • Masking failure/performance degradation
 • Different people have different definitions
    – Perceived uptime
    – Performance Issues
 • What can go wrong?
 • How can we improve it?
What can go wrong?

 • Hardware
   –   Disk failure
   –   Network failure
   –   Power Outages
   –   Bad Dell power packs
 • Software
   –   Virus (and Virus Scanners) – File locking issues
   –   Disk space
   –   Corrupted files
   –   Bad upgrades
   –   OS Upgrades
 • SQL
   – poor tuning or design
   – DB Maintenance
What else can go wrong?


 • People (PEBKAC)
   – Administrators
   – Users
      • Bottlenecks & Concurrency

 • Acts of God
   – Lightning
   – Cleaners
What can we do about it?

    1.   Hardware Solutions
         1.   UPS & Hardware Monitors
         2.   RAID 5 (striping)/Mirroring
         3.   Off site server
         4.   Physical Security
    2.   Software Solutions
         1.   Database Mirroring
         2.   Log shipping, Replication (Can also reduce availability)
         3.   Database Snapshots
         4.   Firewalls
         5.   Disk Space Alerts
         6.   Partitioned Tables
         7.   Security, Change Management (D, T, P), Performance Monitoring/Tuning
    3.   OS Level / Backup Solutions
         1. Failover clustering (Myths of data protection!)
         2. Hot/Cold standby servers
         3. Standard daily backups (with verified procedure; Transaction logs)
 http://www.sql-server-
      performance.com/sql_server_high_availability.asp
What Will We Cover?


• Implementing Database Snapshots
• Configuring a Database Mirror
• Partitioned Tables
• SQL Agent Proxies
• Performing Online Index Operations
• Mirrored Backups
Database Snapshots




                     Maintains historical data
                     • For reporting

                     Safeguards data against
                     • Administrative error
                     • User error
Database Snapshots




                                                Point-in-time reporting
 Mirroring for reporting




Recover from administrative error   Protection from application or user error
 Database Snapshots


• Snapshots are NOT a substitute for your
  backup and recovery setup
• You cannot roll forward
• If either the database or the database
  snapshot is corrupted, reverting from a
  snapshot is unlikely to correct the problem
Configuring a Database Mirror


                                Less than
                                three seconds

                                Transparent
                                client redirect

                                Zero committed
                                work lost

                                Maximum one
                                mirror per DB
Configuring a Database Mirror




 No special hardware
Configuring a Database Mirror


                         Virtually no distance
                         limitations




 No special hardware
Configuring a Database Mirror


             Principal Server




                                Clients


  Witness
  Server




              Mirror Server
Configuring a Database Mirror


              Mirror Server




                                Clients


  Witness
  Server



             Principal Server
Configuring a Database Mirror


              Mirror Server




                                Clients


  Witness
  Server



             Principal Server
Demo
Partitioned Tables



 • Allows for maximum concurrency
 • Creating Partitioned table
    – Create filegroups and corresponding files
    – CREATE PARTITION FUNCTION pfIncome (money) AS
      RANGE LEFT FOR VALUES ('$30,000', '$90,000')
    – CREATE PARTITION SCHEME psIncome AS PARTITION
      pfIncome TO ('fgIncome1', 'fgIncome2', 'fgIncome3')
    – CREATE TABLE tbIncome (cSSN char(9), mIncome
      money) ON psIncome(mIncome)
 • Archive older data into different filegroups
SQL Agent Proxies

 • New credential system that sits on Active
   Directory
 • Allow fine grained control of your jobs
 • Jobs can be run by proxies instead of user logins
 • Previously to run cmd shell type functionaly you
   needed a user in the administrator group which
   opened up security problems
Online Index Operations




                                       Index Created
                          Table

       - Table is accessible for read and update
       - Non-clustered indexes are available during
         clustered index creation
Online Clustered Index Operation

    CREATE CLUSTERED INDEX index_name ON table_name … WITH
    (ONLINE = ON)


                                      Target
      Concurrent      Source        (Clustered
        Users         (Table)         Index)         Locking
Mirrored Backups




                   Mirror 1
Mirrored Backups




                   Mirror 1




                   Mirror 2
Mirrored Backups




                   Mirror 1




                   Mirror 2
Using Media Families and Sets

                       • We back up to a Media
                         Set.

                       • A Media Set may contain
                         multiple Media Families.

                       • Media Families allow
                         spreading 1 backup across
                         multiple physical
                         locations/drives

                       • We can have up to 4
                         Mirrors

                       • Corresponding families
                         across mirrors have
                         identical contents, so we
                         can interchange them
                         during a restore.
Mirrored Backup Example



   The following example creates the
   mirrored media set illustrated in the
   preceding figure and backs up the
   AdventureWorks database to both mirrors.

   BACKUP DATABASE AdventureWorks
   TO TAPE = '\\.\tape0', TAPE = '\\.\tape1' MIRROR
   TO TAPE = '\\.\tape2', TAPE = '\\.\tape3'
   WITH FORMAT, MEDIANAME = 'AdventureWorksSet1'
Demo
Session Summary


• Implementing Database Snapshots
• Configuring a Database Mirror
• Partitioned Tables
• SQL Agent Proxies
• Performing Online Index Operations
• Mirrored Backups
Session 3 Lab

 • High Availability Features

 Download from Course Materials Site (to copy/paste
   scripts) or type manually:
 http://www.ssw.com.au/ssw/events/2007SQL/
Where Else Can I Get Help?

• Free chats and webcasts
• List of newsgroups
• Microsoft community sites
• Community events and columns




www.microsoft.com/technet/community