SQL Azure

1. SQL Azure

2. Announcements • Welcome Back Phoenix! • Microsoft Shutting Down Northwest Data Center • Reason given: changes in local tax laws • Apps hosted there must move to Southwest • Expect they are relocating the data center elsewhere

3. Who I am • Work for Neudesic as a senior consultant • Worked in the industry for about 10 years • From the East coast (lived in AZ for 9 years) • I like books (bribes are accepted) • Pina Coladas, Dancing in the Rain, Long walks on the beach

4. SQL Azure - Overview • Database in the Cloud • First preview CTP came out in late August • Very similar to SQL Server • Database functionality only at present(no SSRS, SSAS, SSB) • Free to use between now and release (November 2009 PDC)

5. SQL Azure - Database Only • SQL Azure does not support all of the features and data types found in SQL Server. Analysis Services, Replication, Reporting Services, and Service Broker are not currently provided as services on the Azure Services Platform.

6. SQL Azure – How to Sign Up • Just visit Azure.com and follow the sign-up link • Your invitation tokenwill come in the mail

7. SQL Azure – Manage from Azure.com

8. SQL Azure – Manage from Azure.com

9. SQL Azure Server Administration Server Name Show Connection String Admin Data Center Admin Password Reset Databases Create New Database

10. Connection Strings • It’s intended that the main difference between SQL Server and SQL Azure is the connection string • Connection String Format:Server=tcp:server.ctp.database.windows.netDatabase=databaseUser ID=userPassword=passwordTrustedConnection=False

11. Create Database

12. Demo: Sql Azure Portal

13. Can I use SQL Server Mgmt Studio? Yes, but it’s a little rocky right now. You need to follow these steps precisely to get it working: 1. Cancel out of the first Connect dialog that pops up.2. click on the New Query button from the toolbar3. enter       Server name: full servername       Login: just the username (no @...) and password4. Under Options, Connect to Database, enter db name 5. Click Connect, ignore the error that is displayed.

14. Step 1: Cancel Connect Dialog CancelDialog

15. Steps 2-3: Click New Query, Enter Info Click New Query Server Name Admin user id and password

16. Step 4: Enter Database Name Enter Database Name

17. Step 5: Click Connect, Ignore Error

18. Create DB Locally

19. Script Table Creation

20. Create Table in SQL Azure

21. Create Table, Insert, Select in SQL Azure

22. Demo: Using Sql Server Management Studio with SQL Azure

23. SQL Azure – Key Benefits • Manageability • Scale and functionality of an enterprise data center w/o the admin overhead • Self-managing capability allows organizations to provision data services without adding to their support burden • Reduce the initial costs of data services by provisioning only what you need. Provision your data storage in minutes. • High Availability • Built on Windows Server and SQL Server technologies, flexible enough to cope with any variations in usage and load. • Replicates redundant copies of your data to multiple physical servers to maintain availability and business continuity. • In the case of a hardware failure, SQL Azure provides automatic failover to optimize availability for your application. • Scalability • Scale your solution with ease. After partitioning your data, the service scales as your data grows. • You only pay for the storage that you use. Scale down the service when you do not need it. • Familiar Development Model and Relational Data Model • Use the same tools and libraries to build solutions that you do for SQL Server. • Data is stored in SQL Azure just like it is stored in SQL Server, by using Transact-SQL. • Familiar database objects - multiple databases with tables, views, stored procedures, indices • No Physical Considerations • SQL Azure servers and databases are virtual objects that do not correspond to physical servers and databases. By insulating you from the physical implementation, SQL Azure enables you to spend time on your database design.

24. Administration • Logical Administration • DBA manages schema creation, statistics management, index tuning, query optimization, and security administration (logins, users, roles…) • Physical Administration • SQL Azure service automatically replicates all data to provide high availably • SQL Azure service manages load balancing and, in case of a server failure, transparent fail-over.

25. Backup • No backup and restore commands • You can still use SQL Server Integration Services and the SQLCMD utility to bulk copy data.

26. Provisioning Model • Each Azure account can have multiple SQL Azure servers • Each SQL Azure server can be associated with one or more databases. • A SQL Azure server is a logical group of databases and acts as a central administrative point for multiple databases. • Each SQL Azure server includes logins similar to those in instances of SQL Server on your premises. • Each SQL Azure server has a fully qualified unique domain name, which is produced during the SQL Azure provisioning process. servername.ctp.database.windows.net

27. Create a Database • Interactively using the SQL Azure portal, or • Programmatically with a CREATE DATABASE statement.

28. Managing Users and Logins • Server-level login administration • Server-level administration for logins and databases in SQL Azure differs from an on-premise instance of SQL Server. • During provisioning, SQL Azure creates a server-level principal login equivalent to the sa login in SQL Server • Additional SQL Azure databases and logins can then be created via T-SQL • SQL Azure provides the same security principals as SQL Server, such as: • SQL Server logins: Authenticate access to SQL Azure at the server level. • Database users: Grant access to SQL Azure at the database level. • Database roles: Group users + grant access to SQL Azure at the database level. • Database-level login administration • Database-level administration for users and roles in SQL Azure is the same as for an on-premise instance of SQL Server.

29. T-SQL Support Supported Fully or Partially Not Available Common Language Runtime (CLR) Database file placement Database mirroring Distributed queries Distributed transactions Filegroup management Global temporary tables Spatial data and indexes SQL Server configuration options SQL Server Service Broker System tables Trace Flags • Constants • Constraints • Cursors • Index management and rebuilding indexes • Local temporary tables • Reserved keywords • Stored procedures • Statistics management • Transactions • Triggers • Tables, joins, and table variables • Transact-SQL language elements such as • Create/drop databases • Create/alter/drop tables • Create/alter/drop users and logins • User-defined functions • Views

30. Programming Model • Use one of two programming models: • For smaller data sets, use a single database, similar to how you use an on-premise instance of SQL Server. • For larger data sets, we recommend that you partition your data across multiple databases and write parallel "fan-out" queries to fetch the data. Doing this optimizes the performance and availability of SQL Azure.

31. Multi-tenancy Issues • Might not always connect • Long running queries are likely going to be throttled • Idle times will be short to free up connections for others • Limits on database size

32. Privacy / Security Issues • Consider encrypting data before it goes to database • Make sure you encrypt your connection to azure via SSMS • Big players are already hosting private data on this - Exchange Hosted Archive(EHA) • Kind of a question mark right now.

33. Demo: Migrating A SQL Server Database and ASP.NET Web Application To Use SQL Azure

34. SQL Azure Resources • Documentationhttp://msdn.microsoft.com/en-us/library/ee336279.aspx • Sign up for SQL Azurehttp://www.Azure.com • SQL Azure Developer Centerhttp://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx • Azure User Grouphttp://www.AzureUserGroup.com