Databases Reference
In-Depth Information
Figure 9-13.
Example application design implementing a partial shard
Managing Transaction Consistency
Because distributed transactions aren't supported in SQL Database, shard libraries for SQL Database don't offer
transactional consistency. But you should look carefully at your transactional needs and what this means to your
application design.
You can add transactional capabilities in the shard library described in this chapter by changing the
ExecuteShardNonQuery
and
ExecuteParallelRoundRobinLoad
methods. To do so, you need to add a separate
transaction context to all connection objects and commit them in a loop at the end of the last execution. If any
exception occurs, you must roll back all the changes.
■
as mentioned earlier, the shard library is an open-source project and is likely to evolve over time. Check for
the latest release to see which features are supported.
Note
Managing Foreign Key Constraints
Another interesting issue to consider in shard databases is related to foreign key constraints. You may quickly realize
that maintaining referential integrity can be challenging regardless of the sharding approach you use.
To maintain relational integrity, the following concerns apply:
•
Data duplication.
Because you don't know which records are where in the shard, the parent
tables need to be duplicated in every database. For example, a table that contains the list of
states (Florida, Illinois, and so on) may need to be replicated across all databases.
•
Identity values.
Adding records in one database can't be easily replicated across to other
databases. Thus, using an identity value as the primary key may be difficult because you aren't
guaranteed to have the same value in all databases in the shard. For example, the StateID
value for Florida may be 10 in one database and 11 in another.
