Windows Azure SQL Database Richmond SQL Server Users Group August

Windows Azure SQL Database Richmond SQL Server Users Group August 9, 2012

Carlos Chacon Jr [email protected] @carloslchacon

SQL Saturday We should have one in Richmond! Dates are already scheduled through June 2013

Agenda Architecture and Layout Build and Deploy your Database Secure your Database Exploring Advanced Capabilities

Cloud Computing IaaS PaaS SaaS Infrastructure-as-a-Service Platform-as-a-Service Software-as-a-Service host build consume

Cloud Computing Packaged Software (as a Service) (as a Service) Applications Applications Data Data Runtime Runtime Runtime Middleware Middleware Middleware Middleware O/S O/S O/S Virtualization Virtualization Servers Servers Storage Storage Networking Networking (as a Service) Runtime Applications You manage Data You manage Applications Data Managed by vendor You manage Virtualization Servers Storage Networking O/S Virtualization Servers Storage Networking Managed by vendor Software Managed by vendor Platform Infrastructure

A Continuous Offering From Private to Public Cloud Physical Virtual IaaS PaaS Windows Azure SQL Database SaaS

Connecting to SQL Server SQL Server A Machine SQL Database Server A TDS Endpoint tabular data stream (TDS) is a Microsoft communication format—Requires version 7.3 and higher On the server, the TDS endpoints are installed by SQL Server during SQL Server installation.

How it Works Applications Tools ODBC ADO.NET Architecture Client Layer - Used by application to communicate directly with SQL Database. Services Layer – Gateway between Client layer and Platform layer. Platform Layer – Includes physical servicers and services that support the Services layer. Infrastructure Layer – IT administration and physical HW and OS. TDS SQL Database Server Connection Routing Billing Provisioning SQL Server SQL Server SQL Server SQL Databas e Fabric SQL Databas e Fabric SQL Databas e Fabric Server Server Server

VM with persistent drive Reliable and always on Click to edit the outline text format Second Outline Level Third Outline Level Fourth Outline Level Fifth Outline Level Windows Azure Storage Sixth Outline Level Seventh

EAST DC WES T DC 500 miles Continuous storage geo-replication Windows Azure Storage

Start With the Basics SQL Database is SQL Server database technology delivered as a service on Windows Azure Ideal for both simple and complex applications Enterprise-ready with automatic support for HA Designed to scale out elastically with demand Get started quickly Choose a plan Choose a billing option Provision servers Install Silverlight

Provision Your Server Server defined Service head that contains databases Connect via automatically generated FQDN (xxx.database.windows.net) Initially contains only a master database Provision servers interactively Log on to Windows Azure Management Portal Create a SQL Database server Specify admin login credentials Add firewall rules and enable service access Automate server provisioning Use Windows Azure Platform PowerShell cmdlets (or use REST API directly)

Creating a SQL Database Server Sport: Fencing Birthdate: 3/3/1985 Birthplace: Beaverton, OR Hometown: Beaverton, OR Residence: Beaverton, OR Ht/Wt: 5'8" / 134 lbs Getting to know Mariel Zagunis Olympics: 2008, 2004 Will be the U.S. flag bearer at the London Opening Ceremony. 2012 Result: Bronze Medal Has fenced with coach Ed Korfanty since she began in the sport. Was the gold medalist at the 2004 and 2008 Olympics in Women's Sabre. DEMO

Database Security There are two ways to secure a database: On the Server Within the Database

SQL Database Firewall IP Address-based access control for SQL Database No IP authorized by default Configurable using the SQL Database Portal Option to disable/enable access from applications hosted in Windows Azure

Database Security Server identity and access control Integrated authentication not supported Connect to master to administer logins and create / drop databases The admin login (configured during service provisioning) is like sa The admin login has full rights on the server (and all databases) and should only be used for administration Manage logins with CREATE / ALTER / DROP LOGIN commands Membership in the loginmanager server role grants CREATE / ALTER / DROP LOGIN privileges Membership in the dbmanager server role

Secure Your Server Sport: Track and Field--SteepleChase Birthdate: 12/12/1989 Birthplace: Glastonbury, CT Hometown: Glastonbury, CT Residence: Princeton, NJ Getting to know Donn Cabral Ht/Wt: 5'10" / 148 lbs Qualified for first Olympics with runner-up finish in 2012 Result: 8th Place steeplechase at Trials. Won NCAA title, set U.S. collegiate record in steeplechase in 2012. Spent five months living in high-altitude tent set up in his dorm at Princeton DEMO

