Navigation

The sharding extension is currently in transition from a seperate 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.

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
phpcomposer.pharinstall. 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.

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 seperately as in his example, Doctrine will do it
all in one step:

<?php// create_schema.phpuseDoctrine\DBAL\Schema\Schema;useDoctrine\Shards\DBAL\SQLAzure\SQLAzureSchemaSynchronizer;require_once'bootstrap.php';$schema=newSchema();$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=newSQLAzureSchemaSynchronizer($conn,$shardManager);$synchronizer->createSchema($schema);// Or jut look at the SQL:echoimplode("\n",$synchronizer->getCreateSchema($schema));

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:

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.

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.

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.

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.