/// <summary>
/// Returns true if we can connect to the database.
/// </summary>
public static bool TryConnectToSqlDatabase()
{
string connectionString =
MultiShardConfiguration.GetConnectionStringForMasterDatabase();
try
{
using (ReliableSqlConnection conn = new ReliableSqlConnection(
connectionString,
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
}
return(true);
}
catch (SqlException e)
{
ConsoleUtils.WriteWarning("Failed to connect to SQL database with connection string:");
Console.WriteLine("\n{0}\n", connectionString);
ConsoleUtils.WriteWarning("If this connection string is incorrect, please update the Sql Database settings in App.Config.\n\nException message: {0}", e.Message);
return(false);
}
}
/// <summary>
/// To drop the database
/// </summary>
/// <param name="server">server</param>
/// <param name="db">db</param>
public static void DropDatabase(string server, string db)
{
ConsoleUtils.WriteInfo("Dropping database {0}", db);
using (ReliableSqlConnection conn = new ReliableSqlConnection(
MultiShardConfiguration.GetConnectionStringForMasterDatabase(),
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
// Determine if we are connecting to Azure SQL DB
cmd.CommandText = "SELECT SERVERPROPERTY('EngineEdition')";
cmd.CommandTimeout = 60;
int engineEdition = conn.ExecuteCommand <int>(cmd);
// Drop the database
if (engineEdition == 5)
{
// Azure SQL DB
cmd.CommandText = string.Format("DROP DATABASE {0}", BracketEscapeName(db));
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = string.Format(
@"ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE {0}",
BracketEscapeName(db));
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Creates a shard map manager, creates a shard map, and creates a shard
/// with a mapping for the full range of 32-bit integers.
/// </summary>
private static void CreateShardMapManagerAndShard()
{
if (MultiShardConfiguration.objShardMapManager != null)
{
ConsoleUtils.WriteWarning("Shard Map Manager already exists");
return;
}
// Create shard map manager database
if (!SqlDatabaseUtils.ExistsDatabase(MultiShardConfiguration.ShardMapManagerServerName, MultiShardConfiguration.ShardMapManagerDatabaseName))
{
SqlDatabaseUtils.CreateDatabase(MultiShardConfiguration.ShardMapManagerServerName, MultiShardConfiguration.ShardMapManagerDatabaseName);
}
// Create shard map manager
string shardMapManagerConnectionString = MultiShardConfiguration.GetConnectionString();
MultiShardConfiguration.objShardMapManager = ShardManagementUtils.CreateOrGetShardMapManager(shardMapManagerConnectionString);
// Create shard map
RangeShardMap <int> shardMap = ShardManagementUtils.CreateOrGetRangeShardMap <int>(
MultiShardConfiguration.objShardMapManager, MultiShardConfiguration.ShardMapName);
// Create schema info so that the split-merge service can be used to move data in sharded tables
// and reference tables.
CreateSchemaInfo(shardMap.Name);
// If there are no shards, add two shards: one for [0,100) and one for [100,+inf)
if (!shardMap.GetShards().Any())
{
CreateShardSample.CreateShard(shardMap, new Range <int>(0, 100));
CreateShardSample.CreateShard(shardMap, new Range <int>(100, 200));
}
}
/// <summary>
/// Executes the Multi-Shard Query sample.
/// </summary>
private static void MultiShardQuery()
{
RangeShardMap <int> shardMap = MultiShardConfiguration.TryGetShardMap();
if (shardMap != null)
{
MultiShardQuerySupport.ExecuteMultiShardQuery(shardMap);
}
}
/// <summary>
/// Executes the Data-Dependent Routing sample.
/// </summary>
private static void DataDepdendentRoutingForDelete()
{
RangeShardMap <int> shardMap = MultiShardConfiguration.TryGetShardMap();
if (shardMap != null)
{
DataDependentRoutingSupport.ExecuteDataDependentRoutingQueryForDelete(shardMap);
}
}
/// <summary>
/// To create new database
/// </summary>
/// <param name="server">server</param>
/// <param name="db">db</param>
public static void CreateDatabase(string server, string db)
{
ConsoleUtils.WriteInfo("Creating database {0}", db);
using (ReliableSqlConnection conn = new ReliableSqlConnection(
MultiShardConfiguration.GetConnectionStringForMasterDatabase(),
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
// Determine if we are connecting to Azure SQL DB
cmd.CommandText = "SELECT SERVERPROPERTY('EngineEdition')";
cmd.CommandTimeout = 60;
int engineEdition = conn.ExecuteCommand <int>(cmd);
if (engineEdition == 5)
{
// Azure SQL DB
SqlRetryPolicy.ExecuteAction(() =>
{
if (!ExistsDatabase(server, db))
{
// Begin creation (which is async for Standard/Premium editions)
cmd.CommandText = string.Format(
"CREATE DATABASE {0} (EDITION = '{1}')",
BracketEscapeName(db),
MultiShardConfiguration.DatabaseEdition);
cmd.CommandTimeout = 60;
cmd.ExecuteNonQuery();
}
});
// Wait for the operation to complete
while (!IsDatabaseOnline(server, db))
{
ConsoleUtils.WriteInfo("Waiting for database {0} to come online...", db);
Thread.Sleep(TimeSpan.FromSeconds(5));
}
}
else
{
// Other edition of SQL DB
cmd.CommandText = string.Format("CREATE DATABASE {0}", BracketEscapeName(db));
conn.ExecuteCommand(cmd);
}
}
}
/// <summary>
/// To check the database is online
/// </summary>
/// <param name="server">server</param>
/// <param name="db">db</param>
/// <returns>true or false</returns>
public static bool IsDatabaseOnline(string server, string db)
{
using (ReliableSqlConnection conn = new ReliableSqlConnection(
MultiShardConfiguration.GetConnectionStringForMasterDatabase(),
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select count(*) from sys.databases where name = @dbname and state = 0";
cmd.Parameters.AddWithValue("@dbname", db);
cmd.CommandTimeout = 60;
int count = conn.ExecuteCommand <int>(cmd);
bool exists = count > 0;
return(exists);
}
}
/// <summary>
/// Writes the shard map's state to the console.
/// </summary>
private static void PrintShardMapState()
{
Console.WriteLine("Current Shard Map state:");
RangeShardMap <int> shardMap = MultiShardConfiguration.TryGetShardMap();
if (shardMap == null)
{
return;
}
// Get all shards
IEnumerable <Shard> allShards = shardMap.GetShards();
// Get all mappings, grouped by the shard that they are on. We do this all in one go to minimise round trips.
ILookup <Shard, RangeMapping <int> > mappingsGroupedByShard = shardMap.GetMappings().ToLookup(m => m.Shard);
if (allShards.Any())
{
// The shard map contains some shards, so for each shard (sorted by database name)
// write out the mappings for that shard
foreach (Shard shard in shardMap.GetShards().OrderBy(s => s.Location.Database))
{
IEnumerable <RangeMapping <int> > mappingsOnThisShard = mappingsGroupedByShard[shard];
if (mappingsOnThisShard.Any())
{
string mappingsString = string.Join(", ", mappingsOnThisShard.Select(m => m.Value));
Console.WriteLine("\t{0} contains key range {1}", shard.Location.Database, mappingsString);
}
else
{
Console.WriteLine("\t{0} contains no key ranges.", shard.Location.Database);
}
}
}
else
{
Console.WriteLine("\tShard Map contains no shards");
}
}
/// <summary>
/// To execute sql script
/// </summary>
/// <param name="server"></param>
/// <param name="db"></param>
/// <param name="schemaFile"></param>
public static void ExecuteSqlScript(string server, string db, string schemaFile)
{
ConsoleUtils.WriteInfo("Executing script {0}", schemaFile);
using (ReliableSqlConnection conn = new ReliableSqlConnection(
MultiShardConfiguration.GetConnectionStringForSelectedDatabase(db),
SqlRetryPolicy,
SqlRetryPolicy))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
// Read the commands from the sql script file
IEnumerable <string> commands = ReadSqlScript(schemaFile);
foreach (string command in commands)
{
cmd.CommandText = command;
cmd.CommandTimeout = 60;
conn.ExecuteCommand(cmd);
}
}
}
/// <summary>
/// Drops all shards and the shard map manager database (if it exists).
/// </summary>
private static void DropAll()
{
RangeShardMap <int> shardMap = MultiShardConfiguration.TryGetShardMap();
if (shardMap != null)
{
// Drop shards
foreach (Shard shard in shardMap.GetShards())
{
SqlDatabaseUtils.DropDatabase(shard.Location.DataSource, shard.Location.Database);
}
}
if (SqlDatabaseUtils.ExistsDatabase(MultiShardConfiguration.ShardMapManagerServerName, MultiShardConfiguration.ShardMapManagerDatabaseName))
{
// Drop shard map manager database
SqlDatabaseUtils.DropDatabase(MultiShardConfiguration.ShardMapManagerServerName, MultiShardConfiguration.ShardMapManagerDatabaseName);
}
// Since we just dropped the shard map manager database, this shardMapManager reference is now non-functional.
// So set it to null so that the program knows that the shard map manager is gone.
MultiShardConfiguration.objShardMapManager = null;
}
/// <summary>
/// Reads the user's choice of a split point, and creates a new shard with a mapping for the resulting range.
/// </summary>
private static void AddShard()
{
RangeShardMap <int> shardMap = MultiShardConfiguration.TryGetShardMap();
if (shardMap != null)
{
// Here we assume that the ranges start at 0, are contiguous,
// and are bounded (i.e. there is no range where HighIsMax == true)
int currentMaxHighKey = shardMap.GetMappings().Max(m => m.Value.High);
int defaultNewHighKey = currentMaxHighKey + 100;
Console.WriteLine("A new range with low key {0} will be mapped to the new shard.", currentMaxHighKey);
int newHighKey = ConsoleUtils.ReadIntegerInput(
string.Format("Enter the high key for the new range [default {0}]: ", defaultNewHighKey),
defaultNewHighKey,
input => input > currentMaxHighKey);
Range <int> range = new Range <int>(currentMaxHighKey, newHighKey);
Console.WriteLine();
Console.WriteLine("Creating shard for range {0}", range);
CreateShardSample.CreateShard(shardMap, range);
}
}
/// <summary>
/// Tries to get the ShardMapManager that is stored in the specified database.
/// </summary>
public static ShardMapManager TryGetShardMapManager()
{
if (!SqlDatabaseUtils.ExistsDatabase(MultiShardConfiguration.ShardMapManagerServerName, MultiShardConfiguration.ShardMapManagerDatabaseName))
{
// Shard Map Manager database has not yet been created
return(null);
}
ShardMapManager shardMapManager;
bool smmExists = ShardMapManagerFactory.TryGetSqlShardMapManager(MultiShardConfiguration.GetConnectionString(),
ShardMapManagerLoadPolicy.Lazy, out shardMapManager);
if (!smmExists)
{
// Shard Map Manager database exists, but Shard Map Manager has not been created
return(null);
}
return(shardMapManager);
}
