SQL Database Technologies for Developers
Download
Report
Transcript SQL Database Technologies for Developers
Consistent experience is everything
Cloud-First Approach
New innovations
SQL DB
SQL DB
SQL DB
SQL Server 2016
Continuous
enhancements
SQL DB
Advanced Analytics
Across customer base
End-to-end mobile BI
Speed
Proven
Agility
Feedback
Enterprise-grade DW
Mission critical OLTP
The best
SQL Server
release in history
Learns
& adapts
Scales
on the fly
Efficient for SaaS
developers
Works in your
environment
Secures and
protects
Learns and adapts with
your application
Anomaly detection
Performance insight
Index advisor
Actionable
recommendations
With no app downtime
5
10
20
50
100
125
250
500
Database transaction units
Basic
Standard
Premium
1,000
1,750
Elastic Database Pools
Basic
Standard
Premium
Min/max DTUs per second per DB
125
200
400
1,000
1,200
Works in your
development
environments
Protects and
secures your app’s
data
SQL Server 2016
Industry Leader
[1] *Gartner “Magic Quadrant for Operational Database Management Systems,” by Donald Feinberg , Merv Adrian , Nick Heudecker, Adam Ronthal, October 2015
[2] *Gartner “Magic Quadrant for Business Intelligence and Analytics Platforms,” by Rita Sallam and Josh Parenteau, February 23, 2016
[3] *Gartner “Magic Quadrant for Data Warehouse and Data Management Solutions for Analytics,” by Mark Beyer and Roxane Edjlali, February 12, 2015
[4] *Gartner “Magic Quadrant for Advanced Analytics Platforms,” by Lisa kart, Gareth Herschel, Alexander Linden, Jim Hare, February 9, 2016.
This graphic was published by Gartner, Inc. as part of a larger research document and should be evaluated in the context of the entire document. The Gartner document is available upon request from Microsoft. Gartner does not endorse any vendor, product or service depicted in its research
publications, and does not advise technology users to select only those vendors with the highest ratings or other designation. Gartner research publications consist of the opinions of Gartner's research organization and should not be construed as statements of fact. Gartner disclaims all warranties,
expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose.
SQL Server Database Engine
Relational, XML, JSON, Spatial,
FullText, Binary, Image, FileTable,
Filestream
In-Memory
Buffer Pool Ext.
Columnstore
Query Store
Row Store
StretchDB
Resource
Governor
Row level security, Transparent Data
Encryption, Always Encrypted, Data
Masking, Auditing, Compliance
SQL Agent, Database Mail, Linked
Servers, Managed Backup, Backup
to Azure
Data Warehousing
AlwaysOn
Dimensional Modeling, Star,
Snowflake, Polybase
Replication
Log Shipping
FCI
Reference
Architectures
AG
Polybase
Physical / Virtual
Deployment
Reporting Services
Native, SharePoint Integrated
R Services
HA/DR
Data Quality Services
Master Data Services
Integration Services
Analysis Services
Tabular, Multi-Dimensional
PowerPivot, BISM, Data Mining, KPI, BISM
Appliances
Polybase
APS
Massively Parallel Processing
SQL Server Database Engine
Relational, XML, JSON, Spatial,
FullText, Binary, Image,
FileTable, Filestream
In-Memory
Buffer Pool Ext.
Columnstore
Query Store
Row Store
StretchDB
Resource
Governor
Row level security, Transparent
Data Encryption, Always
Encrypted, Data Masking,
Auditing, Compliance
SQL Agent, Database Mail, Linked
Servers, Managed Backup, Backup
to Azure
Data Warehousing
AlwaysOn
Dimensional Modeling, Star,
Snowflake, Polybase
Replication
Log Shipping
FCI
Reference
Architectures
AG
Polybase
Physical / Virtual
Deployment
Reporting Services
Native, SharePoint Integrated
R Services
HA/DR
Data Quality Services
Master Data Services
Integration Services
Analysis Services
Tabular, Multi-Dimensional
PowerPivot, BISM, Data Mining, KPI,
BISM
Appliances
Polybase
APS
Massively Parallel
Processing
SQL Server Database Engine
Relational, XML, JSON, Spatial,
FullText, Binary, Image,
FileTable, Filestream
In-Memory
Buffer Pool Ext.
Columnstore
Query Store
Row Store
StretchDB
Resource
Governor
Row level security, Transparent
Data Encryption, Always
Encrypted, Data Masking,
Auditing, Compliance
SQL Agent, Database Mail, Linked
Servers, Managed Backup, Backup
to Azure
Data Warehousing
AlwaysOn
Dimensional Modeling, Star,
Snowflake, Polybase
Replication
Log Shipping
FCI
Reference
Architectures
AG
Polybase
Physical / Virtual
Deployment
Reporting Services
Native, SharePoint Integrated
R Services
HA/DR
Data Quality Services
Master Data Services
Integration Services
Analysis Services
Tabular, Multi-Dimensional
PowerPivot, BISM, Data Mining, KPI,
BISM
Appliances
Polybase
APS
Massively Parallel
Processing
Faster with SQL
2016
•
•
•
•
Non-volatile Memory Support
https://msdn.microsoft.com/en-us/library/ms810613.aspx
Backing Storage
Row Size
(Bytes)
Transactions / s
Latency (ms)
SSD* (Block)
1024
33,592
0.71
SSD* + NVDIMM-N (DAX)
1024
112,270
0.21
Create Table DDL
CREATE TABLE [Customer](
[CustomerID] INT NOT NULL PRIMARY KEY NONCLUSTERED
HASH WITH (BUCKET_COUNT = 1000000),
[Name] NVARCHAR(250) NOT NULL,
[CustomerSince] DATETIME NULL
INDEX [ICustomerSince] NONCLUSTERED
INDEX [ICustomerID-Since] NONCLUSTERED
Create Procedure DDL
(CustomerID, CustomerSince)
CREATE PROCEDURE [dbo].[UpdateOrder]
)
@id INT, @date DATETIME
WITH (MEMORY_OPTIMIZED = ON,
WITH
DURABILITY = SCHEMA_AND_DATA);
NATIVE_COMPILATION,
SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = 'us_english')
-- insert T-SQL here
End-to-end mobile BI
Support for Temporal Data
Track historical data
• Data changes over time (!)
• Databases naturally
provide a current view
• But an historical
perspective is often critical
Time travel
Data audit
Predictive analytics
Repair record-level corruptions
End-to-end mobile BI
CREATE TABLE Products
(
SKU int NOT NULL PRIMARY KEY CLUSTERED
, ProductName varchar(50) NOT NULL
, CatogoryId int NULL
);
SELECT * FROM Products
FOR SYSTEM_TIME AS OF '2015.01.01'
End-to-end mobile BI
CREATE TABLE Products
(
SKU int NOT NULL PRIMARY KEY CLUSTERED
, ProductName varchar(50) NOT NULL
, CatagoryId int NULL
, SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ProductsHistory))
;
Always Encrypted
Protect your data at rest and in motion
Without changes to your application
Always Encrypted
Apps
NEW
SQL Server
Trusted
SELECT Name,SSN,Country FROM
Patients WHERE SSN=@SSN
Client side
SELECT Name,SSN,Country FROM
Patients WHERE SSN=@SSN
@SSN='198-33-0987'
Column
Master
Key
Query
@SSN=0x7ff654ae6d
Enhanced
ADO.NET
Library
Result Set
Result Set
Name
198-33-0987
SSN
Column
Encryption
Key
Country
Denny Usher 0x7ff654ae6d
USA
ciphertext
dbo.Patients
Name
SSN
Country
Philip Wegner
243-24-9812
1x7fg655se2e USA
Denny Usher
198-33-0987
0x7ff654ae6d USA
Alicia Hodge
123-82-1095
0y8fj754ea2c USA
Connect to SQL from all
platform
Your choice of language and tooling
SQL Server
Azure SQL Database
ODBC
ADO.NET
JDBC
FreeTDS
ODBC
PHP
Driver
C#
VB.NET
Java
ODBC
C/C++
db-lib
PHP
Node.js
Driver
SQL DW
FreeTDS
Tedious Node.js
Driver
Pymssql
JavaScript
Pymssql
Django
Python
FreeTDS
TinyTDS
TinyTDS
Ruby
Rails
http://aka.ms/SQLDB
http://Microsoft.com/SQL
http://aka.ms/connect-to-sql
Channel 9
Microsoft Virtual Academy