SQL Server 2005 Administration, Scalability and Reliability

1
SQL Server 2005 Administration, Scalability and
Reliability
Dr Greg Low Readify Greg.Low_at_readify.net
2
Prerequisite Knowledge

• Basic T-SQL Syntax (SELECT, UPDATE)
• Experience executing stored procedures
• Knowledge of how SQL Server uses transaction logs
  and the SQL Server recovery process
• Knowledge of SQL Server 2000 concurrency controls

Level 200
3
Who Am I?

• SQL Server .NET Consultant with Readify
• Director of White Bear Consulting
• Microsoft MVP for .NET
• INETA User Group Relations Chair for Asia-Pacific
• President of Qld MSDN User Group
• President of Qld SQL Server User Group

4
Agenda

• Barriers to availability
• Database server failure or disaster
• User or application error
• Data access concurrency limitations
• Database Partitioning

5
Barriers to AvailabilityOverview

• Business requirements
• Time and location
• Processes
• Expectations
• Technology
• Hardware
• DBMS software
• Application software

6
Barriers to Availability Primary Server or DBMS
Barriers

• Database failure or disaster
• Hardware failure
• Human-made disaster
• Natural disaster

7
Barriers to Availability Primary Server or DBMS
Barriers

• User or application error
• Accidental data modifications
• Malicious data modifications
• Data access concurrency limitations
• Multiple transactions accessing same data
• Persistent data structure changes

8
Agenda

• Barriers to availability
• Database server failure or disaster
• User or application error
• Data access concurrency limitations
• Database Partitioning

9
Database Server Failure or Disaster Overview

• Failover clustering
• Database mirroring
• Now many alternatives available
• Peer to peer replication (new)
• Standard replication topologies
• Log shipping
• Backup and restore
• Detach, copy, attach

10
Database Server Failure or Disaster Failover
Clustering

• Provides a hot standby
• Built on Microsoft Cluster Services (MSCS)
• Multiple nodes (Now 8)
• Automatic failover of instance (20 seconds)
• Certified hardware required
• Zero committed work loss
• Single copy of instance databases
• Standby not available for any other use
• Supports more SQL services

MS-2087 Implementing Microsoft Windows 2000
Clustering
11
Database Server Failure or Disaster Failover
Clustering

• Provides a hot standby
• Built on Microsoft Cluster Services (MSCS)
• Multiple nodes (now 8)
• Automatic failover of instance (20 seconds)
• Certified hardware required
• Zero committed work loss
• Single copy of instance databases
• Standby not available for any other use
• Supports more SQL services

MS-2087 Implementing Microsoft Windows 2000
Clustering
12
Database Server Failure or Disaster Failover
Clustering
13
Database Server Failure or Disaster Database
Mirroring

• Provides a hot standby (instant)
• Database failover
• Very fast automatic failover (less than 3
  seconds)
• Transparent client redirect
• Zero committed work loss
• Some performance impact
• Maximum one mirror for each database
• Standby not directly available for any other use

MS-2733 Updating Your Database Administration
Skills to Microsoft SQL Server 2005
14
Database Server Failure or Disaster Database
Mirroring

• Provides a hot standby (instant)
• Database failover
• Very fast automatic failover (less than 3
  seconds)
• Transparent client redirect
• Zero committed work loss
• Some performance impact
• Maximum one mirror for each database
• Standby not directly available for any other use

MS-2733 Updating Your Database Administration
Skills to Microsoft SQL Server 2005
15
Database Server Failure or Disaster Database
Mirroring Hardware

• No special hardware required
• Requires a second server
• Servers do not need duplicate hardware
• Virtually no distance limitations
• No shared disk arrays
• Transaction latency requirements determine
  acceptable network latency

MS-2733 Updating Your Database Administration
Skills to Microsoft SQL Server 2005
16
Database Server Failure or Disaster Database
Mirroring With Automatic Failover
17
Database Server Failure or Disaster Database
Mirroring With Automatic Failover
18
demonstration
Configuring and Using a Database Mirror
19
User or Application Error Comparison of High
Availability Options
20
User or Application Error Comparison of High
Availability Options
21
Agenda

• Barriers to availability
• Database server failure or disaster
• User or application error
• Data access concurrency limitations
• Database Partitioning

22
User or Application Error Database Snapshots

• Read-only
• Static view of database
• Transaction consistent at point of time
• Does not store all data pages
• Reads only original data pages
• Reads data from snapshot if data has changed
• Reads data from database if data has not changed
• Increases disk I/O of source database

MS-2733 Updating Your Database Administration
Skills to Microsoft SQL Server 2005
23
User or Application Error How Database Snapshots
Work
24
User or Application Error Database Snapshot
Scenarios

• Enabling mirroring database for reporting
• Historical point-in-time reporting
• Recovering from administrative error
• Protecting against application or user error

25
demonstration
Implementing and Using Database Snapshots
26
Agenda

• Barriers to availability
• Database server failure or disaster
• User or application error
• Data access concurrency limitations
• Database Partitioning

27
Data Access Concurrency Limitations Pessimistic
Concurrency Controls

• Default Setting and Behavior
• Designed for Data Integrity
• SELECT Operations Require Shared Resource Locks
• Different Isolation Levels Control Behavior of
  Shared Locks

