Sql server 2008 for developers
Download
1 / 70

SQL Server 2008 for Developers - PowerPoint PPT Presentation


  • 345 Views
  • Updated 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 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



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




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



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



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



  • 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





    Set the service accounts l.jpg
    Set the service accounts

    Use NT AUTHORITY\NETWORK SERVICE




    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



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

    • [email protected]

    • 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

    [email protected]


    ad