SQL Azure

1. SQL Azure Best Practices Tony Petrossian Microsoft Corporation Tony.Petrossian@microsoft.com

2. Agenda • Service Recap • “Server” Management • Connectivity and Tracing • Using multiple databases • Migrating data and schema in and out of SQL Azure

3. SQL Azure Recap • Relational database service • Built on SQL Server technology foundation • Highly scaled • Highly secure • Database “as a Service” – beyond hosting • Customer Value Props • Self-provisioning and capacity on demand • Symmetry w/ on-premises database platform • Automatic high-availability and fault-tolerance • Automated DB maintenance (infrastructure) • Simple, flexible pricing – “pay as you grow”

4. SQL Azure Topology

5. SQL Azure Connection TDS Gateway 1 8 Front-end Node Front-end Node Applications connect directly to a database • “Initial Catalog = <db>” in connection string • No support for context switching (no USE <db>) SQL Azure connection strings follow SQL syntax • Except for an unusual username format Format of username for authentication: • ADO.Net:Data Source=server.database.windows.net;User ID=user@server;Password=password;... • ODBC:Driver={SQL Server Native Client 10.0}; Server=server.database.windows.net; Uid=user@server;Pwd=password;... Protocol Parser Protocol Parser 7 2 TDS Session 1 Gateway Logic Gateway Logic 3 9 Master Cluster Master Node Master Node 5 4 6 Partition Manager Partition Manager Data Node Components Data Node Components Machine 4 Machine 5 Machine 6 Machine 7 SQL Instance SQL Instance SQL Instance SQL Instance SQL DB SQL DB SQL DB SQL DB Master1 DB2 DB3 DB4 DB5 DB2 DB3 Master1 Meta 1 Master1 DB2 DB3 DB4 DB5 DB1 DB7 Scalability and Availability: Fabric, Failover, Replication, and Load balancing Scalability and Availability: Fabric, Failover, Replication, and Load balancing

6. Recommendations

7. Server Management • Where should I create my server? • Co-locate server with Windows Azure app to reduce latency • When should I create a new server? • Trade off between geo/admin/billing • Managing Server • Through the Portal • Add/Drop server • Establish admin credentials • View usage reports • Network access configuration • Through the Master Database • Fine-tune firewall settings through code • User logins • Usage and metrics reporting (billing) • Create/Drop databases • Admin roles permissions • CREATE/DROP database • CREATE/DROP/ALTER login • GRANT/REVOKE rights • Modifying server firewall settings HTTP Portal User DB Master DB TDS User DB User DB SQL Azure Server

8. Each server defines a set of firewall rules Determines access policy based on client IP By default, there is NO ACCESS to server Controlled using TSQL API against Master DB: sys.firewall_rules, sys.sp_set_firewall_rule, sys.sp_delete_firewall_rule Portal UX Server: Network Access Control

9. sys.bandwidth_usage: usage in KB sys.database_usage: instance count by type per day Server: Billing and Reporting

10. Application Design Topics • Most-applicable SQL Best Practices • Connection Pooling • Query Parameterization • Batching • Scaling with data and load • Sharding • Building copies • Deploying and uploading data • Bulk copy, BCP.EXE • SSIS • Microsoft Sync Framework Power Pack for SQL Azure

11. Use Pooled Connections • Increases efficiency by removing re-login • // When pooling, use connection and return immediately • // Do not hold for a long time – pool ensures fast turnaround • // on second use • using (SqlConnection conn = new SqlConnection(…)) • { • conn.Open(); • using (SqlCommandcmd = conn.CreateCommand()) • { • cmd.CommandText = …; • … • } • } • using (SqlConnection conn = new SqlConnection(…)) • { • conn.Open(); …

12. Connections can drop for variety of reasons Idleness for more than 30-minutes Transient (network) errors Intentional throttling First step: reconnect immediately Handles idleness- and transient-disconnects The SQL Azure Gateway handles connection retry for app Connections attempted for ~30s before failure What to do on connection failure? Wait (10 seconds), then retry Check the error messages and follow recommendation Server health can be checked via Portal TSQL APIs will come in later releases Connections: Retry on failure

13. Errors to Catch

