Introduction T o Windows Azure SQL Database

1. Introduction To Windows Azure SQL Database George Huey Principal Architect Evangelist Microsoft Corporation

2. A Continuous Offering From Private To Public Cloud Virtual Physical SaaS IaaS PaaS

3. Agenda Architecture Starting With The Basics Application Migration Database Migration

4. Architecture

5. How It Works Client Layer PHP SQL Server Applications and Tools WCF Data Services 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 of the physical HW and OS. ODBC ADO.NET Tabular Data Stream (TDS) TDS+SSL Services Layer Provisioning Provisioning Provisioning … Billing and Metering Billing and Metering Billing and Metering … Connection Routing Connection Routing Connection Routing … Platform Layer SQL Server SQL Server SQL Server … SQL Database Fabric SQL Database Fabric SQL Database Fabric … Management Services Management Services Management Services … Infrastructure Layer

6. High Availability ! Single Database Multiple Replicas Replica 1 Single Primary Replica 2 DB Replica 3 Replica 4

7. Starting With The Basics

8. The Basics Windows Azure SQL Database • SQL Server database technology as a service • Fully Managed • Enterprise-ready with automatic support for HA • Ideal for simple and complex applications

9. Server Provisioning 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) wappowershell.codeplex.com

10. Application Migration

11. Create Database… Use Familiar Technologies Transact-SQL Languages .NET Framework (C#, Visual Basic, F#) via ADO.NET C / C++ via ODBC Java via Microsoft JDBC provider PHP via Microsoft PHP provider Frameworks OData, Entity Framework, WCF Data Services, NHibernate Tools SQL Server Management Studio (2008 R2 SP1 and later) SQL Server command-line utilities (SQLCMD, BCP) CA Erwin® Data Modeler Embarcadero Technologies DBArtisan® SQL Server Comparison Focus on logical vs. physical administration Database and log files automatically placed Three high-availability replicas maintained for every database Tables require a clustered index Maximum database size is 150 Gb Unsupported SQL Server Features Use command, linked servers, distributed transactions, distributed views, distributed queries, four-part names Service Broker Common Language Runtime (CLR) SQL Agent Native Encryption *Backup / Restore SMO / OLDB

12. Data Access APIs Supported APIs Connection String ADO.Net • Encrypt=True and add @servername to User ID ODBC • Encrypt=yes and add @servername to Uid

13. Data Access APIs cont. Protocols Authentication Mode

14. SQL Database Firewall Internet Securing your data 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 Services Layer SQL Database Firewall Services Layer

15. Application Connectivity Connecting To SQL Database • TDS (Tabular Data Stream) protocol over TCP/IP supported • SSL required • Use firewall rules to connect from outside Microsoft data center ASP.NET EXAMPLE: Considerations And Best Practices • login: [login]@[server] • Idle connections • Long running transactions • DoS guard • Failover events • Throttling • Connection pooling • Latency introduced for updates • No cross-database dependencies • Robust Connection Management through Retry Logic • The Transient Fault Handling Application Block <connectionStrings> <addname="AdventureWorks"connectionString= "Data Source=[server].database.windows.net; Integrated Security=False; Initial Catalog=ProductsDb; User Id=[login]; Password=[password]; Trusted_Connection=False; Encrypt=true;" providerName="System.Data.SqlClient"/> </connectionStrings>

16. Application Topologies SQL Database access from within and outside MS Datacenter (On-premises & Azure Compute) SQL Database access from within MS Datacenter (Azure compute) SQL Database Access from outside MS Datacenter (On-premises) SQL Server App code/ Tools App code/ Tools Application/ Browser SOAP / REST HTTP / HTTPS Windows Azure Windows Azure App Code (ASP.NET) App Code (ASP.NET) SQL Database Data Sync App Code (ASP.NET) App Code (ASP.NET) T-SQL (TDS) Windows Azure T-SQL (TDS) T-SQL (TDS) SQL Database SQL Database SQL Database Code Near Code Far Hybrid

17. Feature Parity • Administration Surface • Physical Server Properties does not apply in SQL Database • You have a master database but no access to server level constructs such as sp_configure, endpoints, DBCC commands, server level DMVs and System Views • Programmability Surface • Certain Features are partially available today • USE, XML processing, deprecated T-SQL etc. • List is available here; http://msdn.microsoft.com/en-us/library/ee336267.aspx • Some features are not available today • Full-text Search, Remote Data Access and Linked Servers, Distributed Transactions, Change Tracking, Service Broker etc. • List is available here; http://msdn.microsoft.com/en-us/library/windowsazure/ff394115.aspx

18. Moving Schema and Data Database Migration

19. Migration Options • SQL Server Management Studio • Data-tier Application (DACPAC & BACPAC) • Generate Script Wizard (SSMS) • Visual Studio • SQL Server Data Tools • SQLAzureMW • Useful for catching unsupported features in SQL Azure • Moves data efficiently • Unofficially supported

20. SQL Server Management StudioGenerate Script Wizard • Scenario • Migration of schema and/or data with fine grain control. • Pros • Native support for SQL Azure Schema options: “Engine Type = SQL Azure” • Ensures correct options and settings are applied for the TSQL script generation. • Requires explicit action on unsupported objects. • Cons • Verbose – INSERT Statements instead of raw data • Data is scripted with a fixed 100 row batch size. • Edit “GO” statements between small batches • Round-trip Efficiency • Use “SET NOCOUNT ON”

21. Generate Script WizardMigrating Databases to SQL Database demo

22. Enhanced Tooling SQL Database Management Portal Web designers for tables, views, stored procs Interactive query editing and execution SQL Server Data Tools (SSDT) Visual Studio IDE for database development Includes modern designers and projects with declarative, model-driven 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 Platform Installer, with SQL Server 2012 and with Visual Studio 2012

23. Data-tier Application (DAC) Packages • Scenarios • Self contained package for moving schema easily through the development lifecycle • What is a DAC Pack? • Single unit for authoring, deploying, and managing the data-tier objects • Development Lifecycle (Visual Studio) • Editing DACs • Schema and DB Code Development, • Code Analyses, Deployment Policy Settings, • Schema Comparison and more… • Building DACs – the self contained database package • Management Lifecycle (SSMS) • Managing DACs • Registering existing database as DACs • Deploying and Upgrading databases using DACs,

24. Data-tier ApplicationMigrating Databases to SQL Database demo

25. SQL Azure Migration Wizard • Scenario • Migration of schema and/or data with fine grain control. • Pros • SQL Azure Compatibility Analysis • Uses SMO and BCP • Built in retry and chunking • Migrate from: • SQL Server to SQL Azure • SQL Azure to SQL Azure • SQL Azure to SQL Server • First try clustered index creation • Create package (great for Test & QA) • Cons • BCP data stored on local file system • Not a full SQL Engine TSQL Parser

26. SQLAzureMWMigrating databases to SQL Database demo

27. Summary– Database Migration

28. Thank You

29. Appendix

30. SQL Database Billing Rates (As of Oct 2013) Based on graduated rate based on database size (Charged at monthly rate per database) Amortized over month -> calculated on daily basis - No Transaction Charges Data Transfers All inbound data transfers are at no charge.

31. SQL Database Architecture