private bool PublishDacPac(ShardBase shard, ShardSetConfig shardSetConfig, string dacPacPath,
string dacPacProfilePath)
{
var publisher = InstantiateDacPacPublisher(shard, shardSetConfig);
return(publisher.PublishDacPac(dacPacPath, dacPacProfilePath));
}
public void Publish(ShardBase shard, ShardSetConfig shardSetConfig, string shardUserName,
string shardUserPassword, string sql, CommandType commandType)
{
var shardConnection =
new ShardConnection
{
ServerInstanceName = shard.ServerInstanceName,
Catalog = shard.Catalog,
UserName = shardUserName,
Password = shardUserPassword,
ShardSetName = shardSetConfig.ShardSetName
};
using (var sqlConnection = GetReliableConnection(shardConnection.ConnectionString))
{
try
{
//open the connection
sqlConnection.Open();
// execute the command
var sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandText = sql;
sqlCommand.CommandType = commandType;
sqlCommand.ExecuteNonQuery();
}
catch (Exception)
{
// todo: Add Logging
throw;
}
}
}
protected static void AddDacPacSettings(ShardSetConfig shardSetConfig)
{
shardSetConfig.SetShardSetSetting("DacPacBlobName", @"AWSales.dacpac");
shardSetConfig.SetShardSetSetting("DacPacProfileBlobName", @"AWSales.Deploy.azuredb.publish.xml");
shardSetConfig.SetShardSetSetting("DacPacSyncProfileBlobName", @"AWSales.Sync.azuredb.publish.xml");
shardSetConfig.SetShardSetSetting("DacPacShouldDeployOnSync", false.ToString());
}
public void Publish_Shards_Asycn()
{
var currentConfig = ShardSetConfig.LoadCurrent(TestShardSetName);
currentConfig.Servers.Clear();
var serverLocation = ConfigurationManager.AppSettings["TestSQLServer"] ?? "(localdb)\v11.0";
var server =
Server.Load(serverLocation) ??
new Server
{
ServerInstanceName = serverLocation,
Location = "Test Server Location",
MaxShardsAllowed = -1,
};
server.MaxShardsAllowed = -1;
server = server.Save();
currentConfig.Servers.Add(server);
currentConfig.Save();
currentConfig.UpdateShardMap();
currentConfig.Save();
currentConfig.DeployShardMap(true);
currentConfig.PublishShardMap(true);
var queue = ShardSetActionQueue.GetQueue();
queue.QueueProcessingEvent += Queue_QueueProcessingEvent;
queue.CheckAndProcessQueue(Guid.NewGuid());
}
public void Elastic_Scale_Up_Test()
{
SaveTestSettings();
SaveTestShardSetConfig();
DeployCurrentShardSetConfigAsync();
// process the queues
var queue = ShardSetActionQueue.GetQueue();
queue.QueueProcessingEvent += Queue_QueueProcessingEvent;
queue.CheckAndProcessQueue(Guid.NewGuid());
// create some test data across the shards
//CreateTestData();
// Reread the test shard set configuration to get the Current Shard Map Id value
// of prior deployment
var shardSetConfig = ShardSetConfig.LoadCurrent(TestShardSetName);
// create a new configuration with more shards
shardSetConfig.TargetShardCount = 8;
shardSetConfig.MaxShardCount = 8;
shardSetConfig.UpdateShardMap();
shardSetConfig.Save();
// redeploy and publish async
shardSetConfig.DeployShardMap(true);
shardSetConfig.PublishShardMap(true);
// execute from queues until clear
queue.CheckAndProcessQueue(Guid.NewGuid());
}
/// <summary>
/// Initializes a new instance of the <see cref="DacPacPublisher"/> class.
/// </summary>
/// <param name="parameters">The dacpac publisher parameters.</param>
public DacPacPublisher(DacPacPublisherParams parameters)
{
_shardSetConfig = parameters.ShardSetConfig;
_shardUserName = parameters.ShardUserName;
_shardUserPassword = parameters.ShardUserPassword;
// create connection object to the shard instance, shard catalog with admin credentials
_shardCatalogConnection =
new ShardConnection
{
ServerInstanceName = parameters.Shard.ServerInstanceName,
Catalog = parameters.Shard.Catalog,
UserName = parameters.ShardAdminUserName,
Password = parameters.ShardAdminPassword,
ShardSetName = parameters.ShardSetConfig.ShardSetName
};
// create connection object to the shard instance, master catalog with admin credentials
_masterCatalogConnection =
new ShardConnection
{
ServerInstanceName = parameters.Shard.ServerInstanceName,
Catalog = "master",
UserName = parameters.ShardAdminUserName,
Password = parameters.ShardAdminPassword,
ShardSetName = parameters.ShardSetConfig.ShardSetName
};
}
/// <summary>
/// Call to the shard set driver when an existing shard is being synchronized.
/// </summary>
/// <param name="shard">The shard.</param>
/// <param name="shardSetConfig">The shard set configuration.</param>
public void SyncShard(ShardBase shard, ShardSetConfig shardSetConfig)
{
// get the settings for dac pac publishing from the shard set setting
// use the sync settings
// Retrieve storage account from connection string.
var connectionString = ConfigurationManager.ConnectionStrings["AzureStorage"];
if (connectionString == null)
{
throw new InvalidOperationException("Connection string to azure storage is required in your app.config");
}
var dacPacBlobName = shardSetConfig.GetShardSetSetting(_dacPacBlobNameKey);
var dacPacSyncProfileBlobName = shardSetConfig.GetShardSetSetting(_dacPacSyncProfileBlobNameKey);
var dacPacShouldDeployOnSync = shardSetConfig.GetShardSetSetting(_dacPacShouldDeployOnSyncKey);
var dacPacsPath = DownloadDacpacsFromBlobStore(connectionString, shardSetConfig.ShardSetName);
var isNewDb = dacPacShouldDeployOnSync.ToLower() == "true" &&
PublishDacPac(shard, shardSetConfig, dacPacsPath + @"\" + dacPacBlobName,
dacPacsPath + @"\" + dacPacSyncProfileBlobName);
UpdateReferenceData(shard, isNewDb);
}
public void T4_Pin_Data_To_Shard()
{
var shardSetConfig = ShardSetConfig.LoadCurrent(TestShardSetName);
var pointerShard = shardSetConfig.Shards.First();
var shardlet = Shardlet.Load(TestShardSetName, "1");
shardlet.MoveToShard(pointerShard, true, true, new Guid(), _queueAndUseWorkerRoles);
}
private static ShardSetConfig CreateTestShardSetConfig()
{
var shardSetConfig = new ShardSetConfig {
ShardSetName = "AWSales"
};
AddDacPacSettings(shardSetConfig);
return(shardSetConfig);
}
public DacPacPublisherParams(ShardBase shard, ShardSetConfig shardSetConfig, string shardUserName,
string shardUserPassword, string shardAdminUserName, string shardAdminPassword)
{
_shard = shard;
_shardSetConfig = shardSetConfig;
_shardUserName = shardUserName;
_shardUserPassword = shardUserPassword;
_shardAdminUserName = shardAdminUserName;
_shardAdminPassword = shardAdminPassword;
}
private ShardConnection GetShardConnection(ShardBase shard, ShardSetConfig shardSetConfig)
{
return(new ShardConnection
{
ServerInstanceName = shard.ServerInstanceName,
Catalog = shard.Catalog,
UserName = _settings.AdminUser,
Password = _settings.AdminPassword,
ShardSetName = shardSetConfig.ShardSetName
});
}
public void Load_Edit_Save_Table_Group_Config()
{
var r = new Random();
var dbSize = r.Next(250);
var currentConfig = ShardSetConfig.LoadCurrent(TestShardSetName);
currentConfig.MaxShardSizeMb = dbSize;
currentConfig.Save();
var newCurrentConfig = ShardSetConfig.LoadCurrent(TestShardSetName);
Assert.AreEqual(dbSize, newCurrentConfig.MaxShardSizeMb);
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="commandText">The command text.</param>
/// <returns>DataTable.</returns>
public DataTable ExecuteQuery(string commandText)
{
//todo: needs another look on how the tasks are being run
var shardSetConfig = ShardSetConfig.LoadCurrent(_shardSetName);
var shardMap = shardSetConfig.ShardMap;
var settings = Settings.Load();
var finalResult = new DataTable();
var totalShards = shardMap.Shards.Count;
var tasks = new Task <DataTable> [totalShards];
var i = 0;
foreach (var shard in shardMap.Shards.ToList())
{
var connectionString =
"Server=" + shard.ServerInstanceName
+ ";Database=" + shard.Catalog
+ ";User Id=" + settings.ShardUser
+ ";Password="******";";
tasks[i] = Task.Run(() => ExecuteQuery(commandText, new ReliableSqlConnection(connectionString).Current));
i++;
}
var allComplete = false;
while (!allComplete)
{
foreach (Task task in tasks)
{
if (!task.Status.Equals(TaskStatus.RanToCompletion))
{
if (task.Status.Equals(TaskStatus.Faulted))
{
Debug.WriteLine("Faulted: " + task.Exception.InnerException.Message);
return(null);
}
Thread.Sleep(25);
break;
}
allComplete = true;
}
}
foreach (var dt in tasks)
{
finalResult.Merge(dt.Result);
}
return(finalResult);
}
private DacPacPublisher InstantiateDacPacPublisher(ShardBase shard, ShardSetConfig shardSetConfig)
{
var parameters =
new DacPacPublisher.DacPacPublisherParams(
shard,
shardSetConfig,
_settings.ShardUser,
_settings.ShardPassword,
_settings.AdminUser,
_settings.AdminPassword);
return(new DacPacPublisher(parameters));
}
/// <summary>
/// Call to the shard set driver when a shardlet is copied from one shard to another.
/// </summary>
/// <param name="sourceShard">The source shard.</param>
/// <param name="destinationShard">The destination shard.</param>
/// <param name="shardSetConfig">The shard set configuration.</param>
/// <param name="shardingKey">The distribution key.</param>
/// <param name="uniqueProcessID">The unique process identifier.</param>
public void CopyShardlet(ShardBase sourceShard, ShardBase destinationShard, ShardSetConfig shardSetConfig,
string shardingKey, Guid uniqueProcessID)
{
//Setup the connections for the source and destination shard
var sourceConnection = GetShardConnection(sourceShard, shardSetConfig);
var destinationConnection = GetShardConnection(destinationShard, shardSetConfig);
var uniqueProcessString = uniqueProcessID.ToString().Trim().Replace("-", string.Empty);
// copy the shardlet
ResetTempTables(uniqueProcessString, destinationConnection);
CopyShardletIntoTempTables(shardingKey, sourceConnection, destinationConnection, uniqueProcessString);
MergeShardletAndDropTempTables(destinationConnection, uniqueProcessString);
}
private static ShardSetConfig ConfigureShardSet(Server server)
{
var shardSetConfig =
new ShardSetConfig
{
AllowDeployments = true,
CurrentShardCount = 0,
MaxShardCount = -1,
MaxShardSizeMb = 100,
MaxShardletsPerShard = 1000,
MinShardSizeMb = 10,
TargetShardCount = 5,
ShardSetName = "AWSales"
};
shardSetConfig.Servers.Add(server);
shardSetConfig.UpdateShardMap();
// add pointer shard
var serverLocation = ConfigurationManager.AppSettings["TestSQLServer"] ?? @"(localdb)\v11.0";
var pointerShard =
new Shard
{
Catalog = "AdvWrkAWSales_HighVolume",
Description = "Database for premium speed orders",
ServerInstanceName = serverLocation
};
shardSetConfig.Shards.Add(pointerShard);
// add dacpac settings
shardSetConfig.SetShardSetSetting("DacPacBlobName", @"AWSales.dacpac");
shardSetConfig.SetShardSetSetting("DacPacProfileBlobName", @"AWSales.Deploy.azuredb.publish.xml");
shardSetConfig.SetShardSetSetting("DacPacSyncProfileBlobName", @"AWSales.Sync.azuredb.publish.xml");
shardSetConfig.SetShardSetSetting("DacPacShouldDeployOnSync", _queueAndUseWorkerRoles.ToString());
try
{
shardSetConfig.Save();
}
catch (DbEntityValidationException e)
{
// todo: log
var errors = e.EntityValidationErrors;
throw;
}
return(shardSetConfig);
}
/// <summary>
/// Call to the shard set driver when a shard is to be deleted.
/// </summary>
/// <param name="shard">The shard.</param>
/// <param name="shardSetConfig">The shard set configuration.</param>
public void DeleteShard(ShardBase shard, ShardSetConfig shardSetConfig)
{
// todo: make sure this check occurs in the caller
//if (!ignorePopulation)
//{
// // todo: inefficient to return the entire list to see if any exist
// var shardDistributionKeys = GetShardDistributionKeys(shard, shardSetConfig);
// if (shardDistributionKeys.Any()) return;
//}
// todo: why is the delete code on the publisher?
var publisher = InstantiateDacPacPublisher(shard, shardSetConfig);
publisher.DropDatabase();
}
/// <summary>
/// Call to the shard set driver when a specific shardlet is to be deleted from the shardlet.
/// </summary>
/// <param name="shard">The shard.</param>
/// <param name="shardSetConfig">The shard set configuration.</param>
/// <param name="shardingKey">The distribution key.</param>
public void DeleteShardlet(ShardBase shard, ShardSetConfig shardSetConfig, string shardingKey)
{
var shardConnection = GetShardConnection(shard, shardSetConfig);
using (var connection = new ReliableSqlConnection(shardConnection.ConnectionString))
{
connection.Open();
var command = new SqlCommand(_deleteShardletProcedure, connection.Current)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters.Add(CreateShardingKeyParameter(shardingKey));
command.ExecuteNonQuery();
}
}
private static void AddTestShardlets(ShardBase shard, ShardSetConfig shardSetConfig,
int initialTestCustomerID, int numberOfTestCustomers, int numberOfTestOrders, int numberOfTestSalesLineNums)
{
var shardCatalogConnection =
new ShardConnection
{
ServerInstanceName = shard.ServerInstanceName,
Catalog = shard.Catalog,
UserName = "******",
Password = "******",
ShardSetName = shardSetConfig.ShardSetName
};
var builder = new TestDataBuilder(GetReferenceConnectionString());
builder.AddTestSalesOrdersInDatabase(shardCatalogConnection.ConnectionString, initialTestCustomerID, numberOfTestCustomers, numberOfTestOrders);
builder.AddTestShoppingCartItemsInDatabase(shardCatalogConnection.ConnectionString, initialTestCustomerID, numberOfTestCustomers, numberOfTestSalesLineNums);
}
public void T6_Scale_In()
{
// read the test shard set configuration
var shardSetConfig = ShardSetConfig.LoadCurrent(TestShardSetName);
// create a new configuration with more shards
shardSetConfig.TargetShardCount = 3;
shardSetConfig.MaxShardCount = 3;
// recalculate the shard map and save a new configuration
shardSetConfig.UpdateShardMap();
shardSetConfig.Save();
// shard deployment
shardSetConfig.DeployShardMap(true);
// update of online shard map
shardSetConfig.PublishShardMap(true);
}
public void T2_Update_Reference_Data_And_Propagate()
{
// Scenario - nightly job updates the CurrencyRate table in AwMain
// Run data update
// Queue a Shard Sync for Range Shards
// Queue a Shard Sync for Pointer Shards
// update the CurrencyRate table in AwMain
LoadCurrencyRatesInAwMain(500);
// Get the shard set configuration
var shardSetConfig = ShardSetConfig.LoadCurrent(TestShardSetName);
// range shard synchronization
shardSetConfig.SyncShards(_queueAndUseWorkerRoles);
// pointer shard synchronization
shardSetConfig.SyncPointerShards(_queueAndUseWorkerRoles);
}
/// <summary>
/// Call to the shard set driver when a shard is to be first created..
/// </summary>
/// <param name="shard">The shard.</param>
/// <param name="shardSetConfig">The shard set configuration.</param>
public void CreateShard(ShardBase shard, ShardSetConfig shardSetConfig)
{
// Retrieve storage account from connection string.
var connectionString = ConfigurationManager.ConnectionStrings["AzureStorage"];
if (connectionString == null)
{
throw new InvalidOperationException("Connection string to azure storage is required in your app.config");
}
var dacPacBlobName = shardSetConfig.GetShardSetSetting(_dacPacBlobNameKey);
var dacPacDeployProfileBlobName = shardSetConfig.GetShardSetSetting(_dacPacProfileBlobNameKey);
var dacPacsPath = DownloadDacpacsFromBlobStore(connectionString, shardSetConfig.ShardSetName);
var isNewDb = PublishDacPac(shard, shardSetConfig, dacPacsPath + @"\" + dacPacBlobName,
dacPacsPath + @"\" + dacPacDeployProfileBlobName);
UpdateReferenceData(shard, isNewDb);
}
private static ShardSetConfig SaveTestShardSetConfig()
{
var shardSetConfig =
new ShardSetConfig
{
AllowDeployments = true,
CurrentShardCount = 0,
MaxShardCount = 5,
MaxShardSizeMb = 100,
MaxShardletsPerShard = 1000,
MinShardSizeMb = 10,
TargetShardCount = 5,
ShardSetName = TestShardSetName
};
// add dacpac settings
AddDacPacSettings(shardSetConfig);
shardSetConfig.Save();
return(shardSetConfig);
}
/// <summary>
/// Executes the non query on all shards in the shard map.
/// </summary>
/// <param name="commandText">The command text.</param>
/// <remarks>
/// NOTE: This only works properly if the current configuration is in sync with the
/// actual shards.
/// </remarks>
public void ExecuteNonQuery(string commandText)
{
var shardSetConfig = ShardSetConfig.LoadCurrent(_shardSetName);
var shardMap = shardSetConfig.ShardMap;
var settings = Settings.Load();
var totalShards = shardMap.Shards.Count;
var tasks = new Task[totalShards];
var i = 0;
foreach (var shard in shardMap.Shards.ToList())
{
var connString = "Server=" + shard.ServerInstanceName + ";Database=" + shard.Catalog + ";User Id=" +
settings.ShardUser + ";Password="******";";
tasks[i] = Task.Run(() => ExecuteNonQuery(commandText, new ReliableSqlConnection(connString).Current));
i++;
}
var allComplete = false;
while (!allComplete)
{
foreach (var task in tasks)
{
if (!task.Status.Equals(TaskStatus.RanToCompletion))
{
if (task.Status.Equals(TaskStatus.Faulted))
{
Debug.WriteLine("Faulted: " + task.Exception.InnerException.Message);
}
Thread.Sleep(25);
break;
}
allComplete = true;
}
}
}
protected static ShardSetConfig SetupTestShardSetConfig()
{
// Get the test shard set configuration
var shardSetConfig = ShardSetConfig.LoadCurrent(TestShardSetName);
// Get the test server
var serverLocation = ConfigurationManager.AppSettings["TestSQLServer"] ?? @"(localdb)\v11.0";
var server = GetTestServer(serverLocation);
// For testing don;t worry about how many shards are in the server
server.MaxShardsAllowed = -1;
server = server.Save();
// Clear out the servers and just add the test server
shardSetConfig.Servers.Clear();
shardSetConfig.Servers.Add(server);
// Update the shard map to point to the databases to the new server
shardSetConfig.UpdateShardMap();
shardSetConfig.Save();
return(shardSetConfig);
}
public bool IsCurrentShardSetConfig(ShardSetConfig shardSetConfig)
{
return(shardSetConfig.ShardSetConfigID ==
_configRepository.GetCurrentTableShardSetID(shardSetConfig.ShardSetID));
}
public IShardSetDriver GetShardSetDriver(ShardSetConfig shardSetConfig)
{
return(_container.Resolve <IShardSetDriver>(shardSetConfig.ShardSetName));
}
public IList <Shard> GetPointerShardsForShardSet(ShardSetConfig shardSetConfig)
{
return(_configRepository.GetShardsForShardSet(shardSetConfig));
}
public Shard AddPointerShardToShardSet(Shard shard, ShardSetConfig shardSetConfig)
{
return(_configRepository.AddShardToShardSet(shard, shardSetConfig));
}
public ShardSetConfig SaveConfiguration(ShardSetConfig shardSetConfig)
{
return(_configRepository.AddShardSetConfig(shardSetConfig));
}
