Take your Database to the Cloud with SQL Azure

SQL Azure (SQLA) is the cloud based relational database service from Microsoft. SQL Azure servers deliver data services like database creation, transactions and management over the Internet. As compared to traditional on-premise data infrastructure, customers no longer have to bear upfront costs for hardware and platforms. The SQL Azure service enables customers to rapidly provision (or drop) their databases as per demand and they are only charged for the days of usage. Additionally, time consuming administration tasks like backups, patches are now taken care by the SQL Azure service. This service model makes SQL Azure equally suitable for large enterprises and small businesses or developers all alike. For DBAs and data developers planning to use SQL Azure as their data tier solution, this article highlights the important considerations involved in developing and managing SQL Azure databases.

SQL Azure Subscriptions

SQL Azure customers can provision data storage (and associated transaction processing) that can scale on-demand and is highly available. SQL Azure customers have two choices of databases. The Web Edition SQLA database is suitable for databases with storage requirements less than 5GB and a Business Edition SQLA database for storage requirements greater than 5 GB. The cost of a SQL Azure database is amortized over month and customers are charged for the number of days customer have the database.

Service Oriented Architecture of SQL Azure

Compared to on-premise SQL Server installations, SQL Azure services are delivered over the Internet. Instead of physical servers, data developers and DBAs have to work with logical servers and databases. Figure below depicts an overview of the SQL Azure architecture.

Tabular Data Stream (TDS): TDS is an application level protocol used for communication between clients and SQL Server. Any protocol that can generate TDS over the Internet is supported. This enables a variety of client applications from PHP to WCF data services to access the SQL Azure services.

SQL Azure Gateway Services: The SQL Azure gateway services are the front end for all client applications trying to access the SQL Azure services. In addition to providing gateway services like user authentication, basic request parsing, firewall verification, blocking DOS attacks, etc., the gateway services are also responsible for account provisioning, billing and usage monitoring.

Node: SQL Azure node is a commodity hardware machine that hosts one single instance of the SQL Server. This SQL Server instance hosts exactly one user database. The database has logical partitions and one partition hosts one SQL Azure database.

SQL Azure Topology: A special process called the fabric runs on every node in SQL Azure. The fabric is responsible for request routing, load balancing, failure detection and reconfiguration in case of adding new nodes or replacing failed nodes.

Logical Database: SQL Azure database is a logical collection of three SQL Server databases. SQL Azure requires that user data is replicated three times (1 primary replica, 2 secondary replicas) to ensure high availability. Each of these replicas is hosted in a logical partition belonging to a node. By design, all the replicas are never stored on the same physical machine thereby drastically reducing the chances of all three replicas failing at the same time. The gateway service is responsible for connecting an incoming request to the primary replica serving the data. The details of which particular node is hosting the replica are never known to client applications accessing the SQL Azure data.

Logical Server: SQL Azure server is not a machine. It is a logical collection of one or more SQL Azure databases. It is the TDS end point to which clients connect. The TDS end point itself is serviced by multiple redundant SQL Azure gateway servers.

Considerations for a data developer

Developing data applications for SQL Azure is very similar to developing for an on-premise SQL Server instance. However, there are a few key differences that developers should be aware of. Both Visual Studio 2010 and SSMS 2008 R2 support connecting to SQL Azure databases. The connection details for SQL Azure server can be obtained from the account information for the Windows Azure Platform account associated with the subscription. The syntax is similar to the connection strings used with on-premise SQL Server. Compared to an on-premise SQL Server instance, services delivered over the Internet will have a performance overhead due to network latency. This is common to all cloud computing services including SQL Azure. However, the overhead can be mitigated through careful planning of transactions (e.g. grouping queries) with the SQL Azure. Developers should also plan for unexpected drop of connections with the SQL Azure server. In such cases the SQL Azure server cannot return a meaningful error message to the application before the session ends. However, if a failed connection is attempted for use, there is an error message informing about connection issue.

Data security

SQL Azure adopts the following data security practices.

Data encryption: Unencrypted connections are not supported by SQL Azure. All communications with SQL Azure happen over https only.

Authentication: SQL Azure supports only SQL credentials. Windows authentication is not supported. SQL Azure also enforces strong passwords and rejects common login names as 'admin' or 'root' or 'administrator'.

