SQL Azure Introduction

1. SQL Azure Introduction 50466 Windows® Azure™ Solutions with Microsoft® Visual Studio® 2010

2. What and Why of SQL Azure • SQL Azure is a highly available, scalable, multi-tenant relational database in the cloud. • As mentioned earlier, SQL Azure is SQL Server at its core. • SQL Azure is not a full-blown SQL Server 2008. This is SQL Server with a number of limitations (covered later). • Some sources have implied SQL Azure is essentially SQL Express. • While not exactly true either, the following quote might help you understand why a comparison to SQL Express can be useful. • Because SQL Azure is SQL Server at its base, most find migrating on-premise databases to the cloud straightforward. • Expect future versions of SQL Azure to contain even more SQL Server features and come closer to true SQL Server in the cloud. “If your application works today against SQL Express edition and does not make use of some of the more advanced features of SQL Server, then your application should work in the cloud with little or no modification.”

3. What and Why of SQL Azure Cont. • Like Windows Azure Storage, SQL Azure is independent of other Windows Azure products. • You can certainly use SQL Azure as a relational database in support of cloud applications. • However, you can also use SQL Azure as a database in support of on-premise applications, as a backup data store, or easily provisioned test environment. • What are the benefits of SQL Azure and why consider it in your architecture?

4. What and Why of SQL Azure Cont. • First and foremost, SQL Azure removes or reduces the typical costs and burdens associated with establishing and managing a data server. • You can provision a SQL Azure instance in minutes. • As with all of Windows Azure, Microsoft owns, operates, and manages the data centers that host your SQL Azure instances. • You simply pay for the SQL Server-like instances/space that you need without having to worry about upgrades, patches, etc.

5. What and Why of SQL Azure Cont. • SQL Azure is scalable. • You can think of the Microsoft Data Centers as offering a bottom-less cup of SQL Server. • As your data grows, your SQL Azure instances can grow to support that need. • While each instance of SQL Azure is limited to 50GB, you learn techniques for how to partition (covered later) your data to spread across many databases. • So, the only real limit to the size of your entire cloud-based database is the size of your wallet. • Importantly, SQL Azure allows you to scale down just as fast as you scaled up. Simply remove instances no longer needed as your data need shrinks.

6. What and Why of SQL Azure Cont. • SQL Azure is always available. • Your database is, just like Windows Azure Storage, replicated three times (an original and two backups). • Therefore, even during hardware failure, your database is available to applications that use it. • You cover how replication is accomplished and managed later in this chapter.

7. What and Why of SQL Azure Cont. • SQL Azure provides a familiar persistence platform to developers and database administrators • When first announce at Professional Developers Conference 2008, Windows Azure did not have a relational database component. • There was a persistent structured storage capability (not unlike table storage), but it was not relational database technology. • Microsoft added SQL Azure because of the feedback from the community. • The result is the familiar SQL, relational database platform in the cloud that the IT community has come to know, if not love. • This allows organizations to leverage existing tools, libraries, and even developer knowledge to build databases and applications that use them. • For example, you can use ADO.NET and Tabular Data Stream (TDS) to work with SQL Azure. • This also allows easy migration from on-premise relational databases to SQL Azure.

8. SQL Azure Architecture • While SQL Azure is based on SQL Server, the architecture is such that you are not connecting directly to SQL Azure as you might connect to SQL Server.

9. SQL Azure Architecture Cont. • In fact, SQL Azure is a service that mimics a SQL Server instance, but in fact wraps multiple instances of SQL Server under the covers. • Thankfully, as a developer or user of the SQL Azure database, the details are obfuscated from you. • However, it is largely because of the architecture that certain commands and features are removed in SQL Azure. • SQL commands and features, for instance, that work based on the physical infrastructure of SQL Server would not work on SQL Azure.

