Big Data Dictionary
Microsoft SQL Azure
Microsoft SQL Azure is a cloud-based relational database service that has been built on Microsoft SQL Server technologies. It provides a highly available, scalable, multi-tenant database service hosted by Microsoft in the cloud. So, applications can create, access and manipulate tables, views, indexes, referential constraints, roles, stored procedures, triggers, and functions. It can execute complex queries, joins across multiple tables, supports aggregation and full-text queries. It also supports Transact-SQL (T-SQL), native ODBC and ADO.NET data access3. In particular, SQL Azure service can be seen as running an instance of SQL server in a cloud hosted server which is automatically managed by Microsoft instead of running on-premise managed server. The below figure illustrates the client access architecture of SQL Azure.
In SQL Azure, a logical database is called a table group which can be keyless or keyed. A keyless table group is an ordinary SQL server database where there are no restrictions on the choices of keys for the tables. On the other hand, if a table group is keyed, then all of its tables must have a common column called the partitioning key which does not need not to be a unique key for each relation. A row group is a set of all rows in a table group that have the same partition key value. SQL Azure requires that each transaction executes on one table group. If the table group is keyed, then the transaction can read and write rows of only one row group. Based on these principles, there are two options for building transaction application that can scale out using SQL azure. The rst option is to store the data in multiple groups where each table group can t comfortably on single machine. In this scenario, the application takes the responsibility for scaling out by partitioning the data into separate table groups. The second option is to design the database as keyed table group so that the SQL azure can perform the scale out process automatically.
In SQL azure, the consistency unit of an object is the set of data that can be read and written by an ACID transaction. Therefore, the consistency unit of a keyed table group is the row group while the consistency unit of a keyless table group is the whole table group. Each replica of a consistency unit is always fully contained in a single instance of SQL server running one one machine. Hence, using the two-phase commit protocol is never required. A query can execute on multiple partitions of a keyed table group with an isolation level of read-committed. Thus, data that the query read from di erent partitions may re ect the execution of di erent transactions. Transactionally consistent reads beyond a consistency unit are not supported.