public static void Run(TimerInfo myTimer, TraceWriter log)
{
log.Info($"ScaleSqlDwByTimer triggered!");
try
{
var sqlServer = ConfigurationManager.AppSettings["SqlServerName"];
var sqlDw = ConfigurationManager.AppSettings["SqlDwName"];
var subscriptionId = ConfigurationManager.AppSettings["SubscriptionId"];
var resourceGroup = ConfigurationManager.AppSettings["SqlDwResourceGroup"];
var resourceId = $"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroup}/providers/Microsoft.Sql/servers/{sqlServer}/databases/{sqlDw}";
var dwLocation = ConfigurationManager.AppSettings["SqlDwLocation"];
var dwuConfigFile = ConfigurationManager.AppSettings["DwuConfigFile"];
var dwuConfigManager = new DwuConfigManager(dwuConfigFile);
// Create a DataWarehouseManagementClient
var dwClient = DwClientFactory.Create(resourceId);
// Get database information
var dbInfo = dwClient.GetDatabase();
dynamic dbInfoObject = JsonConvert.DeserializeObject(dbInfo);
var currentDwu = dbInfoObject.properties.requestedServiceObjectiveName.ToString();
log.Info($"Current DWU is {currentDwu}");
// If current dwu is smaller than default dwu, then scale up to default dwu
if (dwuConfigManager.CompareDwus(currentDwu, dwuConfigManager.DwuConfigs.DefaultDwu) < 0)
{
log.Info($"Scale up to default {dwuConfigManager.DwuConfigs.DefaultDwu}");
dwClient.ScaleWarehouse(dwuConfigManager.DwuConfigs.DefaultDwu, dwLocation);
}
else
{
log.Info($"No need to scale up. Current dwu is same or higher than default dwu {dwuConfigManager.DwuConfigs.DefaultDwu}");
}
}
catch (Exception e)
{
log.Info($"ScaleSqlDwByTimer threw exception: {e.Message}");
}
}
public static async Task <object> Run(HttpRequestMessage req, TraceWriter log)
{
log.Info($"ScaleSqlDW triggered!");
DwScaleLogEntity logEntity = null;
CloudTable dwuScaleLogsTable = null;
_logger = log;
try
{
var storageConnStr = ConfigurationManager.AppSettings["AzureWebJobsStorage"];
var dwLocation = ConfigurationManager.AppSettings["SqlDwLocation"];
var tableName = ConfigurationManager.AppSettings["DwScaleLogsTable"];
var dwuConfigFile = ConfigurationManager.AppSettings["DwuConfigFile"];
var dwuConfigManager = new DwuConfigManager(dwuConfigFile);
string jsonContent = await req.Content.ReadAsStringAsync();
dynamic alert = JsonConvert.DeserializeObject(jsonContent);
if (alert == null || alert.status == null || alert.context == null)
{
return(req.CreateResponse(HttpStatusCode.BadRequest, new
{
error = "Request didn't have required data in it!"
}));
}
// The function will be called both when the alert is Activated (that is, triggered) and when it is Resolved.
// We only respond to Activated alert
if (alert.status != "Activated")
{
var message = $"Alert status is not activated! No scaling triggered!";
log.Info(message);
return(req.CreateResponse(HttpStatusCode.OK, new
{
status = message
}));
}
string alertName = alert.context.name;
// Resource name in the alert looks like this: edudatawh/educationdatawh
string dwName = alert.context.resourceName.ToString().Split('/')[1];
string alertTimeStamp = alert.context.timestamp.ToString("yyyy-MM-ddTHH:mm:ssZ");
// Get or create DW Scale logs table
log.Info($"Get or create {tableName} table if it doesn't exist");
dwuScaleLogsTable = TableClientFactory.CreateTableIfNotExists(storageConnStr, tableName);
// Create log entity
logEntity = new DwScaleLogEntity(dwName, alertTimeStamp)
{
AlertName = alertName,
AlertCondition = alert.context.condition.ToString()
};
// Create a DataWarehouseManagementClient
var dwClient = DwClientFactory.Create(alert.context.resourceId.ToString());
// Get database information
var dbInfo = dwClient.GetDatabase();
dynamic dbInfoObject = JsonConvert.DeserializeObject(dbInfo);
var currentDwu = dbInfoObject.properties.requestedServiceObjectiveName.ToString();
logEntity.DwuBefore = currentDwu;
log.Info($"Current DWU is {currentDwu}");
if (alertName.IndexOf("scale up", StringComparison.OrdinalIgnoreCase) >= 0)
{
var upLevelDwu = dwuConfigManager.GetUpLevelDwu(currentDwu);
if (upLevelDwu != currentDwu)
{
log.Info($"scale up to {upLevelDwu}");
logEntity.Action = "Scale Up";
dwClient.ScaleWarehouse(upLevelDwu, dwLocation);
}
else
{
log.Info($"Can't scale up. It's at MAX level {currentDwu} already");
}
logEntity.DwuAfter = upLevelDwu;
}
else if (alertName.IndexOf("scale down", StringComparison.OrdinalIgnoreCase) >= 0)
{
if (IsInsideScaleUpScheduleTime())
{
var message = $"Can't scale down. It's inside scheduled scale up hours";
logEntity.Error = message;
log.Info(message);
}
else
{
var downLevelDwu = dwuConfigManager.GetDownLevelDwu(currentDwu);
if (downLevelDwu != currentDwu)
{
log.Info($"scale down to {downLevelDwu}");
logEntity.Action = "Scale Down";
dwClient.ScaleWarehouse(downLevelDwu, dwLocation);
}
else
{
log.Info($"Can't scale down. It's at MIN level {currentDwu} already");
}
logEntity.DwuAfter = downLevelDwu;
}
}
log.Info($"Insert log entity to DwScaleLogs table");
TableOperation insertOperation = TableOperation.Insert(logEntity);
dwuScaleLogsTable.Execute(insertOperation);
return(req.CreateResponse(HttpStatusCode.OK, new
{
status = $"Done!"
}));
}
catch (Exception e)
{
log.Info($"ScaleSqlDW threw exception: {e.Message}");
if (logEntity != null && dwuScaleLogsTable != null)
{
logEntity.Error = e.Message;
TableOperation insertOperation = TableOperation.Insert(logEntity);
dwuScaleLogsTable.Execute(insertOperation);
}
return(req.CreateResponse(HttpStatusCode.InternalServerError, new
{
error = $"{e.Message}"
}));
}
}