Database Users: The security model within the database is similar to that of on-premise SQL Server. Database Users are created and mapped to login names, and can be assigned privileges and roles. Users from other SQL Azure databases cannot be given access and in fact cross database queries and the 'USE' statement are not supported.

Transaction handling: SQL Azure supports local transactions with normal T-SQL commands BEGIN TRANSACTION, ROLLBACK TRANSACTION and COMMIT TRANSACTION. SQL Azure databases are configured with READ_COMMITTED_SNAPSHOT and

ALLOW_SNAPSHOT_ISOLATION options; these options are not changeable. Only workaround for this is to use the locking hint WITH (READCOMMITTEDLOCK) with every table in every transaction.

Clustered Index Requirements: In SQL Azure, every table must have a clustered index. When a primary key is declared on a table, a clustered index is created on the table by default. However database developers must also evaluate keys other than the primary key to create the clustered index. When additional non-clustered indexes are created on tables that have a clustered index, those additional indexes reference the table data using the clustered key.

Considerations for a DBA

That SQL Azure database is delivered as a service, also means that traditional DBA tasks like backups, patch application or upgrades are automatically managed by the data center hosting the SQL Azure database. This greatly reduces the management overhead for DBAs allowing them to concentrate on core business issues like database design and optimization. Few of these are discussed in detail below:

Database manager for SQL Azure (formerly known as Houston)

Database manager for SQL Azure is a light weight online tool that can be used for basic database management tasks like authoring and executing queries, designing and editing database schema, and editing table data. Some other important tools available for SQL Azure are:

SQL Server Migration Assistant toolkits: These are designed to tackle the complex manual process customers' deal with when migrating databases. Toolkits are available for migrating data and schema from MS-Access and MYSQL databases into Azure.

SQL Server Integration Services: You can also transfer data to SQL Azure by using the SQL Server 2008 Integration Services (SSIS). This tool can be used for migrating on-premise SQL Server database to SQL Azure. SSIS is part of the SQL Server 2008 standard and enterprise editions.

Performance insight into SQL Azure

SSMS 2008 supports connecting to SQL Azure databases and obtain performance information. The SET STATISTICS T-SQL commands can also be used. However, the SQL Server profiler does not currently support SQL Azure. SSMS can be used to view the actual query plan. This gives insight into the indexes that SQL Azure is using to query the data, the number of rows returned at each step and which step is taking the longest. The SSMS query window can be used to run the SET STATISTICS command. Set statistic will give metrics about queries. An example is given in following code snippet.

SET STATISTICS TIME ON

SELECT *

FROM SalesLT.Customer

INNER JOIN SalesLT.SalesOrderHeader ON

SalesOrderHeader.CustomerId

Similarly to get information on IO performance of a query, you can use the IO ON command.

Running Query Information

In SQL Azure it is also possible to get access to the currently running queries and their execution count. This is available via the Procedure cache and a T-SQL query:

SELECT q.text, s.execution_count

FROM sys.dm_exec_query_stats as s

cross apply sys.dm_exec_sql_text(plan_handle) AS q

ORDER BY s.execution_count DESC

Backup and Restore

Two of the most important reasons for backing up a database are to ensure safety against hardware failure and user errors. In SQL Azure, each user database is replicated three times (one primary replica and two secondary replicas) and each of these replicas is hosted on a different physical hardware. In case of a failure of primary or secondary replica, SQL Azure automatically promotes a healthy secondary replica to primary replica. Remember that SQL Azure guarantees 99.9% availability.

To protect against user errors, SQL Azure allows users to create a copy of their database and store it within the same data center. This copy can be put online in place of the current Azure database at any time, or copied back to the database to restore the backup. In the case of copy, the backup is performed in SQL Azure without downtime to the source database.

Scalability

One of the biggest advantages of SQL Azure is the built-in scalability. Users can add more databases only when and if needed. If the need is temporary, then the un-needed databases can be dropped. In case of heavy load, the SQL Azure load balancer moves primary replicas to a node with lesser load.

Database sharding is a technique of horizontal partitioning data across multiple physical servers to provide application sale out. SQL Azure supports data sharding thus providing virtually unlimited scalability of data for an application.

Conclusion

For data developers and DBAs planning to use the SQL Azure data service as an efficient data—tier solution, this article has presented an overview of the SQL Azure architecture and discussed the important concepts like cloud computing and typical considerations for developers and data administrators. For further reading and building on these concepts you can visit http://ld2.in/139.