Transcript SQL SERVER 2016 Azure

SQL Server 2016 –
Everything Built-in
Madhu Desarapu
Data Platform Architect
[email protected]
937 470 7006
SQL Server 2016 – Product Suite
• SQL Server Relational Engine Enhancements
• SQL Server BI Components Enhancements
• Connector to Big Data World ( Hadoop)
• Integration extension to Data Science World ( R)
SQL Server Engine Enhancements
• Security
• Always Encrypted
• Row Level Security
• Data Masking
• Performance
• Operational Analytics
• Management/Hyper Scale
• Query Store
• Strech Database
• NoSQL Support
• JSON
Big Data world ( Hadoop)
• NoSQL Storage and Compute Platform
• All type of Data
• Structured, Semi Structured, Unstructured, Streaming (IOT)
• Hadoop Platform – Foundation
• Hadoop File System
• Map Reduce /YARN
• Hadoop Applications
• Hbase,Hive,Spark,Pig,Storm
Microsoft HDInsight
•
Azure PaaS Service
• Azure Storage – Instead of Data Disks
• HDInsight Clusters
• Hive ,Storm,Hbase,Spark
• SQL Server 2016 Feature
Microsoft platform leads the way on-premises and cloud
Leader in 2014 for Gartner Magic Quadrants
Operational
Database
Management
Systems
Data
Warehouse
Database
Management
Systems
Business
Intelligence
and Analytics
Platforms
x86 Server
Virtualization
Cloud
Infrastructure
as a Service
Enterprise
Application
Platform as a
Service
Public Cloud
Storage
The evolution of SQL Server
Mission critical
Performance &
productivity
SQL Server 2005
Self-service BI
Cloud-ready
Mission critical &
cloud performance
Advanced analytics
& rich visualizations
Extended support for SQL Server 2005 ends on April 12, 2016
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
Mission-critical performance
Performance
Operational analytics
Insights on operational data;
Works with in-memory OLTP and
disk-based OLTP
In-memory OLTP
enhancements
Greater T-SQL surface area,
terabytes of memory supported,
and greater number of parallel
CPUs
Query data store
Security
Always encrypted
Sensitive data remains encrypted
at all times with ability to query
Row-level security
Apply fine-grained access control
to table rows
Round robin load balancing of
replicas
Automatic failover based on
database health
Other enhancements
Support for SSIS with AlwaysOn
Real-time obfuscation of data to
prevent unauthorized access
Native JSON
TDE support for storage of inmemory OLTP tables
Query data as points in time
Three synchronous replicas for
auto failover across domains
DTC for transactional integrity
across database instances with
AlwaysOn
Audit success/failure of database
operations
Temporal database
support
Enhanced AlwaysOn
Dynamic data masking
Monitor and optimize query plans
Expanded support for JSON data
Availability
Enhanced auditing for OLTP with
ability to track history of record
changes
Scalability
Enhanced database
caching
Cache data with automatic,
multiple TempDB files per
instance in multi-core
environments
Deeper insights across data
Access any data
PolyBase
Insights from data across SQL
Server and Hadoop with
simplicity of T-SQL
Enhanced SSIS
Designer support for previous
SSIS versions
Support for Power Query
Scale and manage
Enterprise-grade
Analysis Services
Enhanced performance and
scalability for analysis services
Single SSDT in Visual
Studio 2015 (CTP3)
Build richer analytics solutions as
part of your development projects
in Visual Studio
Enhanced MDS
Excel add-in 15x faster; more
granular security roles; archival
options for transaction logs; and
reuse entities across models
Powerful Insights
Mobile BI
Business insights for your onpremises data through rich
visualization on mobile devices
with native apps for Windows, iOS
and Android
Enhanced Reporting
Services
New modern reports with rich
visualizations
Advanced analytics
R integration (CTP3)
Bringing predictive analytic
capabilities to your relational
database
Analytics libraries (CTP3)
Expand your “R” script library with
Microsoft Azure Marketplace
Deeper insights across data
Hybrid solutions
Stretch Database
Stretch operational tables in a secure manner
into Azure for cost effective historic data
availability works with Always Encrypted and
Row Level Security
Power BI with on-premises data
New interactive query with Analysis Services.
Customer data stays behind your firewall
Hybrid Scenarios with SSIS
Azure Data Factory integration with SSIS,
package lineage and impact analysis and
connect SSIS to cloud data source
Enhanced Backup to Azure
Faster restore times and 50% reduction in
storage, support larger DBs with Block blobs
and custom backup schedule with local staging
Simplicity
Easy migration of on-premises SQL
Server
Simple point and click migration to Azure
Simplified Add Azure
Replica Wizard
Automatic listener configuration for AlwaysOn
in Azure VMs
Consistency
Common development,
management and identity tools
Including Active Directory, Visual Studio,
Hyper-V and System Center
Consistent Experience from SQL
Server on-premises to Microsoft
Azure IaaS and PaaS
Operational Analytics
BI analysts
IIS Server
What is operational
analytics and what does it
mean to you?
Operational analytics with
disk-based tables
Operational analytics with
In-Memory OLTP
Traditional operational/analytics architecture
BI analysts
Key issues
Complex implementation
IIS Server
Requires two servers (capital
expenditures and operational
expenditures)
Data latency in analytics
More businesses demand;
requires real-time analytics
Minimizing data latency for analytics
BI analysts
Benefits
No data latency
No ETL
IIS Server
No separate data warehouse
Challenges
Analytics queries are resource intensive and
can cause blocking
Minimizing impact on operational
workloads
Sub-optimal execution of analytics on
relational schema
Summary: Operational analytics
Data Warehouse queries can be run on in-memory OLTP workload
with no application changes.
These operations have minimal impact on OLTP workload.
Best performance and scalability available
Offloading analytics workload to Readable Secondary
Minimizing impact on OLTP Example: for HOT/WARM (Predicate)
Minimizing impact on OLTP Example: for HOT/WARM (Time Based)
Benefits of Always Encrypted
Prevents Data
Disclosure
Queries on
Encrypted Data
Application
Transparency
Client-side encryption of
sensitive data using keys that
are never given to the
database system.
Support for equality
comparison, incl. join, group
by and distinct operators.
Minimal application changes
via server and client library
enhancements.
Allows customers to securely store sensitive data outside of their trust boundary.
Data remains protected from high-privileged, yet unauthorized users.
Always Encrypted
Help protect data at rest and in motion, on-premises & cloud
Apps
SQL Server
Trusted
SELECT Name FROM
Patients WHERE SSN=@SSN
Client side
SELECT Name FROM
Patients WHERE SSN=@SSN
@SSN='198-33-0987'
Column
Master
Key
Result Set
Query
@SSN=0x7ff654ae6d
Enhanced
ADO.NET
Library
Result Set
Name
Name
Jim Gray
Jim Gray
Column
Encryption
Key
ciphertext
dbo.Patients
Name
SSN
Country
Jane Doe
243-24-9812 USA
1x7fg655se2e
Jim Gray
198-33-0987 USA
0x7ff654ae6d
John Smith
123-82-1095 USA
0y8fj754ea2c
Summary: Always encrypted
Protect data at rest and in motion, on-premises & cloud
Data remains encrypted
during query
Apps
No app
changes
Master
key
Encrypted
query
TCE-enabled
ADO .NET library
SQL Server
Capability
ADO.Net client library provides
transparent client-side encryption, while
SQL Server executes T-SQL queries on
encrypted data
Benefits
Columnar
key
Row-Level Security.
SQL Server 2016
SQL Database
Row-level security
Protect data privacy by ensuring the right access across rows
Fine-grained access control over specific rows in a
database table
Help prevent unauthorized access when multiple users
share the same tables, or to implement connection
filtering in multitenant applications
Administer via SQL Server Management Studio or SQL
Server Data Tools
Enforcement logic inside the database and schema
bound to the table.
Customer 1
Customer 2
Customer 3
SQL Database
Benefits of row-level security
Fine-grained
access control
Application
transparency
Centralized
security logic
Keeping multi-tenant
databases secure by limiting
access by other users who
share the same tables.
RLS works transparently at
query time, no app changes
needed.
Enforcement logic resides
inside database and is
schema-bound to the table it
protects providing greater
security. Reduced application
maintenance and complexity.
Compatible with RLS in other
leading products.
Store data intended for many consumers in a single database/table while at the same time restricting row-level read & write
access based on users’ execution context.
Dynamic Data Masking
SQL Server 2016
SQL Database
Dynamic Data Masking
Prevent the abuse of sensitive data by hiding it from users
Configuration made easy in the new Azure
portal
Table.CreditCardNo
4465-6571-7868-5796
4468-7746-3848-1978
Policy-driven at the table and column level, for
a defined set of users
Data masking applied in real-time to query
results based on policy
Multiple masking functions available (e.g. full,
partial) for various sensitive data categories
(e.g. Credit Card Numbers, SSN, etc.)
4484-5434-6858-6550
SQL Database
SQL Server 2016 CTP2
Real-time data masking;
partial masking
Other security enhancements
Audit success/failure of
database operations
Enhanced auditing for
OLTP with ability to track
history of record
changes
Transparent Data
Encryption support for
storage of In-memory
OLTP Tables
Backup encryption now
supported with
compression
Security resources
MSDN Documentation
https://msdn.microsoft.com/en-us/library/dn765131.aspx
Security Center for SQL Server Database & SQL Database\
https://msdn.microsoft.com/en-us/bb510589.aspx
SQL Server Security Blog
Additional examples, useful tips and tricks
http://blogs.msdn.com/b/sqlsecurity/
SQL Server Label Security Toolkit
Updated version to take advantage of RLS coming later in CY15
http://sqlserverlst.codeplex.com/
PolyBase and queries
Provides a scalable, T-SQL-compatible query processing
framework for combining data from both universes
Access any data
PolyBase View in SQL Server 2016
PolyBase View
• Execute T-SQL queries against
relational data in SQL Server and
‘semi-structured’ data in HDFS
and/or Azure
• Leverage existing T-SQL skills and
BI tools to gain insights from
different data stores
• Expand the reach of SQL Server to
Hadoop(HDFS)
Access any data
PolyBase use cases
Access any data
Components introduced in SQL Server 2016
PolyBase Engine Service
PolyBase Data Movement Service (with HDFS Bridge)
External table constructs
MR pushdown computation support
Access any data
How to use PolyBase in SQL Server 2016
PolyBase T-SQL
queries submitted
here
PolyBase queries can
only refer to tables
here and/or external
tables here
Access any data
Compute nodes
Head nodes
Set up a Hadoop Cluster
or Azure Storage blob
Install SQL Server
Configure a PolyBase
group
Choose Hadoop flavor
Attach Hadoop Cluster
or Azure Storage
R integration
Revolution R Enterprise and SQL
Big data analytics platform
Based on open source R
High-performance, scalable, full-featured
Statistical and machine-learning algorithms are
performant, scalable, and distributable
Write once, deploy anywhere
Scripts and models can be executed on a variety of
platforms, including non-Microsoft (Hadoop,
Teradata in-DB)
Integration with the R Ecosystem
Analytic algorithms accessed via R function with similar
syntax for R users. Arbitrary R functions/packages can be
used in conjunction
Advanced analytics
SQL Server 2016 R integration scenario
Exploration
Use RRE from R IDE to analyze large datasets
and build predictive and embedded models
with the compute happening on the SQL
Server machine (SQL Server compute
context)
Operationalization
Developer can operationalize R script/model
over SQL Server data by using T-SQL
constructs
DBA can manage resource, secure, and
govern R runtime execution in SQL Server
Advanced analytics
Setup and configuration
SQL Server
2016 setup
Install “Advanced
Analytics with R”
feature in setup
Install RRO on
SQL Server 2016
machine
Install RRE on SQL
Server 2016
machine
Database
configuration
Enable R language
extension in the
database
Configure path for
RRO runtime in
the database
Grant EXECUTE
EXTERNAL SCRIPT
permission to
users
CREATE EXTERNAL EXTENSION [R]
USING SYSTEM LAUNCHER
WITH (RUNTIME_PATH =
'c:\revolution\bin‘)
Advanced analytics
Optional: install R
packages on SQL
Server 2016
machine
GRANT EXECUTE SCRIPT ON
EXTERNAL EXTENSION::R
TO DataScientistsRole;
/* User-defined role /
users */
Management and monitoring
R runtime
usage
Resource
governance via
resource pool
CREATE RESOURCE POOL R_runtime
FOR EXTERNAL EXTENSION
WITH MAX_CPU_PERCENT = 20,
MAX_MEMORY_PERCENT = 10;
Advanced analytics
Monitoring via
DMVs
Troubleshooting
via XEvents /
DMVs
select * from
sys.dm_resource_governor_resouce_po
ols
where name = 'R_runtime';
R script usage from SQL Server
Original R script:
Calling R script from SQL Server:
IrisPredict <- function(data, model){
library(e1071)
predicted_species <- predict(model, data)
return(predicted_species)
}
/* Input table schema */
create table Iris_Data (name varchar(100), length int, width int);
/* Model table schema */
create table my_iris_model (model varbinary(max));
library(RODBC)
conn <- odbcConnect("MySqlAzure", uid = myUser, pwd =
myPassword);
Iris_data <-sqlFetch(conn, "Iris_Data");
Iris_model <-sqlQuery(conn, "select model from my_iris_model");
IrisPredict (Iris_data, model);
declare @iris_model varbinary(max) = (select model from
my_iris_model);
exec sp_execute_external_script
@language = 'R'
, @script = '
IrisPredict <- function(data, model){
library(e1071)
predicted_species <- predict(model, data)
return(predicted_species)
}
IrisPredict(input_data_1, model);
'
, @parallel = default
, @input_data_1 = N'select * from Iris_Data'
, @params = N'@model varbinary(max)'
, @model = @iris_model
with result sets ((name varchar(100), length int, width int
, species varchar(30)));
The values highlighted in yellow are the SQL queries embedded in the original R script
The values highlighted in aqua are the R variables that bind to SQL variables by name
Advanced analytics
R script library in Microsoft Azure Marketplace
Extensibility
Example solutions
Fraud detection
Sales forecasting
Warehouse efficiency
R Integration
Launch
External
Process
R
Benefits
New R
scripts
010010
100100
010101
010010
100100
010101
010010
100100
010101
Microsoft Azure
Machine Learning Marketplace
Predictive maintenance
Analytic library
T-SQL interface
010010
100100
010101
010010
100100
010101
Relational data
Built into SQL Server
Advanced analytics
Data Scientist
Interacts directly
with data
Data Developer/DBA
Manages data and
analytics together
Faster deployment of ML models
Faster performance
(moves compute close to the
data)
Improved scalability
Benefits
Summary: R integration and advanced analytics
Capability
SQL Server
Data Scientists
Analytics library
Publish algorithms, interact
directly with data
Share and collaborate
Manage and deploy
Analytical engines
Full R integration
Fully extensible
R
DBAs
+
Data Management Layer
Relational data
T-SQL interface
Stream data in-memory
Advanced analytics
Manage storage and
analytics together
Business
Analysts
Analysis through TSQL, tools,
and vetted algorithms
Extensible in-database analytics, integrated with
R, exposed through T-SQL
Centralize enterprise library for analytic models
Benefits
Stretch Database
Ever-growing data, ever-shrinking IT
What to do?
Massive tables (hundreds of
millions/billions of rows, TBs size)
Users want/need to retain data
indefinitely
Cold data infrequently accessed but
must be online
Datacenter consolidation
Maintenance challenges
Business SLAs at risk
Hybrid solutions
Expand server and storage
Move data elsewhere
Delete
Stretch SQL Server into Azure
Securely stretch cold tables to Azure with remote query processing
Capability
Azure
Stretch large operational tables
from on-premises to Azure with
the ability to query
Benefits
SQL
SERVER
2016
Hybrid solutions
Stretch Database architecture
Local
data
Eligible
data
Internet Boundary
Local
database
Linked Servers
How it works
Creates a secure linked server
definition in the on-premises SQL
Server
Remote
endpoint
Remote
data
Azure
Hybrid solutions
Linked server definition has the
remote endpoint as the target
Provisions remote resources and
begins to migrate eligible data, if
migration is enabled
Queries against tables run against
both the local database and the
remote endpoint
Typical workflow to enable Stretch Database
-- Enable local server
EXEC sp_configure 'remote data archive' , '1';
RECONFIGURE;
-- Provide administrator credential to connect to
-- Azure SQL Database
CREATE CREDENTIAL <server_address> WITH
IDENTITY = <administrator_user_name>,
SECRET = <administrator_password>
-- Alter database for remote data archive
ALTER DATABASE <database name>
SET REMOTE_DATA_ARCHIVE = ON (SERVER = server name);
GO
-- Alter table for remote data archive
ALTER TABLE <table name>
ENABLE REMOTE_DATA_ARCHIVE
WITH ( MIGRATION_STATE = ON );
GO;
Hybrid solutions
High-level steps
Configure local server for remote
data archive
Create a credential with
administrator permission
Alter specific database for remote
data archive
Alter table for remote data archive
Queries continue working
• Business applications continue working
without disruption
• DBA scripts and tools work as before.
All controls still held in local SQL Server
• Developers continue building or
enhancing applications with existing
tools and methods
Hybrid solutions
Advanced security features supported
Data in motion always via secure channels
(TLS1.1 / 1.2)
Always Encrypted supported if enabled
by user
Encryption key remains on-premises
Row-level security already working
SQL Server and SQL Azure audit already
working
Hybrid solutions
Backup and restore benefits
• DBAs only backup/restore local SQL
Server hot data
• StretchDB ensures remote data is
transactionally consistent with local
• Upon completion of local restore, SQL
Server reconciles with remote using
metadata operation, not data copy
• Time of restore for remote not
dependent on size of data
Hybrid solutions
Microsoft Azure
Jim Gray
Order
Order history
history
Name
Name
Jane Doe
Jane Doe
Jim Gray
Jim Gray
John Smith
John Smith
Bill Brown
Bill Brown
Sue Daniels
Hybrid
Sarahsolutions
Jones
ox7ff654ae6d
3/18/2005
Stretch to cloud
SSN
Date
SSN
Date
2/28/200
Customer data
cm61ba906fd
2/28/200
5
cm61ba906fd
5
3/18/200
Product data
ox7ff654ae6d
3/18/200
5
ox7ff654ae6d
5
4/10/200
Order History
i2y36cg776rg
4/10/200
5
i2y36cg776rg
5
4/27/200
nx290pldo90l
4/27/200
5
nx290pldo90l
5
5/12/200
ypo85ba616rj
5
5/22/200
bns51ra806fd

Query
App
Java Script Object
Notation (JSON)
JSON became ubiquitous
Compact and simple data exchange format
The choice on the web
Recommended scenario
I CAN accept JSON, easily parse and store it as relational
I CAN export relational easily as JSON
I CAN correlate relational and non-relational
Data exchange with JSON
[
{
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
"AccountNumber":"AW29825",
"Price":59.99,
"Quantity":1
SELECT * FROM myTable
FOR JSON AUTO
Number
Date
Customer
Price
Quantity
SO43659
2011-05-31T00:00:00
AW29825
59.99
1
SO43661
2011-06-01T00:00:00
AW73565
24.99
3
SELECT * FROM
OPENJSON(@json)
},
{
"Number":"SO43661",
"Date":"2011-06-01T00:00:00“
"AccountNumber":"AW73565“,
"Price":24.99,
"Quantity":3
}
]
JSON and relational
CREATE TABLE SalesOrderRecord (
Id int PRIMARY KEY IDENTITY,
OrderNumber NVARCHAR(25) NOT NULL,
OrderDate DATETIME NOT NULL,
JSalesOrderDetails NVARCHAR(4000)
CONSTRAINT SalesOrderDetails_IS_JSON
CHECK ( ISJSON(JSalesOrderDetails)>0 ),
JSON is plain text
ISJSON guarantees
consistency
Quantity AS
CAST(JSON_VALUE(JSalesOrderDetails, '$.Order.Qty') AS int)
)
GO
CREATE INDEX idxJson
ON SalesOrderRecord(Quantity)
INCLUDE (Price);
Optimize further with
computed column and
INDEX
How to handle JSON?
No new data type
If you need to store it raw, store it as
NVARCHAR
What is new:
Easy export: FOR JSON
Easy import: OPENJSON
Easy handling: ISJSON, JSON_VALUE
For JSON path
Query
SELECT
OrderNumber AS 'Order.Number',
OrderDate AS 'Order.Date'
FROM SalesOrder
FOR JSON PATH
Result (JSON array)
[
{
"Order":{
"Number":"SO43659",
"Date":"2011-05 31T00:00:00“
},
},
{
"Order":{
"Number":"SO43660",
"Date":"2011-06-01T00:00:00“
},
}
]
For JSON AUTO
Query
SELECT SalesOrderNumber,
OrderDate,
UnitPrice,
OrderQty
FROM Sales.SalesOrderHeader H
INNER JOIN Sales.SalesOrderDetail D
ON H.SalesOrderID = D.SalesOrderID
FOR JSON AUTO
Result
[
{
"SalesOrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"D":[
{"UnitPrice":24.99, "OrderQty":1
}
]
},
{
"SalesOrderNumber":"SO43659" ,
"D":[
{ "UnitPrice":34.40 },
{ "UnitPrice":134.24, "OrderQty":5
}
]
}
]
OPENJSON
{"Orders": { "OrdersArray":
[
{
"Order": {
"Number":"SO43659",
"Date":"2011-05-31T00:00:00“
},
"AccountNumber":"AW29825“,
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order":{
“Number":"SO43661",
"Date":"2011-06-01T00:00:00“
},
"AccountNumber":"AW73565“,
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]} }
OPENJSON (@json, N'$.Orders.OrdersArray')
WITH (
Number
varchar(200) N'$.Order.Number',
Date
datetime
N'$.Order.Date',
Customer varchar(200) N'$.AccountNumber',
Quantity int
N'$.Item.Quantity'
)
Number
Date
Customer
Quantity
SO43659
2011-05-31T00:00:00
AW29825
1
SO43661
2011-06-01T00:00:00
AW73565
3
Query Store
Your flight data recorder
for your database
Have You Ever…?
…had your system down/slowed down and everyone waiting for you to
magically fix the problem ASAP?
…upgraded an application to the latest SQL Server version
and had an issue with a plan change slowing your application down?
…had a problem with your Azure SQL Database and been unable to
determine what was going wrong?
With Query Store…
I CAN get full history of query execution
I CAN quickly pinpoint the most expensive queries
I CAN get all queries that regressed
I CAN easily force better plan from history with a single line of T-SQL
I CAN safely do server restart or upgrade
Query data store
Collects query texts (+ all relevant properties)
Compile
Execute
Stores all plan choices and performance
metrics
Plan Store
Runtime
Stats
Query
Store
Schema
Works across restarts / upgrades / recompiles
Dramatically lowers the bar for perf.
Troubleshooting
New Views
Durability latency controlled by DB option
DATA_FLUSH_INTERNAL_SECONDS
Intuitive and easy plan forcing
Temporal
Query back in time
Why Temporal
Real data sources are dynamic
Historical data may be critical to business success
Traditional databases fail to provide required insights
Time Travel
Data Audit
Workarounds are…
Complex, expensive, limited, inflexible, inefficient
SQL Server 2016 makes life easy
Slowly Changing
Dimensions
Repair recordlevel corruptions
No change in programming model
New Insights
How to start with temporal
No
CREATE temporal
TABLE PERIOD FOR
SYSTEM_TIME…
ALTER regular_table
TABLE ADD
PERIOD…
change in programming model
DML
SELECT * FROM temporal
INSERT / BULK INSERT
UPDATE
DELETE
DDL
MERGE
Querying
New Insights
Temporal
Querying
FOR SYSTEM_TIME
AS OF
FROM..TO
BETWEEN..AND
CONTAINED IN
How system-time works?
Temporal table (actual data)
History Table
* Old versions
Update */ Delete *
Insert / Bulk Insert
How system-time works?
Temporal table (actual data)
History Table
* Include Historical
Version
Regular queries
(current data)
Temporal Queries *
(Time travel,etc.)
Getting insights from temporal – AS OF
Department (current
(current)+ history)
AS OF
BETWEEN..AND
IN
“Get actualCONTAINED
row versions”
DepNum
DepNum
DepName
DepName
MngrID
MngrID
From
To
A001
Marketing
65
2008
∞
A002
A001
Sales
Marketing
56
2007
∞
Department
(history)2
A002
Sales
A002
DepNum
Sales
DepName
5
MngrID
A003
A001
A003
A002
Consulting
Marketing
Consulting
Sales
6
5
10
2
A003
Consulting
6
A003
Consulting
SELECT *
FROM
A001
∞
A001
A002
From
To
2005
2008
2005
2007
2005
2006
10
2009FOR SYSTEM_TIME
2012
Department
BETWEEN
CONTAINED
AS
OF '2006.01.01'
'2006.01.01'
IN ('2007.01.01',
AND '2007.01.01'
'2009.01.01')
∞
A002
A003
A003
2005
period of validity
2015
current time
SELECT * FROM Department
FOR SYSTEM_TIME
AS OF '2010.01.01'
Solution:
History as a stretch table:
PeriodEnd < “Now - 6 months”
Facts:
Azure SQL Database
1. History is much bigger than actual data
2. Retained between 3 and 10 years
3. “Warm”: up to a few weeks/months
4. “Cold”: rarely queried
Temporal database support - BETWEEN
SELECT * FROM Person.BusinessEntityContact
FOR SYSTEM_TIME BETWEEN @Start AND @End
WHERE ContactTypeID = 17
Provides correct information
about stored facts at any
point in time, or between 2
points in time.
There are two orthogonal
sets of scenarios with
regards to temporal data:
System(transaction)-time
Application-time
Performance
SQL Server 2016
SQL Server 2016 – Virtual Labs
https://technet.microsoft.com/en-us/virtuallabs/bb467605.aspx
Select SQL Server – SQL Server 2016
• SQL Server 2016 – Samples
•
AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3
https://www.microsoft.com/en-us/download/details.aspx?id=49502
Restore AdventureWorks2016CTP3 and AdventureWorksDW2016CTP3 databases
Samples are in SQLServer2016CTP3Samples.zip
•
•
Big Data Learning Resources
Big Data Specialization
https://www.coursera.org/specializations/big-data
• Introduction to Big Data
• Hadoop Platform and Application Framework
• Hadoop Platform and Application Framework
• Introduction to Big Data Analytics
• Machine Learning With Big Data
• Graph Analytics for Big Data
Data Science – Advanced Analytics Learning Resources
Data Science and Machine Learning Essentials
https://mva.microsoft.com/en-US/training-courses/data-science-and-machine-learning-essentials14100?l=em40SsXdB_5405050723