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); }