arizona cio cto brown

ADE CTO Conference: 

ADE CTO Conference “SQL Server 2005 & 2008” October 29, 2007

Agenda: 

Agenda Brief (Very Brief!) Background on Statêra & the AZ SQL Server User Group Overview of SQL Server 2005 Relational Engine Integration Services Analysis Services SQL Server 2005 Demos SQL Server 2008 - It’s Coming! SQL Server 2008 Demos Q&A

An Introduction to Statera: 

An Introduction to Statera First Things First It’s Statera Not Stratera! We’re New to Phoenix, but we are not “New” Phoenix Office opened 2005 20 year Phoenix veterans in the Microsoft Partner community Statera Highlights Scorecard POC for Steve Ballmer! Microsoft Gold Partner Preside over the AZ SQL Server User Group

SQL Server User Group: 

SQL Server User Group No cost to attend Meets second Wednesday of most months Meetings are 4:30 to 6:40pm Next meeting is November 14th (we think) Meet at the Microsoft offices at Central & Thomas Technical content More info at www.azsqlserver.com or www.azgroups.com

SQL Server 2005Comprehensive, Integrated Data Platform: 

Notification Services Notifications & Alerts Integration Services ETL SQL Server Engine Relational Database Engine Reporting Services Enterprise Reporting Management Tools Development Tools SQL Server 2005 Comprehensive, Integrated Data Platform Replication Services Data Replication Analysis Services OLAP & Data Mining

Slide8: 

Integration with Visual Studio and .NET Native XML technology Interoperability via Web Services Integrate and Transform Data Analyze, Store and Mine Data Report and Interact with Data High availability for enterprise applications Key security and performance features Focus on self manageability and optimization

What’s New in SQL Server 2005?: 

What’s New in SQL Server 2005? Database Engine Service Broker HTTP Access Database Tuning Advisor Enhanced Read ahead & scan Indexes with Included Columns Multiple Active Result Sets Persisted Computed Columns Try/Catch in T-SQL statements Common Table Expressions Server Events Snapshot Isolation Level Partitioning Synonyms Dynamic Management Views .NET Framework Common Language Runtime Integration CLR-based Types, Functions, & Triggers SQL Server .NET Data Provider Data Types CLR-based Data Types VARCHAR(MAX), VARBINARY(MAX) XML Datatype Database Failure and Redundancy Fail-over Clustering (up to 8 node) Database Mirroring Database Snapshots Enhanced Multi-instance Support XML New XML data type XML Indexes XQUERY Support XML Schema (XSD) support FOR XML PATH XML Data Manipulation Language SQLXML 4.0 Database Maintenance Backup and Restore Enhancements Checksum Integrity Checks Dedicated Administrator Connection Dynamic Configuration AWE Highly-available Upgrade Online Index Operations Online Restore Management Tools MDX & XML/A Query Editor Maintenance Plan Designer Source Control Support Profiler access to non-sa SQLCMD Command Line Tool Database Mail Performance Tuning Profiling Analysis Services Exportable Showplan & Deadlocks Profiler Enhancements New Trace Events Full-text Search Backup/Restore includes FT catalogs Multi-instance service SQL Client .NET Data Provider Server Cursor Support Multiple Active Result Sets Security Catalog and meta-data security Password policy enforcement Fine Grain Administration Rights Separation of Users and Schema Surface Area Configuration Notification Services Embed NS in existing application User-defined match logic Analysis Services Event Provider Replication Seamless DDL replication Merge Web Sync Oracle Publication Peer to Peer Transactional replication Merge replication perf and scalability New monitor and improved UI Analysis Services and Data Mining Analysis Management Objects Windows Integrated Backup and Restore Web Service/XML for Analysis Integration Services and DM Integration Eight new Data Mining algorithms Auto Packaging and Deployment Migration Wizard Integration Services New high performance architecture Visual design and debugging environment Extensible with custom code and scripts XML task and data source SAP connectivity Integrated data cleansing & text mining Slowly changing dimension wizard Improved flow control Integration with other BI products Reporting Services Report Builder Analysis Services Query Designer Enhanced Expression Editor Multi-valued Parameters Date Picker Sharepoint Web Parts Floating Headers Custom Report Items XML Data Provider

Major Subsystems: 

Major Subsystems Database Engine Up to 50 instances in Enterprise Edition Analysis Services Cluster aware, multiple instances SQL Server Integration Services Notification Services Reporting Services SQL Server Agent Fulltext Search Service Broker All have 32-bit and 64-bit versions

