Microsoft SQL Server 2014

Microsoft SQL Server 2014
In-Memory „built-in“
Erich Sageder
Solution Specialst | Application Platform
[email protected]
David Zeller
Solution Architect
[email protected]
DIE ZUKUNFT
SQL SERVER
DIE RESIE
Microsoft and SAP
Bewältigt die weltweit größten SAP
Workloads
Microsoft Technologie für SAP bewältigt die größten SAP
Workloads – auch virtualisiert!
47,500
60,000
259,680
8
120
240
Größter virtualisierter SAPSD 3 Tier Workload
bereits 2005
SQL Server als Plattform für SAP
Datensicherheit
Ausfallsicher
*lt. National Institute of Standards and Technology Comprehensive Vulnerability Database für 2013
Microsoft In-memory Technologien
In-Memory für BI / Analytics
Columnstore
representation
with xVelocity
*Seit 2012 von SAP für SAP BW 7.x zertifiziert
C C
1 2
C
3
C
4
C5 C
6
In-Memory für OLTP
Microsoft In-memory Analytics für SAP
Microsoft Power BI
SAP BW
SAP ERP
(ECC)
Microsoft BI
Microsoft BI
BI Semantic Model
Microsoft
Analytic Platform System
Non-SAP
Daten
SAP BW
Non-SAP
Daten
SAP ERP
(ECC)
Non-SAP
Daten
SAP ERP
(ECC)
strukturiert
und
unstrukturiert
Self-Service BI
Enterprise Self-Service
Near Realtime BI
In-memory in Excel für
ALLE SAP Plattformen
In-memory für ALLE
SAP Plattformen
Skalierbar bis 5 Petabyte!
SAP „fremde“ Daten
SAP „fremde“ Daten
Unstruktierte Daten („Big
Data“)
Wie kann ich SAP mit Microsoft
betreiben?
Vorteile für SAP BW?
SQL Server 2012 ColumnStore im Vergleich mit einer „New
Technology Database“
“New Technology Database” was faster with aggregations (no
flexibility)
“New Technology Database” required 4-8 times more
resources than SQL Server did
Datenbank Server
New Tech
„New Technology“
Database
SQL Server
NTDB POC
Cores
SQL Server was faster with medium aggregations in 70% of the
queries (10 of 14)
24
160
Memory
512 GB
2048 GB
SAPs
42.000
289.000
W&W Shared Storage
EMC VNX 5300
Ja
Nein
8
4
14.000
7.000
Ja
Ja
1x Dell R820
4x Cisco B440 M2
4 x Intel 4650 (8 Cores)
4 x Intel 4650 (10 Cores)
Storage
Virtualisiert
Application Server
Cores
SAPs
Virtualisiert
NTDB
Physische Hardware
SQL Server was always faster with ad-hoc queries
(Self-Service BI)
NTDB
Modell
Prozessor
SAP auf SQL Server im Einsatz
Ergebnisse aus Projekten in Österreich
Verbesserung der Laufzeiten um
durchschnittliche
90%
= Faktor 9x. In Spitzenfällen bis zu
einem Faktor 300x
Reduktion des Speicherbedarfs um
Inbetriebnahme des POC Systems
in nur
80%
48h
Installation, Migration, Tuning (SAP
+ SQL Server)
Die Verbesserungen wurden erzielt/gemessen im Vergleich zu bestehenden produktiven Systemen basierend auf unterschiedlichen Datenplattformen (Oracle, SQL Server, DB 2, …). Vorsysteme ohne
Column Storage und Daten Komprimierung.
SAP auf SQL Server in real-life
Ergebnisse aus Projekten in Österreich
Verbesserung der Laufzeit für
Abrechnungsläufe
50%
Reduktion des Speicherbedarfs um
Inbetriebnahme des POC Systems
in nur
75%
20h
Installation, Migration (SAP + SQL
Server)
Die Gründe der Leistungssteigerung
SAP on Microsoft Technology
Verwendung von SAP
zertifizierter SQL Server inmemory Technology (SQL Server
buffer cache, ColumnStore)
Speziell für schnelle Abfragen im
SAP Business Warehouse:
Spaltenbasierte in-memory
Speicherung der Daten.
ColumnStore bringt
darüberhinaus maximale
Kompression der Daten.
Massive Reduktion des
Speicherbedarfs und dadurch
Beschleunigung von IO
Operationen. Daten können erst
durch Datenkompression inmemory gehalten werden.
Moderne Industrie Standard
Server mit 2-4 CPUs, 512 GB –
1TB Memory und Flash Storage
Kosten reduzieren, Effizienz steigern
SQL Server Referenzen
VLDB
Mission
Critical
HA/DR
Data
Warehouse
Large U.S. Financial
Organization
>1 PB
Critical operations
with > 1,000,000,000 tx/day
Large U.S. Financial
Organization
AlwaysOn, clustering,
log shipping
Large Manufacturing
Organization
80 TB
4 TB/month growth
>80 GB daily growth
>50 TB
>90 GB daily growth
High-Volume
OLTP
180M incremental
fact rows/day
> 500k tx/second
< 10ms latency
Centipede
Mission-critical
database > 120 TB
7x24x365 Synchronous Mirroring
>15B tx/yr
Critical operations
with > 15,000 users
SAP | geo-cluster > 5 miles
No data loss | No downtime
Critical operations
at > 2,200 facilities
AlwaysOn > 400 miles
< 15 sec. recovery
490 TB
9 data nodes
70 GB daily growth
<= 3s query response (80%)
700 million rows/day
Incremental growth
> 60,000 DB tx/second
> 3,000 tx/second
> 2B CLR calculations/day
12.4M users (2.7M regular)
> 23k batch requests/sec
Large OLTP
>10 billion rows in 1 table
Mission-critical
table > 1.9 billion rows
850 mile geo-cluster –
async
5 TB Analysis Services data
50 TB federated environment
100 TB database
1.5 trillion row table
Top SAP Kunden mit Microsoft
Size of ERP Database in Production
10–25 terabytes
5–9 terabytes
2–4 terabytes
1–2 terabytes
Conclusio
• In-memory sinnvoll einsetzen
•
•
•
•
•
für bestehende Hardware
(keine CPU Core Monster)
Keine Abstriche für In-memory im Betrieb z.B. bei
Hochverfügbarkeit und Disaster Recovery, Backup usw.
Real Time BI & Analytics
SAP und nicht-SAP Welt kombinieren – auch für „Big Data“
Beste Kompression - Kosten pro TB so gering wie möglich!
Bestes Preis / Leistungsverhältnis - TCO
© 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to
changing market conditions, it should not be interpreted to be a commitment on the
part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR
STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
NEC Corp., Japan
About NEC:
Large IT Services Provider based in Japan
Revenue 2012: $38,8 Billion, 142,000 employees
About the project:
Consolidated SAP system for 100+ business processes
Replacement for several older UNIX-based core IT-systems
Standardization to the Microsoft Platform
One of the largest SAP systems in the world
120.000 SAP users
At peak times: 7.000+ concurrent SAP users and 1.5 Million transactions per
day
Database size >25 TB
Japan Airlines
About JAL:
• Japan’s second largest airline
• 122 aircrafts (+46 orders), more than 44 Mio. passengers / year
About the project:
• JAL implemented SAP systems on Windows/SQL Server over the last 10 years
• In 2008 JAL implemented Airplane Maintenance module of SAP based on Windows/SQL
Server 2005 platform
• System is used at point of airplane docking and undocking in all airports JAL is heading
to
• System is used to administrate 500K spare parts in different worldwide locations
• System is used to keep maintenance and repair records of each JAL airplane
• Quote from JAL:
‘Without this system being up and running our airplanes would not be able to leave the
airport’
• Live for more than 18 months without a single unplanned outage
• HA configuration:
Onsite Windows Clustering
DR site using SQL Server Log-Shipping
Quanta, Taiwan
About Quanta:
Largest computer manufacturer in the world
Revenue: over $30 Billion, 30,000 employees
Developed out of traditional OEM to ODM business
About the project:
Started SAP R/3 on Windows/SQL in 2000
Approach of having one SAP ERP system per manufacturing location
Running FI, SD, PP, MM, PP, Logistics functionality, heavy .NET integration with
Intranet applications
2007 start of project to consolidate 9 SAP ERP systems into one system
DBMS hardware: HP DL980 (around 100K SAPS)
SAP application layer: 20 x 2-socket servers based on Xeon 5570
Total system size ~700K SAPS
In meantime large database growth of around 600GB/month. Could get solved
with SQL Server database compression
Database volume ~7TB, growing around 200GB/month
Everything fully page compressed
Based on Compression rates achieved, estimated to be around 25TB
uncompressed
Database Compression – Customer Experiences
Page compression rates experienced by Quanta
on some of their productive tables:
Table
Compression rate
COSP
80%
GLPCA
89%
COEP
87%
RESB
90%
ACCTIT
87%
MSEG
87%
FAGLFLEXA
88%
BSIS
90%
COSB
84%
GLFUNCA
89%
Up to 90%
compression:
Significant storage
cost savings
More details: http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000010953
DTEK, Ukraine
About DTEK:
Largest privately owned, vertically integrated energy company in Ukraine
Revenue (2012): over $10 Billion, 50,000+ employees
Developed out of traditional OEM to ODM business
About the project:
Implemented SAP in 2010 on Windows Server 2008 R2 and SQL Server 2008 R2
Centralized IT infrastructure based on the Microsoft platform that includes
clustered databases with 9 TB of data, numerous virtual servers, and SAP business
applications
Managed to reduce downtime by 20 percent
Decreased maintenance costs by 25 percent
Managed to cut support costs by up to 85 percent
14 HP ProLiant BL460c blade server computers
Heavily usage of virtualized Servers using Hyper-V including the Live Migration
feature to facilitate the automatic failover of a virtual machine to another physical
host in the event of a server outage
Miele, Germany
About MIELE:
Global premium brand of domestic appliances and commercial machines in the
field of laundry care, dishwashing and disinfection
Revenue (2012): over $3.5 Billion, 16,700+ employees
About the project:
Migration from AIX/Oracle to the Microsoft Platform
Main reasons for the migration:
Reduction of hardware costs,
Standardization of hardware and operating system platforms
Availability improvements of the SAP Landscape
Already in production using virtualized SAP application servers on Windows
Server 2012 Hyper-V and guest OS Windows 2008 R2 for one of MIELES most
critical SAP system
Total SAPS of the entire SAP landscape: 225,000
Lockheed Martin, USA
About LOCKHEED MARTIN:
One of the world’s premier global security and aerospace companies
Revenue (2012): over $47.2 Billion, 120,000+ employees
About the project:
Migration from Unix/Oracle to the Microsoft Platform
Main reasons for the migration:
Cost reduction
Scalability improvements
Availability improvements of the SAP Landscape
Return on Investment (ROI) for the migration project in 3 years
Windows Server 2008 R2, SQL Server 2008 R2
3,300 names SAP users, 1,400 concurrent SAP users
DB size: 3 TB (before migration), ~800 GB after migration (74% reduced
database size)
Database Compression – Customer Experiences
CLP – China Light Power
Utility company in Hong Kong with ~2.4 million customer contracts.
Annual Revenue: ~USD 7.5 Billion
Compression of the SAP IS-U system resulted in:
Reduction in DB volume from 1.85TB to 500GB (-74%)
Reduction of DB response time/dialog step from 150ms to 100ms (-33%)
Reduction of nightly batch job runtime from 3:15h to 2:05h (-36%)
Backup size reduction from 307GB to 210GB (-32%)
Backup run time reduction from 69min to 32min (-54%)
More details: http://blogs.msdn.com/b/saponsqlserver/archive/2011/02/21/clp-optimizestorage-improve-performance-amp-streamline-dba-tasks-with-sql-2008-r2-pagecompression.aspx
Vinzenz Gruppe Referenz
Die Vinzenz Gruppe ist ein Verbund von 7
Krankenhäusern und 6000 Mitarbeitern.
Asklepios Kliniken
Asklepios ist der größte
Spitalsbetrieb
Deutschlands mit >10000
Usern in Hamburg alleine
Unikliniken Aachen Referenz
Das Universitätsklinikum Aachen ist ein
regionales Zentrum für Patientenpflege,
Forschung und Lehre mit 245000 Patienten
und 2700 Studenten jährlich.
Deliverables from Microsoft
• Contact with Microsoft Headquarters
Connection with Microsoft IT and SQL Server
Development resources for sharing best practices on
running SAP on the Microsoft Platform
• SAP on SQL Blog
Valuable advise and insights from the SQL Development
Team from SAP HQs in Walldorf:
http://blogs.msdn.com/b/saponsqlserver/
Highlights of the SQL Server Releases
SQL Server 2012: SAP related features
New Features:
• AlwaysOn
• xVelocity Memory-Optimized Columnstore Index
• Windows Server Core Support
• Auditing Enhancements
Enhancements:
• Data Compression
• Backup Compression
• Support for up to 15.000 table partitions
• Transparent Data Encryption
• Dynamic Threshold for update Statistics
• Extended support for online index building