Could not find stored procedure '__ShardManagement.spBulkOperationShardMappingsLocal'

Question

I am using azure Elastic database pattern with Entity Framework Code First.

When I create a new Shard Database the follow exception is thrown when I try to create the PointShard map

     // Register the mapping of the tenant to the shard in the shard map.
        // After this step, DDR on the shard map can be used
        PointMapping<Guid> mapping;
        if (!ShardMap.TryGetMappingForKey(key, out mapping))
        {
            ShardMap.CreatePointMapping(key, shard);
        }

Could not find stored procedure '__ShardManagement.spBulkOperationShardMappingsLocal'

The EF schema is being created but the sharding schema and stored procs are not being generated.

How can I ensure that the sharding components of my DbContext are generated?

score 0 · Answer 1 · answered Nov 15 '16 at 00:01

Before deleting a shard database, you should remove it from the shard map manager using ShardMap.DeleteShard. This is the safe way to delete because before allowing the shard to be deleted, the shard map manager will check that there are no mappings to the shard (and if there are no mappings to the shard, then hopefully that shard should be empty of sharded data - although that last part is your responsibility, shard map manager cannot verify that for you).

At this point since you already deleted the database, you can instead remove the shard db from the shard map manager using RecoveryManager.DetachShard. This is normally dangerous to do, since all mappings to that shard will deleted, which is why DeleteShard is preferred.

So then if you create a new database with the same name, you need to call ShardMap.CreateShard to create sharding schemas on that shard db and add that shard db to the shard map manager. However you didn't call this, so the sharding schema wasn't created. (Note that CreateShard will fail if you didn't call DeleteShard or DetachShard earlier.)

score 0 · Answer 2 · answered Nov 15 '16 at 00:31

After creating the shard database you have to call CreateShard to create the tables and stored procedures to support sharding. Here's a snippet of code from our shard creation class. Once the database is created we execute it.

var shardMapManagerConnectionString = ConfigurationManager.ConnectionStrings["CommonConnectionString"].ConnectionString;
var shardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(shardMapManagerConnectionString, ShardMapManagerLoadPolicy.Lazy);
var shardMap = shardMapManager.GetListShardMap<Guid>("shardMapName");

var shardLocation = new ShardLocation(shardServerName, shardName);

var shard = shardMap.CreateShard(shardLocation);

After calling CreateShard you can then call CreatePointMapping.

Could not find stored procedure '__ShardManagement.spBulkOperationShardMappingsLocal'

2 Answers2