MS-2072 Administering a Microsoft SQL Server 2000
Database
28
Data Access Concurrency Limitations Optimistic
Concurrency Controls

• Optional setting and behavior
• Implemented using row versioning
• Designed for data integrity but allows reads of
  committed data
• SELECT operations do not require shared resource
  locks
• Different isolation levels control behavior of
  committed reads

29
Data Access Concurrency Limitations Row
Versioning

• Several Uses
• Triggers for Inserted and Deleted tables
• Multiple Active Result Sets (MARS) sessions
• ONLINE Index Operations
• Optimistic concurrency controls (row snapshots)
• Versions built in tempdb for all modified rows
• Versions deleted when no remaining transaction or
  operation needs them

30
Data Access Concurrency Limitations Snapshot
Isolation Level

• Reads committed data at time of transaction start
• ALLOW _SNAPSHOT_ISOLATION database option
• TRANSACTION ISOLATION LEVEL SNAPSHOT session
  option

31
Data Access Concurrency Limitations ONLINE Index
Operations

• Table accessible for reads and modifications
  during ONLINE index operations
• Existing non-clustered indexes available to
  optimize during ONLINE clustered index operations
• Non-clustered index is not available during its
  index operation

32
Data Access Concurrency Limitations ONLINE Index
Operation Commands

• CREATE INDEX
• ALTER INDEX
• DROP INDEX
• ALTER TABLE
• ADD or DROP of UNIQUE index
• ADD or DROP of PRIMARY KEY with CLUSTERED index
  option

33
Agenda

• Barriers to availability
• Database server failure or disaster
• User or application error
• Data access concurrency limitations
• Database Partitioning

34
Database Partitioning

• Designed for
• VLDB with very large tables (ie 100s GB)
• Large systems with 8, 16, 32 or more real CPUs
• Replace partitioned views where partitions are in
  a single database

35
Database Partitioning

• Partition Function each row of a table is
  assigned to a partition (ie 1, 2, 3)
• Currently only a single column but workaround
  with persisted calculated columns
• Partition Scheme each partition for a partition
  function is mapped to a filegroup

36
demonstration
Implementing and Using Database Partitions
37
Session Summary

• Several new features available in SQL Server 2005
  to protect against server failure
• Database snapshots can be used to protect against
  application, user, or administrative error
• Use the new optimistic concurrency controls and
  ONLINE index operations to reduce database
  contention issues

38
For More Information

• Visit TechNet at www.microsoft.com/technet

39
Microsoft LearningTraining Resources for IT
Professionals
To see the detailed syllabus or to locate a
training providerplease visitwww.microsoft.com/l
earning
40
SQL Server 2005 2 Day Workshops

• Microsoft SQL Server 2005 Database Infrastructure
  Scalability,
• presented by Brent Challis, DDLS
• Microsoft SQL Server 2005 Business Intelligence,
• presented by Peter Myers. Tenix Connections
• Microsoft SQL Server 2005 Development,
• presented by Greg Low, White Bear Consulting

41
SQL Server 2005 Workshops

• Cost 650 and 575 for early birds.  
• For Schedule, Registration and further
  information
• Partners only http//www.microsoft.com/australia/
  partner/training/
• Customers and Partners http//www.microsoft.com/a
  ustralia/events/sql2005 (This page will be
  available later this month)

42
SQL Server 2005 Workshops

• Register Now for Yukon Workshops
• Microsoft Partner can register for all 3
  workshops at www.microsoft.com/australia/partner/t
  raining/default.aspx
• Microsoft Customers can register for all 3
  workshops at
• com.au/YukonCusBI
• com.au/YukonCusDev
• com.au/YukonCusAdmin
• Workshops run nationally and are scheduled
  between 7 March and 30 June 2005, places are
  limited so be quick.

43
User Community

• SQLServer.org.au (Australian SQL Server User
  Group)
• Sign up and attend your local user group!
• SQL Server 2005 Beta 2 Kit
• http//msstore.datacom.com.au/sqlbeta
• 3 CD/DVDs
• SQL Server 2005 Beta 2
• SQL Server 2005 Resource Kit
• SQL Server 2005 Presentations (videos)

44
Code Camp

• Code Camp Oz is happening 23/24th April at
  Charles Sturt University in Wagga Wagga
• Be there!
• www.codecampoz.com

45
The SQL Server 2005 University Masters

• Fully accredited, masters level degree from
  Charles Sturt University
• SQL Server 2005 MCDBA certification included as
  an integral component
• Graduates eligible for full professional level
  membership of the Australian Computing Society
• Part time study delivered via Distance Education
• Qualifies for Government FEE-HELP program
• Places available for experienced applicants
  without previous qualifications

First intake commences September 2005Register
your interest at www.itmasters.info
46
Where Can I Get Help?

• Free chats and webcastswww.microsoft.com/technet/
  community/chats
• www.microsoft.com/technet/community/webcasts
• List of newsgroups
• www.microsoft.com/technet/community/newsgroups
• Microsoft community sites
• www.microsoft.com/technet/community
• Community eventswww.microsoft.com/technet/communi
  ty/events
• Community columns
• www.microsoft.com/technet/community/columns

47
(No Transcript)
48
Session Credits

• Author Steven R. Allen with mods by Greg Low
• Producer/Editor
• Technical Specialists
• Reviewer 1
• Reviewer 2
• Microsoft Reviewers/Editors