Edit

Share via

Adding a shard using Elastic Database tools

  • Article

Applies to: Azure SQL Database

To add a shard for a new range or key

Applications often need to add new shards to handle data that is expected from new keys or key ranges, for a shard map that already exists. For example, an application sharded by Tenant ID may need to create a new shard for a new tenant, or data sharded monthly may need a new shard provisioned before the start of each new month.

If the new range of key values isn't already part of an existing mapping, you can add the new shard and associate the new key or range to that shard.

Example: adding a shard and its range to an existing shard map

This sample uses the TryGetShard (Java, .NET) the CreateShard (Java, .NET), CreateRangeMapping (Java, .NET methods, and creates an instance of the ShardLocation (Java, .NET) class. In the sample below, a database named sample_shard_2 and all necessary schema objects inside of it have been created to hold range [300, 400).

// sm is a RangeShardMap object.
// Add a new shard to hold the range being added.
Shard shard2 = null;

if (!sm.TryGetShard(new ShardLocation(shardServer, "sample_shard_2"),out shard2))
{
    shard2 = sm.CreateShard(new ShardLocation(shardServer, "sample_shard_2"));  
}

// Create the mapping and associate it with the new shard
sm.CreateRangeMapping(new RangeMappingCreationInfo<long>
                            (new Range<long>(300, 400), shard2, MappingStatus.Online));

To add a shard for an empty part of an existing range

In some circumstances, you may have already mapped a range to a shard and partially filled it with data, but you now want upcoming data to be directed to a different shard. For example, you can shard by day range and have already allocated 50 days to a shard, but on day 24, you want future data to land in a different shard. The elastic database split-merge tool can perform this operation, but if data movement isn't necessary (for example, data for the range of days [25, 50), that is, day 25 inclusive to 50 exclusive, doesn't yet exist) you can perform this entirely using the Shard Map Management APIs directly.

Example: splitting a range and assigning the empty portion to a newly added shard

A database named "sample_shard_2" and all necessary schema objects inside of it have been created.

// sm is a RangeShardMap object.
// Add a new shard to hold the range we will move
Shard shard2 = null;

if (!sm.TryGetShard(new ShardLocation(shardServer, "sample_shard_2"),out shard2))
{
    shard2 = sm.CreateShard(new ShardLocation(shardServer,"sample_shard_2"));  
}

// Split the Range holding Key 25
sm.SplitMapping(sm.GetMappingForKey(25), 25);

// Map new range holding [25-50) to different shard:
// first take existing mapping offline
sm.MarkMappingOffline(sm.GetMappingForKey(25));

// now map while offline to a different shard and take online
RangeMappingUpdate upd = new RangeMappingUpdate();
upd.Shard = shard2;
sm.MarkMappingOnline(sm.UpdateMapping(sm.GetMappingForKey(25), upd));

Important

Use this technique only if you are certain that the range for the updated mapping is empty. The preceding methods do not check data for the range being moved, so it is best to include checks in your code. If rows exist in the range being moved, the actual data distribution will not match the updated shard map. Use the split-merge tool to perform the operation instead in these cases.

Related content

Not using elastic database tools yet? Check out our Getting Started Guide. For questions, contact us on the Microsoft Q&A question page for SQL Database and for feature requests, add new ideas or vote for existing ideas in the SQL Database feedback forum.