10. SQL Azure Architecture Cont. • As a client of SQL Azure, you use the tools and APIs familiar to you to access and manage your database. • Microsoft calls this the Client Layer and there isn’t anything new in this layer. • The client layer includes, but is not limited to WCF Data Services, LINQ, Entity Framework, etc. These use ADO.NET or ODBC under the covers. • You can also use ODBC or ADO.NET directly. • At the bottom of the client layer is a tabular data stream (TDS). SQL Azure uses the same TDS interface as SQL Server.

11. SQL Azure Architecture Cont. • As a client, you connect to the Service Layer, not directly to the SQL Server database. • This layer obfuscates the complexity that is SQL Azure. • The Service Layer is exposed through a secure (SSL) TDS connection on port 1433. • The Service Layer takes care of the administrative duties (provisioning, billing and metering) for the pay-as-you-go databases. • Provisioning requires the Service Layer to communicate with the SQL Azure Fabric (covered below). • In addition, the Service Layer routes your request for data to the appropriate server under the covers. • The Service Layer also provides the firewalling, which is covered later.

12. SQL Azure Architecture Cont.

13. SQL Azure Architecture Cont. • Specifically, SQL Azure supports the drivers and libraries listed in the table below when writing applications to use SQL Azure data. • Connecting by using OLE DB is not supported.

14. SQL Azure Architecture Cont. • The Service Layer routes requests to the Platform Layer. • More precisely the Service Layer routes requests to a server hosting SQL Server. • SQL Azure Fabric manages SQL Server running on all those servers. • SQL Azure Fabric provides the automatic replication, failover, and load balancing across the SQL Server instances. • Additionally, management services on the servers monitor the health of the server and allow automated installation of service upgrades and software patches.

15. SQL Azure Architecture Cont. • There are three replicas of your data in the Platform Layer. • Whenever a replica fails, SQL Azure takes the replica out of the pool and creates a new replica. • SQL Azure Fabric may also move a replica (from one server to another) for performance reasons (attempting to keep the loads smooth across all servers). • The SQL Azure Fabric designates one of the replicas as the lead replica. • Therefore, the connection router (in the Service Layer) looks for the lead replica and routes all requests to the server hosting the lead replica. • However, SQL Azure Fabric distributes any changes to the data in the lead replica to the other replicas (using a custom replication process). • When the lead replica fails, SQL Azure Fabric takes the failed instance out of the replica pool and designates a new leader.

16. SQL Azure Architecture Cont. • Of course, the Platform Layer runs on physical hardware (and associated operating system) in the Microsoft Data Center. • While not pictured above, Microsoft calls this layer the Infrastructure Layer. • The Infrastructure Layer includes the physical disks and networks to host your data. • The Infrastructure Layer is similar to a SAN used in many data architectures.

17. SQL Azure vs. SQL Server • SQL Azure is based on SQL Server 2008 R2. • Some of the features of SQL Server 2008 R2 are not in SQL Azure. • SQL Azure supports a subset of Transact-SQL (T-SQL) and the TDS protocol. • For a comprehensive look at the limitations and differences, see msdn.microsoft.com/en-us/library/ee336245.aspx. • For a list of supported and not supported T-SQL language elements in SQL Azure see msdn.microsoft.com/en-us/library/ee336250.aspx. • A paper written by the SQL Azure product team compares SQL Azure against SQL Server. Find the paper at social.technet.microsoft.com/wiki/contents/articles/comparing-sql-server-with-sql-azure.aspx.

18. SQL Azure vs. SQL Server Cont. • The table below contains an abbreviated list of unsupported T-SQL and SQL Server features as presented in the Microsoft Windows Azure Bootcamp.

19. SQL Azure vs. SQL Server Cont.

20. SQL Azure vs. SQL Server Cont. • Importantly, note SQL Azure does not provide backup and restore capability. • Somewhat along the same line, SQL Azure does not support traditional replication or data mirroring. • SQL Azure automatically replicates your data three times. • However, there are times when you need to copy your database for purposes outside of availability. • This shortcoming, and how to handle it, is covered later in the chapter.

