Introduction To Windows Azure SQL Database George Huey

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

Architecture

Architecture

Single Database ! Multiple Replicas Replica 1 DB Replica 2 Replica 3 Replica 4 Single Primary

Starting With The Basics

Windows Azure SQL Database

Server Defined Provision Servers Interactively Automate Server Provisioning

Application Migration

Use Familiar Technologies SQL Server Comparison Unsupported SQL Server Features

Supported Unsupported ADO. Net 3. 5 SP 1 and 4. 0 Ole. DB ODBC - SNAC 10 Entity Framework. Net 3. 5 SP 1 and 4. 0 SQL Server 2008 Driver for PHP v 1. 1

Supported Unsupported TCP/IP over port 1433 Dynamic ports Named pipes Shared memory Supported Unsupported SQL Auth Windows Auth

Securing your data

Connecting To SQL Database Considerations And Best Practices 1. 2. 3. 4. <connection. Strings> <add name="Adventure. Works"connection. String= "Data Source=[server]. database. windows. net; Integrated Security=False; Initial Catalog=Products. Db; User Id=[login]; Password=[password]; Trusted_Connection=False; Encrypt=true; " provider. Name="System. Data. Sql. Client"/> </connection. Strings> 5. 6. 7. 8. 9. 10. • The Transient Fault Handling Application Block

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 Server App code/ Tools ) T-SQL (TDS) Windows Azure SQL Database Code Near Windows Azure Data Sync (ASP. NET) (ASP. NET Windows Azure SQL Database App Code T-SQL (TDS) SOAP / REST HTTP / HTTPS App Code (ASP. NET) (ASP. NET ) T-SQL (TDS) SQL Database Code Far Hybrid

http: //msdn. microsoft. com/en-us/library/ee 336267. aspx http: //msdn. microsoft. com/en-us/library/windowsazure/ff 394115. aspx

Database Migration

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

Generate Script Wizard • Scenario • • Pros • • Migration of schema and/or data with fine grain control. 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 Migrating Databases to SQL Database demo

SQL Database Management Portal SQL Server Data Tools (SSDT)

• 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 Migrating Databases to SQL Database demo

SQLAzure. MW Migrating databases to SQL Database demo

Scenario/Tool Move SQL Schema SQL Azure Generate Migration Script Wizard √ √ DACs BAC √ √ BCP Efficient More work Move Data Move Large Data √ √* √* Limited NA * No Retry

Thank You

Appendix

Data Transfers Database Size Price Per Database Per Month 0 to 100 MB Flat $4. 995 > 100 to 1 GB Flat $9. 99 > 1 GB 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 Outbound Data Transfer US and Europe Asia First 5 GB / Month 1 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 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