/// <summary>
/// Checks for the existence of a specific Azure Sql Database, if it doesn't exist it will create it.
/// </summary>
/// <param name="client">The <see cref="SqlManagementClient"/> that is performing the operation.</param>
/// <param name="serverName">The name of the server that we want to use to create the database.</param>
/// <param name="databaseName">The name of the database we are creating.</param>
/// <param name="databaseEdition">The edition of the database we are creating.</param>
/// <param name="collationName">The database collation name.</param>
/// <param name="sizeInGb">The maximum database size in GB.</param>
/// <param name="createAppUser"></param>
/// <returns>The async <see cref="Task"/> wrapper.</returns>
public static async Task CreateDatabaseIfNotExistsAsync(
this SqlManagementClient client,
string serverName,
string databaseName,
string databaseEdition,
string collationName,
int sizeInGb,
bool createAppUser)
{
Contract.Requires(client != null);
Contract.Requires(!string.IsNullOrWhiteSpace(serverName));
Contract.Requires(!string.IsNullOrWhiteSpace(databaseName));
Contract.Requires(!string.IsNullOrWhiteSpace(databaseEdition));
Contract.Requires(!string.IsNullOrWhiteSpace(collationName));
Contract.Requires(sizeInGb > 0 && sizeInGb <= 250);
DatabaseGetResponse ns = null;
FlexStreams.BuildEventsObserver.OnNext(new CheckIfExistsEvent(AzureResource.SqlDatabase, databaseName));
try
{
ns = await client.Databases.GetAsync(serverName, databaseName);
}
catch (CloudException cex)
{
if (!cex.Error.Message.Contains($"Resource with the name '{databaseName}' does not exist"))
{
throw;
}
}
if (ns != null)
{
FlexStreams.BuildEventsObserver.OnNext(new FoundExistingEvent(AzureResource.SqlDatabase, databaseName));
return;
}
await client.Databases.CreateAsync(
serverName,
new DatabaseCreateParameters
{
Name = databaseName,
Edition = databaseEdition,
CollationName = collationName,
MaximumDatabaseSizeInGB = sizeInGb,
});
FlexStreams.BuildEventsObserver.OnNext(new ProvisionEvent(AzureResource.SqlDatabase, databaseName));
if (!createAppUser)
{
return;
}
using (var adb = new DevOpsAzureDatabase(serverName, databaseName, FlexConfiguration.FlexSaUser, FlexConfiguration.FlexSaPwd))
{
await adb.CreateDatabaseUserAsync(FlexConfiguration.FlexAppUser, FlexConfiguration.FlexAppUser, "dbo");
}
}
/// <summary>
/// Given a <see cref="DatabaseGetResponse"/> this will create and return a <see cref="Database"/>
/// object with the fields filled in.
/// </summary>
/// <param name="response">The response to turn into a <see cref="Database"/></param>
/// <returns>a <see cref="Database"/> object.</returns>
private Database CreateDatabaseFromResponse(DatabaseGetResponse response)
{
return(this.CreateDatabaseFromResponse(
response.Id,
response.Name,
response.CreationDate,
response.Edition,
response.CollationName,
response.MaximumDatabaseSizeInGB,
response.IsFederationRoot,
response.IsSystemObject,
response.SizeMB,
response.ServiceObjectiveAssignmentErrorCode,
response.ServiceObjectiveAssignmentErrorDescription,
response.ServiceObjectiveAssignmentState,
response.ServiceObjectiveAssignmentStateDescription,
response.ServiceObjectiveAssignmentSuccessDate,
response.ServiceObjectiveId));
}
/// <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>
/// 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);
}
private void ValidateImportExport(bool createServer = true, bool createDatabase = true, bool createFirewallRule = true,
string operationName = "Export", bool missingFields = false, HttpStatusCode expectedStatueCode = HttpStatusCode.Accepted)
{
var handler = new BasicDelegatingHandler();
// Management Clients
var sqlClient = Sql2ScenarioHelper.GetSqlClient(handler);
var resClient = Sql2ScenarioHelper.GetResourceClient(handler);
// Variables for server create
string serverName = TestUtilities.GenerateName("csm-sql-ie");
string resGroupName = TestUtilities.GenerateName("csm-rg-servercrud");
string serverLocation = "North Europe";
string adminLogin = "******";
string adminPass = "******";
string version = "12.0";
// Variables for database create
string databaseName = TestUtilities.GenerateName("csm-sql-db_ie");
string databaseEdition = "Standard";
long databaseMaxSize = 5L * 1024L * 1024L * 1024L; // 5 GB
Guid dbSloS1 = new Guid("1b1ebd4d-d903-4baa-97f9-4ea675f5e928"); // S1
string serviceObjectiveName = "S1";
// Create firewall test.
string firewallRuleName = TestUtilities.GenerateName("sql-fwrule");
string startIp1 = "0.0.0.0";
string endIp1 = "255.255.255.255";
string storageKey = "StorageKey";
string storageKeyType = "StorageAccessKey";
string exportBacpacLink = string.Format(CultureInfo.InvariantCulture, "http://test.blob.core.windows.net/databases/{0}.bacpac", databaseName);
string importBacpacLink = "http://test.blob.core.windows.net/databases/test.bacpac";
string testMode = Environment.GetEnvironmentVariable("AZURE_TEST_MODE");
if (testMode == "Record")
{
string importBacpacBaseUrl = Environment.GetEnvironmentVariable("TEST_EXPORT_BACPAC");
storageKey = Environment.GetEnvironmentVariable("TEST_STORAGE_KEY");
exportBacpacLink = string.Format(CultureInfo.InvariantCulture, "{0}/{1}.bacpac", importBacpacBaseUrl, databaseName);
importBacpacLink = Environment.GetEnvironmentVariable("TEST_IMPORT_BACPAC");
Assert.False(string.IsNullOrWhiteSpace(storageKey), "Environment variable TEST_EXPORT_BACPAC has not been set");
Assert.False(string.IsNullOrWhiteSpace(importBacpacBaseUrl), "Environment variable TEST_EXPORT_BACPAC has not been set");
Assert.False(string.IsNullOrWhiteSpace(importBacpacLink), "Environment variable TEST_IMPORT_BACPAC has not been set");
}
// Create the resource group.
resClient.ResourceGroups.CreateOrUpdate(resGroupName, new ResourceGroup()
{
Location = serverLocation,
});
try
{
//////////////////////////////////////////////////////////////////////
// Create server
if (createServer)
{
var createResponse = sqlClient.Servers.CreateOrUpdate(resGroupName, serverName, new ServerCreateOrUpdateParameters()
{
Location = serverLocation,
Properties = new ServerCreateOrUpdateProperties()
{
AdministratorLogin = adminLogin,
AdministratorLoginPassword = adminPass,
Version = version,
}
});
TestUtilities.ValidateOperationResponse(createResponse, HttpStatusCode.Created);
}
//////////////////////////////////////////////////////////////////////
// Create database
if (createServer && createDatabase)
{
var createDbResponse = sqlClient.Databases.CreateOrUpdate(resGroupName, serverName, databaseName, new DatabaseCreateOrUpdateParameters()
{
Location = serverLocation,
Properties = new DatabaseCreateOrUpdateProperties()
{
Edition = databaseEdition,
MaxSizeBytes = databaseMaxSize,
RequestedServiceObjectiveId = dbSloS1
},
});
TestUtilities.ValidateOperationResponse(createDbResponse, HttpStatusCode.Created);
}
//////////////////////////////////////////////////////////////////////
// Create firewall rule
if (createServer && createFirewallRule)
{
var firewallCreate = sqlClient.FirewallRules.CreateOrUpdate(resGroupName, serverName, firewallRuleName, new FirewallRuleCreateOrUpdateParameters()
{
Properties = new FirewallRuleCreateOrUpdateProperties()
{
StartIpAddress = startIp1,
EndIpAddress = endIp1,
}
});
TestUtilities.ValidateOperationResponse(firewallCreate, HttpStatusCode.Created);
}
//////////////////////////////////////////////////////////////////////
//Import Export
try
{
ImportExportResponse importExportResponse = null;
if (operationName == ExportOperationName)
{
ExportRequestParameters parameters = new ExportRequestParameters()
{
AdministratorLogin = adminLogin,
AdministratorLoginPassword = adminPass
};
if (!missingFields)
{
parameters.StorageKey = storageKey;
parameters.StorageKeyType = storageKeyType;
parameters.StorageUri = new Uri(exportBacpacLink);
}
importExportResponse = sqlClient.ImportExport.Export(resGroupName, serverName, databaseName, parameters);
}
else if (operationName == ImportOperationName)
{
ImportRequestParameters parameters = new ImportRequestParameters()
{
AdministratorLogin = adminLogin,
AdministratorLoginPassword = adminPass,
StorageKey = storageKey,
StorageKeyType = storageKeyType,
StorageUri = new Uri(importBacpacLink),
DatabaseName = databaseName
};
if (!missingFields)
{
parameters.DatabaseMaxSize = databaseMaxSize;
parameters.Edition = databaseEdition;
parameters.ServiceObjectiveName = serviceObjectiveName;
}
importExportResponse = sqlClient.ImportExport.Import(resGroupName, serverName, parameters);
}
else if (operationName == ImportExistingDbOperationName)
{
ImportExtensionRequestParameteres parameters = new ImportExtensionRequestParameteres()
{
Properties = new ImportExtensionProperties
{
AdministratorLogin = adminLogin,
AdministratorLoginPassword = adminPass,
StorageKey = storageKey,
StorageKeyType = storageKeyType,
StorageUri = new Uri(importBacpacLink)
}
};
importExportResponse = sqlClient.ImportExport.ImportToExistingDatabase(resGroupName, serverName, databaseName, parameters);
}
if (expectedStatueCode == HttpStatusCode.Accepted)
{
Assert.Equal(importExportResponse.Status, OperationStatus.InProgress);
ImportExportOperationStatusResponse statusResponse = sqlClient.ImportExport.GetImportExportOperationStatus(importExportResponse.OperationStatusLink);
while (statusResponse.Status == Microsoft.Azure.OperationStatus.InProgress)
{
statusResponse = sqlClient.ImportExport.GetImportExportOperationStatus(importExportResponse.OperationStatusLink);
if (statusResponse.Status == Microsoft.Azure.OperationStatus.InProgress)
{
ValidateImportExportOperationStatusResponseProperties(statusResponse);
}
}
Assert.Equal(statusResponse.Status, OperationStatus.Succeeded);
ValidateImportExportOperationStatusResponseProperties(statusResponse.Properties);
}
TestUtilities.ValidateOperationResponse(importExportResponse, expectedStatueCode);
}
catch (CloudException exception)
{
Assert.Equal(exception.Response.StatusCode, expectedStatueCode);
}
if (operationName == ImportOperationName && !createDatabase && !missingFields)
{
DatabaseGetResponse databaseGetResponse = sqlClient.Databases.Get(resGroupName, serverName, databaseName);
TestUtilities.ValidateOperationResponse(databaseGetResponse);
}
}
finally
{
// Clean up the resource group.
resClient.ResourceGroups.Delete(resGroupName);
}
}