14. Connection Pattern Keep it inside the main loop • while(true) • { • using(SqlConnection connection = newSqlConnection(connStr)) • { • try • { • connection.Open(); • using (SqlCommandcmd = connection.CreateCommand()) • { • cmd.CommandText = @"SetBCPJobStartTime"; • cmd.CommandType = CommandType.StoredProcedure; • cmd.Parameters.Add(newSqlParameter(@"@BCPJobId", BCPJobId)); • cmd.ExecuteNonQuery(); • } • } • catch (SQLExceptionSqexc) • { • // deal with error • } • catch (Exceptionexc) • { • // deal with error • } • } • // more stuff • // ….. • }

15. Each session assigned a unique ‘sessionId’ Tracks session state and service errors Retrievable from CONTEXT_INFO() Save this with each new connection If you need support, support personnel will greatly appreciate that value Tracing Connectivity Problems

16. Tracing Helper Pattern • Guid? sessionId = null; • using (SqlConnection conn = new SqlConnection(…)) • { • // Grab sessionId from new connection • using (SqlCommandcmd = conn.CreateCommand()) • { • conn.Open(); • cmd.CommandText = • "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())"; • sessionId = new Guid(cmd.ExecuteScalar().ToString()); • } • // continue processing • ... • }

17. Batching: push logic to Server Use stored procedures and batching Limit number of round trips to server Example: batch 10 statements vs. 10 round-trips Parameterized queries Parameterize queries (limits compiles) Declare all parameters, type and length Lack of parameter size leads to cache bloat Maximize your Performance

18. Declare Parameter Lengths! // Length inferred: leads to cache bloat  cmd.CommandText = "SELECT c1 FROM dbo.tbl WHERE c2 = @1"; cmd.Parameters.Add("@1", SqlDbType.NVarChar).Value = "1"; … cmd.Parameters.Add("@1", SqlDbType.NVarChar).Value = "22"; (@1 nvarchar(1)) SELECT c1 FROM dbo.tbl WHERE c2 = @1(@1 nvarchar(2)) SELECT c1 FROM dbo.tbl WHERE c2 = @1 // Length supplied: no cache bloat  cmd.CommandText = "SELECT c1 FROM dbo.tbl WHERE c2 = @1"; cmd.Parameters.Add("@1", SqlDbType.NVarChar, 128).Value = "1"; (@1 nvarchar(128)) SELECT c1 FROM dbo.tbl WHERE c2 = @1

19. SQL Azure balances databases across machines Divide your data into smaller chunks Makes for better load-balancing Ensures we can place your data on the most-appropriate servers Using shorter transactions Ensures we can respond to issues faster Avoid impacting others sharing the same box Thus avoiding being throttled by SQL Azure Maximize elasticity benefits

20. Multi-Database Case Study Kelly Blue Book http://www.kbb.com/ • Provider of vehicle valuation data • > 13 million visitors to their site per month • 2.5 GB Database Size • Data refreshed every week • Replicate data across 6 databases for increased perf • Increase/Decrease database count based on demand

21. Copies: Managing Lots of Read Access App LB Server Node 76 Node 2 Node 14 Node 19 Node 21 Node 33 Node 99 Master DB DBC1 DBC2 DBC3 DBC4 DBC5 DBC6 SQL Azure Cluster

22. Sharding Example: TicketDirect Architecture Client Applications Azure Roles .Net Service Bus Castellan.Azure • Box Office sales • Ticket Printing • System Administration • Venue/Event Management • Partitioning WCF Distributed Cache Worker MemCache http:// TicketDirect .* Dynamic Worker (tasks uploaded as blobs) PartitionerWorker On PremiseSQL Server -- - --- - - Azure Storage -- - --- - - Tables to record server & partition information Blobs to store web and worker role resources Queues for communication between clients and roles Castellan Venue Castellan.old (VB6) SQL Azure Castellan Venue DB Castellan Venue DB Castellan Venue DB Castellan Venue DB’s Castellan Venue DB’s Castellan Venue DB’s Castellan Venue 1 Partition(s) Venue 2 Partition(s) Venue N Partition(s) ... One application DB, many venue DB’s – each partitioned in to many parts (40+)

23. Shards: Managing Lots of Transactions App Server Node 76 Node 2 Node 14 Node 19 Node 21 Node 33 Node 55 Node 99 Master DB DB1 DB2 DB3 DB3 DB4 DB5 DB6 SQL Azure Cluster

24. Migrating Databases to SQL Azure

25. Managing the Schema • Moving between SQL Server & SQL Azure • Use SSMS script generation tool • SQL Azure Migration Wizard • http://sqlazuremw.codeplex.com • Use Data-Tier Application • VS2010, SQL Server 2008 R2, SQL Azure SU2 (April 2010) • Build your own scripts • Watch out for unsupported T-SQL • Don’t depend on instance or server level objects

26. SQL Azure supports standard SQL data import and export patterns Use bulk loading patterns where possible BCP – console .EXE bulk load/export tool SSIS – SQL integration server Bulk APIs in ODBC and ADO.Net SQL Azure supports data synchronization With on-premises DBs and client stores Always good advice: Break batches up into smaller, consumable chunks Add retry and tracing logic to ensure robust resume in face of failures Getting Data In and Out

27. Data Import: ADO.Net Bulk Copy API // Bulk data import using (SqlBulkCopy bulk = newSqlBulkCopy(newSqlConnection(conn)) { DestinationTableName = "dbo.data", BatchSize = 2000, // Transaction size (length) BulkCopyTimeout = 10000, // Transaction timeout NotifyAfter = 1000, // Progress callback }) { bulk.SqlRowsCopied += newSqlRowsCopiedEventHandler( myProgressCallback); bulk.WriteToServer(sourceDataReader); }

28. Data Export/Import: BCP.EXE // BCP example SET SRV=somesrv.database.windows.net SET LOGIN=mylohin@somesrv SET PW=something SET S_DR=C:\flats SET DB=TPCH bcp %DB%.dbo.supplier in %S_DR%\supplier.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|" bcp %DB%.dbo.nationin %S_DR%\nation.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|" bcp %DB%.dbo.regionin %S_DR%\region.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|" bcp %DB%.dbo.customerin %S_DR%\customer.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|" bcp %DB%.dbo.partin %S_DR%\part.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|“ bcp %DB%.dbo.supplier out %S_DR%\supplier.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|" bcp %DB%.dbo.nationout %S_DR%\nation.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|" bcp %DB%.dbo.region out %S_DR%\region.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|" bcp %DB%.dbo.customer out %S_DR%\customer.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|" bcp %DB%.dbo.part out %S_DR%\part.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

29. Many SQL Server patterns apply to SQL Azure Use SQL best practices wherever possible Patterns discussed: Connectivity (to database, not server) Tracing and support Batching, Pooling and Parameterization Getting data in and out Summary

30. Additional Resources • Azure Prices and Packages: • http://www.microsoft.com/WindowsAzure/offers/ • SQL Azure Information: • http://www.microsoft.com/windowsazure/sqlazure/ MSDN Resources • http://www.microsoft.com/windowsazure/sqlazure/

31. Thank you for your Attention! • For more Information please contact • Tony Petrossian • Principal Program Manager • SQL Azure Development • Microsoft Corporation • tonypet@microsoft.com