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