public async Task Dispatch([NotNull] DashboardContext context)
{
var response = new Response()
{
Status = true
};
var jobId = context.Request.GetQuery("Id");
var action = context.Request.GetQuery("Action");
if (!JobAgent.IsValidJobId(jobId))
{
response.Status = false;
response.Message = $"The Job Id {jobId} not found";
await context.Response.WriteAsync(JsonConvert.SerializeObject(response));
return;
}
if ("Stop".Equals(action))
{
JobAgent.StopBackgroundJob(jobId);
}
if ("Start".Equals(action))
{
JobAgent.StartBackgroundJob(jobId);
}
context.Response.StatusCode = (int)HttpStatusCode.OK;
await context.Response.WriteAsync(JsonConvert.SerializeObject(response));
}
public void TestCreateUpdateDropJobCredential()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
Server server = context.CreateServer(resourceGroup);
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
try
{
// Create database only required parameters
string dbName = SqlManagementTestUtilities.GenerateName();
var db1 = sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, server.Name, dbName, new Database()
{
Location = server.Location,
});
Assert.NotNull(db1);
// Create agent
string agentName = "agent";
JobAgent agent = sqlClient.JobAgents.CreateOrUpdate(resourceGroup.Name, server.Name, agentName, new JobAgent
{
Location = server.Location,
DatabaseId = db1.Id
});
// Create credential
JobCredential credential = sqlClient.JobCredentials.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, SqlManagementTestUtilities.DefaultLogin, new JobCredential
{
Username = "******",
Password = "******"
});
// Update credential
credential = sqlClient.JobCredentials.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, SqlManagementTestUtilities.DefaultLogin, new JobCredential
{
Username = SqlManagementTestUtilities.DefaultLogin,
Password = SqlManagementTestUtilities.DefaultPassword
});
// List credentials
sqlClient.JobCredentials.ListByAgent(resourceGroup.Name, server.Name, agent.Name);
// Delete credential
sqlClient.JobCredentials.Delete(resourceGroup.Name, server.Name, agent.Name, credential.Name);
}
finally
{
// Clean up resource group
context.DeleteResourceGroup(resourceGroup.Name);
}
}
}
public async Task Dispatch([NotNull] Dashboard.DashboardContext context)
{
if (!"GET".Equals(context.Request.Method, StringComparison.InvariantCultureIgnoreCase))
{
context.Response.StatusCode = 405;
return;
}
var periodicJob = new List <PeriodicJob>();
periodicJob.AddRange(JobAgent.GetAllJobStopped());
await context.Response.WriteAsync(JsonConvert.SerializeObject(periodicJob));
}
public async Task Dispatch([NotNull] Dashboard.DashboardContext context)
{
if (!"GET".Equals(context.Request.Method, StringComparison.InvariantCultureIgnoreCase))
{
context.Response.StatusCode = 405;
return;
}
var recurringJob = _connection.GetRecurringJobs();
var periodicJob = new List <PeriodicJob>();
if (recurringJob.Count > 0)
{
recurringJob.ForEach((x) =>
{
periodicJob.Add(new PeriodicJob
{
Id = x.Id,
Cron = x.Cron,
CreatedAt = x.CreatedAt,
Error = x.Error,
LastExecution = x.LastExecution.HasValue ? x.LastExecution.Value.ToString("G") : "N/A",//x.LastExecution,
Method = x.Job.Method.Name,
JobState = "Running",
Class = x.Job.Type.Name,
Queue = x.Queue,
LastJobId = x.LastJobId,
LastJobState = x.LastJobState,
NextExecution = x.NextExecution.HasValue ? x.NextExecution.Value.ToString("G") : "N/A",
Removed = x.Removed,
TimeZoneId = x.TimeZoneId
});
});
}
//Add job was stopped:
periodicJob.AddRange(JobAgent.GetAllJobStopped());
await context.Response.WriteAsync(JsonConvert.SerializeObject(periodicJob));
}
/// <summary>
/// Creates or updates an agent
/// </summary>
/// <param name="model"></param>
/// <returns>The upserted Azure SQL Database Agent</returns>
public AzureSqlElasticJobAgentModel UpsertAgent(AzureSqlElasticJobAgentModel model)
{
// Construct database id
string databaseId = string.Format("/subscriptions/{0}/resourceGroups/{1}/providers/Microsoft.Sql/servers/{2}/databases/{3}",
AzureSqlElasticJobCommunicator.Subscription.Id,
model.ResourceGroupName,
model.ServerName,
model.DatabaseName);
// Set agent params
var param = new JobAgent
{
Location = model.Location,
Tags = model.Tags,
DatabaseId = databaseId
};
// Send response
var resp = Communicator.CreateOrUpdateAgent(model.ResourceGroupName, model.ServerName, model.AgentName, param);
// Return formatted response
return(CreateAgentModelFromResponse(model.ResourceGroupName, model.ServerName, resp));
}
public void TestStartStopGetJobExecution()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
Server server = context.CreateServer(resourceGroup);
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
try
{
// Allow all conenctions for test
sqlClient.FirewallRules.CreateOrUpdate(resourceGroup.Name, server.Name, "allowAll", new FirewallRule
{
StartIpAddress = "0.0.0.0",
EndIpAddress = "255.255.255.255",
});
// Create database only required parameters
string dbName = SqlManagementTestUtilities.GenerateName();
var db1 = sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, server.Name, dbName, new Database()
{
Location = server.Location,
});
Assert.NotNull(db1);
// Create agent
string agentName = "agent";
JobAgent agent = sqlClient.JobAgents.CreateOrUpdate(resourceGroup.Name, server.Name, agentName, new JobAgent
{
Location = server.Location,
DatabaseId = db1.Id
});
// Create credential
JobCredential credential = sqlClient.JobCredentials.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, SqlManagementTestUtilities.DefaultLogin, new JobCredential
{
Username = SqlManagementTestUtilities.DefaultLogin,
Password = SqlManagementTestUtilities.DefaultPassword
});
// Create target group
JobTargetGroup targetGroup = sqlClient.JobTargetGroups.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, "tg1", new JobTargetGroup
{
Members = new List <JobTarget>
{
// server target
new JobTarget
{
ServerName = server.Name,
DatabaseName = db1.Name,
Type = JobTargetType.SqlDatabase,
MembershipType = JobTargetGroupMembershipType.Include,
}
}
});
// Create job that runs once
Job job1 = sqlClient.Jobs.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, "job1", new Job
{
Description = "Test description",
Schedule = new JobSchedule
{
Enabled = true,
Type = JobScheduleType.Once,
}
});
// Create job step
JobStep step1 = sqlClient.JobSteps.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, job1.Name, "step1", new JobStep
{
Credential = credential.Id,
Action = new JobStepAction
{
Value = "SELECT 1"
},
TargetGroup = targetGroup.Id
});
// Create job execution from job1 - do sync so we can be sure a step execution succeeds
JobExecution jobExecution = sqlClient.JobExecutions.Create(resourceGroup.Name, server.Name, agent.Name, job1.Name);
// List executions by agent
sqlClient.JobExecutions.ListByAgent(resourceGroup.Name, server.Name, agent.Name);
// List executions by job
sqlClient.JobExecutions.ListByJob(resourceGroup.Name, server.Name, agent.Name, job1.Name);
// Get root job execution
jobExecution = sqlClient.JobExecutions.Get(resourceGroup.Name, server.Name, agent.Name, job1.Name, jobExecution.JobExecutionId.Value);
// List step executions by root execution
sqlClient.JobStepExecutions.ListByJobExecution(resourceGroup.Name, server.Name, agent.Name, job1.Name, jobExecution.JobExecutionId.Value);
// Get step1 execution
sqlClient.JobStepExecutions.Get(resourceGroup.Name, server.Name, agent.Name, job1.Name, jobExecution.JobExecutionId.Value, step1.Name);
// List target executions by root job execution
sqlClient.JobTargetExecutions.ListByJobExecution(resourceGroup.Name, server.Name, agent.Name, job1.Name, jobExecution.JobExecutionId.Value);
// List target executions by job step
IPage <JobExecution> targetStepExecutions = sqlClient.JobTargetExecutions.ListByStep(resourceGroup.Name, server.Name, agent.Name, job1.Name, jobExecution.JobExecutionId.Value, step1.Name);
Assert.Single(targetStepExecutions);
// Get target execution
sqlClient.JobTargetExecutions.Get(resourceGroup.Name, server.Name, agent.Name, job1.Name, jobExecution.JobExecutionId.Value, step1.Name, Guid.Parse(targetStepExecutions.FirstOrDefault().Name));
// Cancel the job execution
sqlClient.JobExecutions.Cancel(resourceGroup.Name, server.Name, agent.Name, job1.Name, jobExecution.JobExecutionId.Value);
}
finally
{
context.DeleteResourceGroup(resourceGroup.Name);
}
}
}
public void TestCreateUpdateDropJobStep()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
Server server = context.CreateServer(resourceGroup);
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
try
{
// Create database only required parameters
string dbName = SqlManagementTestUtilities.GenerateName();
var db1 = sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, server.Name, dbName, new Database()
{
Location = server.Location,
});
Assert.NotNull(db1);
// Create agent
string agentName = "agent";
JobAgent agent = sqlClient.JobAgents.CreateOrUpdate(resourceGroup.Name, server.Name, agentName, new JobAgent
{
Location = server.Location,
DatabaseId = db1.Id
});
// Create credential
JobCredential credential = sqlClient.JobCredentials.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, SqlManagementTestUtilities.DefaultLogin, new JobCredential
{
Username = SqlManagementTestUtilities.DefaultLogin,
Password = SqlManagementTestUtilities.DefaultPassword
});
// Create target group
JobTargetGroup targetGroup = sqlClient.JobTargetGroups.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, "tg1", new JobTargetGroup
{
Members = new List <JobTarget>
{
// server target
new JobTarget
{
ServerName = server.Name,
Type = JobTargetType.SqlServer,
RefreshCredential = credential.Id,
MembershipType = JobTargetGroupMembershipType.Include,
}
}
});
// Create job that runs once
Job job1 = sqlClient.Jobs.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, "job1", new Job
{
Description = "Test description",
Schedule = new JobSchedule
{
Enabled = true,
Type = JobScheduleType.Once,
}
});
// Create step with min params
JobStep step1 = sqlClient.JobSteps.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, job1.Name, "step1", new JobStep
{
Credential = credential.Id,
Action = new JobStepAction
{
Value = "SELECT 1"
},
TargetGroup = targetGroup.Id
});
// Update step with max params
step1 = sqlClient.JobSteps.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, job1.Name, "step1", new JobStep
{
Credential = credential.Id,
Action = new JobStepAction
{
Value = "SELECT 1",
Source = "Inline",
Type = "TSql"
},
TargetGroup = targetGroup.Id,
ExecutionOptions = new JobStepExecutionOptions
{
InitialRetryIntervalSeconds = 100,
MaximumRetryIntervalSeconds = 1000,
RetryAttempts = 1000,
RetryIntervalBackoffMultiplier = 1.5,
TimeoutSeconds = 10000
},
Output = new JobStepOutput
{
ResourceGroupName = "rg1",
ServerName = "s1",
DatabaseName = "db1",
SchemaName = "dbo",
TableName = "tbl",
SubscriptionId = new Guid(),
Credential = credential.Id,
Type = JobStepOutputType.SqlDatabase
}
});
// List steps by job
sqlClient.JobSteps.ListByJob(resourceGroup.Name, server.Name, agent.Name, job1.Name);
// Delete job step
sqlClient.JobSteps.Delete(resourceGroup.Name, server.Name, agent.Name, job1.Name, step1.Name);
}
finally
{
context.DeleteResourceGroup(resourceGroup.Name);
}
}
}
public void TestCreateUpdateDropJob()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
Server server = context.CreateServer(resourceGroup);
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
try
{
// Create database only required parameters
string dbName = SqlManagementTestUtilities.GenerateName();
var db1 = sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, server.Name, dbName, new Database()
{
Location = server.Location,
});
Assert.NotNull(db1);
// Create agent
string agentName = "agent";
JobAgent agent = sqlClient.JobAgents.CreateOrUpdate(resourceGroup.Name, server.Name, agentName, new JobAgent
{
Location = server.Location,
DatabaseId = db1.Id
});
// Create job that repeats every 5 min from now. Starting now and ending in a day.
Job job1 = sqlClient.Jobs.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, "job1", new Job
{
Description = "Test description",
Schedule = new JobSchedule
{
Enabled = false,
StartTime = new DateTime(),
EndTime = new DateTime().AddDays(1),
Type = JobScheduleType.Recurring,
Interval = "PT5M"
}
});
// Update job to run once
job1 = sqlClient.Jobs.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, "job1", new Job
{
Description = "Test description",
Schedule = new JobSchedule
{
Enabled = true,
Type = JobScheduleType.Once,
}
});
// List job
sqlClient.Jobs.ListByAgent(resourceGroup.Name, server.Name, agent.Name);
// Delete job
sqlClient.Jobs.Delete(resourceGroup.Name, server.Name, agent.Name, job1.Name);
}
finally
{
context.DeleteResourceGroup(resourceGroup.Name);
}
}
}
public void TestCreateUpdateDropTargetGroup()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
Server server = context.CreateServer(resourceGroup);
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
try
{
// Create database only required parameters
string dbName = SqlManagementTestUtilities.GenerateName();
var db1 = sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, server.Name, dbName, new Database()
{
Location = server.Location,
});
Assert.NotNull(db1);
// Create agent
string agentName = "agent";
JobAgent agent = sqlClient.JobAgents.CreateOrUpdate(resourceGroup.Name, server.Name, agentName, new JobAgent
{
Location = server.Location,
DatabaseId = db1.Id
});
// Create credential
JobCredential credential = sqlClient.JobCredentials.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, SqlManagementTestUtilities.DefaultLogin, new JobCredential
{
Username = SqlManagementTestUtilities.DefaultLogin,
Password = SqlManagementTestUtilities.DefaultPassword
});
// Create target group
JobTargetGroup targetGroup = sqlClient.JobTargetGroups.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, "tg1", new JobTargetGroup
{
Members = new List <JobTarget>
{
// server target
new JobTarget
{
ServerName = "s1",
Type = JobTargetType.SqlServer,
RefreshCredential = credential.Id,
MembershipType = JobTargetGroupMembershipType.Include,
}
}
});
// Update target group with each type of target
targetGroup = sqlClient.JobTargetGroups.CreateOrUpdate(resourceGroup.Name, server.Name, agent.Name, "tg1", new JobTargetGroup
{
Members = new List <JobTarget>
{
// server target
new JobTarget
{
ServerName = "s1",
Type = JobTargetType.SqlServer,
RefreshCredential = credential.Id,
MembershipType = JobTargetGroupMembershipType.Include,
},
// db target
new JobTarget
{
DatabaseName = "db1",
ServerName = "s1",
Type = JobTargetType.SqlDatabase,
MembershipType = JobTargetGroupMembershipType.Include,
},
// shard map target
new JobTarget
{
ShardMapName = "sm1",
DatabaseName = "db1",
ServerName = "s1",
RefreshCredential = credential.Id,
Type = JobTargetType.SqlShardMap,
MembershipType = JobTargetGroupMembershipType.Exclude,
},
// elastic pool target
new JobTarget
{
ElasticPoolName = "ep1",
ServerName = "s1",
RefreshCredential = credential.Id,
Type = JobTargetType.SqlElasticPool,
MembershipType = JobTargetGroupMembershipType.Exclude,
},
}
});
// List target groups
sqlClient.JobTargetGroups.ListByAgent(resourceGroup.Name, server.Name, agent.Name);
// Delete target group
sqlClient.JobTargetGroups.Delete(resourceGroup.Name, server.Name, agent.Name, targetGroup.Name);
}
finally
{
context.DeleteResourceGroup(resourceGroup.Name);
}
}
}
/// <summary>
/// Convert JobAgent model to AzureSqlDatabaseAgentModel
/// </summary>
/// <param name="resourceGroupName">The resource group the server is in</param>
/// <param name="serverName">The server the agent is in</param>
/// <param name="resp">The management client server response to convert</param>
/// <returns>The converted agent model</returns>
private static AzureSqlElasticJobAgentModel CreateAgentModelFromResponse(string resourceGroupName, string serverName, JobAgent resp)
{
string databaseName = new ResourceIdentifier(resp.DatabaseId).ResourceName;
int? workerCount = resp.Sku.Capacity;
AzureSqlElasticJobAgentModel agent = new AzureSqlElasticJobAgentModel
{
ResourceGroupName = resourceGroupName,
ServerName = serverName,
AgentName = resp.Name,
Location = resp.Location,
DatabaseName = databaseName,
WorkerCount = workerCount,
ResourceId = resp.Id,
Tags = TagsConversionHelper.CreateTagDictionary(TagsConversionHelper.CreateTagHashtable(resp.Tags), false),
DatabaseId = resp.DatabaseId,
State = resp.State,
Type = resp.Type
};
return(agent);
}
/// <summary>
/// Creates or updates a job agent.
/// </summary>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='resourceGroupName'>
/// The name of the resource group that contains the resource. You can obtain
/// this value from the Azure Resource Manager API or the portal.
/// </param>
/// <param name='serverName'>
/// The name of the server.
/// </param>
/// <param name='jobAgentName'>
/// The name of the job agent to be created or updated.
/// </param>
/// <param name='parameters'>
/// The requested job agent resource state.
/// </param>
/// <param name='cancellationToken'>
/// The cancellation token.
/// </param>
public static async Task <JobAgent> BeginCreateOrUpdateAsync(this IJobAgentsOperations operations, string resourceGroupName, string serverName, string jobAgentName, JobAgent parameters, CancellationToken cancellationToken = default(CancellationToken))
{
using (var _result = await operations.BeginCreateOrUpdateWithHttpMessagesAsync(resourceGroupName, serverName, jobAgentName, parameters, null, cancellationToken).ConfigureAwait(false))
{
return(_result.Body);
}
}
/// <summary>
/// Creates or updates a job agent.
/// </summary>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='resourceGroupName'>
/// The name of the resource group that contains the resource. You can obtain
/// this value from the Azure Resource Manager API or the portal.
/// </param>
/// <param name='serverName'>
/// The name of the server.
/// </param>
/// <param name='jobAgentName'>
/// The name of the job agent to be created or updated.
/// </param>
/// <param name='parameters'>
/// The requested job agent resource state.
/// </param>
public static JobAgent BeginCreateOrUpdate(this IJobAgentsOperations operations, string resourceGroupName, string serverName, string jobAgentName, JobAgent parameters)
{
return(operations.BeginCreateOrUpdateAsync(resourceGroupName, serverName, jobAgentName, parameters).GetAwaiter().GetResult());
}
/// <summary>
/// PUT: Creates an Azure SQL Database Agent
/// </summary>
/// <param name="resourceGroupName">The resource group name</param>
/// <param name="serverName">The server name</param>
/// <param name="agentName">The agent name</param>
/// <param name="parameters">The agent's create parameters</param>
/// <returns>The newly created agent</returns>
public JobAgent CreateOrUpdateAgent(string resourceGroupName, string serverName, string agentName, JobAgent parameters)
{
return(GetCurrentSqlClient().JobAgents.CreateOrUpdate(resourceGroupName, serverName, agentName, parameters));
}
