Uts short course sql server 2005 for developers
Download
1 / 33

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


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.

Related searches for UTS Short Course SQL Server 2005 for Developers

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

Download Presentation

UTS Short Course SQL Server 2005 for Developers

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


UTS Short CourseSQL Server 2005for 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?

  • 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?

  • Implementing Database Snapshots

  • Configuring a Database Mirror

  • Partitioned Tables

  • SQL Agent Proxies

  • Performing Online Index Operations

  • Mirrored Backups


  • Maintains historical data

  • For reporting

  • Safeguards data against

  • Administrative error

  • User error

Database Snapshots


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


Less than three seconds

Transparent client redirect

Zero committed work lost

Maximum one mirror per DB

Configuring a Database Mirror


Configuring a Database Mirror

No special hardware


Configuring a Database Mirror

Virtually no distance limitations

No special hardware


Clients

Configuring a Database Mirror

Principal Server

Witness Server

Mirror Server


Clients

Configuring a Database Mirror

Mirror Server

Witness Server

Principal Server


Clients

Configuring a Database Mirror

Mirror Server

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


  • - Table is accessible for read and update

  • Non-clustered indexes are available during

  • clustered index creation

Online Index Operations

Index Created

Table


Target (Clustered Index)

Concurrent Users

Source (Table)

Locking

Online Clustered Index Operation

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


Mirrored Backups

Mirror 1


Mirrored Backups

Mirror 1

Mirror 2


Mirrored Backups

Mirror 1

Mirror 2


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

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


ad
  • Login