SQL Server 2008 for Developers - PowerPoint PPT Presentation

Sql server 2008 for developers l.jpg
Download
1 / 70

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.

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

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


Sql server 2008 for developers l.jpg

SQL Server 2008 for Developers

UTS Short Course


Peter gfader l.jpg

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

Course Website

  • http://sharepoint.ssw.com.au/Training/UTSSQL/Pages/

    • Course Timetable

    • Course Materials


Course overview l.jpg

Course Overview


What we did last week l.jpg

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


Homework l.jpg

Homework?


Sql 2008 high availability features l.jpg

SQL 2008 High Availability Features

Session 3


Agenda l.jpg

Agenda

Agenda

  • What is High Availability?

  • What can go wrong?

  • What can we do about it?


What is high availability l.jpg

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

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

What can go wrong? (continued)

  • Hardware

    • Disk failure

    • Network failure

    • Power Outages


What can go wrong l.jpg

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

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

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

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

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

Database Snapshots


Slide20 l.jpg

Point-in-time reporting

Mirroring for reporting

Recover from administrative error

Protection from application or user error


Database snapshots21 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

  • Query from snapshot  current database


Database snapshots22 l.jpg

Database Snapshots

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


Database snapshots23 l.jpg

Database Snapshots


Snapshot vs backup vs detach l.jpg

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


  • Database mirror l.jpg

    Database mirror


    Slide26 l.jpg

    Configuring a Database Mirror

    No special hardware


    Configuring a database mirror l.jpg

    Easy to setup

    Transparent client redirect

    Zero committed work lost

    Maximum one mirror per DB

    Configuring a Database Mirror


    Slide28 l.jpg

    Configuring a Database Mirror

    Virtually no distance limitations

    No special hardware


    Slide29 l.jpg

    Configuring a Database Mirror

    Clients

    Principal Server

    Witness Server

    Mirror Server


    Configuring a database mirror30 l.jpg

    Configuring a Database Mirror

    Clients

    Mirror Server

    Witness Server

    Principal Server


    Slide31 l.jpg

    Configuring a Database Mirror

    Clients

    Mirror Server

    Witness Server

    Principal Server


    Before you mirror your database l.jpg

    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


    Using the mirror wizard l.jpg

    Using the Mirror Wizard


    Configure end point on the principal l.jpg

    Configure End Point on the Principal


    Configure end point on the mirror l.jpg

    Configure End Point on the Mirror


    Set the service accounts l.jpg

    Set the service accounts

    Use NT AUTHORITY\NETWORK SERVICE


    Mirror is configured l.jpg

    Mirror is configured


    Warning about fqdn l.jpg

    Warning about FQDN


    Mirror operating modes l.jpg

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

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

    Database Mirroring Monitor

    • Lets you view the status and history of your current mirrors


    Thresholds l.jpg

    Thresholds


    Might come in handy l.jpg

    Might come in handy

    • Disable MirroringALTER DATABASE myDatabase SET PARTNER OFF

    • Put DB from "Recovering..." into available online modeRESTORE DATABASE myDatabase WITH RECOVERY


    Partitioned tables l.jpg

    Partitioned tables


    Partitioned tables54 l.jpg

    Partitioned Tables


    Partitioned tables55 l.jpg

    Partitioned Tables

    • Allows for maximum concurrency

    • Partitioned Table parallelism

      • Improved a lot in SQL 2008

    • Archive older data into different filegroups


    Sql server agent l.jpg

    SQL Server agent


    Sql server agent recap l.jpg

    SQL Server Agent (recap)

    • Windows Service

    • Executes SQL Server jobs

      • Administrative tasks


    Sql agent proxies l.jpg

    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 operations l.jpg

    Online Index Operations


    Online index operations60 l.jpg

    Online Index Operations

    • - Table is accessible for read and update

    • Non-clustered indexes are available during

    • clustered index creation

    Index Created

    Table


    Slide61 l.jpg

    CREATE NONCLUSTERED INDEX IX_TextTable_MyKey

    ON [TestTable] ([MyKey])

    WITH (ONLINE = ON);

    GO


    Mirrored backups l.jpg

    Mirrored Backups


    Mirrored backups63 l.jpg

    Mirrored Backups

    Backup

    Mirror 2


    Mirrored backups64 l.jpg

    Mirrored Backups

    Mirror 1

    Mirror 2


    Slide65 l.jpg

    Mirrored Backups

    Mirror 1

    Mirror 2


    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

      • 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 l.jpg

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

    3things…

    • PeterGfader@ssw.com.au

    • http://peitor.blogspot.com

    • twitter.com/peitor


    Slide70 l.jpg

    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

    info@ssw.com.auwww.ssw.com.au


  • Login