Build Your Database Use familiar technologies Supports Transact-SQL Supports popular languages .NET Framework (C#, Visual Basic, F#) via ADO.NET C / C via ODBC Java via Microsoft JDBC provider PHP via Microsoft PHP provider Supports popular frameworks OData, Entity Framework, WCF Data Services, NHibernate Supports popular tools SQL Server Management Studio (2008 R2 and later) SQL Server command-line utilities (SQLCMD, BCP) CA Erwin Data Modeler Embarcadero Technologies DBArtisan

Build Your Database Thin client database development SQL Database management portal Web designers for tables, views, stored procs Interactive query editing and execution Rich client database development Introducing SQL Server Data Tools (SSDT) (formerly BIDS) Visual Studio IDE for database development Develop and test in both connected and disconnected states Platform targeting for both SQL Server (2005 and above) and SQL Database Get it free with Web PI, with SQL Server 2012 and with Visual

Deploy Your Database Data-tier Application Framework (DAC Fx) Alternative to traditional script based approach Dramatically simplifies deployment, migration and versioning of databases Provides a single unit of deployment for schema (dacpac) or for schema data (bacpac) Supports automatic versioning of database schema Supports platform targeting for both SQL Server (2005 and above) and SQL Database How to get the latest DAC Fx Build from scratch or extract from existing db With SQL Server Data Tools With SQL Server 2012 Management Studio With SQL Database Import/Export Service Via sqldacexamples.codeplex.com

Dac Deployment Sport: Rowing—Men's Eight Birthdate: 8/17/1987 Birthplace: Alamosa, CO Hometown: DeKalb, IL Residence: Chula Vista, CA Getting to know Ross James Won gold with his brother, Grant, in the eight boat during the 2008 World Rowing Under 23 Championships. Will row on the men's eight in London after qualifying at the Final Olympic Qualification Regatta. Did not have a television until college. Ht/Wt: 6'5" / 194 lbs 2012 Result: 4th Place DEMO

Azure SQL Quirks Idle connections terminated after 30 minutes Unsupported SQL Server features Long running transactions (24 hours) are terminated BACKUP / RESTORE or Attaching database Throttling is enabled USE command, linked servers, distributed transactions, distributed views, distributed queries, four-part names Imposed Limitations Differences in comparison to SQL Server Service Broker Common Language Runtime (CLR) Focus on logical vs. physical administration SQL Agent Database and log files automatically placed Windows Authentication Three high-availability replicas maintained for every database Databases are fully contained Tables require a clustered index Maximum database size is 150 Gb / Max 149 databases per server [login]@[server]

Exploring advanced Capabilities

Visualize Your Data SQL Reporting is SQL Server Reporting Services technology delivered as a service on the Windows Azure Platform Ideal for operational reporting against SQL Database data Enterprise-ready with automatic support for HA Designed to scale elastically with demand Get started quickly Provision report server via Windows Azure Management Portal Build reports with Reporting Services Report Designer Deploy reports to report server Render reports with Visual Studio ReportViewer controls

Visualize Your Data Use familiar technologies Visual Studio Report Designer RDL (Report Definition Language) Report items and visualizations Report Server Web Service / URL access Visual Studio ReportViewer Control Identity and access control SQL authentication supported (no support for integrated authentication) Admin user configured during provisioning Admin user has all rights on server, use for administration only Provision users using portal or web service Add users to appropriate item (Browser, Publisher) and system roles (User, Admin) Grant item-level permissions to folders or reports Differences in comparison to Reporting Services Focus on logical vs. physical administration Report catalog and temporary database automatically provisioned Windows Azure Management Portal replaces Report manager SSL Required SQL Database data sources only Unsupported Reporting Services features Report Builder Subscriptions, schedules, caching Custom extensions Report models External images

Synchronize Your Data SQL Data Sync Microsoft Sync Framework technology delivered as a service on the Windows Azure Platform Ideal for scheduling synchronization between data sets hosted in SQL Database or SQL Server Uses a hub and spoke topology Special considerations Conflict resolution policy configured centrally (hub or client wins) Sync direction configured between each client and the hub (to hub, from hub, bi-directional) Sync schedule must be between 5 minutes and 1 month Data sets include multiple tables and can be filtered, triggers are added to data set tables Tables added to hub and client schemas Agent must be installed for on-prem clients (Currently in pre-release) Charged for data 'download'

Synchronize Your Data Provisioning synchronization groups Deploy database to hub and clients Set synchronization schedule Set conflict resolution policy Define data set Add SQL Database clients to topology Install agents on SQL Server clients and add them to topology using keys Deploy topology Operational considerations Manual synchronization supported Hub updates require one synchronization to distribute to all clients Client updates require two synchronizations to distribute to all clients Sync Framework triggers may affect application

scale out your data SQL Federation is table horizontal partitioning— splitting tables up by row. This type of horizontal partitioning is often referred to as ‘sharding’. lingo ------------ Microsoft SQL Federation provides Integrated database sharding that can scale to hundreds of nodes Multi-tenancy via flexible repartitioning Online split operations to minimize downtime Automatic data discovery regardless of changes in how data is partitioned Special considerations A logical database can contain multiple federations Distribution scheme supports int, bigint, guid, and varbinary types Filtering routes connection to appropriate shard regardless of changes in partitions Merge, fan-out queries and automatic distribution of schema changes not supported in

Dac Deployment Sport: Field Hockey Birthdate: 7/12/1991 Birthplace: Philadelphia Hometown: Perkasie, PA Residence: Coronado, CA Getting to know Julia Reinprecht Ht/Wt: 5'3" / 126 lbs Nickname is Smalls 2012 Result: Did not get out of group play Sister Katie is also a member of U.S. field hockey team · Is the youngest member of U.S. field hockey team DEMO

Where Can I learn more? Azure Training Kit http://goo.gl/2xz2R http://www.windowsazure.com www.sqldatatpartners.com/discussion posting of slide deck

Questions?