Transcript SQL Server 2016 New Innovations

HDC: SQL Server 2016
New Features & Demos
Phil Brammer (@PhilBrammer)
JSON and Microsoft
Dynamics
Bing
OneDrive
Office
Yammer
TFS
A top feature request on MS Connect (1050 votes)
JSON and SQL Server
Built-in functions
Table 2 JSON
Formats result set
as JSON text.
[
SO43659
2011-05-31T00:00:00
MSFT
59.99
1
SO43661
2011-06-01T00:00:00
Nokia
24.99
3
ISJSON
JSON_VALUE
JSON_MODIFY
{
},
{
JSON 2 table
Migrates JSON
text to table
]
}
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
"AccountNumber":"AW29825",
"Price":59.99,
"Quantity":1
"Number":"SO43661",
"Date":"2011-06-01T00:00:00“
"AccountNumber":"AW73565“,
"Price":24.99,
"Quantity":3
Temporal Data
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 record-level
corruptions
No change in programming model
New Insights
Row-Level Security
Protect data privacy by ensuring
the right access across rows
Customer 1
Fine-grained access control over specific rows in a database
table
Customer 2
Help prevent unauthorized access when multiple users
share the same tables, or to implement connection filtering
in multitenant applications
SQL Database
Administer via SQL Server Management Studio or SQL
Server Data Tools
Enforcement logic inside the database and schema is
bound to the table
Security
Customer 3
Row-Level Security
Traditional RLS workloads
Custom business logic to determine which rows each user can SELECT, INSERT, UPDATE, and DELETE
based on their role, department, and security level
Target sectors: Finance, insurance, healthcare, energy, and government
Multitenant databases
Ensuring tenants can only access their own rows of data in a shared database, with enforcement logic
in the database rather than in the app tier
For example: multitenant shards with elastic database tools in SQL Database
Reporting, analytics, and data warehousing
Different users access same database through various reporting tools, and work with different
subsets of data based on their identity/role
Security
Dynamic Data Masking
Prevent the abuse of sensitive
data by hiding it from users
Table.CreditCardNo
4465-6571-7868-5796
4468-7746-3848-1978
Configuration made easy in the new Azure portal
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
(credit card numbers, SSN, etc.)
Security
4484-5434-6858-6550
SQL Database
SQL Server 2016
Real-time data masking;
partial masking
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
Enhanced AlwaysOn
Row-level security
Round-robin load balancing of
replicas
Sensitive data remains encrypted
at all times with ability to query
Apply fine-grained access control
to table rows
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
Automatic failover based on
database health
DTC for transactional integrity
across database instances with
AlwaysOn
Audit success/failure of database
operations
Temporal database
support
Three synchronous replicas for
automatic failover across domains
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 multicore environments
Deeper insights across data
Access any data
PolyBase
Insights from data across SQL
Server and Hadoop with the
simplicity of T-SQL
Enhanced SSIS
Designer support for previous SSIS
versions
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
Expand your “R” script library with
Microsoft Azure Marketplace
Hyperscale cloud
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
Enhanced backup to Azure
Faster restore times and 50% reduction in
storage. Supports 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
Suite of advisors for upgrading to SQL
Server 2016
SQL Server 2016 Upgrade Advisor in the
adoption of new SQL Server features
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, HyperV, and System Center
Consistent experience from SQL
Server on-premises to Microsoft
Azure IaaS and PaaS