Using SQL Azure Federations via PHP
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: https://www.microsoft.com/download/en/details.aspx?id=20098. The documentation for the drivers is here: https://msdn.microsoft.com/en-us/library/ee229547(SQL.10).aspx.
Create a SQL Azure Server
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.
Create a Database
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)
die(print_r(sqlsrv_errors(), true));
else
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)
die(print_r(sqlsrv_errors(), true));
else
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:
Create a Federation
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:
$serverName = "tcp:SERVERID.database.windows.net, 1433";
$connectionOptions = array("Database" => "SalesDB", "UID" => "USER@SERVERID", "PWD" => "PASSWORD");
$conn = sqlsrv_connect($serverName, $connectionOptions);
if($conn === false)
die(print_r(sqlsrv_errors(), true));
else
echo "Connected via sqlsrv!<br />";
// Create federation Orders_Federation (be sure to connect to SalesDB)
$sql = "CREATE FEDERATION Orders_Federation (CustId INT RANGE)";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
die(print_r(sqlsrv_errors()));
else
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.
View Federation Members
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,
member_id,
distribution_name,
CAST(range_low AS INT) AS range_low,
CAST(range_high AS INT) AS range_high
FROM sys.federation_member_distributions";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
die(print_r(sqlsrv_errors()));
else
{
echo "Federation members retrieved!<br />";
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
{
echo "<pre>";
print_r($row);
echo "</pre>";
}
}
This will become more interesting when we split a federation (details below). I’ll come back to this later.
Create Federated Tables
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:
$serverName = "tcp:SERVERID.database.windows.net, 1433";
$connectionOptions = array("Database" => "SalesDB", "UID" => "USER@SERVERID", "PWD" => "PASSWORD", "MultipleActiveResultSets" => false);
$conn = sqlsrv_connect($serverName, $connectionOptions);
if($conn === false)
die(print_r(sqlsrv_errors(), true));
else
echo "Connected via sqlsrv!<br />";
// Create tables
$sql1 = "USE FEDERATION Orders_Federation (CustId = 0) WITH RESET, FILTERING = OFF;";
$stmt = sqlsrv_query($conn, $sql1);
if($stmt === false)
die(print_r(sqlsrv_errors()));
else
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,
FirstName nvarchar(50),
LastName nvarchar(50),
PRIMARY KEY (CustomerId) )
FEDERATED ON (CustId = CustomerID)
-- Create Orders table
create table Orders
(
CustomerId int NOT NULL,
OrderId int NOT NULL,
OrderDate datetime,
PRIMARY KEY (OrderId, CustomerId)
)
FEDERATED ON (CustId = CustomerId)
-- Create OrderItems table
CREATE TABLE OrderItems(
CustomerID int NOT NULL,
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
PRIMARY KEY (OrderId, CustomerId, ProductId) )
FEDERATED ON (CustId = CustomerId)";
$stmt = sqlsrv_query($conn, $sql2);
if($stmt === false)
die(print_r(sqlsrv_errors()));
else
echo "Tables created!<br />";
Notes on the code snippet above:
- Note that in the $connectionOptions array MultipleActiveResultSets is turned off. This is necessary to execute the query.
- Two queries are executed. The first one connects to our federation and the second one creates the tables. Trying to execute both of these queries as a single batch will result in an error (USE FEDERATION cannot be part of a batch query).
- The CustId = 0 in the USE FEDERATION query connects us to the federation member containing CustId = 0. This is only syntactically necessary here.
- Because the Products table is created without the FEDERATED ON clause, it is created in the root database and not in the federation members. (In this example, I expect my other tables to grow, hence they are federated. I don’t expect my products to grow so fast as to require federation).
Insert Data
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.)
$sql1 = "USE FEDERATION Orders_Federation (CustId = 0) WITH RESET, FILTERING = OFF;";
$stmt = sqlsrv_query($conn, $sql1);
if($stmt === false)
die(print_r(sqlsrv_errors()));
else
echo "Connected to Orders_Federation!<br />";
$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 )
INSERT INTO Products (ProductID, SupplierID, ProductName, Price)
VALUES ( 388, 1001, 'Fusion Generator Module 5 kV', 10.50 )
INSERT INTO Products (ProductID, SupplierID, ProductName, Price)
VALUES ( 389, 1001, 'Bypass Filter 400 MHz Low Pass', 10.50 )
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (10, 'Van Nuys', 'Catherine', 'Abel')
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (20, 'Abercrombie', 'Kim', 'Branch')
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (30, 'Contoso', 'Frances', 'Adams')
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (40, 'A. Datum Corporation', 'Mark', 'Harrington')
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (50, 'Adventure Works', 'Keith', 'Harris')
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (60, 'Alpine Ski House', 'Wilson', 'Pais')
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (70, 'Baldwin Museum of Science', 'Roger', 'Harui')
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (80, 'Blue Yonder Airlines', 'Pilar', 'Pinilla')
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (90, 'City Power & Light', 'Kari', 'Hensien')
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
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'
INSERT INTO Orders (CustomerId, OrderId, OrderDate)
VALUES (@customerId, @orderId, GetDate())
INSERT INTO OrderItems (CustomerID, OrderID, ProductID, Quantity)
VALUES (@customerId, @orderId, 389, 2)
SET @orderId = 30
SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Brehm' and FirstName = 'Peter'
INSERT INTO Orders (CustomerId, OrderId, OrderDate)
VALUES (@customerId, @orderId, GetDate())
INSERT INTO OrderItems (CustomerID, OrderID, ProductID, Quantity)
VALUES (@customerId, @orderId, 387, 3)
SET @orderId = 40
SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Pais' and FirstName = 'Wilson'
INSERT INTO Orders (CustomerId, OrderId, OrderDate)
VALUES (@customerId, @orderId, GetDate())
INSERT INTO OrderItems (CustomerID, OrderID, ProductID, Quantity)
VALUES (@customerId, @orderId, 388, 1)
";
$stmt2 = sqlsrv_query($conn, $sql2);
if($stmt2 === false)
die(print_r(sqlsrv_errors()));
else
echo "Data inserted. <br />";
The connection code for the above example is the same as in the Creating Federated Tables section.
Split a Federation
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)";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
die(print_r(sqlsrv_errors()));
else
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.
Inserting Data After a Split
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;";
$stmt = sqlsrv_query($conn, $sql1);
if($stmt === false)
die(print_r(sqlsrv_errors()));
else
echo "Connected to Orders_Federation!<br />";
$params2 = array(55, 37);
$sql2 = "DECLARE @customerId INT
SET @customerId = ?
INSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)
VALUES (@customerId, 'Microsoft', 'Swan', 'Brian')
DECLARE @orderId INT
SET @orderId = ?
INSERT INTO Orders (CustomerId, OrderId, OrderDate)
VALUES (@customerId, @orderId, GetDate())
INSERT INTO OrderItems (CustomerID, OrderID, ProductID, Quantity)
VALUES (@customerId, @orderId, 389, 1)";
$stmt2 = sqlsrv_query($conn, $sql2, $params2);
if($stmt2 === false)
die(print_r(sqlsrv_errors()));
else
echo "Data inserted. <br />";
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.
Query a Federation Member with Filtering OFF
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
$sql1 = "USE FEDERATION Orders_Federation (CustId = 0) WITH RESET, FILTERING = OFF;";
$stmt1 = sqlsrv_query($conn, $sql1);
if($stmt1 === false)
die(print_r(sqlsrv_errors()));
else
echo "Connected to Orders_Federation!<br />";
$sql2 = "SELECT * FROM Customers";
$stmt2 = sqlsrv_query($conn, $sql2);
if($stmt2 === false)
die(print_r(sqlsrv_errors()));
else
echo "Data retrieved. <br />";
while($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC))
{
echo "<pre>";
print_r($row);
echo "</pre>";
}
The connection code for this example is the same as in the Creating Federated Tablesexample.
Query a Federation Member with Filtering ON
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).
$serverName = "tcp:SERVERID.database.windows.net, 1433";
$connectionOptions = array("Database" => "SalesDB", "UID" => "USER@SERVERID", "PWD" => "PASSWORD", "MultipleActiveResultSets" => false, "ReturnDatesAsStrings" => true);
$conn = sqlsrv_connect($serverName, $connectionOptions);
if($conn === false)
die(print_r(sqlsrv_errors(), true));
else
echo "Connected via sqlsrv!<br />";
$sql1 = "USE FEDERATION Orders_Federation (CustId = 90) WITH RESET, FILTERING = ON;";
$stmt1 = sqlsrv_query($conn, $sql1);
if($stmt1 === false)
die(print_r(sqlsrv_errors()));
else
echo "Connected to Orders_Federation!<br />";
$sql2 = "SELECT * FROM Customers;
SELECT * FROM Orders;
SELECT * FROM OrderItems;";
$stmt2 = sqlsrv_query($conn, $sql2);
if($stmt2 === false)
die(print_r(sqlsrv_errors()));
else
echo "Data retrieved. <br />";
do{
while($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC))
{
echo "<pre>";
print_r($row);
echo "</pre>";
}
}while(sqlsrv_next_result($stmt2));
Note that I changed the connection code slightly: ReturnDatesAsStrings is set to true in the $connectionOptions.
Here is the output:
Additional Notes
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.
Additional Resources
Getting Started with PHP and SQL Azure
Introduction to Fan Out Queries
https://blogs.msdn.com/b/cbiyikoglu/
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.
Thanks.
-Brian