uts short course sql server 2005 for developers
Download
Skip this Video
Download Presentation
UTS Short Course SQL Server 2005 for Developers

Loading in 2 Seconds...

play fullscreen
1 / 33

UTS Short Course SQL Server 2005 for Developers - PowerPoint PPT Presentation


  • 268 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'UTS Short Course SQL Server 2005 for Developers' - Anita


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
course website
Course Website

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

  • Course Timetable
  • Course Materials
about justin
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: [email protected]
  • Blog: http://kingjustin.com
what is high availability
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
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
What else can go wrong?
  • People (PEBKAC)
    • Administrators
    • Users
      • Bottlenecks & Concurrency
  • Acts of God
    • Lightning
    • Cleaners
what can we do about it
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_high_availability.asp

what will we cover
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
database snapshots11
Database Snapshots

Point-in-time reporting

Mirroring for reporting

Recover from administrative error

Protection from application or user error

database snapshots12
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
configuring a database mirror16
Configuring a Database Mirror

Virtually no distance limitations

No special hardware

configuring a database mirror17
ClientsConfiguring a Database Mirror

Principal Server

Witness Server

Mirror Server

configuring a database mirror18
ClientsConfiguring a Database Mirror

Mirror Server

Witness Server

Principal Server

configuring a database mirror19
ClientsConfiguring a Database Mirror

Mirror Server

Witness Server

Principal Server

partitioned tables
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
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
- Table is accessible for read and update
  • Non-clustered indexes are available during
  • clustered index creation
Online Index Operations

Index Created

Table

online clustered index operation
Target (Clustered Index)

Concurrent Users

Source (Table)

Locking

Online Clustered Index Operation

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

mirrored backups26
Mirrored Backups

Mirror 1

Mirror 2

mirrored backups27
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.

Using Media Families and Sets
mirrored backup example
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'

session summary
Session Summary
  • Implementing Database Snapshots
  • Configuring a Database Mirror
  • Partitioned Tables
  • SQL Agent Proxies
  • Performing Online Index Operations
  • Mirrored Backups
session 3 lab
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
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

ad