public static async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", Route = "getadwstatus")] HttpRequest req,
ILogger log, Microsoft.Azure.WebJobs.ExecutionContext context)
{
string currentStatus;
dynamic dbInfoObject;
log.LogInformation("autoscaleup of adw initiated");
var config = new ConfigurationBuilder()
.SetBasePath(context.FunctionAppDirectory)
.AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables()
.Build();
var azureServiceTokenProvider = new AzureServiceTokenProvider();
var kvClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));
var kvURL = config["KeyVaultURL"];
try
{
var dwLocation = config["SqlDwLocation"];
//var tableName = config["DwScaleLogsTable"];
var dwuConfigFile = config["DwuConfigFile"];
var resourceIdSecret = config["ResourceId"];
var resourceId = (await kvClient.GetSecretAsync(kvURL, resourceIdSecret)).Value;
//string startupPath = Directory.GetParent(Environment.CurrentDirectory).Parent.Parent.FullName;
//string dwuConfigFilePath = startupPath + "\\" + dwuConfigFile;
//var dwuConfigManager = new DwuConfigManager(dwuConfigFilePath);
//var taskResult = await Task.Factory.StartNew<int>(() =>
//{
// Thread.Sleep(300000);
// return 0;
//});
// Create a DataWarehouseManagementClient
var dwClient = await DwClientFactory.Create(resourceId.ToString(), context);
do
{
// Get database information
var dbInfo = dwClient.GetDatabase();
dbInfoObject = JsonConvert.DeserializeObject(dbInfo);
currentStatus = dbInfoObject.properties.status.ToString();
//logEntity.DwuBefore = currentDwu;
log.LogInformation($"Current DWU is {currentStatus}");
} while (currentStatus != "Online");
return(new OkObjectResult(dbInfoObject));
}
catch (Exception ex)
{
return(new BadRequestObjectResult($"Bad Operation {ex}"));
}
}
public static async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", Route = "userActivitiesinADW")] HttpRequestMessage req,
ILogger log, ExecutionContext context)
{
log.LogInformation("Get User Activities in ADW");
var config = new ConfigurationBuilder()
.SetBasePath(context.FunctionAppDirectory)
.AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables()
.Build();
var azureServiceTokenProvider = new AzureServiceTokenProvider();
var kvClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));
var kvURL = config["KeyVaultURL"];
try
{
var dwLocation = config["SqlDwLocation"];
//var tableName = config["DwScaleLogsTable"];
var dwuConfigFile = config["DwuConfigFile"];
var resourceIdSecret = config["ResourceId"];
var resourceId = (await kvClient.GetSecretAsync(kvURL, resourceIdSecret)).Value;
//string startupPath = Directory.GetParent(Environment.CurrentDirectory).Parent.Parent.FullName;
//string dwuConfigFilePath = startupPath + "\\" + dwuConfigFile;
//var dwuConfigManager = new DwuConfigManager(dwuConfigFilePath);
// Create a DataWarehouseManagementClient
var dwClient = await DwClientFactory.Create(resourceId.ToString(), context);
// Get database information
var dbInfo = dwClient.GetDatabase();
dynamic dbInfoObject = JsonConvert.DeserializeObject(dbInfo);
var currentStatus = dbInfoObject.properties.status.ToString();
//logEntity.DwuBefore = currentDwu;
log.LogInformation($"Current Status is {currentStatus}");
if (currentStatus != "Online")
{
return(new BadRequestObjectResult("Bad Operation"));
}
HttpResponseMessage res = dwClient.UserActivities();
dynamic UserActivityObject = JsonConvert.DeserializeObject(res.Content.ReadAsStringAsync().Result);
if (res.StatusCode != HttpStatusCode.OK)
{
return(new NotFoundObjectResult("Internal Server Error"));
}
return(new OkObjectResult(UserActivityObject));
}
catch (Exception ex)
{
return(new BadRequestObjectResult($"Bad Operation {ex}"));
}
}
public static async Task <HttpResponseMessage> pause(
[HttpTrigger(AuthorizationLevel.Function, "get", Route = "pauseADW")] HttpRequestMessage req,
ILogger log, ExecutionContext context)
{
log.LogInformation("pause the resumed ADW");
var config = new ConfigurationBuilder()
.SetBasePath(context.FunctionAppDirectory)
.AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables()
.Build();
try
{
var dwLocation = config["SqlDwLocation"];
//var tableName = config["DwScaleLogsTable"];
var dwuConfigFile = config["DwuConfigFile"];
var resourceId = config["ResourceId"];
//string startupPath = Directory.GetParent(Environment.CurrentDirectory).Parent.Parent.FullName;
//string dwuConfigFilePath = startupPath + "\\" + dwuConfigFile;
//var dwuConfigManager = new DwuConfigManager(dwuConfigFilePath);
// Create a DataWarehouseManagementClient
var dwClient = DwClientFactory.Create(resourceId.ToString(), context);
// Get database information
var dbInfo = dwClient.GetDatabase();
dynamic dbInfoObject = JsonConvert.DeserializeObject(dbInfo);
var currentStatus = dbInfoObject.properties.status.ToString();
//logEntity.DwuBefore = currentDwu;
log.LogInformation($"Current Status is {currentStatus}");
if (currentStatus != "Online")
{
return(req.CreateResponse(HttpStatusCode.BadRequest, "Bad Operation"));
}
HttpResponseMessage res = dwClient.Pause();
if (res.StatusCode != HttpStatusCode.Accepted)
{
return(req.CreateResponse(HttpStatusCode.InternalServerError, "internal error"));
}
return(req.CreateResponse(HttpStatusCode.Accepted, "ADW paused"));
}
catch (Exception ex)
{
return(req.CreateResponse(HttpStatusCode.BadRequest, "Bad Operation"));
}
}
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 <IActionResult> getadwinformation(
[HttpTrigger(AuthorizationLevel.Function, "get", Route = "getadwinformation")] HttpRequest req,
ILogger log, ExecutionContext context)
{
log.LogInformation("autoscaleup of adw initiated");
var config = new ConfigurationBuilder()
.SetBasePath(context.FunctionAppDirectory)
.AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables()
.Build();
try
{
var dwLocation = config["SqlDwLocation"];
//var tableName = config["DwScaleLogsTable"];
var dwuConfigFile = config["DwuConfigFile"];
var resourceId = config["ResourceId"];
//string startupPath = Directory.GetParent(Environment.CurrentDirectory).Parent.Parent.FullName;
//string dwuConfigFilePath = startupPath + "\\" + dwuConfigFile;
//var dwuConfigManager = new DwuConfigManager(dwuConfigFilePath);
// Create a DataWarehouseManagementClient
var dwClient = DwClientFactory.Create(resourceId.ToString(), context);
// Get database information
var dbInfo = dwClient.GetDatabase();
dynamic dbInfoObject = JsonConvert.DeserializeObject(dbInfo);
var currentDwu = dbInfoObject.properties.requestedServiceObjectiveName.ToString();
//logEntity.DwuBefore = currentDwu;
log.LogInformation($"Current DWU is {currentDwu}");
return(new OkObjectResult(dbInfoObject));
}
catch (Exception ex)
{
return(new NotFoundResult());
}
}
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}"
}));
}
}
public static async Task <IActionResult> scale(
[HttpTrigger(AuthorizationLevel.Function, "put", Route = "scaleADW")] HttpRequestMessage req,
ILogger log, ExecutionContext context)
{
dynamic informationString = null;
dynamic scalingResponseObject = null;
dynamic UserActivityObject = null;
string forceScaling = GetValueFromUrl(req);
log.LogInformation("resume the paused ADW");
var config = new ConfigurationBuilder()
.SetBasePath(context.FunctionAppDirectory)
.AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables()
.Build();
var azureServiceTokenProvider = new AzureServiceTokenProvider();
var kvClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));
var kvURL = config["KeyVaultURL"];
try
{
var dwLocation = config["SqlDwLocation"];
//var tableName = config["DwScaleLogsTable"];
var dwuConfigFile = config["DwuConfigFile"];
var resourceIdSecret = config["ResourceId"];
var resourceId = (await kvClient.GetSecretAsync(kvURL, resourceIdSecret)).Value;
//string startupPath = Directory.GetParent(Environment.CurrentDirectory).Parent.Parent.FullName;
//string dwuConfigFilePath = startupPath + "\\" + dwuConfigFile;
// var dwuConfigManager = new DwuConfigManager(dwuConfigFilePath);
dynamic CurrentServiceObject = JsonConvert.DeserializeObject(req.Content.ReadAsStringAsync().Result);
string currentDwu = CurrentServiceObject.currentServiceObjectiveName.ToString();
// Create a DataWarehouseManagementClient
var dwClient = await DwClientFactory.Create(resourceId.ToString(), context);
// Get database information
var dbInfo = dwClient.GetDatabase();
dynamic dbInfoObject = JsonConvert.DeserializeObject(dbInfo);
var currentStatus = dbInfoObject.properties.status.ToString();
var skusize = dbInfoObject.sku.name.ToString();
//logEntity.DwuBefore = currentDwu;
log.LogInformation($"Current Status is {currentStatus}");
//if (currentStatus != "Paused")
//{
// return req.CreateResponse(HttpStatusCode.BadRequest, "Bad Operation");
//}
if (skusize != currentDwu)
{
if (currentStatus == "Online")
{
if (forceScaling == "No")
{
UserActivityObject = GetUserActivity(log, dwClient);
if (UserActivityObject.properties.activeQueriesCount == 0)
{
scalingResponseObject = ScaleDataWarehouse(dwClient, currentDwu, dwLocation);
}
else
{
informationString = currentStatus == "Online" ? JsonConvert.DeserializeObject("{\"operation\":\"" + currentStatus + "\",\"activeQueriesCount\":\"" + UserActivityObject.properties.activeQueriesCount + "}") : JsonConvert.DeserializeObject("{\"operation\":\"" + currentStatus + "\"}");
}
}
else
{
scalingResponseObject = ScaleDataWarehouse(dwClient, currentDwu, dwLocation);
}
}
else if (currentStatus == "Scaling")
{
informationString = JsonConvert.DeserializeObject("{\"operation\":\"Scaling\"}");
}
else
{
informationString = currentStatus == "Online" ? JsonConvert.DeserializeObject("{\"operation\":\"" + currentStatus + "\",\"activeQueriesCount\":\"" + UserActivityObject.properties.activeQueriesCount + "}") : JsonConvert.DeserializeObject("{\"operation\":\"" + currentStatus + "\"}");
}
}
else
{
informationString = currentStatus == "Online" ? JsonConvert.DeserializeObject("{\"operation\":\"" + currentStatus + "\",\"comment\":\"Same Sku Size\"}") : JsonConvert.DeserializeObject("{\"operation\":\"" + currentStatus + "\"}");
}
// return new OkObjectResult(scalingResponseObject);
return(scalingResponseObject != null
? (ActionResult) new OkObjectResult(scalingResponseObject)
: (ActionResult) new OkObjectResult(informationString));
}
catch (Exception ex)
{
return(new BadRequestObjectResult($"Bad Operation {ex}"));
}
//return name != null
// ? (ActionResult)new OkObjectResult($"Hello, {name}")
// : new BadRequestObjectResult("Please pass a name on the query string or in the request body");
}