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

UTS Short Course SQL Server 2005 - PowerPoint PPT Presentation


  • 267 Views
  • Updated 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.

Related searches for UTS Short Course SQL Server 2005

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' - 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
Uts short course sql server 2005 for developers l.jpg

UTS Short CourseSQL Server 2005for Developers


Course website l.jpg
Course Website

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

  • Course Timetable

  • Course Materials


About justin l.jpg
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


Session 3 sql 2005 high availability features l.jpg
Session 3: SQL 2005 High Availability Features


What is high availability l.jpg
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 l.jpg
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 l.jpg
What else can go wrong?

  • People (PEBKAC)

    • Administrators

    • Users

      • Bottlenecks & Concurrency

  • Acts of God

    • Lightning

    • Cleaners


What can we do about it l.jpg
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 l.jpg
What Will We Cover?

  • Implementing Database Snapshots

  • Configuring a Database Mirror

  • Partitioned Tables

  • SQL Agent Proxies

  • Performing Online Index Operations

  • Mirrored Backups


Database snapshots l.jpg

  • Safeguards data against

  • Administrative error

  • User error

Database Snapshots


Database snapshots11 l.jpg
Database Snapshots

Point-in-time reporting

Mirroring for reporting

Recover from administrative error

Protection from application or user error


Database snapshots12 l.jpg
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 l.jpg

Less than three seconds

Transparent client redirect

Zero committed work lost

Maximum one mirror per DB

Configuring a Database Mirror


Configuring a database mirror15 l.jpg
Configuring a Database Mirror

No special hardware


Configuring a database mirror16 l.jpg
Configuring a Database Mirror

Virtually no distance limitations

No special hardware


Configuring a database mirror17 l.jpg

Clients

Configuring a Database Mirror

Principal Server

Witness Server

Mirror Server


Configuring a database mirror18 l.jpg

Clients

Configuring a Database Mirror

Mirror Server

Witness Server

Principal Server


Configuring a database mirror19 l.jpg

Clients

Configuring a Database Mirror

Mirror Server

Witness Server

Principal Server



Partitioned tables l.jpg
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 l.jpg
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 l.jpg

Online Index Operations

Index Created

Table


Online clustered index operation l.jpg

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 l.jpg
Mirrored Backups

Mirror 1

Mirror 2


Mirrored backups27 l.jpg
Mirrored Backups

Mirror 1

Mirror 2


Using media families and sets l.jpg

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 l.jpg
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 l.jpg
Session Summary

  • Implementing Database Snapshots

  • Configuring a Database Mirror

  • Partitioned Tables

  • SQL Agent Proxies

  • Performing Online Index Operations

  • Mirrored Backups


Session 3 lab l.jpg
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 l.jpg
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