Larry Franks and Brian Swan on Open Source and Device Development in the Cloud
Among the December updates to the Windows Azure platform was the introduction of SQL Azure Federations. In a nutshell, SQL Azure Federations introduces an abstraction layer for the sharding of SQL Azure databases. The value in federations lies in your ability to have elastic scalability of the database layer of your application (to match the elastic scalability of the rest of your application when it’s running in the cloud). And, one nice thing about the way federations work is that nearly everything can be done with simple SQL commands. Of course, that means that using SQL Azure Federations via PHP should be easy. So in this post, I’ll introduce you to SQL Azure federations by showing you how to use them via PHP.
Note that I’ll use the SQL Server Drivers for PHP to connect to SQL Azure. You can download the drivers here: http://www.microsoft.com/download/en/details.aspx?id=20098. The documentation for the drivers is here: http://msdn.microsoft.com/en-us/library/ee229547(SQL.10).aspx.
Creating a SQL Azure server is the only step you can’t do via PHP, and I can’t do any better than this article, Getting Started: Create your SQL Azure Server and Database, for showing you how to do it.( Just do Task 1 though…I’ll walk you through Task 2 (Creating a Database) using PHP.) Make note of the 10-character server ID and the user/password for your server…you’ll need that info in the code below.
You can create a SQL Azure database through the developer portal (as shown in the tutorial linked to above), but you can also do it via PHP. Here’s how to do it (I think most of this code speaks for itself, but I’ll add some comments afterwards):
$serverName = "tcp:SERVERID.database.windows.net, 1433";
$connectionOptions = array("Database" => "master", "UID" => "USER@SERVERID", "PWD" => "PASSWORD");
$conn = sqlsrv_connect($serverName, $connectionOptions);
if($conn === false)
echo "Connected via sqlsrv!<br />";
// Create database (be sure to connect to master)
$sql = "CREATE DATABASE SalesDB";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
echo "SalesDB database created!<br />";
I think the important things to point out in the code above are the connection details. Notice the $serverName specifies both the protocol (tcp) and the port (1433). The SERVERID is the 10-character name of your SQL Azure server, which you need as part of the server name (SERVERID.database.windows.net) and as part of your user name (USER@SERVERID) in the $connectionOptions array. Also note that I’m connecting to the master database. Beyond that, creating a SQL Azure database is the same as creating a SQL Server database.
If you want to verify that the SalesDB was, in fact, created, you can do that by logging into the developer portal, selecting Database, and clicking on your server. This is what you should see:
The next step is to create a SQL Azure Federation. What exactly is a Federation? Full details are here, but the short description is a that a Federation is a database object that manages much of the complexity that usually comes with implementing sharding. Creating a Federation is straightforward…notice that all the “magic” is in the SQL:
$connectionOptions = array("Database" => "SalesDB", "UID" => "USER@SERVERID", "PWD" => "PASSWORD");
// Create federation Orders_Federation (be sure to connect to SalesDB)
$sql = "CREATE FEDERATION Orders_Federation (CustId INT RANGE)";
echo "Orders_Federation created!<br />";
Note that I’m specifying SalesDB as the database in my $conectionOptions.
The CustId in the $sql string defines a federation distribution key, which determines the distribution of data to partitions within the federation. The federation distribution key must be an INT, BIGINT, UNIQUEIDENTIFIER, or VARBINARY (up to 900 bytes). RANGE in the query specifies the type of partitioning. For more detailed information, see CREATE FEDERATION.
Executing the query above creates your first federation member (think “first shard”). So you now have your root database (SalesDB) and one federation member, whose name is opaque (which is the point in Federations…you don’t need to know the names of the federated databases). However, you can get information about the member by executing this code (with the same connection code as in the example above):
$sql = "SELECT federation_id,
CAST(range_low AS INT) AS range_low,
CAST(range_high AS INT) AS range_high
echo "Federation members retrieved!<br />";
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
This will become more interesting when we split a federation (details below). I’ll come back to this later.
The next step is to create tables in our root database and federation members. The following code connects to our federation object and does this. Be sure to read the notes that follow the example:
$connectionOptions = array("Database" => "SalesDB", "UID" => "USER@SERVERID", "PWD" => "PASSWORD", "MultipleActiveResultSets" => false);
// Create tables
$sql1 = "USE FEDERATION Orders_Federation (CustId = 0) WITH RESET, FILTERING = OFF;";
$stmt = sqlsrv_query($conn, $sql1);
echo "Connected to Orders_Federation!<br />";
$sql2 = "-- Create Products table
CREATE TABLE Products(
ProductID int NOT NULL,
SupplierID int NOT NULL,
ProductName nvarchar(50) NOT NULL,
Price decimal(12,2) NOT NULL,
PRIMARY KEY(ProductId) )
-- Create Customers table
CREATE TABLE Customers(
CustomerID int NOT NULL,
CompanyName nvarchar(50) NOT NULL,
PRIMARY KEY (CustomerId) )
FEDERATED ON (CustId = CustomerID)
-- Create Orders table
create table Orders
CustomerId int NOT NULL,
OrderId int NOT NULL,
PRIMARY KEY (OrderId, CustomerId)
FEDERATED ON (CustId = CustomerId)
-- Create OrderItems table
CREATE TABLE OrderItems(
OrderID int NOT NULL,
Quantity int NOT NULL,
PRIMARY KEY (OrderId, CustomerId, ProductId) )
FEDERATED ON (CustId = CustomerId)";
$stmt = sqlsrv_query($conn, $sql2);
echo "Tables created!<br />";
Notes on the code snippet above:
Once we have created a federation object and a federation member, inserting data is almost exactly the same as it is for SQL Server. The only difference is that we need to connect to a federation member first. (Note #2 above applies to this code example also.)
$sql2 = "INSERT INTO Products (ProductID, SupplierID, ProductName, Price)
VALUES ( 386, 1001, 'Titanium Extension Bracket Left Hand', 5.25 )
INSERT INTO Products (ProductID, SupplierID, ProductName, Price)
VALUES ( 387, 1001, 'Titanium Extension Bracket Right Hand', 5.25 )
VALUES ( 388, 1001, 'Fusion Generator Module 5 kV', 10.50 )
VALUES ( 389, 1001, 'Bypass Filter 400 MHz Low Pass', 10.50 )
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (10, 'Van Nuys', 'Catherine', 'Abel')
VALUES (20, 'Abercrombie', 'Kim', 'Branch')
VALUES (30, 'Contoso', 'Frances', 'Adams')
VALUES (40, 'A. Datum Corporation', 'Mark', 'Harrington')
VALUES (50, 'Adventure Works', 'Keith', 'Harris')
VALUES (60, 'Alpine Ski House', 'Wilson', 'Pais')
VALUES (70, 'Baldwin Museum of Science', 'Roger', 'Harui')
VALUES (80, 'Blue Yonder Airlines', 'Pilar', 'Pinilla')
VALUES (90, 'City Power & Light', 'Kari', 'Hensien')
VALUES (100, 'Coho Winery', 'Peter', 'Brehm')
DECLARE @orderId INT
DECLARE @customerId INT
SET @orderId = 10
SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Hensien' and FirstName = 'Kari'
INSERT INTO Orders (CustomerId, OrderId, OrderDate)
VALUES (@customerId, @orderId, GetDate())
INSERT INTO OrderItems (CustomerID, OrderID, ProductID, Quantity)
VALUES (@customerId, @orderId, 388, 4)
SET @orderId = 20
SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Harui' and FirstName = 'Roger'
VALUES (@customerId, @orderId, 389, 2)
SET @orderId = 30
SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Brehm' and FirstName = 'Peter'
VALUES (@customerId, @orderId, 387, 3)
SET @orderId = 40
SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Pais' and FirstName = 'Wilson'
VALUES (@customerId, @orderId, 388, 1)
$stmt2 = sqlsrv_query($conn, $sql2);
if($stmt2 === false)
echo "Data inserted. <br />";
The connection code for the above example is the same as in the Creating Federated Tables section.
Here is where the value of SQL Azure Federations really begins to show. Now that my first federation member is beginning to fill up with data, I can execute a query that will create a second federation member and move data from the first member to the second. In the example below, I move all data with CustId >= 60 to the second federation:
// Split federation member
$sql = "ALTER FEDERATION Orders_Federation SPLIT AT (CustID = 60)";
echo "Federation split!<br />";
The split takes a few minutes to complete. If you wait a few minutes after executing the query, then execute the query in the View Federation Members section above, you should see something like this:
As you can see, our federation now has two members. The first member contains data with CustId’s that go from the bottom of the INT range up to (but not including) 60, and the second member contains CustId’s >= 60. As our data grows, we can issue more split commands to federate our data across more members.
A logical question to ask after we’ve split a federation is “How do I insert data into my federated tables?” And the answer is fairly simple: Generate a new distribution key, connect to the appropriate federation member, and insert. Fortunately, connecting to the appropriate federation member is easy: just specify CustId = (new Id) in the USE FEDERATION query. So, adding a new customer, order and order item with CustId = 55 might look something like this:
$sql1 = "USE FEDERATION Orders_Federation (CustId = 55) WITH RESET, FILTERING = OFF;";
$params2 = array(55, 37);
$sql2 = "DECLARE @customerId INT
SET @customerId = ?
VALUES (@customerId, 'Microsoft', 'Swan', 'Brian')
SET @orderId = ?
VALUES (@customerId, @orderId, 389, 1)";
$stmt2 = sqlsrv_query($conn, $sql2, $params2);
To get that data back, see the Query a Federation Member with Filtering ON section below.
As you can see, choosing a federation distribution key that can be randomly generated is important to making sure that data is evenly distributed across federation members.
Note: The USE FEDERATION statement does not support the use of parameters.
To retrieve data, we (again) connect to a federation member. To determine which member we connect to, we specify a value for the federation distribution key. In the example below, CustId = 0 is specified in the USE FEDERATION query, so we are connected to the member that has the range containing CustId 0. Because FILTERING = OFF in the query, all data from the federation member is returned (i.e. the data is not filtered by the specified CustId). in this case, all data from the Customers table is returned from the specified federation member:
// Query federation member
$stmt1 = sqlsrv_query($conn, $sql1);
if($stmt1 === false)
$sql2 = "SELECT * FROM Customers";
echo "Data retrieved. <br />";
while($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC))
The connection code for this example is the same as in the Creating Federated Tablesexample.
In this example, I’ll connect to the second federation member by specifying a CustId that falls into the range for that member. I will also set FILTERING = ON when connecting to the federation member. This will filter the returned data by the specified CustId, so we’ll get back only the data related to CustId = 90. (If we set FILTERING = OFF, we’d get back all data from the federation member).
$connectionOptions = array("Database" => "SalesDB", "UID" => "USER@SERVERID", "PWD" => "PASSWORD", "MultipleActiveResultSets" => false, "ReturnDatesAsStrings" => true);
$sql1 = "USE FEDERATION Orders_Federation (CustId = 90) WITH RESET, FILTERING = ON;";
$sql2 = "SELECT * FROM Customers;
SELECT * FROM Orders;
SELECT * FROM OrderItems;";
while($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC))
Note that I changed the connection code slightly: ReturnDatesAsStrings is set to true in the $connectionOptions.
Here is the output:
One potential shortcoming of the current implementation of SQL Azure Federations is the lack of an easy way to scale back your federation members. As you can see, it is easy to add federation members and distribute data to them, but it is not currently easy to consolidate data into fewer federation members. The SQL Azure team is looking at ways to make scaling back as easy as it is to scale out.
Getting Started with PHP and SQL Azure
Introduction to Fan Out Queries
Transact-SQL Reference (SQL Azure Database)
That’s it for now...just an introduction to SQL Azure Federations, really. To get a deeper look, check out some of the Additional Resources above.
Share this on Twitter