presenta
CP003
Azure SQL Database
V12 updates and
comparison with SQL Server
Francesco Diaz - @francedit
[email protected]
Agenda
•
Data Platform Continuum
•
SQL Database Architecture
•
SQL Database V12 Server New Features
•
Replica
•
Authentication
•
Security
Data platform continuum
Hybrid Cloud On premises Shared Lower cost Dedicated Higher costHigher administration Lower administration
SQL Database Service
Predictable performance levels Programmatic scale-out
Dashboard views of database metrics
Self-service restore Disaster recovery Compliance-enabled
Familiar & compatible Programmatic
Self-managed
A relational database as a service, fully managed by Microsoft.
For cloud-designed apps when near-zero administration and enterprise-grade capabilities are key.
How is it different from virtual machines?
SQL Server in a virtual machine Azure SQL Database
Best for…
Azure SQL Database
Azure SQL Database Benchmark (ASDB)
Upgrading/Downgrading Service Tier
ALTER DATABASE Customers
MODIFY (edition='Standard')
T-SQL:
PowerShell:
Set-AzureSqlDatabase $ctx $db
-MaxSizeGB 50 -ServiceObjective
V12 Server Feature
Highlights
Pre-V12 Logical Server
• many database incompatibilities • service tiers
• Web, Business (not available anymore) • Basic, Standard, Premium
• upgrade to V12 (one-way!)
Logical Server versions
V12 Logical Server
• Highly compatible with SQL 2014 db engine
• (and SQL 2016 [CTP])
• Service tiers:
• Basic, Standard, Premium only
•
Added Support:
Table Partitioning
More DBCC commands CLR Assemblies
• Data-Masking, RLS, TDE (preview) • Azure AD authentication (preview)
SQL Database V12
XML Indexes
Tables with no Clustered Index In-Memory (Premium)
Full Text Search…
https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/
•
Auditing
•
Dynamic Data-Masking
•
Row Level Security
•
Transparent Data Encryption
•
Always Encrypted
Security Improvements in V12
Track database events
Mask credit card number
XXXX-XXXX-XXXX-1234
Bank clerk sees data for his
accounts only
•
Enable Auditing to track database events.
Access to data
Schema changes (DDL)
Data changes (DML)
Accounts, roles, and permissions (DCL)
Stored Procedure, Login and, Transaction
Management.
•
Save them to Storage Account.
Activities & Events
Access to data
Schema changes (DDL)
Data changes (DML)
Accounts, roles, and permissions (DCL)
Security exceptions
Access via Azure Storage
and Excel & Power Query
Viewing Audit data
Row Level Security
https://channel9.msdn.com/Shows/Data-Exposed/Row-Level-Security-in-Azure-SQL-Database
Row Level Security
Row Level Security
Transparent Data Encryption
Azure AD Authentication with SQL v12 DB
stringConnectionString= @"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated;";
stringConnectionString= @"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Password; [email protected]; PWD=MyPassWord!";
•
Only native, federated AD members are supported (NO Microsoft
Accounts e.g.
[email protected]
)
•
Authorization can be given to ADDUsers and AAD Groups
•
Only one AAD Admin (user or group) at the time
•
Some tools like Excel are not supported
•
Authentication is done via .NET Framework Data Provider fo
SqlServer (framework 4.6) – therefore sqlcmd not supported
•
Two-factor authentication not supported
Transactional
Replication
• Seamless integration with SQL Server replication features • Transactional Replication supported • Available in SQL 2016 CTP3 Will come to previous
• Elastic Database tools: These two tools simplify development and management of
sharded database solutions. The tools are: the Elastic Database client library and the Elastic Database split-merge tool.
• Elastic Database pools (preview): A pool is a collection of databases to which you can
add or remove databases at any time. The databases in the pool share a fixed
amount of resources (known as database throughput units, or DTUs). You pay a fixed price for the resources, which enables you to easily calculate costs while managing performance.
• Elastic Database jobs (preview): Use jobs to manage large numbers of Azure SQL
databases. Easily perform administrative operations such as schema changes,
credentials management, reference data updates, performance data collection or tenant (customer) telemetry collection using jobs.
• Elastic Database query (preview): Enables you to run a Transact-SQL query that spans
multiple databases. This enables connection to reporting tools such as Excel, PowerBI, Tableau, etc.
Elastic
•
Run TSQL scripts
on the database
pool
•
Requires Service
Setup
Cloud Service, SQL Azure Database, Service Bus, Storage Account•
Built-in automatic
retries in case of
transient failures
•
Integrated with
elastic pools in the
new Azure Portal
Scaling Out (Sharding)
OPTIONAL SECTION
•
Set of .Net Libraries to enable Sharding of the data tier.
•
Has 4 main components:
•
Shard Map Management
•
Data Dependent Routing
•
Multi-Shard Queries
•
Split-Merge Management
• Metadata container of defined Maps and Shards
Shard Map Manager
Key Shard
1 Server 1, DB A 2 Server 1, DB A 3 Server 1, DB B 4 Server 1, DB B
Key Range Shard
• Responsible for handing out connections to the correct database based on the
shard map
Data Dependent Routing
Key Range Shard
