SQLAzure Sharding Tutorial

Install Doctrine

For this tutorial we will install Doctrine and the Sharding Extension through Composer which is the easiest way to install Doctrine. Composer is a new package manager for PHP. Download the composer.phar from their website and put it into a newly created folder for this tutorial. Now create a composer.json file in this project root with the following content:

{

"require": {

"doctrine/dbal": "2.2.2", "doctrine/shards": "0.2"

}

}

Open up the commandline and switch to your tutorial root directory, then call php composer.phar install. It will grab the code and install it into the vendor subdirectory of your project. It also creates an autoloader, so that we don't have to care about this.

Setup Connection

The first thing to start with is setting up Doctrine and the database connection:

1
<?php
// bootstrap.php
use Doctrine\DBAL\DriverManager;
use Doctrine\Shards\DBAL\SQLAzure\SQLAzureShardManager;

require_once "vendor/autoload.php";

$conn = DriverManager::getConnection(array(
    'driver'   => 'pdo_sqlsrv',
    'dbname'   => 'SalesDB',
    'host'     => 'tcp:dbname.windows.net',
    'user'     => 'user@dbname',
    'password' => 'XXX',
    'platform'       => new \Doctrine\DBAL\Platforms\SQLAzurePlatform(),
    'driverOptions'  => array('MultipleActiveResultSets' => false),
    'sharding' => array(
        'federationName'   => 'Orders_Federation',
        'distributionKey'  => 'CustId',
        'distributionType' => 'integer',
    )
));

$shardManager = new SQLAzureShardManager($conn);
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Create Database

Create a new database using the Azure/SQL Azure management console.

Create Schema

Doctrine has a powerful schema API. We don't need to use low-level DDL statements to generate the database schema. Instead you can use an Object-Oriented API to create the database schema and then have Doctrine turn it into DDL statements.

We will recreate Brians example schema with Doctrine DBAL. Instead of having to create federations and schema separately as in his example, Doctrine will do it all in one step:

1
<?php
// create_schema.php
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Shards\DBAL\SQLAzure\SQLAzureSchemaSynchronizer;

require_once 'bootstrap.php';

$schema = new Schema();

$products = $schema->createTable('Products');
$products->addColumn('ProductID', 'integer');
$products->addColumn('SupplierID', 'integer');
$products->addColumn('ProductName', 'string');
$products->addColumn('Price', 'decimal', array('scale' => 2, 'precision' => 12));
$products->setPrimaryKey(array('ProductID'));
$products->addOption('azure.federated', true);

$customers = $schema->createTable('Customers');
$customers->addColumn('CustomerID', 'integer');
$customers->addColumn('CompanyName', 'string');
$customers->addColumn('FirstName', 'string');
$customers->addColumn('LastName', 'string');
$customers->setPrimaryKey(array('CustomerID'));
$customers->addOption('azure.federated', true);
$customers->addOption('azure.federatedOnColumnName', 'CustomerID');

$orders = $schema->createTable('Orders');
$orders->addColumn('CustomerID', 'integer');
$orders->addColumn('OrderID', 'integer');
$orders->addColumn('OrderDate', 'datetime');
$orders->setPrimaryKey(array('CustomerID', 'OrderID'));
$orders->addOption('azure.federated', true);
$orders->addOption('azure.federatedOnColumnName', 'CustomerID');

$orderItems = $schema->createTable('OrderItems');
$orderItems->addColumn('CustomerID', 'integer');
$orderItems->addColumn('OrderID', 'integer');
$orderItems->addColumn('ProductID', 'integer');
$orderItems->addColumn('Quantity', 'integer');
$orderItems->setPrimaryKey(array('CustomerID', 'OrderID', 'ProductID'));
$orderItems->addOption('azure.federated', true);
$orderItems->addOption('azure.federatedOnColumnName', 'CustomerID');

// Create the Schema + Federation:
$synchronizer = new SQLAzureSchemaSynchronizer($conn, $shardManager);
$synchronizer->createSchema($schema);

// Or jut look at the SQL:
echo implode("\n", $synchronizer->getCreateSchema($schema));
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

View Federation Members

To see how many shard instances (called Federation Members) your SQLAzure database currently has you can ask the ShardManager to enumerate all shards:

1
<?php
// view_federation_members.php
require_once "bootstrap.php";

$shards = $shardManager->getShards();
foreach ($shards as $shard) {
    print_r($shard);
}
2
3
4
5
6
7
8

Insert Data

Now we want to insert some test data into the database to see the behavior when we split the shards. We use the same test data as Brian, but use the Doctrine API to insert them. To insert data into federated tables we have to select the shard we want to put the data into. We can use the ShardManager to execute this operation for us:

1
<?php
// insert_data.php
require_once "bootstrap.php";

$shardManager->selectShard(0);

$conn->insert("Products", array(
    "ProductID" => 386,
    "SupplierID" => 1001,
    "ProductName" => 'Titanium Extension Bracket Left Hand',
    "Price" => 5.25,
));
$conn->insert("Products", array(
    "ProductID" => 387,
    "SupplierID" => 1001,
    "ProductName" => 'Titanium Extension Bracket Right Hand',
    "Price" => 5.25,
));
$conn->insert("Products", array(
    "ProductID" => 388,
    "SupplierID" => 1001,
    "ProductName" => 'Fusion Generator Module 5 kV',
    "Price" => 10.50,
));
$conn->insert("Products", array(
    "ProductID" => 388,
    "SupplierID" => 1001,
    "ProductName" => 'Bypass Filter 400 MHz Low Pass',
    "Price" => 10.50,
));