21. SQL Azure vs. SQL Server Cont. • Another key differentiation point is on database size limits. • SQL Azure databases are limited in size. • For all practical purposes, there is no size limits to data storage in SQL Server. • You must select the size of SQL Azure database that meets your needs. With increased size comes increased cost (covered below). • SQL Azure database instances today can be 1, 5, 10, 20, 30, 40, or 50GB. • Unfortunately, the size of the database does not increment automatically. • Once a database reaches its limit, the database allows only selects and deletes. Attempts to insert into the database result in an error (error code 40544). • If your database grows beyond its initial limit, (say 5GB) you must alter the database (and of course pay the additional cost for the new bigger database).

22. SQL Azure vs. SQL Server Cont. • If your database grows beyond 50GB (the current storage limit in SQL Azure) you must consider an alternate database design. • Database partitioning is covered later as a way to manage very large databases across multiple SQL Azure instances.

23. SQL Azure Costs • The size of your database determines SQL Azure cost. • The table below lists the cost (in US Dollars) for the available database size up to the current limit of 50GB. • You can find the complete SQL Azure pricing guide at www.microsoft.com/windowsazure/pricing/#sql.

24. SQL Azure Costs Cont. • Importantly, the monthly fee is amortized over the month and charged on a daily basis. • You might note that Microsoft has designated the smaller sized databases as “Web Edition” instances. • Larger databases are designated as “Business Edition” instances. • Except for the size differential, the only other feature listed for Business Edition databases is • Use Web Edition databases in support of Web applications where less storage is typically required. • Data from Web applications is often off-loaded at some time to an enterprise database. • Business Edition databases are meant to support larger, enterprise applications (like SaaS ISV apps says the Microsoft pricing page) apps “Additional features in the future like auto-partition, CLR, fanouts etc.”

25. SQL Azure Costs Cont. • In addition to the monthly costs associated to the database, you pay bandwidth charges (the same charge applies for Windows Azure Storage). • That is, you pay for data transferred into and out of your database. • The table below lists the bandwidth costs (in US Dollars). • According to Microsoft, it costs more to build and operate Asian data centers. • Importantly, data transfers within a region are free! • If you have cloud-based applications, putting them in the same region as your database helps save you money. • The Service Level Agreement for SQL Azure ensures your database will be available 99.99% of any calendar month.

26. SQL Azure Costs Cont. • As discussed in the table storage chapter, SQL Azure is more expensive than Windows Azure Storage, and in particular table storage. • Table storage has also a 100TB limit compared to the current 50GB limit in SQL Azure. • SQL Azure offers more features (relationships, indexes, queries, etc.) but at a price. • SQL Azure is also based on the familiar (relational database, T-SQL, TDS, etc.), making migration to SQL Azure easier. • Weigh all your options as you think about your data storage needs.

27. SQL Azure Account Setup and Server Provisioning • To create a database in the cloud, start where all cloud development begins: in the Developer Portal. • You must have a Windows Azure account to use SQL Azure. • You can start at the Windows Azure Developer Portal (windows.azure.com) and click on the Database in the Navigation tab displayed on the left of the Portal.

28. SQL Azure Account Setup and Server Provisioning Cont.

29. SQL Azure Account Setup and Server Provisioning Cont. • When you click on “Database” in the Home view, you are taken to the Database view.

30. SQL Azure Account Setup and Server Provisioning Cont. • Through the Database view, you can manage your Azure subscriptions, SQL Azure servers, and databases.

31. SQL Azure Account Setup and Server Provisioning Cont. • Before you can create a database, you first need a SQL Azure server. • Click on the Create a new SQL Azure Server link to create a SQL Azure server and an administrator to manage the server.

32. SQL Azure Account Setup and Server Provisioning Cont. • A wizard will take you through the required information for the server and administrator.

33. SQL Azure Account Setup and Server Provisioning Cont. • A server (or more appropriately a virtual server) is a logical container for your SQL Azure database instances. • The server is virtual in that your server’s databases (and all their replicas) may stretch across multiple physical servers in the data center. • Today, you are allowed only one SQL Azure server per Windows Azure platform subscription.

