sql server 2008 for developers
Download
Skip this Video
Download Presentation
SQL Server 2008 for Developers

Loading in 2 Seconds...

play fullscreen
1 / 70

SQL Server 2008 for Developers - PowerPoint PPT Presentation


  • 350 Views
  • Uploaded on

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.

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 'SQL Server 2008 for Developers' - Roberta


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
peter gfader
Peter Gfader
  • Specializes in
    • C# and .NET (Java not anymore)
    • TestingAutomated tests
    • Agile, ScrumCertified Scrum Trainer
    • Technology aficionado
      • Silverlight
      • ASP.NET
      • Windows Forms
course website
Course Website
  • http://sharepoint.ssw.com.au/Training/UTSSQL/Pages/
    • Course Timetable
    • Course Materials
what we did last week
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
agenda
Agenda

Agenda

  • What is High Availability?
  • What can go wrong?
  • What can we do about it?
what is high availability
What is high availability?
  • Different people have different definitions
    • Perceived uptime
    • Performance
  • What can go wrong?
  • How can we improve it?
what is high availability10
What is high availability?
  • Different people have different definitions
    • Perceived uptime
    • Performance Issues
  • What can go wrong?
  • How can we improve it?
what can go wrong continued
What can go wrong? (continued)
  • Hardware
    • Disk failure
    • Network failure
    • Power Outages
what can go wrong
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
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 continued 3 1
What can we do about it? (continued 3-1)
  • 1. Hardware Solutions
    • UPS & Hardware Monitors
    • RAID / Mirroring
    • Off site server
    • Firewall
    • Physical Security
what can we do about it continued 3 2
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
what can we do about it 3 3
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
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
slide20

Point-in-time reporting

Mirroring for reporting

Recover from administrative error

Protection from application or user error

database snapshots21
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
database snapshots22
Database Snapshots
  • CREATE DATABASE AdventureWorks_dbss1800 ON( NAME = AdventureWorks2008_Data, FILENAME = \'C:\data\AdventureWorks_data_1800.ss\' )AS SNAPSHOT OF AdventureWorks;GO
snapshot vs backup vs detach
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
configuring a database mirror

Easy to setup

Transparent client redirect

Zero committed work lost

Maximum one mirror per DB

Configuring a Database Mirror
slide28

Configuring a Database Mirror

Virtually no distance limitations

No special hardware

slide29

Configuring a Database Mirror

Clients

Principal Server

Witness Server

Mirror Server

configuring a database mirror30
Configuring a Database Mirror

Clients

Mirror Server

Witness Server

Principal Server

slide31

Configuring a Database Mirror

Clients

Mirror Server

Witness Server

Principal Server

before you mirror your database
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
set the service accounts
Set the service accounts

Use NT AUTHORITY\NETWORK SERVICE

mirror operating modes
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
database mirroring failover
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
database mirroring monitor
Database Mirroring Monitor
  • Lets you view the status and history of your current mirrors
might come in handy
Might come in handy
  • Disable MirroringALTER DATABASE myDatabase SET PARTNER OFF
  • Put DB from "Recovering..." into available online modeRESTORE DATABASE myDatabase WITH RECOVERY
partitioned tables55
Partitioned Tables
  • Allows for maximum concurrency
  • Partitioned Table parallelism
    • Improved a lot in SQL 2008
  • Archive older data into different filegroups
sql server agent recap
SQL Server Agent (recap)
  • Windows Service
  • Executes SQL Server jobs
    • Administrative tasks
sql agent proxies
SQL Agent Proxies
  • New credential system
  • Sits on Active Directory
  • Fine grained control of your jobs
  • Jobs can be run by proxies instead of user loginsPreviously to run cmd shell type functionality you needed a user in the administrator group which opened up security problems
online index operations60
Online Index Operations
  • - Table is accessible for read and update
  • Non-clustered indexes are available during
  • clustered index creation

Index Created

Table

slide61

CREATE NONCLUSTERED INDEX IX_TextTable_MyKey

ON [TestTable] ([MyKey])

WITH (ONLINE = ON);

GO

mirrored backups63
Mirrored Backups

Backup

Mirror 2

mirrored backups64
Mirrored Backups

Mirror 1

Mirror 2

slide65

Mirrored Backups

Mirror 1

Mirror 2

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
    • Database snapshots
    • Mirrored backups
    • Online Index Operations

Download from Course Materials Site (to copy/paste scripts) or type manually:

http://tinyurl.com/utssql2009

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
  • SQL Server user groups (www.sqlserver.org.au)
  • www.microsoft.com/technet/community
3 things
3things…
slide70
Thank You!

Gateway Court Suite 10 81 - 91 Military Road Neutral Bay, Sydney NSW 2089 AUSTRALIA

ABN: 21 069 371 900

Phone: + 61 2 9953 3000 Fax: + 61 2 9953 3105

[email protected]

ad