Introduction To Windows Azure SQL Database George Huey Principal Architect Evangelist Microsoft Corporation A Continuous Offering From Private To Public Cloud Physical Virtual IaaS PaaS SaaS Agenda Architecture Starting With The Basics Application Migration Database Migration Architecture
How It Works Client Layer SQL Server Applications and Tools PHP 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
WCF Data Services ADO.NET Tabular Data Stream (TDS) TDS+SSL Services Layer Provisioning Provisioning Provisioning Provisioning Provisioning Provisioning Billing and Metering Metering Billing and Metering Metering Billing and
Metering Metering Connection Connection Routing Routing Connection Connection Routing Routing Connection Connection Routing Routing Platform Layer SQL SQL Server Server SQL
SQL Server Server SQL SQL Server Server SQL SQL Database Database Fabric Fabric SQL SQL Database Database Fabric Fabric SQL SQL Database Database Fabric
Managemen Managemen tt Services Services Managemen Managemen tt Services Services Managemen Managemen t Services Infrastructure Layer High Availability Single Database ! Multiple Replicas Replica
1 DB Replica 2 Replica 3 Replica 4 Single Primary Starting With The Basics 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 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 Application Migration Create Database Use Familiar Technologies SQL Server Comparison Transact-SQL Focus on logical vs. physical administration
Languages Database and log files automatically placed .NET Framework (C#, Visual Basic, F#) via ADO.NET Three high-availability replicas maintained for every database C / C++ via ODBC Tables require a clustered index 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 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 Data Access APIs Supported APIs Supported Unsupported ADO.Net .Net 3.5 SP1 and 4.0 ODBC - SNAC 10 Entity Framework .Net 3.5 SP1 and 4.0 SQL Server 2008 Driver for PHP v1.1 OleDB Connection String ADO.Net
ODBC Encrypt=True and add @servername to User ID Encrypt=yes and add @servername to Uid Data Access APIs cont. Protocols Supported Unsupported TCP/IP over port 1433 Dynamic ports Named pipes Shared memory Authentication Mode Supported Unsupported SQL Auth Windows Auth SQL Database Firewall
Securing your data Internet 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 SQL Database Firewall Services Layer Services Layer Application Connectivity Connecting To SQL Database 1. 2. TDS (Tabular Data Stream) protocol over TCP/IP supported SSL required Use firewall rules to connect from outside Microsoft data center ASP.NET EXAMPLE:
name="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"/> 3. Considerations And Best Practices 1. login: [login]@[server] 2. Idle connections 3. Long running transactions 4.
DoS guard 5. Failover events 6. Throttling 7. Connection pooling 8. Latency introduced for updates 9. No cross-database dependencies 10.Robust Connection Management through Retry Logic
The Transient Fault Handling Application Bl ock Application Topologies SQL Database access from within MS Datacenter (Azure compute) SQL Database Access from outside MS Datacenter (On-premises) SQL Database access from within and outside MS Datacenter (On-premises & Azure Compute) SQL Application/ Browser App code/ Tools Serve App code/ Tools
r )) T-SQL (TDS) Windows Azure SQL Database Code Near Window s Azure Data Sync (ASP.NET) (ASP.NET (ASP.NET Windo ws Azure
SQL Database AppApp App Code Code Code T-SQL (TDS) SOAP / REST HTTP / HTTPS AppApp App Code Code Code (ASP.NET) (ASP.NET (ASP.NET )) T-SQL (TDS)
SQL Database SQL Database Code Far Hybrid 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 Database Migration Moving Schema and Data
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 SQL Server Management Studio Scenario Generate Script Wizard 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 Generate Script Wizard demo Migrating Databases to SQL Database 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 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, Data-tier Application demo Migrating Databases to SQL Database SQL Azure Migration Scenario Wizard 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 SQLAzureMW
Migrating databases to SQL Database demo Summary Database Migration Scenario/Tool Move SQL Schema SQL Azure Generat Migratio e Script n Wizard Wizard DACs BAC
BCP Efficient More work Move Data * Limited * NA * Move Large Data * *
No Retry Thank You Appendix SQL Database Billing Rates (As of Oct 2013) Database Size Price Per Database Per Month 0 to 100 MB Flat $4.995 > 100 to 1 GB Flat $9.99 > 1GB to 10 GB $9.99 for first GB, $3.996 per additional GB > 10 GB to 50 GB $45.96 for first 10 GB, $1.996 for each additional GB
> 50 GB to 150 GB $125.88 for first 50 GB, $0.999 for each additional GB Based on graduated rate based on database size (Charged at monthly rate per database) Amortized over month -> calculated on daily basis - No Transaction Charges Outbound Data Transfer US and Europe Asia First 5 GB / Month 1 Free Free 5 GB - 10 TB 2 / Month $0.12 per GB $0.19 per GB Next 40 TB / Month
$0.09 per GB $0.15 per GB All inbound data transfers are at no charge.Next 100 TB / Month $0.07 per GB $0.13 per GB Next 350 TB / Month $0.05 per GB $0.12 per GB Greater than 500 TB / Month Contact us Contact us Data Transfers SQL Database Architecture
