public SqlConnection GetConnection(TTenantIdentity tenant)
{
var connection = _shardMap.OpenConnectionForKey(tenant, _config.ConnectionCredentials);
// Set TenantId in SESSION_CONTEXT to shardingKey to enable Row-Level Security filtering
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = @"exec sp_set_session_context @key=N'TenantId', @value=@shardingKey";
cmd.Parameters.AddWithValue("@shardingKey", tenant);
cmd.ExecuteNonQuery();
}
return(connection);
}
public void BasicScenarioListShardMaps()
{
bool success = true;
string shardMapName = "PerTenantShardMap";
try
{
#region DeployShardMapManager
// Deploy shard map manager.
ShardMapManagerFactory.CreateSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerCreateMode.ReplaceExisting);
#endregion DeployShardMapManager
#region GetShardMapManager
// Obtain shard map manager.
ShardMapManager shardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
#endregion GetShardMapManager
#region CreateListShardMap
// Create a single user per-tenant shard map.
ListShardMap <int> perTenantShardMap = shardMapManager.CreateListShardMap <int>(shardMapName);
#endregion CreateListShardMap
#region CreateShardAndPointMapping
for (int i = 0; i < ScenarioTests.s_perTenantDBs.Length; i++)
{
// Create the shard.
Shard s = perTenantShardMap.CreateShard(
new ShardLocation(
Globals.ShardMapManagerTestsDatasourceName,
ScenarioTests.s_perTenantDBs[i]));
// Create the mapping.
PointMapping <int> p = perTenantShardMap.CreatePointMapping(
i + 1,
s);
}
#endregion CreateShardAndPointMapping
#region UpdatePointMapping
// Let's add another point 5 and map it to same shard as 1.
PointMapping <int> mappingForOne = perTenantShardMap.GetMappingForKey(1);
PointMapping <int> mappingForFive = perTenantShardMap.CreatePointMapping(5, mappingForOne.Shard);
Assert.IsTrue(mappingForOne.Shard.Location.Equals(mappingForFive.Shard.Location));
// Move 3 from PerTenantDB3 to PerTenantDB for 5.
PointMapping <int> mappingToUpdate = perTenantShardMap.GetMappingForKey(3);
bool updateFailed = false;
// Try updating that shard in the mapping without taking it offline first.
try
{
perTenantShardMap.UpdateMapping(
mappingToUpdate,
new PointMappingUpdate
{
Shard = mappingForFive.Shard
});
}
catch (ShardManagementException smme)
{
Assert.AreEqual(smme.ErrorCode, ShardManagementErrorCode.MappingIsNotOffline);
updateFailed = true;
}
Assert.IsTrue(updateFailed);
// Perform the actual update.
PointMapping <int> newMappingFor3 = MarkMappingOfflineAndUpdateShard <int>(
perTenantShardMap, mappingToUpdate, mappingForFive.Shard);
// Verify that update succeeded.
Assert.IsTrue(newMappingFor3.Shard.Location.Equals(mappingForFive.Shard.Location));
Assert.IsTrue(newMappingFor3.Status == MappingStatus.Offline);
// Update custom field for the updated mapping.
//PointMapping<int> veryNewMappingFor3 = perTenantShardMap.UpdatePointMapping(
// newMappingFor3,
// new PointMappingUpdate
// {
// Custom = new byte[] { 0x12, 0x34 }
// });
#endregion UpdatePointMapping
#region DeleteMapping
// Find the shard by location.
PointMapping <int> mappingToDelete = perTenantShardMap.GetMappingForKey(5);
bool operationFailed = false;
// Try to delete mapping while it is online, the delete should fail.
try
{
perTenantShardMap.DeleteMapping(mappingToDelete);
}
catch (ShardManagementException smme)
{
operationFailed = true;
Assert.AreEqual(smme.ErrorCode, ShardManagementErrorCode.MappingIsNotOffline);
}
Trace.Assert(operationFailed);
// The mapping must be taken offline first before it can be deleted.
mappingToDelete = perTenantShardMap.UpdateMapping(
mappingToDelete,
new PointMappingUpdate
{
Status = MappingStatus.Offline,
});
perTenantShardMap.DeleteMapping(mappingToDelete);
// Verify that delete succeeded.
try
{
PointMapping <int> deletedMapping = perTenantShardMap.GetMappingForKey(5);
}
catch (ShardManagementException smme)
{
Assert.AreEqual(smme.ErrorCode, ShardManagementErrorCode.MappingNotFoundForKey);
}
#endregion DeleteMapping
#region OpenConnection without Validation
using (SqlConnection conn = perTenantShardMap.OpenConnectionForKey(
2,
Globals.ShardUserConnectionString,
ConnectionOptions.None))
{
}
#endregion OpenConnection without Validation
#region OpenConnection with Validation
// Use the stale state of "shardToUpdate" shard & see if validation works.
bool validationFailed = false;
try
{
using (SqlConnection conn = perTenantShardMap.OpenConnection(
mappingToDelete,
Globals.ShardUserConnectionString,
ConnectionOptions.Validate))
{
}
}
catch (ShardManagementException smme)
{
validationFailed = true;
Assert.AreEqual(smme.ErrorCode, ShardManagementErrorCode.MappingDoesNotExist);
}
Assert.AreEqual(validationFailed, true);
#endregion OpenConnection with Validation
#region OpenConnection without Validation and Empty Cache
// Obtain a new ShardMapManager instance
ShardMapManager newShardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
// Get the ShardMap
ListShardMap <int> newPerTenantShardMap = newShardMapManager.GetListShardMap <int>(shardMapName);
using (SqlConnection conn = newPerTenantShardMap.OpenConnectionForKey(
2,
Globals.ShardUserConnectionString,
ConnectionOptions.None))
{
}
#endregion
#region OpenConnection with Validation and Empty Cache
// Use the stale state of "shardToUpdate" shard & see if validation works.
validationFailed = false;
// Obtain a new ShardMapManager instance
newShardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
// Get the ShardMap
newPerTenantShardMap = newShardMapManager.GetListShardMap <int>(shardMapName);
// Create a new mapping
PointMapping <int> newMappingToDelete = newPerTenantShardMap.CreatePointMapping(6,
newPerTenantShardMap.GetMappingForKey(1).Shard);
// Delete the mapping
newMappingToDelete = newPerTenantShardMap.UpdateMapping(
newMappingToDelete,
new PointMappingUpdate
{
Status = MappingStatus.Offline,
});
newPerTenantShardMap.DeleteMapping(newMappingToDelete);
try
{
using (SqlConnection conn = newPerTenantShardMap.OpenConnection(
newMappingToDelete,
Globals.ShardUserConnectionString,
ConnectionOptions.Validate))
{
}
}
catch (ShardManagementException smme)
{
validationFailed = true;
Assert.AreEqual(smme.ErrorCode, ShardManagementErrorCode.MappingDoesNotExist);
}
Assert.AreEqual(validationFailed, true);
#endregion
#region OpenConnectionAsync without Validation
using (SqlConnection conn = perTenantShardMap.OpenConnectionForKeyAsync(
2,
Globals.ShardUserConnectionString,
ConnectionOptions.None).Result)
{
}
#endregion
#region OpenConnectionAsync with Validation
// Use the stale state of "shardToUpdate" shard & see if validation works.
validationFailed = false;
try
{
using (SqlConnection conn = perTenantShardMap.OpenConnectionAsync(
mappingToDelete,
Globals.ShardUserConnectionString,
ConnectionOptions.Validate).Result)
{
}
}
catch (AggregateException ex)
{
ShardManagementException smme = ex.InnerException as ShardManagementException;
if (smme != null)
{
validationFailed = true;
Assert.AreEqual(smme.ErrorCode, ShardManagementErrorCode.MappingDoesNotExist);
}
}
Assert.AreEqual(validationFailed, true);
#endregion
#region OpenConnectionAsync without Validation and Empty Cache
// Obtain a new ShardMapManager instance
newShardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
// Get the ShardMap
newPerTenantShardMap = newShardMapManager.GetListShardMap <int>(shardMapName);
using (SqlConnection conn = newPerTenantShardMap.OpenConnectionForKeyAsync(
2,
Globals.ShardUserConnectionString,
ConnectionOptions.None).Result)
{
}
#endregion
#region OpenConnectionAsync with Validation and Empty Cache
// Use the stale state of "shardToUpdate" shard & see if validation works.
validationFailed = false;
// Obtain a new ShardMapManager instance
newShardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
// Get the ShardMap
newPerTenantShardMap = newShardMapManager.GetListShardMap <int>(shardMapName);
// Create a new mapping
newMappingToDelete = newPerTenantShardMap.CreatePointMapping(6,
newPerTenantShardMap.GetMappingForKey(1).Shard);
// Delete the mapping
newMappingToDelete = newPerTenantShardMap.UpdateMapping(
newMappingToDelete,
new PointMappingUpdate
{
Status = MappingStatus.Offline,
});
newPerTenantShardMap.DeleteMapping(newMappingToDelete);
try
{
using (SqlConnection conn = newPerTenantShardMap.OpenConnectionAsync(
newMappingToDelete,
Globals.ShardUserConnectionString,
ConnectionOptions.Validate).Result)
{
}
}
catch (AggregateException ex)
{
ShardManagementException smme = ex.InnerException as ShardManagementException;
if (smme != null)
{
validationFailed = true;
Assert.AreEqual(smme.ErrorCode, ShardManagementErrorCode.MappingDoesNotExist);
}
}
Assert.AreEqual(validationFailed, true);
#endregion
#region LookupPointMapping
// Perform tenant lookup. This will populate the cache.
for (int i = 0; i < ScenarioTests.s_perTenantDBs.Length; i++)
{
PointMapping <int> result = shardMapManager
.GetListShardMap <int>("PerTenantShardMap")
.GetMappingForKey(i + 1);
Trace.WriteLine(result.Shard.Location);
// Since we moved 3 to database 1 earlier.
Assert.IsTrue(result.Shard.Location.Database == ScenarioTests.s_perTenantDBs[i != 2 ? i : 0]);
}
// Perform tenant lookup. This will read from the cache.
for (int i = 0; i < ScenarioTests.s_perTenantDBs.Length; i++)
{
PointMapping <int> result = shardMapManager
.GetListShardMap <int>("PerTenantShardMap")
.GetMappingForKey(i + 1);
Trace.WriteLine(result.Shard.Location);
// Since we moved 3 to database 1 earlier.
Assert.IsTrue(result.Shard.Location.Database == ScenarioTests.s_perTenantDBs[i != 2 ? i : 0]);
}
#endregion LookupPointMapping
}
catch (ShardManagementException smme)
{
success = false;
Trace.WriteLine(String.Format("Error Category: {0}", smme.ErrorCategory));
Trace.WriteLine(String.Format("Error Code : {0}", smme.ErrorCode));
Trace.WriteLine(String.Format("Error Message : {0}", smme.Message));
if (smme.InnerException != null)
{
Trace.WriteLine(String.Format("Storage Error Message : {0}", smme.InnerException.Message));
if (smme.InnerException.InnerException != null)
{
Trace.WriteLine(String.Format("SqlClient Error Message : {0}", smme.InnerException.InnerException.Message));
}
}
}
Assert.IsTrue(success);
}