AGENDA
Azure SQL
Azure SQL
Azure is a cloud based SQL service, provided to
subscribers, to host their databases.
Azure SQL
SQL Server on Azure Virtual Machines – Full Control over
application management and deployment
Azure SQL Database – SQL Database
Azure SQL Options
Azure SQL
Azure SQL Database SQL Server
Building Software-as-a-Service applications. Large databases that are bigger than 1 TB in size. New cloud-designed applications that have time constraints in
development and marketing.
Existing applications. Applications that need built-in automatic high-availability, disaster
recovery solution, and upgrade mechanisms.
SQL Server applications that require accessing on-premises resources (such as, Active Directory).
If you have hundreds or thousands of databases but you do not want to manage the underlying operating system, hardware, and
configuration settings.
If you need a customized IT environment with full administrative rights.
Applications using scale-out patterns. Rapid development and test scenarios.
Databases of up to 500 GB in size. Disaster recovery for on-premises SQL Server applications
Azure SQL
Service Tier DTU MAX DB Size Max Concurrent Req Max Concurrent Logins MaxSessions Benchmark Trx Rate
Basic 5 2 GB 30 30 300 16,600 transactions per hour
Standard/S0 10 250 GB 60 60 600 521 transactions per minute
Standard/S1 20 250 GB 90 90 900 934 transactions per minute
Standard/S2 50 250 GB 120 120 1,200 2,570 transactions per minute
Standard/S3 100 250 GB 200 200 2,400 5,100 transactions per minute
Premium/P1 125 500 GB 200 200 2,400 105 transactions per second
Premium/P2 250 500 GB 400 400 4,800 228 transactions per second
Premium/P4 500 500 GB 800 800 9,600 447 transactions per second
Premium/P6(formerly P3) 1000 500 GB 1,600 1,600 19,200 735 transactions per second
Azure SQL
Ask your license provider
SQL 2016
What’s New in SQL 2016
What’s New in Business Intelligence
SSIS
SQL 2016
Always Encrypted
Row-Level Security (RLS) restricts which users can
view what data in a table, based on a function. SQL
Server 2016 introduces this feature, which is very
useful in multi-tenant environments where you may
want to limit data access based on customer ID
Row Level Security
Data Security
A new capability that protects data at rest and in motion.
SQL 2016
Dynamic Data Masking
Stretch Database – new technology that lets
you dynamically stretch your warm and cold
transactional data to Microsoft Azure
Enhanced hybrid backup to Azure -- Enables
faster backups to Microsoft Azure and faster
restores to SQL Server in Azure Virtual
Machines. Also, you can stage backups
on-premises prior to uploading to Azure.
Stretch Database
• Microsoft introduces the unique
capability to use our in-memory
column store delivering 100X faster
queries with memory OLTP for
in-memory performance and real-time
operational analytics.
• Limitations:
• Like other indexes on a Memory
Optimized Table, the Columnstore Index must be defined when the table is created.
• The Columnstore Index must include all columns in the base table. (This limitation doesn’t exist for Columnstore Indexes on normal tables.)
• The Columnstore Index must include all rows in the base table. Or in other words, it can’t be a filtered index.
SQL 2016
Feature/Limit MS SQL 2014 MS SQL 2016
Maximum size of durable table 256 MB 2TB ?
LOB (varbinary(max), [n]varchar(max)) Not supported Supported (not in CTP) ? Transparent Data Encryption (TDE) Not supported Supported ?
ALTER PROCEDURE / sp_recompile Not supported Supported (fully online) Nested native procedure calls Not supported Supported
Natively-compiled scalar UDFs Not supported Supported (no clear documentation in MSDN)?
ALTER TABLE Not supported Partially supported (offline)
DML triggers Not supported Not Supported (MSDN)
Indexes on NULL-able columns Not supported Not Supported Foreign Keys \Check/Unique Constraints Not supported Not Supported
Parallelism Not supported Supported ?
OUTER JOIN, OR, NOT, UNION [ALL], DISTINCT, EXISTS, IN
Not supported Supported (Natively-compiled sp)
SSMS Table Designer Not supported Supported
