// Bootstrap Elastic Scale by creating a new shard map manager and a shard map on // the shard map manager database if necessary. public Sharding(string smmserver, string smmdatabase, string smmconnstr) { // Connection string with administrative credentials for the root database SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder(smmconnstr); connStrBldr.DataSource = smmserver; connStrBldr.InitialCatalog = smmdatabase; // Deploy shard map manager. ShardMapManager smm; if (!ShardMapManagerFactory.TryGetSqlShardMapManager(connStrBldr.ConnectionString, ShardMapManagerLoadPolicy.Lazy, out smm)) { this.ShardMapManager = ShardMapManagerFactory.CreateSqlShardMapManager(connStrBldr.ConnectionString); } else { this.ShardMapManager = smm; } ListShardMap<int> sm; if (!ShardMapManager.TryGetListShardMap<int>("ElasticScaleWithDapper", out sm)) { this.ShardMap = ShardMapManager.CreateListShardMap<int>("ElasticScaleWithDapper"); } else { this.ShardMap = sm; } }
private bool RefreshShardListMap(List<Tuple<long, string>> ListMappings = null) { try { string shardServer1 = WingtipTicketApp.Config.PrimaryDatabaseServer; string shardServer2 = WingtipTicketApp.Config.ShardDatabaseServer; string ticketsDbName = WingtipTicketApp.Config.TicketsDbName; Shard shard1 = null, shard2 = null; PointMapping<long> lmpg; // check if shard map manager exists and if not, create it (Idempotent / tolerant of re-execute) if (!ShardMapManager.TryGetListShardMap(mapManagerName, out lsm)) lsm = ShardMapManager.CreateListShardMap<long>(mapManagerName); try { // check if shard exists and if not, create it (Idempotent / tolerant of re-execute) if (!lsm.TryGetShard(new ShardLocation(shardServer1, ticketsDbName), out shard1)) shard1 = lsm.CreateShard(new ShardLocation(shardServer1, ticketsDbName)); } catch // sometimes, it may throw an error stating that a concurrent user recently changed some settings. //This is a retry logic to cover this scenario. { Thread.Sleep(500); // check if shard map manager exists and if not, create it (Idempotent / tolerant of re-execute) if (!ShardMapManager.TryGetListShardMap(mapManagerName, out lsm)) lsm = ShardMapManager.CreateListShardMap<long>(mapManagerName); // check if shard exists and if not, create it (Idempotent / tolerant of re-execute) if (!lsm.TryGetShard(new ShardLocation(shardServer1, ticketsDbName), out shard1)) shard1 = lsm.CreateShard(new ShardLocation(shardServer1, ticketsDbName)); } if (!lsm.TryGetShard(new ShardLocation(shardServer2, ticketsDbName), out shard2)) shard2 = lsm.CreateShard(new ShardLocation(shardServer2, ticketsDbName)); // Check if mapping exists and if not, create it (Idempotent / tolerant of re-execute) if (ListMappings != null) foreach (Tuple<long, string> mapping in ListMappings) if (!lsm.TryGetMappingForKey(mapping.Item1, out lmpg)) { if (mapping.Item2 == shardServer1) lsm.CreatePointMapping(new PointMappingCreationInfo<long>(mapping.Item1, shard1, MappingStatus.Online)); else if (mapping.Item2 == shardServer2) lsm.CreatePointMapping(new PointMappingCreationInfo<long>(mapping.Item1, shard2, MappingStatus.Online)); } return true; } catch { return false; } }
public TestShardMap(ListShardMap <TKey> lsm) { _lsm = lsm; }
/// <summary> /// Get existing point mapping from a list shard map /// </summary> /// <param name="lsm">List shard map</param> /// <returns>Valid existing point mapping, null if no point mappings found in given shard map</returns> private PointMapping<int> GetRandomPointMapping(ListShardMap<int> lsm) { // Get all point mappings without storing the results in the cache so that OpenConnection will fetch mapping again. IReadOnlyList<PointMapping<int>> allMappings = lsm.GetMappings(); if (allMappings.Count == 0) return null; int index = _r.Next(allMappings.Count); return allMappings[index]; }
public List <string[]> ExecuteMultiShardQuery(ListShardMap <int> shardMap, string credentialsConnectionString) { // Get the shards to connect to IEnumerable <Shard> shards = shardMap.GetShards(); // Create the multi-shard connection using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString)) { // Create a simple command using (MultiShardCommand cmd = conn.CreateCommand()) { // Because this query is grouped by CustomerID, which is sharded, // we will not get duplicate rows. cmd.CommandText = @" SELECT c.CustomerId, c.Name AS CustomerName, o.OrderID AS OrderCount FROM dbo.Customers AS c INNER JOIN dbo.Orders AS o ON c.CustomerID = o.CustomerID ORDER BY 1"; // Append a column with the shard name where the row came from cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn; // Allow for partial results in case some shards do not respond in time cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults; // Allow the entire command to take up to 30 seconds cmd.CommandTimeout = 30; // Execute the command. // We do not need to specify retry logic because MultiShardDataReader will internally retry until the CommandTimeout expires. using (MultiShardDataReader reader = cmd.ExecuteReader()) { // Get the column names TableFormatter formatter = new TableFormatter(GetColumnNames(reader).ToArray()); int rows = 0; while (reader.Read()) { // Read the values using standard DbDataReader methods object[] values = new object[reader.FieldCount]; reader.GetValues(values); // Extract just database name from the $ShardLocation pseudocolumn to make the output formater cleaner. // Note that the $ShardLocation pseudocolumn is always the last column int shardLocationOrdinal = values.Length - 1; values[shardLocationOrdinal] = ExtractDatabaseName(values[shardLocationOrdinal].ToString()); // Add values to output formatter formatter.AddRow(values); rows++; } Console.WriteLine(formatter.ToString()); Console.WriteLine("({0} rows returned)", rows); return(formatter._rows); } } } }
private bool RefreshShardListMap(List <Tuple <long, string> > ListMappings = null) { try { string shardServer1 = WingtipTicketApp.Config.PrimaryDatabaseServer; string shardServer2 = WingtipTicketApp.Config.ShardDatabaseServer; string ticketsDbName = WingtipTicketApp.Config.TicketsDbName; Shard shard1 = null, shard2 = null; PointMapping <long> lmpg; // check if shard map manager exists and if not, create it (Idempotent / tolerant of re-execute) if (!ShardMapManager.TryGetListShardMap(mapManagerName, out lsm)) { lsm = ShardMapManager.CreateListShardMap <long>(mapManagerName); } try { // check if shard exists and if not, create it (Idempotent / tolerant of re-execute) if (!lsm.TryGetShard(new ShardLocation(shardServer1, ticketsDbName), out shard1)) { shard1 = lsm.CreateShard(new ShardLocation(shardServer1, ticketsDbName)); } } catch // sometimes, it may throw an error stating that a concurrent user recently changed some settings. //This is a retry logic to cover this scenario. { Thread.Sleep(500); // check if shard map manager exists and if not, create it (Idempotent / tolerant of re-execute) if (!ShardMapManager.TryGetListShardMap(mapManagerName, out lsm)) { lsm = ShardMapManager.CreateListShardMap <long>(mapManagerName); } // check if shard exists and if not, create it (Idempotent / tolerant of re-execute) if (!lsm.TryGetShard(new ShardLocation(shardServer1, ticketsDbName), out shard1)) { shard1 = lsm.CreateShard(new ShardLocation(shardServer1, ticketsDbName)); } } if (!lsm.TryGetShard(new ShardLocation(shardServer2, ticketsDbName), out shard2)) { shard2 = lsm.CreateShard(new ShardLocation(shardServer2, ticketsDbName)); } // Check if mapping exists and if not, create it (Idempotent / tolerant of re-execute) if (ListMappings != null) { foreach (Tuple <long, string> mapping in ListMappings) { if (!lsm.TryGetMappingForKey(mapping.Item1, out lmpg)) { if (mapping.Item2 == shardServer1) { lsm.CreatePointMapping(new PointMappingCreationInfo <long>(mapping.Item1, shard1, MappingStatus.Online)); } else if (mapping.Item2 == shardServer2) { lsm.CreatePointMapping(new PointMappingCreationInfo <long>(mapping.Item1, shard2, MappingStatus.Online)); } } } } return(true); } catch { return(false); } }
public void DateGetPointMappingsForRange() { ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager( Globals.ShardMapManagerConnectionString, ShardMapManagerLoadPolicy.Lazy); ListShardMap <DateTime> lsm = smm.GetListShardMap <DateTime>(DateTimeShardMapperTests.s_listShardMapName); Assert.IsNotNull(lsm); Shard s1 = lsm.CreateShard(new ShardLocation(Globals.ShardMapManagerTestsDatasourceName, DateTimeShardMapperTests.s_shardedDBs[0])); Assert.IsNotNull(s1); Shard s2 = lsm.CreateShard(new ShardLocation(Globals.ShardMapManagerTestsDatasourceName, DateTimeShardMapperTests.s_shardedDBs[1])); Assert.IsNotNull(s2); DateTime val1 = DateTime.Now.Subtract(TimeSpan.FromMinutes(10)); PointMapping <DateTime> p1 = lsm.CreatePointMapping(val1, s1); Assert.IsNotNull(p1); DateTime val2 = DateTime.Now.Subtract(TimeSpan.FromMinutes(20)); PointMapping <DateTime> p2 = lsm.CreatePointMapping(val2, s1); Assert.IsNotNull(p2); DateTime val3 = DateTime.Now.Subtract(TimeSpan.FromMinutes(30)); PointMapping <DateTime> p3 = lsm.CreatePointMapping(val3, s2); Assert.IsNotNull(p2); // Get all mappings in shard map. int count = 0; IEnumerable <PointMapping <DateTime> > allMappings = lsm.GetMappings(); using (IEnumerator <PointMapping <DateTime> > mEnum = allMappings.GetEnumerator()) { while (mEnum.MoveNext()) { count++; } } Assert.AreEqual(3, count); // Get all mappings in specified range. Range <DateTime> wantedRange = new Range <DateTime>(val3.AddMinutes(-5), val3.AddMinutes(15)); count = 0; IEnumerable <PointMapping <DateTime> > mappingsInRange = lsm.GetMappings(wantedRange); using (IEnumerator <PointMapping <DateTime> > mEnum = mappingsInRange.GetEnumerator()) { while (mEnum.MoveNext()) { count++; } } Assert.AreEqual(2, count); // Get all mappings for a shard. count = 0; IEnumerable <PointMapping <DateTime> > mappingsForShard = lsm.GetMappings(s1); using (IEnumerator <PointMapping <DateTime> > mEnum = mappingsForShard.GetEnumerator()) { while (mEnum.MoveNext()) { count++; } } Assert.AreEqual(2, count); // Get all mappings in specified range for a particular shard. count = 0; IEnumerable <PointMapping <DateTime> > mappingsInRangeForShard = lsm.GetMappings(wantedRange, s1); using (IEnumerator <PointMapping <DateTime> > mEnum = mappingsInRangeForShard.GetEnumerator()) { while (mEnum.MoveNext()) { count++; } } Assert.AreEqual(1, count); }
public ShardManager(ListShardMap <T> shardMap, IOptions <ShardingConfiguration> options) { _shardMap = shardMap; _shardingConfig = options.Value; }
public static void ShardMapManagerLoadTestsInitialize(TestContext testContext) { // Clear all connection pools. SqlConnection.ClearAllPools(); using (SqlConnection conn = new SqlConnection(Globals.ShardMapManagerTestConnectionString)) { conn.Open(); // Create ShardMapManager database using (SqlCommand cmd = new SqlCommand( string.Format(Globals.CreateDatabaseQuery, Globals.ShardMapManagerDatabaseName), conn)) { cmd.ExecuteNonQuery(); } // Create shard databases for (int i = 0; i < ShardMapManagerLoadTests.s_shardedDBs.Length; i++) { using (SqlCommand cmd = new SqlCommand( string.Format(Globals.CreateDatabaseQuery, ShardMapManagerLoadTests.s_shardedDBs[i]), conn)) { cmd.ExecuteNonQuery(); } } // cleanup for deadlock monitoring foreach (string q in s_deadlockDetectionCleanupQueries) { using (SqlCommand cmd = new SqlCommand(q, conn)) { try { cmd.ExecuteNonQuery(); } catch (SqlException) { } } } // setup for deadlock monitoring foreach (string q in s_deadlockDetectionSetupQueries) { using (SqlCommand cmd = new SqlCommand(q, conn)) { try { cmd.ExecuteNonQuery(); } catch (SqlException) { } } } } // Create shard map manager. ShardMapManagerFactory.CreateSqlShardMapManager( Globals.ShardMapManagerConnectionString, ShardMapManagerCreateMode.ReplaceExisting); // Create list shard map. ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager( Globals.ShardMapManagerConnectionString, ShardMapManagerLoadPolicy.Lazy); ListShardMap <int> lsm = smm.CreateListShardMap <int>(ShardMapManagerLoadTests.s_listShardMapName); Assert.IsNotNull(lsm); // Create range shard map. RangeShardMap <int> rsm = smm.CreateRangeShardMap <int>(ShardMapManagerLoadTests.s_rangeShardMapName); Assert.IsNotNull(rsm); // Add 'InitialShardCount' shards to list and range shard map. for (int i = 0; i < ShardMapManagerLoadTests.InitialShardCount; i++) { ShardCreationInfo si = new ShardCreationInfo( new ShardLocation(Globals.ShardMapManagerTestsDatasourceName, ShardMapManagerLoadTests.s_shardedDBs[i]), ShardStatus.Online); Shard sList = lsm.CreateShard(si); Assert.IsNotNull(sList); Shard sRange = rsm.CreateShard(si); Assert.IsNotNull(sRange); } // Initialize retry policy s_retryPolicy = new TransientFaultHandling.RetryPolicy <TransientFaultHandling.SqlDatabaseTransientErrorDetectionStrategy>( new TransientFaultHandling.ExponentialBackoff(5, TimeSpan.FromMilliseconds(100), TimeSpan.FromSeconds(5), TimeSpan.FromMilliseconds(100))); }
public TenantSqlConnectionProvider(ListShardMap <TTenantIdentity> shardMap, IOptions <ShardingConfiguration> options) { _shardMap = shardMap; _config = options.Value; }