1 / 70

SQL Server 2008 for Developers

SQL Server 2008 for Developers. UTS Short Course. Peter Gfader. Specializes in C# and .NET (Java not anymore) Testing Automated tests Agile, Scrum Certified Scrum Trainer Technology aficionado Silverlight ASP.NET Windows Forms. Course Website.

Roberta
Download Presentation

SQL Server 2008 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. SQL Server 2008 for Developers UTS Short Course

  2. Peter Gfader • Specializes in • C# and .NET (Java not anymore) • TestingAutomated tests • Agile, ScrumCertified Scrum Trainer • Technology aficionado • Silverlight • ASP.NET • Windows Forms

  3. Course Website • http://sharepoint.ssw.com.au/Training/UTSSQL/Pages/ • Course Timetable • Course Materials

  4. Course Overview

  5. What we did last week • Basic T-SQL syntax • New Data Types • Inline variable assignment • Table Value Parameters • DDL (Data Definition Language) Triggers • CTE (Common Table Expressions) • TOP % WITH TIES • XML Queries • PIVOT/UNPIVOT

  6. Homework?

  7. SQL 2008 High Availability Features Session 3

  8. Agenda Agenda • What is High Availability? • What can go wrong? • What can we do about it?

  9. What is high availability? • Different people have different definitions • Perceived uptime • Performance • What can go wrong? • How can we improve it?

  10. What is high availability? • Different people have different definitions • Perceived uptime • Performance Issues • What can go wrong? • How can we improve it?

  11. What can go wrong? (continued) • Hardware • Disk failure • Network failure • Power Outages

  12. What can go wrong? • Software • Virus (and Virus Scanners) – File locking issues • Disk space • Corrupted files • Bad upgrades • OS Upgrades • SQL • poor tuning or design • DB Maintenance

  13. What else can go wrong? • People (PEBKAC) • Administrators • Users • Bottlenecks & Concurrency • Acts of God • Lightning • Cleaners

  14. What can we do about it? (continued 3-1) • 1. Hardware Solutions • UPS & Hardware Monitors • RAID / Mirroring • Off site server • Firewall • Physical Security

  15. What can we do about it? (continued 3-2) • 2. Software Solutions • Database Mirroring • Log shipping • Auto backup transaction log, and restore • Replication (Can also reduce availability) • Monitor, Change • Database Snapshots • Alerts • OS: Disk Space, ... • DB: Logs,... • Partitioned Tables • Firewalls

  16. What can we do about it? (3-3) • 3. OS Level / Backup Solutions • Security • Change Management • Performance Monitoring/Tuning • Hot/Warm/Cold standby servers • Standard daily backups • Verified procedure • Transaction logs

  17. What Will We Cover? • Implementing Database Snapshots • Configuring a Database Mirror • Partitioned Tables • SQL Agent Proxies • Performing Online Index Operations • Mirrored Backups

  18. Database Snapshots

  19. Point-in-time reporting Mirroring for reporting Recover from administrative error Protection from application or user error

  20. 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 • Query from snapshot  current database

  21. Database Snapshots • CREATE DATABASE AdventureWorks_dbss1800 ON( NAME = AdventureWorks2008_Data, FILENAME = 'C:\data\AdventureWorks_data_1800.ss' )AS SNAPSHOT OF AdventureWorks;GO

  22. Database Snapshots

  23. Snapshot vs. Backup vs. Detach • Snapshot • Only go back • SELECT statements • Backup • Rollback and Forward possible • Smaller • Detach database and copy • DB goes offline • Closes all connections

  24. Database mirror

  25. Configuring a Database Mirror No special hardware

  26. Easy to setup Transparent client redirect Zero committed work lost Maximum one mirror per DB Configuring a Database Mirror

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

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

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

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

  31. Before you mirror your database • Principal Instance • Take a full backup and a log backup as well • Copy the full/log backups from Principal Instance to Mirror instance • Mirror Instance • Restore with NORECOVERY option the full backup • Apply the log backup

  32. Using the Mirror Wizard

  33. Configure End Point on the Principal

  34. Configure End Point on the Mirror

  35. Set the service accounts Use NT AUTHORITY\NETWORK SERVICE

  36. Mirror is configured

  37. Warning about FQDN

  38. Mirror Operating Modes • High Performance (asynchronous) • Commits are done on the principal and transferred to the mirror • High Safety (synchronous) • Commits are written to both databases

  39. Database Mirroring Failover • What happens when something bad happens to our principal server… • You can make it failover to the mirror • This means that the two servers swap roles for the time being

  40. Database Mirroring Monitor • Lets you view the status and history of your current mirrors

  41. Thresholds

More Related