SQL Azure: Building a Shard (part 1) - Designing the Shard Library Object

Let's build a shard library that can be used by applications that need to load and update data against multiple SQL Azure databases as quickly and transparently as possible. For the purpose of building an efficient shard library, you stipulate the following requirements for the shard:

1. Adding new databases should be simple and transparent to the client code.

2. Adding new databases shouldn't affect performance negatively.

3. The library should function with SQL Server, SQL Azure, or both.

4. The library should optionally cache results for fast retrieval.

5. The library should support mass or selective reads and writes.

6. Data returned by the library should be accepted as a data source for controls.

These requirements have very specific implications from a technology standpoint. 

Table 2. Technologies Used to Build the Shard

Technology	Requirement	Comment
Configuration file	1	The configuration file stores the list of databases that make up the shard.
Multithreading	2	Using the TPL lets the library to spawn multiple threads to use computers with multiple CPUs, allowing parallel execution of SQL statements.
SqlClient	3	Using SqlCommand objects allows the shard to connect to both SQL Azure and SQL Server databases.
Caching	4	Caching lets the library store results temporarily to avoid unnecessary roundtrips.
Breadcrumbs	5	The library creates a virtual column for each record returned that stores a breadcrumb identifying the database a record it came from.
DataTable	6	The library returns a DataTable object that can be bound to objects easily.

1. Designing the Shard Library Object

The library accepts requests directly from client applications and can be viewed as an API. Note that you're using extension methods to make this API blend in with the existing SqlCommand class; this in turn minimizes the amount of code on the client and makes the application easier to read.

Figure 1 shows where the library fits in a typical application design. It also shows how the library hides the complexity of parallel processing and caching from the client application. Finally, the shard library abstracts the client code from dealing directly with multiple databases.

Figure 1. Shard library object diagram

A sample application is provided to demonstrate how to use the shard library. Although the application is very simple, it uses all the features of the shard for reference.

NOTE

Check http://EnzoSqlShard.Codeplex.com for the latest shard library. This shard library is made available as an open source project.