overview timetopicspeaker 1430-1500what's new overview for dba scalability roger 1500-1530bi...
Post on 19-Jan-2018
228 Views
Preview:
DESCRIPTION
TRANSCRIPT
SQL Server 2008 R2
Overview
Michael EpprechtMicrosoft (Schweiz) GmbHmichael.epprecht@microsoft.com
Roger BreuMicrosoft (Schweiz) GmbHroger.breu@microsoft.com
AgendaWhat‘s New Overview
25‘SQL Server 2008 R2 Scalability
10‘BI Overview
25‘
New in SQL Server 2008 R2
New Editions
SQL Server 2008 R2 DataCenter
SQL Server 2008 R2 Parallel Data Warehouse
SQL Azure (Databases in the cloud)
New Capabilities
Scale to 256 logical processors
Master Data Services
High Scale Complex Event Processing with StreamInsight
Massive Parallel Processing (appliance like Model with PDW)
New FastTrack Reference Architectures with HP, DELL,
BULL, IBM, EMC, NEC
Report Builder 3.0
New Features
Data Compression for Unicode Datatypes
Application & Multiserver Management
SysPrep for Standalone SQL Server Instances
Management Tools support for SQL Azure: SSMS, SCLCMD, SMO
Hyper-V Live Migration Support
PowerPivot for SharePoint
PowerPivot for Excel
Publish and reuse report part to and from Report Gallery
Support report as data source through ATOM data feed
Map Support in Reporting Services (Bing, ESRI, SQL Spatial Shapefile)
Sparklines, Data bar, KPI in Reporting Services
Data-tier Application Project in Visual Studio 2010
* ~Data rate: <5000 events/sec, ~Latency: >5 s, embedded in ISV Apps/on the Edge** ~Data rate: >5000 events/sec, ~Latency: <5 s, DataCenter Server Setups*** Procs are physical sockets
Capabilities by Core EditionsStandard Enterprise Datacenter
Memory 64GB RAM 2TB RAM OS Max
CPU Support 4 Procs*** Up to 8 Procs >8 Procs Up to 256 cores
Virtualization Licensing Virtualization 1 VM/ license Virtualization 4 VM/
license Max. Virtualization
Virtualization Support Hyper-V Live Migration Hyper-V Live Migration Hyper-V Live Migration
OLTP & Multi-Server Mgt.
Backup Compression (new from Enterprise)Support for Managed Instance
Control Point for up to 25 instances
Control Point to manage max number of instances
Business Intelligence
Componentized Reports (SSRS)Advanced Visualizations (maps)*Basic StreamInsight
PowerPivot for SharePointMaster Data ServicesUnicode Compression*Basic StreamInsight
PowerPivot for SharePointMaster Data ServicesUnicode Compression**Parallel High-Scale StreamInsight
SysPrep
Similar to Windows SysPrepDifference from Windows SysPrep:
We do not fully configure SQL Server and then remove the configuration settings during Windows SysPrep
SysPrep supported for Relational Engine and Reporting Services
Rolling Upgrades for Service Packs since SQL Server 2008
SysPrep in SQL Server 2008 R2
Application and Multiserver Management
Database Management Trends
Number of
databases
Number of DBAs
2000 2010
Hardware capacit
y
Underutilized
hardware
Overburdened
administrators
Future
Customer ProfilesHundreds of small databasesMost DBs have one data fileAverage DB size < 4 GBReactive management – fix it when get a phone callDifficult for developers and DBAs to manage app lifecycleTime to problem resolution keeps shrinking
Multi-Server Management
DO NOT ERASE!
The SQL Server Utility
Central point of reasoning for multi-server management
Simple to setup and use - a key advantage over competitors
Management Studio
DBA
SQL0
2
SQL0
3
SQL0
4
SQL0
1Managed Instances
SQL0
5
UCP
Measure Data Visualization
CPU Utilizatio
n
Storage Utilizatio
n
Health State Visualization
Dashboard
List View
Detail View
DBA
SQL Server Management Studio
Make Changes,
Create policies
6
Hand-off to DBA8
DEVELOP DEPLOYMANAGE
DBA
SQL Server Management Studio
Visual Studio 2010
Developer
Managed Server Group
Finance
DBA
Create Control Point,
ControlPoint1
Managed Server Group
ControlPoint
Finance
Enroll Instances, Register
DACs
2
3
Reverse Engineer
DAC
4
5Handoff to Dev
Compile+ Build
.dacpac
7
Deploy /Upgrade
DAC
9
SQL Server StreamInsight
What is CEP?Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency.
Database Applications
Event-driven Applications
Query Paradigm
Ad-hoc queries or requests
Continuous standing queries
Latency Seconds, hours, days Milliseconds or lessData Rate Hundreds of
events/secTens of thousands of events/sec or more
request
response
Eventoutput streaminput
stream
Late
ncy
Months
Days
Minutes
Seconds
100 ms
< 1ms
0
Aggregate Data Rate (Events/sec.)10 100 1,000 10,000 100,000 ~1million
Relational Database Applications
Manufacturing ApplicationsMonitoring Applications
Web Analytics Applications
Financial Trading
Applications
Data Warehousing Applications
Operational Analytics Applications,
e.g., Logistics, etc.
StreamInsight Scenarios
Scenarios for Event Processing
Overview: CEP Platform from Microsoft
CEP Engine
Output Adapters
Input Adapters
Event
Standing Queries
Event sources Event targets
`
Event
Event
Event
Event
Event
Event
C_ID
C_NAME
C_ZIP
Event
Event
Static reference data
CEP Application Development
Development experience with .NET, C#, LINQ and Visual Studio 2008
CEP platform from Microsoft to build event-driven applications
Event-driven applications are fundamentally
different from traditional database applications: queries are continuous, consume and produce streams, and compute results incrementally
Flexible adapter SDK with high
performance to connect to different event sources and
sinks
The CEP platform does the heavy lifting for you
to deal with temporal characteristics of event
stream data
Microsoft’s CEP Solution
Data Sources, Operations, Assets, Feeds, Sensors, Devices
Monitor &
Record
Operational Data Store & Archive
CEP Engine
f(x) g(y)
CEP Enginef(x) f'(x)
g(y) h(x,y)
History
Deploy
Resultsf'(x) h(x,y)
Manage&
Benefit
Mine&
Design
Input Data Streams
Input Data Streams
OutputData Streams
StreamInsight DeploymentLightweight embedded engine
StreamInsight is available as a set of DLLsStreamInsight can be included into your applicationsLow footprint, small overheadFacilitates deployments close to the data source
StreamInsight Windows serviceRuns the engine as a Windows serviceApplications can share incoming streamsWell-suited for more centralized deployments
InstallationSmall, lightweight MSIInstalls in 2 minutes
SQL Server Scalability
• New Max Specs• TPC Benchmarks
Single-system scalabilityImproved performance (parallelization)Industry trend – soon industry standard servers with 8CPU will have 128 logical processorsIncreased flexibility in deployment (Single vs. Multiple Instance)
We have customers who really need to scale up!
Why scale up?
Category MetricLargest single database 80 TBLargest table 20 TBBiggest total data 1 customer 2.5 PBHighest transactions per second 1 db
36,000
Fastest I/O subsystem in production
18 GB/sec
Fastest “real time” cube 15 sec latencydata load for 1TB 20 minutesLargest cube 4.2 TB
Top statistics for SQL Server
Large Scale customer implementations
RSS Aggregator NewsGator Manages 2.5 Billion Articles with SQL Server 2008; high availability with database mirroring
Astronomical database mapping the sky
Global Online Gaming Company Deploying 100 Terabytes database
PLM Software supports 5,000 Users with SQL Server 2008; 50 % data compression
1 petabyte1 million
transactions per day
5000 concurrent
users
2.5 billion articles
Avatar’s digital image library built on SQL Server
> 1 petabyte
Improved scalability with up to 256 logical processors (determined by operating system maximum) Support for memory in excess of 2TBSupport for Solid State disks
Improved I/O performance with UCS-2 Unicode and non-Unicode data compressionFaster, smaller backups with backup compressionResource utilization management with Resource Governor and Windows System Resource Manager
Benefit from Scalable and Predictable Performance for Physical Servers
SQL Server0
500
1000
1500
2000
2500
2,012.70
SQL Server Real World Performance*
tpsE
* Source: www.tpc.org, As of November 23, 2009
Support for up to 256 CPUs with SQL Server 2008 R2 Datacenter EditionSQL Server performance demonstrated in TPC–E benchmarks
TPC-E most accurately reflects typical workloadsWorld record TPC-E benchmark, November 2009
A new world-record TPC-E benchmark resultUnisys ES7000 Model 7600R Enterprise ServerWindows Server 2008 R2/SQL Server 2008 R2 DC x64performance record of 2,012 tpsE (transactions per second for the TPC-E benchmark)96-core Xeon platform
same Unisys server achieved 102,778 QphH (queries per hour) on the TPC-H 3TB benchmark, showing data warehousing performance 70 percent higher than the previous record on Windows. A Microsoft Dynamics CRM benchmark run on IBM System x3650 M2 servers with Intel Solid State Drives demonstrated 20,000 concurrent users across 20 xRM applications on Microsoft Dynamics CRM with an average response time of 0.10 seconds.
TPC Benchmarks
Unisys SQL PowerRackExtremely balanced architectureIntel Xeon 7400 Series 6-Core or 4-core � �processors Up to 16 sockets / 96 cores Up to 1TB memoryUp to 56 PCIe slotsTruly useable hot-plug PCIe>99.995% availability; Enterprise-class RASES7000 is integrated with a powerful DSI3600 disk SAN Storage subsystemUp to 3-GB/sec, and 250,000 IOPS per unit up to 10 units per PowerRackWindows Server 2008R2/SQL Server 2008 R2
Config Cores
RAM Storage
IO Bandwith
Entry 24 256GB 4-10TB 4000MBMid Range 48 512GB 8-20TB 8000MBPerformance
96 1024GB
16-50TB
16000MB
New Fast Track Data Warehouse 3.0 References will be anounced…
Software:SQL Server 2008/R2 EnterpriseWindows Server 2008/R2
Hardware:Tight specifications for servers, storage and networking‘Per core’ building block
Configuration guidelines:Physical table structuresIndexesCompressionSQL Server settingsWindows Server settingsLoading
SQL Server Parallel Data WarehouseA data warehouse appliance with massive scalability
High Scalability from 10s to 100s of TBHigh scale through Massively Parallel Processing (MPP) systemChoice of hardware vendorLow cost through commodity hardwareDeep integration with Microsoft BI
Demo Session Tomorrow
Master Data Services
Wikipedia
Master data management (MDM) comprises a set of processes and tools that consistently defines and manages the non-transactional data entities of an organization.
What is Master Data Management?
Inconsistencies cost –
mistakes, incorrect reports
Master Data Challenges
No single, trusted
source of the truth
Multiple stakeholder
s for the same record
No dimension
maintenance capability
No system to maintain attributes
Conflicting Views of Product,
Customer Location, etc
Inability to enforce
data stewardshi
p processes
MDS Capabilities
Master Data Hub
Built on SQL Server
Authoritative Source
Versioning & Transaction
Logging
Stewardship Portal
Data Steward CrUD
experienceMaster Data
Administration
Modeling
Model Documentation
Import & Export
Stewardship
Process
Workflow
Notifications
Business Rules
Master Data
Platform
Any Domain
Model Deployment
API Supports all Operations
Stewardship Portal
38
Data Steward CrUD
experienceMaster Data
Administration Modeling Model Documentation
Import & Export
MDS
Stewardship Process
Master Data Services Layer
Stewardship Portal
Change Notification
Bulk Extract
Create Notification
ERP 1
Master DataDatabase
LayerDataWarehouse
Item Create
Attribute Change
Approval Request
ERP 2
MDS Opportunities
Create Domain-specific models
Business model consulting
Extend platform into existing processes
Master Data
Platform
Any Domain
Model Deployment
API Supports all Operations
PowerPivot
Add-in for Excel 2010
42Microsoft Confidential—Preliminary Information Subject to Change
Import From Many Data Sources
43Microsoft Confidential—Preliminary Information Subject to Change
Import Many Rows
44Microsoft Confidential—Preliminary Information Subject to Change
Use Standard Excel Functions
45Microsoft Confidential—Preliminary Information Subject to Change
Use Standard Excel Formulas
46Microsoft Confidential—Preliminary Information Subject to Change
… plus some others
Import Data Feeds from Reports
47Microsoft Confidential—Preliminary Information Subject to Change
Build PivotTables
48Microsoft Confidential—Preliminary Information Subject to Change
Relationships Detected
49Microsoft Confidential—Preliminary Information Subject to Change
Analytic Application Created
50Microsoft Confidential—Preliminary Information Subject to Change
Analytic Application Created …
51Microsoft Confidential—Preliminary Information Subject to Change
Share with the rest of the Team
… and Published …
53Microsoft Confidential—Preliminary Information Subject to Change
… with Fidelity
54Microsoft Confidential—Preliminary Information Subject to Change
What about managing all of this?
Reporting Services
Saving Report Parts
Saving Report parts
And re-using them
Version Control
Maps in Reporting Services
Thank you for your Attention!For more Information please contact
Michael EpprechtIT Pro Evangelist
michael.epprecht@microsoft.comTel +41 43 456 66 68Mobile +41 78 844 66 68
Microsoft Schweiz GmbHRichtistrasse 3CH-8304 Wallisellen
Roger BreuTechnology Solution Professional DataPlatform
roger.breu@microsoft.comTel +41 43 456 61 16Mobile +41 78 844 61 16
Microsoft Schweiz GmbHRichtistrasse 3CH-8304 Wallisellen
top related