New Management Tools: 

New Management Tools New integrated tools suite Support for all SQL Server products Support for: SQL Server 7.0 SQL Server 2000 SQL Server 2005 New operations capabilities SQL Server Agent Core performance and scalability enhancements

Database Tuning Advisor : 

Database Tuning Advisor Formerly “Index Tuning Wizard” Not just for indexes and indexed views Adds partition and multi-database tuning support Exploits new indexing features For example: Index with Included Columns, Indexed View enhancements High availability recommendation mode recommends only indexes that can be built online

SQL Server Service Broker: 

SQL Server Service Broker Guaranteed, asynchronous message queuing Sent or Received by internal or external processes Fully transactional Queuing uses internal transactions

Failover Solutions At A Glance: 

Failover Solutions At A Glance Both Provide Automatic detection and failover Manual failover Transparent client connect Zero work loss Database Views mitigate DBA or application error Database Mirroring Database scope Standard servers Fastest failover Limited reporting on standby Duplicate copy of database Failover Clustering System scope Requires certified hardware No reporting on standby Single copy of database

Fast RecoveryRestart or Restore: 

Fast Recovery Restart or Restore SQL Server 2000 Database is available after Undo completes SQL Server 2005 Database is available when Undo begins Undo Redo Available Undo Redo Available Time

Programmability: 

Programmability Common Language Runtime (CLR) Assemblies Can write Stored Procedures, Functions, Triggers, Data Types, Aggregates in any CLR programming language Synonym – Alternative name for an object Can change name or location (schema, database, or server) of base object Can use a simple 1-part synonym name in place of the more complex 2-, 3-, or 4-part name of the base object User-Defined Aggregate Functions Multiple Active Results Sets Multiple results sets on a single connection Do not need to use cursors to do this anymore

Data Types: 

Data Types Varchar(1-8000), Nvarchar(1-4000), Varbinary(1-8000) Maximum column length still 8,000 bytes Rows can now exceed page size Varchar(max), Nvarchar(max), Varbinary(max) Can hold greater than 8000 characters / bytes XML – A full-fledged data type UDTs – User-defined data types using CLR

What is SQL Server Integration Services?: 

What is SQL Server Integration Services? The successor to Data Transformation Services A new SQL Server Business Intelligence application The platform for a new generation of high performance data integration technologies

SQL Server Integration Services: 

SQL Server Integration Services Complete rewrite in SQL Server 2005 If you used Data Transformation Services (DTS) before, you will not recognize it now! Pipeline architecture Included numerous previously developer coded tasks Error flows allow bad data to be handled in the pipeline Checkpoint / restart of packages Rich development environment based on Visual Studio Configurations for packages (Dev / Test / Deploy), digital signing Many logging and monitoring options New data sources, including Web services

Analysis Services: 

Analysis Services provides a rich & friendly business view of complex, heterogeneous data sources. Supports variety of data experiences: OLAP pivoting, “Relational” reporting, executive KPI dashboards, predictive analytics, advanced visualizations and LOB apps. Analysis Services Converts data to Business Information Analysis Services OLAP is a technology that facilitates data viewing, analysis, and navigation. More than a particular storage technology, OLAP is a conceptual model for viewing and analyzing data.

What’s A Multidimensional Data Cube?: 

What’s A Multidimensional Data Cube? Products Stores Time Sales Dollars Data Cube

Data Mining: 

Data Mining The Database Contains the Information Good/bad customers Profitability The type of customer that did/did not respond to direct mail/survey... Can show the relationship between outside temperature and sales of canned soup Included at no additional cost Significant investment Important, new algorithms Association rules, time series, regression trees, sequence clustering, neural nets, Naïve Bayes

What’s New in SQL Server 2008?: 

What’s New in SQL Server 2008?

List of New Features (So Far): 

List of New Features (So Far)

Some Interesting 2008 Features: 

Some Interesting 2008 Features

New Features Detailed: 

New Features Detailed

New Features Detailed (con’t): 

New Features Detailed (con’t)

New Features (con’t): 

New Features (con’t)

Still More New Features (con’t): 

Still More New Features (con’t)

Yet More New Features (con’t): 

Yet More New Features (con’t)

The Good and Bad News: 

The Good and Bad News

Where and When : 

Where and When

Slide34: 

Questions…