Databases Reference
In-Depth Information
•
Shard definition.
A list of SQL Database instances created in a server in Azure. The consumer
application can automatically detect which databases are part of the shard by connecting to
the master database. If all databases created are part of the shard, enumerating the records in
sys.databases
give you all the databases in the shard.
•
Breadcrumbs.
A technique that leaves a small trace that can be used downstream for
improved decisions. In this context, breadcrumbs can be added to datasets to indicate which
database a record came from. This helps in determining which database to connect to in order
to update a record and avoids spreading requests to all databases.
When using a shard, a consumer typically issues CRUD (create, read, update, and delete) operations. Each
operation has unique properties depending on the approach chosen. Table
2-2
outlines some possible combinations
of techniques to help you decide which sharding method is best for you. The left column describes the connection
mechanism used by the shard, and the top row identifies the shard's storage mechanism.
Table 2-2.
Shard access techniques
Horizontal partitions
Mirror
Decision rules
Rules determine how equally records are
spread in the shard.
Create
: Apply rules.
Read
: Connect to all databases with rules included
as part of a
WHERE
clause, or choose a database
based on the rules. Add breadcrumbs for
update and delete operations.
Update
: Apply rules or use breadcrumbs,
and possibly move records to another database
if the column updated is part of the rule.
Delete
: Apply rules, or use breadcrumbs
when possible.
This combination doesn't seem to
provide a benefit. Mirrored databases
aren't partitioned, and so no rule
exists to find a record.
Round-robin
Records are placed randomly in databases based
on the available connection. No logic can be
applied to determine which database
contains which records.
All records are copied to all databases.
Use a single database (called the
primary database
) for writes.
Create
: Insert a record in the primary
database only.
Create
: Insert a record in the current database.
Read
: Connect to all databases,
issue statements, and concatenate result sets.
Add breadcrumbs for update and delete operations.
Update
: Connect to all databases
(or use breadcrumbs),
and apply updates using a primary key.
Delete
: Same as update.
Read
: Connect to any database in a
round-robin fashion.
Update
: Update a record in the
primary database only.
Delete
: Delete a record in the primary
database only.
Shards can be very difficult to implement. Make sure you test thoroughly when implementing shards. You can
also look at some of the shard libraries that have been developed. The shard library found on CodePlex and explained
further in this topic (in Chapter 9) uses .NET 4.0; you can find it with its source code at
http://enzosqlshard.codeplex.com
.
