AZR 221 Windows Azure SQL Database and SQL

AZR 221 Windows Azure SQL Database and SQL Reporting for Developers Dr Greg Low Principal Mentor – SQL Down Under

Working with Windows Azure SQL Database Scaling out using Federations Scaling out using Data Sync Reporting

Isn't this just a hosted SQL database? Windows Azure SQL Database (was SQL Azure) SQL as a service Massively distributed cluster Based on commodity hardware Focus on logical vs physical design

Is my data secure? Security built-in Logical firewall DOS attack detection SSL connectivity Not yet available Encryption

Can I rely on my data being available? HA built-in Primary plus two replicas Near-instant failover Rolling upgrades only when all available Read -> Primary Write -> Quorum

Do I get my own SQL Server? Logical servers assigned to subscriptions Server = TDS endpoint at gateway Can be programmatically provisioned master database Different to on-premises version View for firewall rules

How big can my databases be? Web up to 5 GB, Business up to 150 GB Main difference is growth limit Pricing encourages multi-tenant Could be on different physical servers

Management Portal & Database Design/Query

Will this fix my performance issues? Probably not WASD is different to an on-premises server Important to understand what is offered and limitations SQL Server 2012 based engine (Quarterly updates) Commodity hardware Multi-tenanted architecture Latency

How do I avoid performance issues? Database needs appropriate design (no magic fix) Indexing Avoid fragmentation Architect for scale-out vs scale-up On-premises => buy enough hardware Azure => design for multi-DB, enough DBs Use async (non-blocking) design patterns

Is latency really an issue? Possibly significant in local region Need to Avoid chatty interfaces Minimize round trips Choose appropriate data centre (via testing not geography) Co-locate applications and data within DC

Management Studio and Latency

What tools can I use? Management Portal (manage servers) SQL Server Management Studio (SSMS) Use 2008 SP 1 or later Database Manager (within portal) Design tables, edit table data Does not really compete with SSMS SQL Server Data Tools Perform local validation before deployment

How to I migrate by apps and data? Scripting options in SSMS Ensure WASD target Check for scripting issues Data-tier applications. dacpac and. bacpac files SQL Azure Migration Wizard SQL Server Integration Services bcp

Do I need to change my apps? Large subset of T-SQL supported Tables must have a clustered index Not all components supported Agent, Full-Text, Service Broker Dependencies, CLR Continuous improvements – recently added: MARS, Spatial

Database scripting

What if other apps don't play fair? WASD is not intended for high resource requirement apps Shared resources tempdb Worker threads Disk Network

How does Azure balance loads? Main load balancer (periodic) Balances utilization across all servers Swaps or moves workloads Reactive load balancer (as needed) Solves short term issues Fast solution to avoid throttling on hot machines

So what's this throttling thing? Throttling Service Protects systems based on actual vs safe loads Soft throttle => busiest databases only Hard throttle => all databases on server

How do I avoid throttling? Optimize database code Up-to-date statistics Minimal index fragmentation Monitor throttle causes Many hard throttles – check code & design Check DMVs (eg: sys. dm_exec_query_stats) Decode errors returned

Will my app stay connected? No But retry logic needed for on-premises systems anyway Design for an assumption of failure

Are there code examples? Entlib (via Nu. Get) Transient Fault Handling Application Block Reliable. Sql. Connection (connection + retry policy) Policy (count, count + timespan, etc) Execute. No. Query. With. Retrying event exposed Execute. Action method can invoke LINQ

How do I backup and restore? COPY DATABASE Transactionally-consistent duplicate database Async operation & can be cross-server Import/Export wizard Uses Azure Storage Not transactionally-consistent Quiesce database first or use copy database

Can I point DB Scripts at WASD? Yes, but don't include data

Method Execute against local server Execute against WASD directly Time 55 secs 1 hr 52 mins 13 secs Export bacpac from local server to Azure storage Import bacpac from Azure storage 13 secs 52 secs

Working with bacpac, storage and copy database

Working with Windows Azure SQL Database Scaling out using Federations Scaling out using Data Sync Reporting

int bigint varbinary (900 bytes max) guid

Federated Distributed by key Contain data that is the reason for federation Reference Cloned to each federation member Typically small lookup data Central Created in federation root Typically low-traffic objects eg: metadata

Federations

Working with Windows Azure SQL Database Scaling out using Federations Scaling out using Data Sync Reporting

Windows Azure SQL Database SQL Azure Database Hub On-premises SQL Server

Windows Azure SQL Database Agent Service On-premises SQL Server SQL Azure Database Hub

Deploy database to hub and clients Configure sync group Determine dataset contents & filtering Determine sync schedule Determine conflict resolution policy Install agent service on on-premises system Add all clients to topology and deploy topology

Data sync

Working with Windows Azure SQL Database Scaling out using Federations Scaling out using Data Sync Reporting

SSRS delivered as an Azure service Worker role in Azure Sticky gateway Primary target: embedded reporting in web apps Rapid provisioning Best to provision in same DC as SQL data

Report designer & RDL Deployment mechanisms Web services and URL access Report viewer control (2010) Item level permissions Plus: Built-in HA Elastic scale-out options Same SQL authentication only SSL only WASD data sources only Less: Report Builder, Schedules, Subscriptions, Custom Extensions, External Images Different

SQL Reporting

Windows Azure SQL Database is ready for business Windows Azure SQL Database enables new opportunities