You are browsing a version that is no longer maintained. |
SQLAzure Sharding Tutorial
The sharding extension is currently in transition from a separate Project into DBAL. Class names may differ. |
This tutorial builds upon the Brian Swans tutorial on SQLAzure Sharding and turns all the examples into examples using the Doctrine Sharding support.
It introduces SQL Azure Sharding, which is an abstraction layer in SQL Azure to support sharding. Many features for sharding are implemented on the database level, which makes it much easier to work with than generic sharding implementations.
For this tutorial you need an Azure account. You don't need to deploy the code on Azure, you can run it from your own machine against the remote database.
You can look at the code from the 'examples/sharding' directory. |
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 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:
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.
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.
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.
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.