34. SQL Azure Account Setup and Server Provisioning Cont. • When you create a server, you must select the region of the data center in which it resides. • Remember, if your application is in the same location as your database there are no bandwidth charges between the cloud-based application and the database.

35. SQL Azure Account Setup and Server Provisioning Cont. • SQL Azure does not use Affinity Groups. You establish affinity simply by picking the same region as your cloud-based applications (or other databases).

36. SQL Azure Account Setup and Server Provisioning Cont. • The administrator account that you establish during the server creation is similar to the server-level principal system administrator (sa) in SQL Server. • The SQL Azure administrator always has permission to manage all server-level and database-level security. • When creating the server, you must provide an administrator’s username and password into fields provided.

37. SQL Azure Account Setup and Server Provisioning Cont. • Passwords must be “strong”, which means they must be at least eight characters long and follow password complexity policies for SQL Server 2008 R2.

38. SQL Azure Account Setup and Server Provisioning Cont.

39. SQL Azure Account Setup and Server Provisioning Cont. • After creating a server (and server administrator login), the Portal presents you with your server’s admin page. • Your new server is listed under your subscriptions. • In this display, you can find your server’s generated name and URL.

40. SQL Azure Account Setup and Server Provisioning Cont. • Think of the URL as equivalent to the name of a SQL Server machine. In fact, you use the URL as you use a SQL Server’s name in connection strings, tools, etc. • Remember, however, your SQL Azure databases spread across multiple machines. Therefore, this URL is just a façade into the Service Layer.

41. SQL Azure Account Setup and Server Provisioning Cont. • By default, SQL Azure provisions a master database for you. • You can locate the master database by clicking on the server listed under your subscription in the Portal. • The master database is similar to the master database in SQL Server and serves the same purpose. • The master database is a read-only database that holds logins, login permissions (to create databases for example), firewall settings, and usage metrics.

42. SQL Azure Account Setup and Server Provisioning Cont. • You must be able to connect to the master database to CREATE, ALTER, or DROP logins or databases. • Don’t worry, Microsoft does not charge you for the master database.

43. SQL Azure Account Setup and Server Provisioning Cont. • Through the icon menu bar on the Portal, there are facilities to manage the server (drop, admin password reset, etc) and create/manage associated databases. • Databases you create are tied to the server.

44. SQL Azure Account Setup and Server Provisioning Cont. • If you drop the server, you drop all the databases tied to it. • Each SQL Azure server can contain multiple databases. • Click on the Create icon (in the Database category) in the icon menu bar to create a new database for the server. • Each database can have tables, views, indices, stored procedures, etc. just as you would find in a SQL Server database.

45. SQL Azure Account Setup and Server Provisioning Cont. • When creating a database, you specify the database name, edition (Web or Business), and maximum size. The edition determines the available max sizes. • You can alter the name, edition type, and size of the database with an ALTER DATABASE T-SQL statement (more on how to execute statements to the database in a bit). • Once created, the database shows up in the list of databases.

46. SQL Azure Account Setup and Server Provisioning Cont. • Click on a database in the list of databases and then click on the Drop database button to remove the database.

47. SQL Azure Account Setup and Server Provisioning Cont. • As you see in moment, you can also use other tools to create, delete, and manage databases.

48. SQL Azure Account Setup and Server Provisioning Cont. • How do you connect to the SQL Azure databases you create? • Conveniently, a button is provided to see the ADO.NET, ODBC and PHP code connection strings. • Once created, select the database from the database listing under the server. Then click on the View button under the Connection Strings in the Properties display

49. SQL Azure Account Setup and Server Provisioning Cont. • The Connection Strings utility comes in handy when building applications (more later).

50. SQL Azure Account Setup and Server Provisioning Cont. • You can also test the connection strings by pushing the Test Connectivity icon in the menu bar with the database selected.