Introduction To Windows Azure SQL Database

Introduction To Windows Azure SQL Database

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

Recently Viewed Presentations

  • Aucun titre de diapositive - flmm-lmi.org

    Aucun titre de diapositive - flmm-lmi.org

    Presentation Outline How LMI helps define issues and solutions A brief description of the aging-population situation in Quebec Emploi-Québec's Action Strategy for workers aged 45 and over Some Strategy-related examples of the use of LMI How LMI helps define issues...
  • Poster title goes here, containing strictly only the

    Poster title goes here, containing strictly only the

    Simply highlight this text and replace it by typing in your own text, or copy and paste your text from a MS Word document or a PowerPoint slide presentation. The body text / font size should be between 24 and...
  • DEV-B307: Building Modern, HTML5-based Business Apps on ...

    DEV-B307: Building Modern, HTML5-based Business Apps on ...

    Building Modern, HTML5-based Business Apps on Windows Azure with Visual Studio LightSwitch. ... Plumbing is all handled for you, the only code you write is the business logic ... DEV-B307: Building Modern, HTML5-based Business Apps on Windows Azure with Visual...
  • Performance Enhancement of TFRC in Wireless Networks

    Performance Enhancement of TFRC in Wireless Networks

    Classes: A Deeper Look Systems Programming const objects and const member functions Composition Friendship this pointer Dynamic memory management new and delete operators static class members and member functions Abstract Data Types 21.2 const (Constant) Objects and const Member Functions...
  • Chapter 1 HW Solutions - Michigan State University

    Chapter 1 HW Solutions - Michigan State University

    Chapter 1 HW Solutions ... strontium sulfite E) cobalt (II) bromide (cobaltous bromide) F) tin (II) iodide (stannous iodide) G) chromium (III) nitrate (chromic nitrate) #42 continued H) zinc hydrogen phosphate (notice the lack of roman numerals—it has a definite...
  • Act of Union

    Act of Union

    To make up for the loss of the British market the British North American colonies signed a ten year Reciprocity Treaty with the United States in 1854. Reciprocity Agreement provided free entry (no tariffs) for the natural products (wheat, fish,...
  • Pronouns and Antecedents - Home - Polk School District

    Pronouns and Antecedents - Home - Polk School District

    Pronouns: Subjective Case Standard ELACC6L1a: Ensure that pronouns are in the proper case (subjective, objective, possessive) This relates to writing because you MUST make sure that you are using pronouns in the correct places in your writing. Subjective Case Subject...
  • Racing Change John Johnson, PMP CSM SPC November

    Racing Change John Johnson, PMP CSM SPC November

    "New Cost" includes the Amortized Capability Investment (planning, development, O&M) and resources needed to operate the business with new capability. This ensures the topline is net profit, or gains (higher throughputs and lower costs) minus the investment. All values are...