$conn->insert("Customers", array(
    'CustomerID' => 10,
    'CompanyName' => 'Van Nuys',
    'FirstName' => 'Catherine',
    'LastName' => 'Abel',
));
$conn->insert("Customers", array(
    'CustomerID' => 20,
    'CompanyName' => 'Abercrombie',
    'FirstName' => 'Kim',
    'LastName' => 'Branch',
));
$conn->insert("Customers", array(
    'CustomerID' => 30,
    'CompanyName' => 'Contoso',
    'FirstName' => 'Frances',
    'LastName' => 'Adams',
));
$conn->insert("Customers", array(
    'CustomerID' => 40,
    'CompanyName' => 'A. Datum Corporation',
    'FirstName' => 'Mark',
    'LastName' => 'Harrington',
));
$conn->insert("Customers", array(
    'CustomerID' => 50,
    'CompanyName' => 'Adventure Works',
    'FirstName' => 'Keith',
    'LastName' => 'Harris',
));
$conn->insert("Customers", array(
    'CustomerID' => 60,
    'CompanyName' => 'Alpine Ski House',
    'FirstName' => 'Wilson',
    'LastName' => 'Pais',
));
$conn->insert("Customers", array(
    'CustomerID' => 70,
    'CompanyName' => 'Baldwin Museum of Science',
    'FirstName' => 'Roger',
    'LastName' => 'Harui',
));
$conn->insert("Customers", array(
    'CustomerID' => 80,
    'CompanyName' => 'Blue Yonder Airlines',
    'FirstName' => 'Pilar',
    'LastName' => 'Pinilla',
));
$conn->insert("Customers", array(
    'CustomerID' => 90,
    'CompanyName' => 'City Power & Light',
    'FirstName' => 'Kari',
    'LastName' => 'Hensien',
));
$conn->insert("Customers", array(
    'CustomerID' => 100,
    'CompanyName' => 'Coho Winery',
    'FirstName' => 'Peter',
    'LastName' => 'Brehm',
));

$conn->executeUpdate("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)"
);
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132

This puts the data into the currently only existing federation member. We selected that federation member by picking 0 as distribution value, which is by definition part of the only existing federation.

Split Federation

Now lets split the federation, creating a second federation member. SQL Azure will automatically redistribute the data into the two federations after you executed this command.

1
<?php
// split_federation.php
require_once 'bootstrap.php';

$shardManager->splitFederation(60);
2
3
4
5

This little script uses the shard manager with a special method only existing on the SQL AZure implementation splitFederation. It accepts a value at at which the split is executed.

If you reexecute the view_federation_members.php script you can now see that there are two federation members instead of just one as before. You can see with the rangeLow and rangeHigh parameters what customers and related entries are now served by which federation.

Inserting Data after Split

Now after we splitted the data we now have to make sure to be connected to the right federation before inserting data. Lets add a new customer with ID 55 and have him create an order.

1
<?php
// insert_data_aftersplit.php
require_once 'bootstrap.php';

$newCustomerId = 55;

$shardManager->selectShard($newCustomerId);

$conn->insert("Customers", array(
    "CustomerID" => $newCustomerId,
    "CompanyName" => "Microsoft",
    "FirstName" => "Brian",
    "LastName" => "Swan",
));

$conn->insert("Orders", array(
    "CustomerID" => 55,
    "OrderID" => 37,
    "OrderDate" => date('Y-m-d H:i:s'),
));

$conn->insert("OrderItems", array(
    "CustomerID" => 55,
    "OrderID" => 37,
    "ProductID" => 387,
    "Quantity" => 1,
));
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

As you can see its very important to pick the right distribution key in your sharded application. Otherwise you have to switch the shards very often, which is not really easy to work with. If you pick the sharding key right then it should be possible to select the shard only once per request for the major number of use-cases.

Fan-out the queries accross multiple shards should only be necessary for a small number of queries, because these kind of queries are complex.

Querying data with filtering off

To access the data you have to pick a shard again and then start selecting data from it.

1
<?php
// query_filtering_off.php
require_once "bootstrap.php";

$shardManager->selectShard(0);

$data = $conn->fetchAll('SELECT * FROM Customers');
print_r($data);
2
3
4
5
6
7
8

This returns all customers from the shard with distribution value 0. This will be all customers with id 10 to less than 60, since we split federations at 60.

Querying data with filtering on

One special feature of SQL Azure is the possibility to database level filtering based on the sharding distribution values. This means that SQL Azure will add WHERE clauses with distributionkey=current distribution value conditions to each distribution key.

1
<?php
// query_filtering_on.php
require_once "bootstrap.php";

$shardManager->setFilteringEnabled(true);
$shardManager->selectShard(55);

$data = $conn->fetchAll('SELECT * FROM Customers');
print_r($data);
2
3
4
5
6
7
8
9

Now you only get the customer with id = 55. The same holds for queries on the Orders and OrderItems table, which are restricted by customer id = 55.