UTS Short Course SQL Server 2005 for Developers

1
UTS Short CourseSQL Server 2005for
Developers
2
Course Website

• http//www.ssw.com.au/ssw/events/2007SQL/
• Course Timetable
• Course Materials

3
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_at_ssw.com.au
• Blog http//kingjustin.com

4
Session 3 SQL 2005 High Availability Features
5
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?

6
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

7
What else can go wrong?

• People (PEBKAC)
• Administrators
• Users
• Bottlenecks Concurrency
• Acts of God
• Lightning
• Cleaners

8
What can we do about it?

• Hardware Solutions
• UPS Hardware Monitors
• RAID 5 (striping)/Mirroring
• Off site server
• Physical Security
• Software Solutions
• Database Mirroring
• Log shipping, Replication (Can also reduce
  availability)
• Database Snapshots
• Firewalls
• Disk Space Alerts
• Partitioned Tables
• Security, Change Management (D, T, P),
  Performance Monitoring/Tuning
• OS Level / Backup Solutions
• Failover clustering (Myths of data protection!)
• Hot/Cold standby servers
• Standard daily backups (with verified procedure
  Transaction logs)
• http//www.sql-server-performance.com/sql_server_h
  igh_availability.asp

9
What Will We Cover?

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

10
Database Snapshots
11
Database Snapshots
Point-in-time reporting
Mirroring for reporting
Recover from administrative error
Protection from application or user error
12
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

13
(No Transcript)
14
Configuring a Database Mirror
15
Configuring a Database Mirror
No special hardware
16
Configuring a Database Mirror
Virtually no distance limitations
No special hardware
17
Configuring a Database Mirror
Principal Server
Witness Server
Mirror Server
18
Configuring a Database Mirror
Mirror Server
Witness Server
Principal Server
19
Configuring a Database Mirror
Mirror Server
Witness Server
Principal Server
20
Demo
21
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

22
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

23
Online Index Operations
Index Created
Table
24
Online Clustered Index Operation
CREATE CLUSTERED INDEX index_name ON table_name
WITH (ONLINE ON)
25
Mirrored Backups
Mirror 1
26
Mirrored Backups
Mirror 1
Mirror 2
27
Mirrored Backups
Mirror 1
Mirror 2
28
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.

29
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'

30
Demo
31
Session Summary

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

32
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/

33
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