SQL Database Federations: Enhancing SQL to enable Data Sharding for Scalability in the Cloud

I am thrilled to announce the availability of a new specification called SQL Database Federations, which describes additional SQL capabilities that enable data sharding (horizontal partitioning of data) for scalability in the cloud.

The specification has been released under the Microsoft Open Specification Promise. With these additional SQL capabilities, the database tier can provide built-in support for data sharding to elastically scale-out the data. This is yet another milestone in our Openness and Interoperability journey.

As you may know, multi-tier applications scale-out their front and middle tiers for elastic scale-out. With this model, as the demand on the application varies, administrators add and remove new instances of the front end and middle tier nodes to handle the workload.

However, the database tier in general does not yet provide built-in support for such an elastic scale-out model and, as a result, applications had to custom build their own data-tier scale-out solution. Using the additional SQL capabilities for data sharding described in the SQL Database Federations specification the database tier can now provide built-in support to elastically scale-out the data-tier much like the middle and front tiers of applications. Applications and middle-tier frameworks can also more easily use data sharding and delegate data tier scale-out to database platforms.

Openness and interoperability are important to Microsoft, our customers, partners, and developers, and so the publication of SQL Database Federations specification under the Microsoft Open Specification Promise will enable applications and middle-tier frameworks to more easily use data sharding, and also enable database platforms to provide built-in support for data sharding  in order to elastically scale-out the data.

Also of note: The additional SQL capabilities for data sharding described in the SQL Database Federations specification are now supported in Microsoft SQL Azure via the SQL Azure Federation feature.

Here is an example that uses Microsoft SQL Azure to illustrate the use of the additional SQL capabilities for data sharding described in the SQL Database Federations specification.

-- Assume the existence of a user database called sales_db. Connect to sales_db and create a federation called orders_federation to scale out the tables: customers and orders. This creates the federation represented as an object in the sales_db database (root database for this federation) and also creates the first federation member of the federation.

CREATE FEDERATION orders_federation(c_id BIGINT RANGE)
GO

-- Deploy schema to root, create tables in the root database (sales_db)

CREATE TABLE application_configuration(…)
GO

…

-- Connect to the federation member and deploy schema to the federation member

USE FEDERATION orders_federation(c_id=0) …
GO

-- Create federated tables: customers and orders

CREATE TABLE customers (customer_id BIGINT PRIMARY KEY, …) FEDERATED ON (c_id = customer_id)
GO

CREATE TABLE orders (…, customer_id BIGINT NOT NULL) FEDERATED ON (c_id = customer_id)
GO

-- To scale out customer’s orders, SPLIT the federation data into two federation members

USE FEDERATION ROOT …
GO

ALTER FEDERATION orders_federation SPLIT AT(c_id=100)
GO

-- Connect to the federation member that contains the value ‘55’

USE FEDERATION orders_federation(c_id=55) …
GO

-- Query the federation member that contains the value ‘55’

UPDATE orders SET last_order_date=getutcdate()…
GO

I am confident that you will find the additional SQL capabilities for data sharding described in the SQL Database Federations specification very useful as you consider scaling-out the data-tier of your applications. We welcome your feedback on the SQL Database Federations specification.

Thanks,

Ram Jeyaraman

Senior Program Manager, Microsoft’s Interoperability Group