/// <summary>
/// Retrieves one or more firewall rules on the specified server.
/// </summary>
/// <param name="serverName">The name of the server to retrieve firewall rules for.</param>
/// <param name="ruleName">
/// The specific name of the rule to retrieve, or <c>null</c> to
/// retrieve all rules on the specified server.
/// </param>
/// <returns>A list of firewall rules on the server.</returns>
internal IEnumerable <SqlDatabaseServerFirewallRuleContext> GetAzureSqlDatabaseServerFirewallRuleProcess(
string serverName,
string ruleName)
{
// Get the SQL management client for the current subscription
SqlManagementClient sqlManagementClient = SqlDatabaseCmdletBase.GetCurrentSqlClient();
// Retrieve the list of databases
FirewallRuleListResponse response = sqlManagementClient.FirewallRules.List(serverName);
IEnumerable <FirewallRule> firewallRules = response.FirewallRules;
if (!string.IsNullOrEmpty(ruleName))
{
// Firewall rule name is specified, find the one
// with the specified rule name and return that.
firewallRules = firewallRules.Where(p => p.Name == ruleName);
if (!firewallRules.Any())
{
throw new ItemNotFoundException(string.Format(
CultureInfo.InvariantCulture,
Resources.GetAzureSqlDatabaseServerFirewallRuleNotFound,
ruleName,
serverName));
}
}
else
{
// Firewall rule name is not specified, return all
// firewall rules.
}
IEnumerable <SqlDatabaseServerFirewallRuleContext> processResult = firewallRules.Select(p => new SqlDatabaseServerFirewallRuleContext()
{
OperationDescription = CommandRuntime.ToString(),
OperationStatus = Services.Constants.OperationSuccess,
OperationId = response.RequestId,
ServerName = serverName,
RuleName = p.Name,
StartIpAddress = p.StartIPAddress,
EndIpAddress = p.EndIPAddress
});
return(processResult);
}
/// <summary>
/// Process the get quota request using the supplied server name. This will use the REST API
/// to make the request.
/// </summary>
/// <param name="quotaName"></param>
private void ProcessWithServerName(string quotaName)
{
try
{
SqlManagementClient sqlManagementClient = GetCurrentSqlClient();
// Retrieve the list of servers
QuotaListResponse response = sqlManagementClient.Quotas.List(this.ServerName);
IEnumerable <Quota> quotas = response.Quotas;
if (!string.IsNullOrEmpty(quotaName))
{
// Quota name is specified, find the one with the
// same name.
quotas = response.Quotas.Where(q => q.Name == quotaName);
if (quotas.Count() == 0)
{
throw new ItemNotFoundException(string.Format(
CultureInfo.InvariantCulture,
Resources.GetAzureSqlDatabaseServerNotFound,
quotaName));
}
}
// Construct the result
IEnumerable <SqlDatabaseServerQuotaContext> processResult = quotas.Select(
quota => new SqlDatabaseServerQuotaContext
{
OperationStatus = Services.Constants.OperationSuccess,
OperationDescription = CommandRuntime.ToString(),
OperationId = response.RequestId,
ServerName = this.ServerName,
Name = quota.Name,
State = quota.State,
Type = quota.Type,
Value = quota.Value,
});
this.WriteObject(processResult);
}
catch (Exception ex)
{
this.WriteErrorDetails(ex);
}
}
public void TestGetAndListElasticPool()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
Server server = context.CreateServer(resourceGroup);
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
Dictionary <string, ElasticPool> inputs = new Dictionary <string, ElasticPool>();
// Create elastic pools to run the get/List tests on.
for (int i = 0; i < 3; i++)
{
string name = SqlManagementTestUtilities.GenerateName();
inputs.Add(name, new ElasticPool()
{
Location = server.Location,
Sku = SqlTestConstants.DefaultElasticPoolSku(),
});
sqlClient.ElasticPools.CreateOrUpdate(resourceGroup.Name, server.Name, name, inputs[name]);
}
// Get each database and compare to the results of create database
//
foreach (var ep in inputs)
{
var response = sqlClient.ElasticPools.Get(resourceGroup.Name, server.Name, ep.Key);
SqlManagementTestUtilities.ValidateElasticPool(ep.Value, response, ep.Key);
}
var listResponse = sqlClient.ElasticPools.ListByServer(resourceGroup.Name, server.Name);
Assert.Equal(inputs.Count(), listResponse.Count());
foreach (var ep in listResponse)
{
SqlManagementTestUtilities.ValidateElasticPool(inputs[ep.Name], ep, ep.Name);
}
foreach (var ep in inputs)
{
SqlManagementTestUtilities.ValidateElasticPool(ep.Value, listResponse.Single(e => e.Name == ep.Key), ep.Key);
}
}
}
/// <summary>
/// Creates a new server in the current subscription.
/// </summary>
/// <param name="adminLogin">
/// The administrator login name for the new server.
/// </param>
/// <param name="adminLoginPassword">
/// The administrator login password for the new server.
/// </param>
/// <param name="location">
/// The location in which to create the new server.
/// </param>
/// <returns>The context to the newly created server.</returns>
internal SqlDatabaseServerContext NewAzureSqlDatabaseServerProcess(
string adminLogin,
string adminLoginPassword,
string location,
float?version)
{
// Do nothing if force is not specified and user cancelled the operation
if (!Force.IsPresent &&
!ShouldProcess(
Resources.NewAzureSqlDatabaseServerDescription,
Resources.NewAzureSqlDatabaseServerWarning,
Resources.ShouldProcessCaption))
{
return(null);
}
// Get the SQL management client for the current subscription
SqlManagementClient sqlManagementClient = GetCurrentSqlClient();
// Set the retry policty to not retry attempts.
sqlManagementClient.SetRetryPolicy(new RetryPolicy(new DefaultHttpErrorDetectionStrategy(), 0));
// Issue the create server request
ServerCreateResponse response = sqlManagementClient.Servers.Create(
new ServerCreateParameters()
{
Location = location,
AdministratorUserName = adminLogin,
AdministratorPassword = adminLoginPassword,
Version = version.HasValue ? version.Value.ToString("F1") : null
});
SqlDatabaseServerContext operationContext = new SqlDatabaseServerContext()
{
OperationStatus = Services.Constants.OperationSuccess,
OperationDescription = CommandRuntime.ToString(),
OperationId = response.RequestId,
ServerName = response.ServerName,
Location = location,
AdministratorLogin = adminLogin,
};
return(operationContext);
}
public async void TestCreateUpdateGetServerVulnerabilityAssessments()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
Server server = context.CreateServer(resourceGroup);
// Turn ON Server ATP(threat detection) as a prerequisite to use VA
ServerSecurityAlertPolicy updatedServerPolicy = new ServerSecurityAlertPolicy
{
State = SecurityAlertsPolicyState.Enabled,
EmailAccountAdmins = true
};
//Set security alert policy for server
sqlClient.ServerSecurityAlertPolicies.CreateOrUpdate(resourceGroup.Name, server.Name, updatedServerPolicy);
// Verify Policy is empty to begin with
ServerVulnerabilityAssessment policyThatWasReceived = sqlClient.ServerVulnerabilityAssessments.Get(resourceGroup.Name, server.Name);
Assert.Null(policyThatWasReceived.StorageContainerPath);
Assert.Null(policyThatWasReceived.StorageAccountAccessKey);
Assert.False(policyThatWasReceived.RecurringScans.IsEnabled);
// Set policy and then get policy and verify correctness
ServerVulnerabilityAssessment policyThatWasSet = await SetPolicy(context, sqlClient, resourceGroup, server);
policyThatWasReceived = sqlClient.ServerVulnerabilityAssessments.Get(resourceGroup.Name, server.Name);
Assert.Equal(policyThatWasSet.StorageContainerPath, policyThatWasReceived.StorageContainerPath);
Assert.Null(policyThatWasSet.StorageAccountAccessKey);
Assert.Equal(policyThatWasSet.RecurringScans.IsEnabled, policyThatWasReceived.RecurringScans.IsEnabled);
SqlManagementTestUtilities.AssertCollection(policyThatWasSet.RecurringScans.Emails, policyThatWasReceived.RecurringScans.Emails);
Assert.Equal(policyThatWasSet.RecurringScans.EmailSubscriptionAdmins, policyThatWasReceived.RecurringScans.EmailSubscriptionAdmins);
// Delete policy and then get policy and verify correctness
sqlClient.ServerVulnerabilityAssessments.Delete(resourceGroup.Name, server.Name);
// Get policy after deletion
policyThatWasReceived = sqlClient.ServerVulnerabilityAssessments.Get(resourceGroup.Name, server.Name);
Assert.Null(policyThatWasReceived.StorageContainerPath);
Assert.Null(policyThatWasReceived.StorageAccountAccessKey);
Assert.False(policyThatWasReceived.RecurringScans.IsEnabled);
};
}
/// <summary>
/// Retrieve information on latest service objective with service objective
/// </summary>
/// <param name="serviceObjective">The service objective to refresh.</param>
/// <returns>
/// An object containing the information about the specific service objective.
/// </returns>
public ServiceObjective GetServiceObjective(ServiceObjective serviceObjectiveToRefresh)
{
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Get the SQL management client
SqlManagementClient sqlManagementClient = this.subscription.CreateClient <SqlManagementClient>();
this.AddTracingHeaders(sqlManagementClient);
// Retrieve the specified database
ServiceObjectiveGetResponse response = sqlManagementClient.ServiceObjectives.Get(
this.serverName,
serviceObjectiveToRefresh.Id.ToString());
// Construct the resulting Database object
ServiceObjective serviceObjective = CreateServiceObjectiveFromResponse(response);
return(serviceObjective);
}
/// <summary>
/// Retrieves the list of all databases' operations on the server.
/// </summary>
/// <returns>An array of all operations on the server.</returns>
public DatabaseOperation[] GetDatabasesOperations()
{
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Get the SQL management client
SqlManagementClient sqlManagementClient = this.subscription.CreateClient <SqlManagementClient>();
this.AddTracingHeaders(sqlManagementClient);
// Retrieve the operations on specified server
// We do not validate the number of operations returned since it's possible that there is no
// database operations on a new created server.
DatabaseOperationListResponse response = sqlManagementClient.DatabaseOperations.ListByServer(
this.serverName);
// Construct the resulting database operations array
DatabaseOperation[] operations = response.Select(operation => CreateDatabaseOperationsFromResponse(operation)).ToArray();
return(operations);
}
protected override bool CheckExistence()
{
if (Parameters.Properties.ResourceGroupExists)
{
using (var client = new SqlManagementClient(GetCredentials()))
{
var serverList = client.Servers.ListAsync(Parameters.Tenant.SiteName).Result;
var server = serverList.Servers.FirstOrDefault(s => s.Name.Equals(Parameters.GetSiteName(Position)));
//if (server != null)
//{
// var getResult = client.AuditingPolicy.GetServerPolicyAsync(Parameters.Tenant.SiteName, Parameters.GetSiteName(Position)).Result;
// return getResult.AuditingPolicy != null;
//}
}
}
return(false);
}
public void TestCheckServerNameInvalid()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
string serverName = SqlManagementTestUtilities.GenerateName().ToUpperInvariant(); // upper case is invalid
CheckNameAvailabilityResponse response = sqlClient.Servers.CheckNameAvailability(new CheckNameAvailabilityRequest
{
Name = serverName,
});
Assert.False(response.Available);
Assert.Equal(serverName, response.Name);
Assert.NotNull(response.Message);
Assert.Equal(CheckNameAvailabilityReason.Invalid, response.Reason);
}
}
public void FailoverPrimary()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
var rg = context.CreateResourceGroup(ManagedInstanceTestUtilities.Region);
ManagedInstance managedInstance = context.CreateManagedInstance(rg);
Assert.NotNull(managedInstance);
var resourceGroupName = rg.Name;
// Wait for first full backup to finish
if (HttpMockServer.Mode == HttpRecorderMode.Record)
{
Thread.Sleep(TimeSpan.FromMinutes(6));
}
sqlClient.ManagedInstances.Failover(resourceGroupName, managedInstance.Name, ReplicaType.Primary);
}
}
public void ShortTermRetentionOnLiveDatabase()
{
using (SqlManagementTestContext Context = new SqlManagementTestContext(this))
{
SqlManagementClient sqlClient = Context.GetClient <SqlManagementClient>();
ResourceGroup resourceGroup = Context.CreateResourceGroup();
ManagedInstance managedInstance = CreateManagedInstance(Context, sqlClient, resourceGroup, "ShortTermRetentionOnLiveDatabase");
// Create managed database
//
string dbName = SqlManagementTestUtilities.GenerateName(_testPrefix);
var db1 = sqlClient.ManagedDatabases.CreateOrUpdate(resourceGroup.Name, managedInstance.Name, dbName, new ManagedDatabase()
{
Location = managedInstance.Location,
});
Assert.NotNull(db1);
int basicRetention = 7;
int invalidValue = 3;
int validValue = 20;
// Attempt to increase retention period to 7 days and verfiy that the operation succeeded.
ManagedBackupShortTermRetentionPolicy parameters0 = new ManagedBackupShortTermRetentionPolicy(retentionDays: basicRetention);
sqlClient.ManagedBackupShortTermRetentionPolicies.CreateOrUpdateWithHttpMessagesAsync(resourceGroup.Name, managedInstance.Name, dbName, parameters0);
Microsoft.Rest.ClientRuntime.Azure.TestFramework.TestUtilities.Wait(TimeSpan.FromSeconds(3));
ManagedBackupShortTermRetentionPolicy policy = sqlClient.ManagedBackupShortTermRetentionPolicies.Get(resourceGroup.Name, managedInstance.Name, dbName);
Assert.Equal(basicRetention, policy.RetentionDays);
// Attempt to increase retention period to 3 days and verfiy that the operation fails.
ManagedBackupShortTermRetentionPolicy parameters1 = new ManagedBackupShortTermRetentionPolicy(retentionDays: invalidValue);
sqlClient.ManagedBackupShortTermRetentionPolicies.CreateOrUpdateWithHttpMessagesAsync(resourceGroup.Name, managedInstance.Name, dbName, parameters1);
Microsoft.Rest.ClientRuntime.Azure.TestFramework.TestUtilities.Wait(TimeSpan.FromSeconds(3));
policy = sqlClient.ManagedBackupShortTermRetentionPolicies.Get(resourceGroup.Name, managedInstance.Name, dbName);
Assert.Equal(basicRetention, policy.RetentionDays);
// Attempt to increase retention period to 20 days and verfiy that the operation succeeded .
ManagedBackupShortTermRetentionPolicy parameters2 = new ManagedBackupShortTermRetentionPolicy(retentionDays: validValue);
sqlClient.ManagedBackupShortTermRetentionPolicies.CreateOrUpdateWithHttpMessagesAsync(resourceGroup.Name, managedInstance.Name, dbName, parameters2);
Microsoft.Rest.ClientRuntime.Azure.TestFramework.TestUtilities.Wait(TimeSpan.FromSeconds(3));
policy = sqlClient.ManagedBackupShortTermRetentionPolicies.Get(resourceGroup.Name, managedInstance.Name, dbName);
Assert.Equal(validValue, policy.RetentionDays);
}
}
protected override bool CheckExistence()
{
if (Parameters.Properties.ResourceGroupExists)
{
using (var client = new SqlManagementClient(GetCredentials()))
{
var serverList = client.Servers.ListAsync(Parameters.Tenant.SiteName).Result;
var server = serverList.Servers.FirstOrDefault(s => s.Name.Equals(Parameters.GetSiteName(Position)));
if (server != null)
{
var databaseList = client.Databases.ListAsync(Parameters.Tenant.SiteName, Parameters.GetSiteName(Position)).Result;
return(databaseList.Databases.Any(database => database.Name.Equals(Parameters.Tenant.DatabaseName)));
}
}
}
return(false);
}
static void CreateSqlServer(TokenCloudCredentials creds)
{
var client = new SqlManagementClient(creds);
var someProperties = new ServerCreateOrUpdateProperties
{
AdministratorLogin = adminAccountName,
AdministratorLoginPassword = adminAccountPwd,
Version = "12"
};
var parameters = new ServerCreateOrUpdateParameters(someProperties, location);
// { "AuthorizationFailed: The client 'xxxxx-xxxxx-xxxxx-xxxxx'
// with object id 'xxxxx-xxxxx-xxxxx-xxxxx' does not have authorization
// to perform action 'Microsoft.Sql/servers/write' over scope
// '/subscriptions/xxxxx-xxxxx-xxxxx-xxxxx/resourceGroups/MY_RESOURCE_GROUP_NAME/providers/Microsoft.Sql/servers/MY_SERVER_NAME'."}
ServerGetResponse aCreateResponse
= client.Servers.CreateOrUpdate(resourceGroupName, serverName, parameters);
}
public void TestLongTermRetentionV2ResourceGroupBasedBackups()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
Server server = context.CreateServer(resourceGroup);
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
Database database = sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, server.Name, SqlManagementTestUtilities.GenerateName(), new Database {
Location = server.Location
});
// Get the backups under the resource group, server and database. Assert there are no backups returned.
//
IPage <LongTermRetentionBackup> backups = sqlClient.LongTermRetentionBackups.ListByResourceGroupLocation(resourceGroup.Name, server.Location);
backups = sqlClient.LongTermRetentionBackups.ListByResourceGroupServer(resourceGroup.Name, server.Location, server.Name);
backups = sqlClient.LongTermRetentionBackups.ListByResourceGroupDatabase(resourceGroup.Name, server.Location, server.Name, database.Name);
Assert.Throws(typeof(CloudException), () => sqlClient.LongTermRetentionBackups.GetByResourceGroup(resourceGroup.Name, server.Location, server.Name, database.Name, "backup"));
}
}
/// <summary>
/// Retrieve a specific database from the current context
/// </summary>
/// <param name="databaseName">The name of the database to retrieve</param>
/// <returns>A database object</returns>
public Database GetDatabase(string databaseName)
{
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Get the SQL management client
SqlManagementClient sqlManagementClient = this.subscription.CreateClient <SqlManagementClient>();
this.AddTracingHeaders(sqlManagementClient);
// Retrieve the specified database
DatabaseGetResponse response = sqlManagementClient.Databases.Get(
this.serverName,
databaseName);
// Construct the resulting Database object
Database database = CreateDatabaseFromResponse(response);
return(database);
}
/// <summary>
/// Retrieves one or more servers in the current subscription.
/// </summary>
/// <param name="serverName">
/// The specific name of the server to retrieve, or <c>null</c> to
/// retrieve all servers in the current subscription.
/// </param>
/// <returns>A list of servers in the subscription.</returns>
internal IEnumerable <SqlDatabaseServerContext> GetAzureSqlDatabaseServersProcess(string serverName)
{
// Get the SQL management client for the current subscription
SqlManagementClient sqlManagementClient = GetCurrentSqlClient();
// Retrieve the list of servers
ServerListResponse response = sqlManagementClient.Servers.List();
IEnumerable <Management.Sql.Models.Server> servers = response.Servers;
if (!string.IsNullOrEmpty(serverName))
{
// Server name is specified, find the one with the
// specified rule name and return that.
servers = response.Servers.Where(s => s.Name == serverName);
if (!servers.Any())
{
throw new ItemNotFoundException(string.Format(
CultureInfo.InvariantCulture,
Resources.GetAzureSqlDatabaseServerNotFound,
serverName));
}
}
else
{
// Server name is not specified, return all servers
// in the subscription.
}
// Construct the result
IEnumerable <SqlDatabaseServerContext> processResult = servers.Select(server => new SqlDatabaseServerContext
{
OperationStatus = Services.Constants.OperationSuccess,
OperationDescription = CommandRuntime.ToString(),
OperationId = response.RequestId,
ServerName = server.Name,
Location = server.Location,
Version = server.Version,
AdministratorLogin = server.AdministratorUserName,
State = server.State,
});
return(processResult);
}
/// <summary>
/// Updates a firewall rule on the specified server.
/// </summary>
/// <param name="serverName">The name of the server containing the firewall rule.</param>
/// <param name="ruleName">The name of the firewall rule to update.</param>
/// <param name="startIpAddress">The starting IP address for the firewall rule.</param>
/// <param name="endIpAddress">The ending IP address for the firewall rule.</param>
/// <returns>The updated firewall rule.</returns>
internal SqlDatabaseServerFirewallRuleContext SetAzureSqlDatabaseServerFirewallRuleProcess(
string serverName,
string ruleName,
string startIpAddress,
string endIpAddress)
{
// Do nothing if force is not specified and user cancelled the operation
if (!Force.IsPresent &&
!ShouldProcess(
string.Format(CultureInfo.InvariantCulture, Resources.SetAzureSqlDatabaseServerFirewallRuleDescription, ruleName, serverName),
string.Format(CultureInfo.InvariantCulture, Resources.SetAzureSqlDatabaseServerFirewallRuleWarning, ruleName, serverName),
Resources.ShouldProcessCaption))
{
return(null);
}
// Get the SQL management client for the current subscription
SqlManagementClient sqlManagementClient = GetCurrentSqlClient();
// Update the specified firewall rule
FirewallRuleUpdateResponse response = sqlManagementClient.FirewallRules.Update(
serverName,
ruleName,
new FirewallRuleUpdateParameters()
{
Name = ruleName,
StartIPAddress = startIpAddress,
EndIPAddress = endIpAddress,
});
SqlDatabaseServerFirewallRuleContext operationContext = new SqlDatabaseServerFirewallRuleContext()
{
OperationDescription = CommandRuntime.ToString(),
OperationStatus = Services.Constants.OperationSuccess,
OperationId = response.RequestId,
ServerName = serverName,
RuleName = ruleName,
StartIpAddress = response.FirewallRule.StartIPAddress,
EndIpAddress = response.FirewallRule.EndIPAddress
};
return(operationContext);
}
/// <summary>
/// Retrieve information on operation with the guid
/// </summary>
/// <param name="OperationGuid">The Guid of the operation to retrieve.</param>
/// <returns>An object containing the information about the specific operation.</returns>
public DatabaseOperation GetDatabaseOperation(Guid OperationGuid)
{
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Get the SQL management client
SqlManagementClient sqlManagementClient = this.subscription.CreateClient <SqlManagementClient>();
this.AddTracingHeaders(sqlManagementClient);
// Retrieve the specified Operation
DatabaseOperationGetResponse response = sqlManagementClient.DatabaseOperations.Get(
this.serverName,
OperationGuid.ToString());
// Construct the resulting Operation object
DatabaseOperation operation = CreateDatabaseOperationFromResponse(response);
return(operation);
}
private void CreateFirewallRule()
{
using (var client = new SqlManagementClient(GetCredentials()))
{
// Sleep for 30 seconds to give the Firewall setting some time
// known bug if switched on too fast
Thread.Sleep(30000);
var firewallResult = client.FirewallRules.CreateOrUpdateAsync(Parameters.Tenant.SiteName, Parameters.GetSiteName(Position), "OpenAll",
new FirewallRuleCreateOrUpdateParameters()
{
Properties = new FirewallRuleCreateOrUpdateProperties()
{
StartIpAddress = "0.0.0.0",
EndIpAddress = "255.255.255.255",
}
}).Result;
}
}
/// <summary>
/// Update a database on the server.
/// </summary>
/// <param name="databaseName">The name of the database to modify.</param>
/// <param name="newDatabaseName">The new name of the database.</param>
/// <param name="databaseMaxSizeInGB">The new maximum size of the database.</param>
/// <param name="databaseEdition">The new edition of the database.</param>
/// <param name="serviceObjective">The new service objective of the database.</param>
/// <returns>The updated database.</returns>
public Database UpdateDatabase(
string databaseName,
string newDatabaseName,
int?databaseMaxSizeInGB,
DatabaseEdition?databaseEdition,
ServiceObjective serviceObjective)
{
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Get the SQL management client
SqlManagementClient sqlManagementClient = this.subscription.CreateClient <SqlManagementClient>();
this.AddTracingHeaders(sqlManagementClient);
// Retrieve the specified database
DatabaseGetResponse database = sqlManagementClient.Databases.Get(
this.serverName,
databaseName);
// Update the database with the new properties
DatabaseUpdateResponse response = sqlManagementClient.Databases.Update(
this.serverName,
databaseName,
new DatabaseUpdateParameters()
{
Id = database.Id,
Name = !string.IsNullOrEmpty(newDatabaseName) ?
newDatabaseName : database.Name,
Edition = databaseEdition.HasValue && (databaseEdition != DatabaseEdition.None) ?
databaseEdition.ToString() : (database.Edition ?? string.Empty),
CollationName = database.CollationName ?? string.Empty,
MaximumDatabaseSizeInGB = databaseMaxSizeInGB.HasValue ?
databaseMaxSizeInGB.Value : database.MaximumDatabaseSizeInGB,
ServiceObjectiveId = serviceObjective != null ?
serviceObjective.Id.ToString() : null,
});
// Construct the resulting Database object
Database updatedDatabase = CreateDatabaseFromResponse(response);
return(updatedDatabase);
}
public async void TestCreateUpdateGetDatabaseVulnerabilityAssessments()
{
string testPrefix = "sqlvulnerabilityassessmentcrudtest-";
SqlManagementClient sqlClient = m_testFixture.Context.GetClient <SqlManagementClient>();
// Create managed database
//
string dbName = SqlManagementTestUtilities.GenerateName(testPrefix);
var db1 = sqlClient.ManagedDatabases.CreateOrUpdate(m_testFixture.ResourceGroup.Name, m_testFixture.ManagedInstance.Name, dbName, new ManagedDatabase()
{
Location = m_testFixture.ManagedInstance.Location,
});
Assert.NotNull(db1);
// Verify Policy is empty to begin with
DatabaseVulnerabilityAssessment policyThatWasReceived = sqlClient.ManagedDatabaseVulnerabilityAssessments.Get(m_testFixture.ResourceGroup.Name, m_testFixture.ManagedInstance.Name, dbName);
Assert.Null(policyThatWasReceived.StorageContainerPath);
Assert.Null(policyThatWasReceived.StorageContainerSasKey);
Assert.False(policyThatWasReceived.RecurringScans.IsEnabled);
// Set policy and then get policy and verify correctness
DatabaseVulnerabilityAssessment policyThatWasSet = await SetPolicy(m_testFixture.Context, sqlClient, m_testFixture.ResourceGroup, m_testFixture.ManagedInstance, dbName);
policyThatWasReceived = sqlClient.ManagedDatabaseVulnerabilityAssessments.Get(m_testFixture.ResourceGroup.Name, m_testFixture.ManagedInstance.Name, dbName);
Assert.Equal(policyThatWasSet.StorageContainerPath, policyThatWasReceived.StorageContainerPath);
Assert.Null(policyThatWasSet.StorageContainerSasKey);
Assert.Equal(policyThatWasSet.RecurringScans.IsEnabled, policyThatWasReceived.RecurringScans.IsEnabled);
SqlManagementTestUtilities.AssertCollection(policyThatWasSet.RecurringScans.Emails, policyThatWasReceived.RecurringScans.Emails);
Assert.Equal(policyThatWasSet.RecurringScans.EmailSubscriptionAdmins, policyThatWasReceived.RecurringScans.EmailSubscriptionAdmins);
// Delete policy and then get policy and verify correctness
sqlClient.ManagedDatabaseVulnerabilityAssessments.Delete(m_testFixture.ResourceGroup.Name, m_testFixture.ManagedInstance.Name, dbName);
// Get policy after deletion
policyThatWasReceived = sqlClient.ManagedDatabaseVulnerabilityAssessments.Get(m_testFixture.ResourceGroup.Name, m_testFixture.ManagedInstance.Name, dbName);
Assert.Null(policyThatWasReceived.StorageContainerPath);
Assert.Null(policyThatWasReceived.StorageContainerSasKey);
Assert.False(policyThatWasReceived.RecurringScans.IsEnabled);
}
public void TestMoveOutOfHyperscalePoolAndGetActivity()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup(TestEnvironmentUtilities.DefaultStagePrimaryLocation);
Server server = context.CreateServer(resourceGroup, TestEnvironmentUtilities.DefaultStagePrimaryLocation);
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
// Create a Hyperscale elasticPool with specified replica count of 2
//
string epName = SqlManagementTestUtilities.GenerateName();
var epInput = new ElasticPool()
{
Location = server.Location,
Sku = new Microsoft.Azure.Management.Sql.Models.Sku("HS_Gen5_4"),
HighAvailabilityReplicaCount = 2
};
var returnedEp = sqlClient.ElasticPools.CreateOrUpdate(resourceGroup.Name, server.Name, epName, epInput);
SqlManagementTestUtilities.ValidateElasticPool(epInput, returnedEp, epName);
// Create a database in elastic pool
string dbName = SqlManagementTestUtilities.GenerateName();
var dbInput = new Database()
{
Location = server.Location,
ElasticPoolId = returnedEp.Id
};
sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, server.Name, dbName, dbInput);
// Move database out of elastic pool
dbInput = new Database()
{
Location = server.Location,
Sku = new Microsoft.Azure.Management.Sql.Models.Sku("HS_Gen5_4")
};
var returnedDb = sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, server.Name, dbName, dbInput);
// Verify database has the same value for replica count as pool
Assert.Equal(2, returnedDb.HighAvailabilityReplicaCount);
}
}
static DatabaseCreateOrUpdateResponse CreateOrUpdateDatabase(SqlManagementClient sqlMgmtClient, string resourceGroupName, string serverName, string databaseName, string databaseEdition, string databasePerfLevel)
{
// Retrieve the server that will host this database
Server currentServer = sqlMgmtClient.Servers.Get(resourceGroupName, serverName).Server;
// Create a database: configure create or update parameters and properties explicitly
DatabaseCreateOrUpdateParameters newDatabaseParameters = new DatabaseCreateOrUpdateParameters()
{
Location = currentServer.Location,
Properties = new DatabaseCreateOrUpdateProperties()
{
CreateMode = DatabaseCreateMode.Default,
Edition = databaseEdition,
RequestedServiceObjectiveName = databasePerfLevel
}
};
DatabaseCreateOrUpdateResponse dbResponse = sqlMgmtClient.Databases.CreateOrUpdate(resourceGroupName, serverName, databaseName, newDatabaseParameters);
return(dbResponse);
}
public void TestCheckServerNameAlreadyExists()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
Server server = context.CreateServer(resourceGroup);
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
// Check name of a server that we know exists (because we just created it)
CheckNameAvailabilityResponse response = sqlClient.Servers.CheckNameAvailability(new CheckNameAvailabilityRequest
{
Name = server.Name
});
Assert.False(response.Available);
Assert.Equal(server.Name, response.Name);
Assert.NotNull(response.Message);
Assert.Equal(CheckNameAvailabilityReason.AlreadyExists, response.Reason);
}
}
private async Task CreateTenantAsync(TenantCreationDetails details)
{
var sqlAzureManagementConfiguration = ConfigOptions.Value;
var cred = await CredentialFactory.GetAsync(SqlAzureManagementApiResource);
var c = new SqlManagementClient(cred);
c.SubscriptionId = sqlAzureManagementConfiguration.SubscriptionId;
Stuff.Noop(c.Capabilities);
foreach (var s in c.Servers.List())
{
if (s.Name == sqlAzureManagementConfiguration.ServerName)
{
foreach (var db in c.Databases.ListByServer(sqlAzureManagementConfiguration.ResourceGroupName, s.Name))
{
if (db.Name == sqlAzureManagementConfiguration.TenantModelDatabaseName)
{
var newDatabaseName = string.Format(sqlAzureManagementConfiguration.NewTenantDatabaseNameFormat, details.TenantName);
await CopyDatabaseInitiateAsync(s, db, newDatabaseName);
var tenantId = await Tdb.TenantIdReserveAsync(newDatabaseName);
var id = new TenantInitializeDetails(details)
{
DatabaseServer = s.Name,
DatabaseName = newDatabaseName,
TenantId = tenantId
};
var initJob = BackgroundJob.ContinueWith <ITenantManagementJobs>(JobInfo.JobId.ToString(), j => j.InitializeNewTenantAsync(id));
initJob = BackgroundJob.ContinueWith <ITenantManagementJobs>(initJob, j => j.AddTenantToShardManagerAsync(id));
var bj = (IBackgroundJobClient) new TenantedBackgroundJobClient(GlobalContext, new HardcodedTraffkTenantFinder(tenantId));
bj.ContinueWith <ITenantJobs>(initJob, j => j.ReconfigureFiscalYears(new Bal.Settings.FiscalYearSettings {
CalendarMonth = 1, CalendarYear = 2000, FiscalYear = 2000
}));
return;
}
}
}
}
throw new Exception($"Could not find Model database [{sqlAzureManagementConfiguration.TenantModelDatabaseName}] on server [{sqlAzureManagementConfiguration.ServerName}]");
}
protected override bool CreateOrUpdate()
{
var created = true;
try
{
// Skip if exists
if (!CheckExistence())
{
// Sleep for 30 seconds to give the Traffic Manager Some time
// known bug if auditing switched on too fast
Thread.Sleep(30000);
using (var client = new SqlManagementClient(GetCredentials()))
{
var createResult = client.AuditingPolicy.CreateOrUpdateServerPolicyAsync(
Parameters.Tenant.SiteName,
Parameters.GetSiteName(Position),
new ServerAuditingPolicyCreateOrUpdateParameters()
{
Properties = new ServerAuditingPolicyProperties()
{
AuditingState = "Enabled",
StorageAccountKey = Parameters.Tenant.StoragePrimaryKey,
StorageAccountName = Parameters.Tenant.SiteName,
StorageAccountResourceGroupName = Parameters.Tenant.SiteName,
StorageAccountSubscriptionId = Settings.AccountSubscriptionId,
EventTypesToAudit = "PlainSQL_Success,PlainSQL_Failure,ParameterizedSQL_Success,ParameterizedSQL_Failure,StoredProcedure_Success,StoredProcedure_Success"
}
}).Result;
}
}
}
catch (Exception ex)
{
created = false;
Message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
}
return(created);
}
public void TestCreateDeleteReplicationLinks()
{
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
ResourceGroup resourceGroup = context.CreateResourceGroup();
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
string databaseName = "testdb";
Dictionary <string, string> tags = new Dictionary <string, string>();
//Create a server and a database
var v12Server = context.CreateServer(resourceGroup);
var dbInput = new Database()
{
Location = v12Server.Location
};
var database = sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, v12Server.Name, databaseName, dbInput);
// Create another server
var v12Server2 = context.CreateServer(resourceGroup);
// Create another database as an online secondary of the first database
var dbInput2 = new Database()
{
Location = v12Server2.Location,
CreateMode = CreateMode.OnlineSecondary,
SourceDatabaseId = database.Id
};
var database2 = sqlClient.Databases.CreateOrUpdate(resourceGroup.Name, v12Server2.Name, databaseName, dbInput2);
// Get replication link name
var replicationLinks = sqlClient.ReplicationLinks.ListByDatabase(resourceGroup.Name, v12Server2.Name, databaseName);
string replicationLinkId = replicationLinks.First().Name;
// Delete replication link and verify that no more links are returned
sqlClient.ReplicationLinks.Delete(resourceGroup.Name, v12Server2.Name, databaseName, replicationLinkId); replicationLinks = sqlClient.ReplicationLinks.ListByDatabase(resourceGroup.Name, v12Server2.Name, databaseName);
replicationLinks = sqlClient.ReplicationLinks.ListByDatabase(resourceGroup.Name, v12Server2.Name, databaseName);
Assert.True(replicationLinks.Count() == 0);
}
}
/// <summary>
/// Closes the session by disposing the clients
/// </summary>
/// <returns></returns>
public bool CloseSession()
{
try
{
if (ResourceManagementClient != null)
{
ResourceManagementClient.Dispose();
}
if (SqlManagementClient != null)
{
SqlManagementClient.Dispose();
}
return(true);
}
catch (Exception)
{
//TODO: trace
return(false);
}
}
public void TestCheckServerNameAvailable()
{
string suiteName = this.GetType().FullName;
using (SqlManagementTestContext context = new SqlManagementTestContext(this))
{
SqlManagementClient sqlClient = context.GetClient <SqlManagementClient>();
string serverName = SqlManagementTestUtilities.GenerateName();
CheckNameAvailabilityResponse response = sqlClient.Servers.CheckNameAvailability(new CheckNameAvailabilityRequest
{
Name = serverName
});
Assert.True(response.Available);
Assert.Equal(serverName, response.Name);
Assert.Equal(null, response.Message);
Assert.Equal(null, response.Reason);
}
}
public Server CreateServer(ResourceGroup resourceGroup, string location)
{
SqlManagementClient sqlClient = GetClient <SqlManagementClient>();
string version12 = "12.0";
string serverName = SqlManagementTestUtilities.GenerateName();
Dictionary <string, string> tags = new Dictionary <string, string>();
var v12Server = sqlClient.Servers.CreateOrUpdate(resourceGroup.Name, serverName, new Server()
{
AdministratorLogin = SqlManagementTestUtilities.DefaultLogin,
AdministratorLoginPassword = SqlManagementTestUtilities.DefaultPassword,
Version = version12,
Tags = tags,
Location = location,
});
SqlManagementTestUtilities.ValidateServer(v12Server, serverName, SqlManagementTestUtilities.DefaultLogin, version12, tags, location);
return(v12Server);
}
