SQL Server 2008

Your questions answered…

SQL Server 2008

Which boat are you in?

Your developers and IT pros can‟t wait to start using all

the new stuff

Your developers and IT pros aren‟t interested in all the

new stuff

You think it‟s all too new – let‟s just wait for the first

service pack to come out

You don‟t know what to think amongst all the marketing

hype

SQL Server 2008

New features in SQL Server 2008Transparent Data Encryption

External Key Management

Data Auditing

Pluggable CPU

Transparent Failover for Database Mirroring

Declarative Management Framework

Server Group Management

Powershell

Streamlined Installation

Enterprise System Management

Performance Data Collection

System Analysis

Data Compression

Query Optimization Modes

Resource Governor

Entity Data Model

LINQ

Visual Entity Designer

Entity Aware Adapters

Synchronized Programming Model

Visual Studio Support

SQL Server Conflict Detection

FILESTREAM data type

Integrated Full Text Search

Sparse Columns

Large User Defined Types

Date/Time Data Type

LOCATION data type

SPATIAL data type

Virtual Earth Integration

Partitioned Table Parallelism

Query Optimizations

Persistent Lookups

Change Data Capture

Backup Compression

MERGE SQL Statement

Data Profiling

Star Join

SQL Server Change Tracking

Transparent Data Encryption

Enterprise Reporting Engine

Internet Report Deployment

Block Computations

Scale out Analysis

Export to Word and Excel

Author reports in Word and Excel

Report Builder Enhancements

TABLIX

Charts and Gauges controls

Rich Formatted Data

Personalized Perspectives

… and many more

SQL Server 2008

Dynamic Development

Beyond Relational

Pervasive Insight

Enterprise Data Platform

Mobile and

Desktop

OLAP

FILE

XML

RDBMS

Services

Query

Analysis

Reporting Integration

Synch

Search

CloudServer

Platform vision

SQL Server 2008

What are we looking for?

Trusted

Security, Reliability and Performance

Productive

Developers

IT Pros

Breadth of solutions

Integration Services

Analysis Services

Reporting Services

Synch/Search

SQL Server 2008

Trusted

Auditing

Encryption

Compression

Performance

Ensuring Business continuity

SQL Server 2008

Auditing

Native DDL for Audit configuration and management

Create an Audit object to automatically log actions to:

File

Windows Application Log

Windows Security Log

Ability to define granular Audit Actions of Users or Roles

on DB objects

SQL Server 2008

Native Encryption

Transparent Data Encryption

Encrypts data at rest and within

backups

No impact to existing apps

Encryption/decryption performed

at database level

Support for hardware based key

management

SQL Server 2008

DEK

Client Application

Encrypted data page

SQL Server 2008

Compression

Within database

Row compression

Page compression

Within backups

SQL Server 2008

Encryption, Compression and Auditing

SQL Server 2008

Filtered Indexes

Improve query performance and lower storage and

maintenance costs

Of benefit for well known sets of data (<=50% of full table

set)

Also very useful as a filter for UNIQUE indexes!

SQL Server 2008

Resource Governor

Manage workload and system resource consumption

3 new concepts

Resource Pools

Workload Groups

Classifier Functions

Directs each scheduler as to how to prioritize work within

each virtual instance (pool)

SQL Server 2008

Resource Governor

SQL Server 2008

Ensuring Business Continuity

Enhanced Mirroring

Auto-Page Repair

Log Stream Compression

Hot-add CPU

64 bit Enterprise only

sp_configure „affinity mask‟ and „affinity I/O mask‟

SQL Server 2008

New Date and Time data types

DATE, TIME, DATETIME2

DATETIMEOFFSET

GROUPING SETS

Specify multiple group by clauses

Multi row INSERT

+= assignment

Inline variable initialisation

General T-SQL EnhancementsProductive – for Developers

SQL Server 2008

MERGE statementProductive – for Developers

New operation for performing multiple DML operations

across a full outer join

So you can perform INSERTS, UPDATES and DELETES

as appropriate

Key scenarios

Conditional insert / update

Synchronization

SQL Server 2008

Allows TABLE types to be passed as a parameter to a

stored procedure

Use by

Defining a User Defined Table type

Declaring a parameter in sproc or function as that table type

passing as READONLY

Call by passing a table instance

Table Value Parameters Productive – for Developers

SQL Server 2008

Hierarchies and HierarchyId data type

File system based data

Sparse columns

Allows columns to be marked as SPARSE indicating they

may not have any data attached

Integrated Full Text Search

Beyond Relational Productivity – for Developers

SQL Server 2008

