1 / 33

UTS Short Course SQL Server 2005 for Developers

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.

Anita
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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@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_high_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. Maintains historical data • For reporting • Safeguards data against • Administrative error • User error 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. Less than three seconds Transparent client redirect Zero committed work lost Maximum one mirror per DB Configuring a Database Mirror

  14. Configuring a Database Mirror No special hardware

  15. Configuring a Database Mirror Virtually no distance limitations No special hardware

  16. Clients Configuring a Database Mirror Principal Server Witness Server Mirror Server

  17. Clients Configuring a Database Mirror Mirror Server Witness Server Principal Server

  18. Clients Configuring a Database Mirror Mirror Server Witness Server Principal Server

  19. Demo

  20. 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

  21. 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

  22. - Table is accessible for read and update • Non-clustered indexes are available during • clustered index creation Online Index Operations Index Created Table

  23. Target (Clustered Index) Concurrent Users Source (Table) Locking Online Clustered Index Operation CREATE CLUSTERED INDEX index_name ON table_name … WITH (ONLINE = ON)

  24. Mirrored Backups Mirror 1

  25. Mirrored Backups Mirror 1 Mirror 2

  26. Mirrored Backups Mirror 1 Mirror 2

  27. 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

  28. 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'

  29. Demo

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

  31. 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/

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

More Related