Vector based, industry spatial standards

Two new data types:

GEOMETRY

GEOGRAPHY

Implemented as managed UDT‟s in separate

assembly

Spatial DataProductivity – for Developers

SQL Server 2008

Use ADO.NET Entities to rationalize our data and simplify

development

Object Relational Mapping

Leverage LINQ for querying

Reflects a shift towards domain driven architectures over

traditional data silos

At Intergen we use other ORMs instead…

But LINQ is great

ADO.NET Entity Framework and LINQ Productive – for Developers

SQL Server 2008

T-SQL EnhancementsMERGE statementTable Value Parameters

SQL Server 2008

Configuration ServersProductive – for IT Pros

Group servers together for configuration or management

Multicast query – target all servers in the group at the

same time!

Collectively inspect or apply policy

SQL Server 2008

Policy Based ManagementProductive – for IT Pros

New declarative policy system to manage instances

Uses DDL triggers and SQLCLR to enforce policy

Supports “true transactional” events

Fully scriptable

SQL Server 2008

PowershellProductive – for IT Pros

A new tool for scripting on Windows

SQL Server integration

Object Explorer: Right-click, Start PowerShell

SQL Agent integration

Can now create PowerShell job steps.

External access

Native Providers to access SMO data and Policy

Provides direct SMO access

Invoke-sqlcmd cmdlet

SQL Server 2008

Configuration ServersPolicy Based ManagementPowershell

SQL Server 2008

Data Profiling

Change Data Capture

C# and VB.NET scripting

Lookup Transformation enhancements

Performance improvements

New Performance Counters

Integration ServicesBreadth of solutions

SQL Server 2008

Information about what changed at the source

Operation (Insert, Update, Delete)

Update mask (which columns changed)

Changes captured from the log asynchronously

Minimal impact on source system

Log reader can be scheduled to run during idle time

Enabled per table

Hidden change tables store captured changes

One change table per source table that is tracked

CDC APIs provide access to change data

Change

Tables

OLTP

Data Warehouse

Change Data CaptureBreadth of solutions

SQL Server 2008

Data Profiling

SQL Server 2008

Design it Right – help baked into design tools

Best practices

Performance tuning tricks

Configuration warnings

Performance Monitoring

Scalable backups

Scalable shared databases

Analysis ServicesBreadth of solutions

SQL Server 2008

Combined Report Designer/Builder

New Chart/Graph/Guage visualisations

Rendering to Word, Excel, PDF, RTF

Report Designer improvements

Integration with SharePoint

Reporting ServicesBreadth of solutions

SQL Server 2008

Scale Breaks

Frame Effects

Drawing Styles

Multiple Chart Areas

Calculated Series

Striplines

Multiple Legends

SQL Server 2008

Gauge Samples

Radial Gauge Linear Gauge

SQL Server 2008

Synch services – v1.0

Fulltext indexing built into the SQL engine

Synch/SearchBreadth of solutions

SQL Server 2008

Dynamic Development

Beyond Relational

Pervasive Insight

Enterprise Data Platform

Mobile and

Desktop

OLAP

FILE

XML

RDBMS

Services

Query

Analysis

Reporting Integration

Synch

Search

CloudServer

Platform vision

SQL Server 2008

Enterprise Editions and featuresCategory Feature Name Enterprise StandardHigh Availability Database snapshots Yes No

High Availability Online indexing Yes No

High Availability Online restore Yes No

High Availability Backup compression Yes No

Scalability Partitioning Yes No

Scalability Data Compression Yes No

Scalability Resource Governor Yes No

Security Fine grained auditing Yes No

Security Transparent database encryption Yes No

Integration Services Fuzzy logic/term extraction tasks Yes No

Integration Services Data mining tasks Yes No

Integration Services Dimension/Partition processing tasks Yes No

Data Warehouse Scaling Change data capture Yes No

Data Warehouse Scaling Star join query optimization Yes No

Data Warehouse Scaling Partitioned cubes Yes No

Data Warehouse Scaling Proactive caching Yes No

Multi-Dimensional Analytics Writeback dimensions Yes No

Multi-Dimensional Analytics Perspectives Yes No

Reporting Data driven subscriptions Yes No

Reporting Infinite click-through Yes No

SQL Server 2008

Quick wins/best bets

Trusted platform

Auditing, Database Encryption, Backup Compression,

Resource Governor, Management Data Warehouse

Developer Productivity

Everything except ADO.NET Entity Framework

IT Pro Productivity

Configuration Servers, Policy-based management,

Powershell

Business Intelligence

Falling into the pit of success