/// <summary>
/// Creates a new sql database.
/// </summary>
/// <param name="databaseName">The name for the new database</param>
/// <param name="databaseMaxSizeInGB">The maximum size of the new database</param>
/// <param name="databaseCollation">The collation for the new database</param>
/// <param name="databaseEdition">The edition for the new database</param>
/// <returns>The newly created Sql Database</returns>
public Database CreateNewDatabase(
string databaseName,
int?databaseMaxSizeInGB,
string databaseCollation,
DatabaseEdition databaseEdition,
ServiceObjective serviceObjective)
{
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Get the SQL management client
SqlManagementClient sqlManagementClient = this.subscription.CreateClient <SqlManagementClient>();
this.AddTracingHeaders(sqlManagementClient);
// Create the database
DatabaseCreateResponse response = sqlManagementClient.Databases.Create(
this.serverName,
new DatabaseCreateParameters()
{
Name = databaseName,
Edition = databaseEdition != DatabaseEdition.None ?
databaseEdition.ToString() : DatabaseEdition.Web.ToString(),
CollationName = databaseCollation ?? string.Empty,
MaximumDatabaseSizeInGB = databaseMaxSizeInGB ??
(databaseEdition == DatabaseEdition.Business || databaseEdition == DatabaseEdition.Premium ? 10 : 1),
ServiceObjectiveId = serviceObjective != null ? serviceObjective.Id.ToString() : null,
});
// Construct the resulting Database object
Database database = CreateDatabaseFromResponse(response);
return(database);
}
/// <summary>
/// Converts the response from the service to a powershell database object
/// </summary>
/// <param name="resourceGroup">The resource group the server is in</param>
/// <param name="serverName">The name of the Azure Sql Database Server</param>
/// <param name="pool">The service response</param>
/// <returns>The converted model</returns>
private AzureSqlElasticPoolRecommendationModel CreateRecommendedElasticPoolModelFromResponse(string resourceGroup, string serverName, Management.Sql.Models.RecommendedElasticPool pool)
{
AzureSqlElasticPoolRecommendationModel recommendationModel = new AzureSqlElasticPoolRecommendationModel();
recommendationModel.ResourceGroupName = resourceGroup;
recommendationModel.ServerName = serverName;
recommendationModel.RecommendedElasticPoolName = pool.Name;
DatabaseEdition edition = DatabaseEdition.None;
Enum.TryParse <DatabaseEdition>(pool.Properties.DatabaseEdition, out edition);
recommendationModel.DatabaseEdition = edition;
recommendationModel.Dtu = pool.Properties.Dtu;
recommendationModel.DatabaseDtuMax = pool.Properties.DatabaseDtuMax;
recommendationModel.DatabaseDtuMin = pool.Properties.DatabaseDtuMin;
recommendationModel.StorageMB = pool.Properties.StorageMB;
recommendationModel.ObservationPeriodStart = pool.Properties.ObservationPeriodStart;
recommendationModel.ObservationPeriodEnd = pool.Properties.ObservationPeriodEnd;
recommendationModel.MaxObservedDtu = pool.Properties.MaxObservedDtu;
recommendationModel.MaxObservedStorageMB = pool.Properties.MaxObservedStorageMB;
recommendationModel.Tags = pool.Tags as Dictionary <string, string>;
return(recommendationModel);
}
/// <summary>
/// Construct AzureSqlDatabaseModel from Management.Sql.Models.Database object
/// </summary>
/// <param name="resourceGroup">Resource group</param>
/// <param name="serverName">Server name</param>
/// <param name="database">Database object</param>
public AzureSqlDatabaseModel(string resourceGroup, string serverName, Management.Sql.Models.Database database)
{
Guid id = Guid.Empty;
DatabaseEdition edition = DatabaseEdition.None;
ResourceGroupName = resourceGroup;
ServerName = serverName;
CollationName = database.Properties.Collation;
CreationDate = database.Properties.CreationDate;
CurrentServiceObjectiveName = database.Properties.ServiceObjective;
MaxSizeBytes = database.Properties.MaxSizeBytes;
DatabaseName = database.Name;
Status = database.Properties.Status;
Tags = database.Tags as Dictionary <string, string>;
ElasticPoolName = database.Properties.ElasticPoolName;
Location = database.Location;
ResourceId = database.Id;
CreateMode = database.Properties.CreateMode;
EarliestRestoreDate = database.Properties.EarliestRestoreDate;
Guid.TryParse(database.Properties.CurrentServiceObjectiveId, out id);
CurrentServiceObjectiveId = id;
Guid.TryParse(database.Properties.DatabaseId, out id);
DatabaseId = id;
Enum.TryParse <DatabaseEdition>(database.Properties.Edition, true, out edition);
Edition = edition;
Guid.TryParse(database.Properties.RequestedServiceObjectiveId, out id);
RequestedServiceObjectiveId = id;
}
/// <summary>
/// Construct AzureSqlDatabaseModel from Management.Sql.LegacySdk.Models.Database object
/// </summary>
/// <param name="resourceGroup">Resource group</param>
/// <param name="serverName">Server name</param>
/// <param name="database">Database object</param>
public AzureSqlDatabaseModel(string resourceGroup, string serverName, Management.Sql.Models.Database database)
{
DatabaseEdition edition = DatabaseEdition.None;
DatabaseReadScale readScale = DatabaseReadScale.Enabled;
ResourceGroupName = resourceGroup;
ServerName = serverName;
CollationName = database.Collation;
CreationDate = database.CreationDate.Value;
CurrentServiceObjectiveName = database.ServiceLevelObjective;
MaxSizeBytes = long.Parse(database.MaxSizeBytes);
DatabaseName = database.Name;
Status = database.Status;
Tags = TagsConversionHelper.CreateTagDictionary(TagsConversionHelper.CreateTagHashtable(database.Tags), false);
ElasticPoolName = database.ElasticPoolName;
Location = database.Location;
ResourceId = database.Id;
CreateMode = database.CreateMode;
EarliestRestoreDate = database.EarliestRestoreDate;
CurrentServiceObjectiveId = database.CurrentServiceObjectiveId.Value;
DatabaseId = database.DatabaseId.Value;
Enum.TryParse <DatabaseEdition>(database.Edition, true, out edition);
Edition = edition;
RequestedServiceObjectiveId = database.RequestedServiceObjectiveId.Value;
Enum.TryParse <DatabaseReadScale>(database.ReadScale.ToString(), true, out readScale);
ReadScale = readScale;
}
/// <summary>
/// Converts the response from the service to a powershell database object
/// </summary>
/// <param name="resourceGroupName">The resource group the server is in</param>
/// <param name="serverName">The name of the Azure Sql Database Server</param>
/// <param name="database">The service response</param>
/// <returns>The converted model</returns>
public static AzureSqlDatabaseModel CreateDatabaseModelFromResponse(string resourceGroup, string serverName, Management.Sql.Models.Database database)
{
AzureSqlDatabaseModel model = new AzureSqlDatabaseModel();
Guid id = Guid.Empty;
DatabaseEdition edition = DatabaseEdition.None;
model.ResourceGroupName = resourceGroup;
model.ServerName = serverName;
model.CollationName = database.Properties.Collation;
model.CreationDate = database.Properties.CreationDate;
model.CurrentServiceLevelObjectiveName = database.Properties.ServiceObjective;
model.MaxSizeBytes = database.Properties.MaxSizeBytes;
model.DatabaseName = database.Name;
model.Status = database.Properties.Status;
model.Tags = database.Tags as Dictionary <string, string>;
model.ElasticPoolName = database.Properties.ElasticPoolName;
model.Location = database.Location;
Guid.TryParse(database.Properties.CurrentServiceObjectiveId, out id);
model.CurrentServiceObjectiveId = id;
Guid.TryParse(database.Properties.DatabaseId, out id);
model.DatabaseId = id;
Enum.TryParse <DatabaseEdition>(database.Properties.Edition, true, out edition);
model.Edition = edition;
Guid.TryParse(database.Properties.RequestedServiceObjectiveId, out id);
model.RequestedServiceObjectiveId = id;
return(model);
}
/// <summary>
/// Creates a new Sql Database.
/// </summary>
/// <param name="databaseName">The name for the new database.</param>
/// <param name="databaseMaxSize">The max size for the database.</param>
/// <param name="databaseCollation">The collation for the database.</param>
/// <param name="databaseEdition">The edition for the database.</param>
/// <returns>The newly created Sql Database.</returns>
public Database CreateNewDatabase(
string databaseName,
int?databaseMaxSize,
string databaseCollation,
DatabaseEdition databaseEdition)
{
// Create a new request Id for this operation
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Create the new entity and set its properties
Database database = new Database();
database.Name = databaseName;
if (databaseMaxSize != null)
{
database.MaxSizeGB = (int)databaseMaxSize;
}
if (!string.IsNullOrEmpty(databaseCollation))
{
database.CollationName = databaseCollation;
}
if (databaseEdition != DatabaseEdition.None)
{
database.Edition = databaseEdition.ToString();
}
// Save changes
this.AddToDatabases(database);
try
{
this.SaveChanges(SaveChangesOptions.None);
// Re-Query the database for server side updated information
database = this.RefreshEntity(database);
if (database == null)
{
throw new ApplicationException(Resources.ErrorRefreshingDatabase);
}
}
catch
{
this.ClearTrackedEntity(database);
throw;
}
// Load the extra properties for this object.
this.LoadExtraProperties(database);
return(database);
}
private bool IsDatabaseInServiceTierForPolicy(DatabaseAuditingPolicyModel model, string clientId)
{
AzureSqlDatabaseCommunicator dbCommunicator = new AzureSqlDatabaseCommunicator(Context);
Management.Sql.Models.Database database = dbCommunicator.Get(model.ResourceGroupName, model.ServerName, model.DatabaseName, clientId);
DatabaseEdition edition = DatabaseEdition.None;
Enum.TryParse<DatabaseEdition>(database.Properties.Edition, true, out edition);
if (edition == DatabaseEdition.Basic || edition == DatabaseEdition.Standard || edition == DatabaseEdition.Premium || edition == DatabaseEdition.DataWarehouse)
{
return true;
}
return false;
}
private void cbEdition_SelectedIndexChanged(object sender, EventArgs e)
{
DatabaseEdition edition = (DatabaseEdition)cbEdition.SelectedItem;
lbMaxDatabaseSize.Text = edition.Size.Last().Value;
cbPerformanceLevel.DataSource = edition.PerformanceLevel;
cbPerformanceLevel.DisplayMember = "key";
int originalLevel = cbPerformanceLevel.SelectedIndex;
switch (edition.Edition.Value)
{
case "web":
gbPreviewRegistration.Visible = lablePerformance.Visible = cbPerformanceLevel.Visible = false;
gbNotRecommended.Visible = true;
break;
case "business":
gbPreviewRegistration.Visible = lablePerformance.Visible = cbPerformanceLevel.Visible = false;
gbNotRecommended.Visible = true;
break;
case "basic":
cbPerformanceLevel.SelectedIndex = 0;
gbPreviewRegistration.Visible = lablePerformance.Visible = cbPerformanceLevel.Visible = true;
gbNotRecommended.Visible = false;
break;
case "standard":
cbPerformanceLevel.SelectedIndex = 2;
gbPreviewRegistration.Visible = lablePerformance.Visible = cbPerformanceLevel.Visible = true;
gbNotRecommended.Visible = false;
break;
case "premium":
cbPerformanceLevel.SelectedIndex = 0;
gbPreviewRegistration.Visible = lablePerformance.Visible = cbPerformanceLevel.Visible = true;
gbNotRecommended.Visible = false;
break;
default:
break;
}
if (originalLevel == cbPerformanceLevel.SelectedIndex)
{
cbPerformanceLevel_SelectedIndexChanged(null, null);
}
}
/// <summary>
/// Creates a new Sql Database.
/// </summary>
/// <param name="databaseName">The name for the new database.</param>
/// <param name="databaseMaxSizeGb">The max size for the database in GB.</param>
/// <param name="databaseMaxSizeBytes">The max size for the database in bytes.</param>
/// <param name="databaseCollation">The collation for the database.</param>
/// <param name="databaseEdition">The edition for the database.</param>
/// <param name="serviceObjective">The service object to assign to the database</param>
/// <returns>The newly created Sql Database.</returns>
public Database CreateNewDatabase(
string databaseName,
int?databaseMaxSizeGb,
long?databaseMaxSizeBytes,
string databaseCollation,
DatabaseEdition databaseEdition,
ServiceObjective serviceObjective)
{
return(CreateNewDatabase(
databaseName,
databaseMaxSizeGb,
databaseMaxSizeBytes,
databaseCollation,
databaseEdition,
serviceObjective == null ? null : serviceObjective.Name));
}
public async Task <bool> CreateDatabaseAsync(string dbName, string elasticPool, string location,
CreateMode createMode, DatabaseEdition databaseEdition,
string collation = "SQL_Latin1_General_CP1_CI_AS")
{
var dbInner = new DatabaseInner
{
ElasticPoolName = elasticPool,
Location = location,
Collation = collation,
CreateMode = createMode,
Edition = databaseEdition,
};
var resultDbInner = await client.Databases.CreateOrUpdateAsync(azureResourceGroup, azureSQLServer, dbName, dbInner);
return(string.IsNullOrEmpty(resultDbInner.Name) ? false : true);
}
/// <summary>
/// Creates a new sql database.
/// </summary>
/// <param name="databaseName">The name for the new database</param>
/// <param name="databaseMaxSize">The maximum size of the new database</param>
/// <param name="databaseCollation">The collation for the new database</param>
/// <param name="databaseEdition">The edition for the new database</param>
/// <returns>The newly created Sql Database</returns>
public Database CreateNewDatabase(
string databaseName,
int?databaseMaxSize,
string databaseCollation,
DatabaseEdition databaseEdition)
{
this.clientRequestId = SqlDatabaseManagementHelper.GenerateClientTracingId();
ISqlDatabaseManagement channel = GetManagementChannel();
SqlDatabaseInput input = new SqlDatabaseInput();
input.Name = databaseName;
input.CollationName = databaseCollation ?? string.Empty;
//determine the edition
if (databaseEdition != DatabaseEdition.None)
{
input.Edition = databaseEdition.ToString();
}
else
{
input.Edition = string.Empty;
}
//determine the maximum size
if (databaseMaxSize.HasValue)
{
input.MaxSizeGB = databaseMaxSize.ToString();
}
//create a new database on the server
SqlDatabaseResponse response =
channel.EndNewDatabase(
channel.BeginNewDatabase(this.subscriptionId, this.serverName, input, null, null));
Database database = CreateDatabaseFromResponse(response);
return(database);
}
/// <summary>
/// Construct AzureSqlDatabaseModel from Management.Sql.LegacySdk.Models.Database object
/// </summary>
/// <param name="resourceGroup">Resource group</param>
/// <param name="serverName">Server name</param>
/// <param name="database">Database object</param>
public AzureSqlDatabaseModel(string resourceGroup, string serverName, Management.Sql.LegacySdk.Models.Database database)
{
Guid id = Guid.Empty;
DatabaseEdition edition = DatabaseEdition.None;
DatabaseReadScale readScale = DatabaseReadScale.Enabled;
ResourceGroupName = resourceGroup;
ServerName = serverName;
CollationName = database.Properties.Collation;
CreationDate = database.Properties.CreationDate;
CurrentServiceObjectiveName = database.Properties.ServiceObjective;
MaxSizeBytes = database.Properties.MaxSizeBytes;
DatabaseName = database.Name;
Status = database.Properties.Status;
Tags = TagsConversionHelper.CreateTagDictionary(TagsConversionHelper.CreateTagHashtable(database.Tags), false);
ElasticPoolName = database.Properties.ElasticPoolName;
Location = database.Location;
ResourceId = database.Id;
CreateMode = database.Properties.CreateMode;
EarliestRestoreDate = database.Properties.EarliestRestoreDate;
Guid.TryParse(database.Properties.CurrentServiceObjectiveId, out id);
CurrentServiceObjectiveId = id;
Guid.TryParse(database.Properties.DatabaseId, out id);
DatabaseId = id;
Enum.TryParse <DatabaseEdition>(database.Properties.Edition, true, out edition);
Edition = edition;
Guid.TryParse(database.Properties.RequestedServiceObjectiveId, out id);
RequestedServiceObjectiveId = id;
Enum.TryParse <DatabaseReadScale>(database.Properties.ReadScale, true, out readScale);
ReadScale = readScale;
ZoneRedundant = false;
}
/// <summary>
/// Converts the response from the service to a powershell database object
/// </summary>
/// <param name="resourceGroupName">The resource group the server is in</param>
/// <param name="serverName">The name of the Azure Sql Database Server</param>
/// <param name="pool">The service response</param>
/// <returns>The converted model</returns>
private AzureSqlElasticPoolModel CreateElasticPoolModelFromResponse(string resourceGroup, string serverName, Management.Sql.Models.ElasticPool pool)
{
AzureSqlElasticPoolModel model = new AzureSqlElasticPoolModel();
model.ResourceId = pool.Id;
model.ResourceGroupName = resourceGroup;
model.ServerName = serverName;
model.ElasticPoolName = pool.Name;
model.CreationDate = pool.Properties.CreationDate ?? DateTime.MinValue;
model.DatabaseDtuMax = (int)pool.Properties.DatabaseDtuMax;
model.DatabaseDtuMin = (int)pool.Properties.DatabaseDtuMin;
model.Dtu = (int)pool.Properties.Dtu;
model.State = pool.Properties.State;
model.StorageMB = pool.Properties.StorageMB;
model.Tags = pool.Tags as Dictionary <string, string>;
model.Location = pool.Location;
DatabaseEdition edition = DatabaseEdition.None;
Enum.TryParse <DatabaseEdition>(pool.Properties.Edition, out edition);
model.Edition = edition;
return(model);
}
/// <summary>
/// process the request using the connection context.
/// </summary>
/// <param name="databaseName">the name of the database to alter</param>
/// <param name="maxSizeGb">the new maximum size for the database</param>
/// <param name="edition">the new edition for the database</param>
private void ProcessWithConnectionContext(string databaseName, int? maxSizeGb, long? maxSizeBytes, DatabaseEdition? edition)
{
try
{
// Update the database with the specified name
Database database = this.ConnectionContext.UpdateDatabase(
databaseName,
this.NewDatabaseName,
maxSizeGb,
maxSizeBytes,
edition,
this.ServiceObjective);
if (this.Sync.IsPresent)
{
// Wait for the operation to complete on the server.
database = CmdletCommon.WaitForDatabaseOperation(this, this.ConnectionContext, database, this.DatabaseName, false);
}
// If PassThru was specified, write back the updated object to the pipeline
if (this.PassThru.IsPresent)
{
this.WriteObject(database);
}
if (this.ConnectionContext.GetType() == typeof(ServerDataServiceCertAuth))
{
if (this.MyInvocation.BoundParameters.ContainsKey("Database"))
{
this.Database.CopyFields(database);
}
}
}
catch (Exception ex)
{
SqlDatabaseExceptionHandler.WriteErrorDetails(
this,
this.ConnectionContext.ClientRequestId,
ex);
}
}
/// <summary>
/// Process the request using a temporary connection context using certificate authentication
/// </summary>
/// <param name="databaseName">The name of the database to update</param>
/// <param name="maxSizeGb">the new size for the database or null</param>
/// <param name="edition">the new edition for the database or null</param>
private void ProcessWithServerName(string databaseName, int? maxSizeGb, long? maxSizeBytes, DatabaseEdition? edition)
{
Func<string> GetClientRequestId = () => string.Empty;
try
{
// Get the current subscription data.
WindowsAzureSubscription subscription = WindowsAzureProfile.Instance.CurrentSubscription;
// Create a temporary context
ServerDataServiceCertAuth context =
ServerDataServiceCertAuth.Create(this.ServerName, subscription);
GetClientRequestId = () => context.ClientRequestId;
// Remove the database with the specified name
Database database = context.UpdateDatabase(
databaseName,
this.NewDatabaseName,
maxSizeGb,
maxSizeBytes,
edition,
this.ServiceObjective);
if (this.Sync.IsPresent)
{
// Wait for the operation to complete on the server.
database = CmdletCommon.WaitForDatabaseOperation(this, context, database, this.DatabaseName, false);
}
// Update the passed in database object
if (this.MyInvocation.BoundParameters.ContainsKey("Database"))
{
this.Database.CopyFields(database);
database = this.Database;
}
// If PassThru was specified, write back the updated object to the pipeline
if (this.PassThru.IsPresent)
{
this.WriteObject(database);
}
}
catch (Exception ex)
{
SqlDatabaseExceptionHandler.WriteErrorDetails(
this,
GetClientRequestId(),
ex);
}
}
/// <summary>
/// Updates the property on the database with the name <paramref name="databaseName"/>.
/// </summary>
/// <param name="databaseName">The database to update.</param>
/// <param name="newDatabaseName">
/// The new database name, or <c>null</c> to not update.
/// </param>
/// <param name="databaseMaxSize">
/// The new database max size, or <c>null</c> to not update.
/// </param>
/// <param name="databaseEdition">
/// The new database edition, or <c>null</c> to not update.
/// </param>
/// <param name="serviceObjective">
/// The new service objective, or <c>null</c> to not update.
/// </param>
/// <returns>The updated database object.</returns>
public Database UpdateDatabase(
string databaseName,
string newDatabaseName,
int? databaseMaxSizeGb,
long? databaseMaxSizeBytes,
DatabaseEdition? databaseEdition,
ServiceObjective serviceObjective)
{
// Find the database by name
Database database = GetDatabase(databaseName);
// Update the name if specified
if (newDatabaseName != null)
{
database.Name = newDatabaseName;
}
// Update the max size and edition properties
if (databaseMaxSizeGb != null)
{
database.MaxSizeGB = (int)databaseMaxSizeGb;
}
if (databaseMaxSizeBytes != null)
{
database.MaxSizeBytes = (long)databaseMaxSizeBytes;
}
database.Edition = databaseEdition == null ? null : databaseEdition.ToString();
database.IsRecursiveTriggersOn = null;
// Update the service objective property if specified
if (serviceObjective != null)
{
database.ServiceObjectiveId = serviceObjective.Id;
}
else
{
database.ServiceObjectiveId = null;
}
// Mark the database object for update and submit the changes
this.UpdateObject(database);
try
{
this.SaveChanges();
}
catch
{
this.RevertChanges(database);
throw;
}
return this.GetDatabase(database.Name);
}
/// <summary>
/// Creates a new Sql Database.
/// </summary>
/// <param name="databaseName">The name for the new database.</param>
/// <param name="databaseMaxSizeGb">The max size for the database in GB.</param>
/// <param name="databaseMaxSizeBytes">The max size for the database in bytes.</param>
/// <param name="databaseCollation">The collation for the database.</param>
/// <param name="databaseEdition">The edition for the database.</param>
/// <param name="serviceObjective">The service object to assign to the database</param>
/// <returns>The newly created Sql Database.</returns>
public Database CreateNewDatabase(
string databaseName,
int?databaseMaxSizeGb,
long?databaseMaxSizeBytes,
string databaseCollation,
DatabaseEdition databaseEdition,
string serviceObjectiveName)
{
builder["Database"] = null;
string commandText = "CREATE DATABASE [{0}] ";
if (!string.IsNullOrEmpty(databaseCollation))
{
commandText += " COLLATE {1} ";
}
List <string> arguments = new List <string>();
if (databaseMaxSizeGb != null || databaseMaxSizeBytes != null)
{
arguments.Add(" MAXSIZE={2} ");
}
if (databaseEdition != DatabaseEdition.None)
{
arguments.Add(" EDITION='{3}' ");
}
if (!string.IsNullOrEmpty(serviceObjectiveName))
{
arguments.Add(" SERVICE_OBJECTIVE='{4}' ");
}
if (arguments.Count > 0)
{
commandText += "(" + string.Join(", ", arguments.ToArray()) + ")";
}
string maxSizeVal = string.Empty;
if (databaseMaxSizeGb != null)
{
maxSizeVal = databaseMaxSizeGb.Value.ToString() + "GB";
}
else if (databaseMaxSizeBytes != null)
{
if (databaseMaxSizeBytes > (500 * 1024 * 1024))
{
maxSizeVal = (databaseMaxSizeBytes / (1024 * 1024 * 1024)).ToString() + "GB";
}
else
{
maxSizeVal = (databaseMaxSizeBytes / (1024 * 1024)).ToString() + "MB";
}
}
SqlCollationCheck(databaseCollation);
commandText = string.Format(
commandText,
SqlEscape(databaseName),
databaseCollation,
SqlEscape(maxSizeVal),
SqlEscape(databaseEdition.ToString()),
SqlEscape(serviceObjectiveName));
builder["Database"] = null;
using (var connection = CreateConnection())
{
using (DbCommand command = connection.CreateCommand())
{
command.CommandTimeout = commandTimeout;
command.CommandText = commandText;
connection.Open();
command.ExecuteNonQuery();
}
}
return(GetDatabase(databaseName));
}
private void Init()
{
List <Collation> colList = CollationHelper.GetCollationList();
cbCollations.DataSource = colList;
string dbCol = CommonFunc.GetAppSettingsStringValue("DBCollation");
if (dbCol.Length > 0)
{
int index = 0;
foreach (Collation col in colList)
{
if (dbCol.Equals(col.Name, StringComparison.CurrentCultureIgnoreCase))
{
cbCollations.SelectedIndex = index;
break;
}
++index;
}
}
if (_TargetServerInfo.ServerType != ServerTypes.AzureSQLDB)
{
gbDatabaseSize.Enabled = false;
return;
}
gbDatabaseSize.Enabled = true;
_major = Convert.ToInt32(_TargetServerInfo.Version.Substring(0, 2));
List <DatabaseEdition> editions = new List <DatabaseEdition>();
// Web = 100 MB, 1 GB, 5 GB
// Business = 10, 20, 30, 40, 50, 100, 150
// Basic = 100 MB, 500 MB, 1, 2
// Standard = 100 MB, 500 MB, 1, 2, 5, 10, 20, 30, 40, 50, 100, 150, 200, 250
// Premium = 100 MB, 500 MB, 1, 2, 5, 10, 20, 30, 40, 50, 100, 150, 200, 250, 300, 400, 500
DatabaseEdition webEdition = new DatabaseEdition(new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseWebEdition, "web"));
DatabaseEdition busEdition = new DatabaseEdition(new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseBusinessEdition, "business"));
DatabaseEdition basEdition = new DatabaseEdition(new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseBasicEdition, "basic"));
DatabaseEdition stdEdition = new DatabaseEdition(new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseStandardEdition, "standard"));
DatabaseEdition preEdition = new DatabaseEdition(new KeyValuePair <string, string>(Properties.Resources.CreateDatabasePremiumEdition, "premium"));
KeyValuePair <string, string> dbSize100mb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeOneHundredMB, "100 MB");
KeyValuePair <string, string> dbSize500mb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeFiveHundredMB, "500 MB");
KeyValuePair <string, string> dbSize1gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeOneGB, "1 GB");
KeyValuePair <string, string> dbSize2gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeTwoGB, "2 GB");
KeyValuePair <string, string> dbSize5gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeFiveGB, "5 GB");
KeyValuePair <string, string> dbSize10gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeTenGB, "10 GB");
KeyValuePair <string, string> dbSize20gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeTwentyGB, "20 GB");
KeyValuePair <string, string> dbSize30gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeThirtyGB, "30 GB");
KeyValuePair <string, string> dbSize40gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeFourtyGB, "40 GB");
KeyValuePair <string, string> dbSize50gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeFiftyGB, "50 GB");
KeyValuePair <string, string> dbSize100gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeOneHundredGB, "100 GB");
KeyValuePair <string, string> dbSize150gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeOneHundredFiftyGB, "150 GB");
KeyValuePair <string, string> dbSize200gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeTwoHundredGB, "200 GB");
KeyValuePair <string, string> dbSize250gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeTwoHundredFiftyGB, "250 GB");
KeyValuePair <string, string> dbSize300gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeThreeHundredGB, "300 GB");
KeyValuePair <string, string> dbSize400gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeFourHundredGB, "400 GB");
KeyValuePair <string, string> dbSize500gb = new KeyValuePair <string, string>(Properties.Resources.CreateDatabaseSizeFiveHundredGB, "500 GB");
webEdition.Size.Add(dbSize100mb);
webEdition.Size.Add(dbSize1gb);
webEdition.Size.Add(dbSize5gb);
busEdition.Size.Add(dbSize10gb);
busEdition.Size.Add(dbSize20gb);
busEdition.Size.Add(dbSize30gb);
busEdition.Size.Add(dbSize40gb);
busEdition.Size.Add(dbSize50gb);
busEdition.Size.Add(dbSize100gb);
busEdition.Size.Add(dbSize150gb);
basEdition.Size.Add(dbSize100mb);
basEdition.Size.Add(dbSize500mb);
basEdition.Size.Add(dbSize1gb);
basEdition.Size.Add(dbSize2gb);
stdEdition.Size.Add(dbSize100mb);
stdEdition.Size.Add(dbSize500mb);
stdEdition.Size.Add(dbSize1gb);
stdEdition.Size.Add(dbSize2gb);
stdEdition.Size.Add(dbSize5gb);
stdEdition.Size.Add(dbSize10gb);
stdEdition.Size.Add(dbSize20gb);
stdEdition.Size.Add(dbSize30gb);
stdEdition.Size.Add(dbSize40gb);
stdEdition.Size.Add(dbSize50gb);
stdEdition.Size.Add(dbSize100gb);
stdEdition.Size.Add(dbSize150gb);
stdEdition.Size.Add(dbSize200gb);
stdEdition.Size.Add(dbSize250gb);
preEdition.Size.Add(dbSize100mb);
preEdition.Size.Add(dbSize500mb);
preEdition.Size.Add(dbSize1gb);
preEdition.Size.Add(dbSize2gb);
preEdition.Size.Add(dbSize5gb);
preEdition.Size.Add(dbSize10gb);
preEdition.Size.Add(dbSize20gb);
preEdition.Size.Add(dbSize30gb);
preEdition.Size.Add(dbSize40gb);
preEdition.Size.Add(dbSize50gb);
preEdition.Size.Add(dbSize100gb);
preEdition.Size.Add(dbSize150gb);
preEdition.Size.Add(dbSize200gb);
preEdition.Size.Add(dbSize250gb);
preEdition.Size.Add(dbSize300gb);
preEdition.Size.Add(dbSize400gb);
preEdition.Size.Add(dbSize500gb);
webEdition.PerformanceLevel.Add(new KeyValuePair <string, string>("", ""));
busEdition.PerformanceLevel.Add(new KeyValuePair <string, string>("", ""));
basEdition.PerformanceLevel.Add(new KeyValuePair <string, string>(Properties.Resources.PerformanceLevelBasicFiveDTUs, ""));
int eol_Year = Convert.ToInt32(CommonFunc.GetAppSettingsStringValue("EndOfLife_Year"));
int eol_Month = Convert.ToInt32(CommonFunc.GetAppSettingsStringValue("EndOfLife_Month"));
int eol_Day = Convert.ToInt32(CommonFunc.GetAppSettingsStringValue("EndOfLife_Day"));
DateTime eol_dt = new DateTime(eol_Year, eol_Month, eol_Day);
bool past_eol = DateTime.Compare(DateTime.Now, eol_dt) < 0 ? false : true;
stdEdition.PerformanceLevel.Add(new KeyValuePair <string, string>(Properties.Resources.PerformanceLevelStandardS0, "S0"));
stdEdition.PerformanceLevel.Add(new KeyValuePair <string, string>(Properties.Resources.PerformanceLevelStandardS1, "S1"));
stdEdition.PerformanceLevel.Add(new KeyValuePair <string, string>(Properties.Resources.PerformanceLevelStandardS2, "S2"));
preEdition.PerformanceLevel.Add(new KeyValuePair <string, string>(Properties.Resources.PerformanceLevelPremiumP1, "P1"));
preEdition.PerformanceLevel.Add(new KeyValuePair <string, string>(Properties.Resources.PerformanceLevelPremiump2, "P2"));
preEdition.PerformanceLevel.Add(new KeyValuePair <string, string>(Properties.Resources.PerformanceLevelPremiumP3, "P3"));
if (_major > 11)
{
stdEdition.PerformanceLevel.Add(new KeyValuePair <string, string>(Properties.Resources.PerformanceLevelStandardS3, "S3"));
}
else
{
if (!past_eol)
{
editions.Add(webEdition);
}
if (!past_eol)
{
editions.Add(busEdition);
}
}
editions.Add(basEdition);
editions.Add(stdEdition);
editions.Add(preEdition);
cbEdition.DataSource = editions;
cbEdition.DisplayMember = "Display";
cbEdition.SelectedItem = stdEdition;
}
/// <summary>
/// Creates a new sql database.
/// </summary>
/// <param name="databaseName">The name for the new database</param>
/// <param name="databaseMaxSize">The maximum size of the new database</param>
/// <param name="databaseCollation">The collation for the new database</param>
/// <param name="databaseEdition">The edition for the new database</param>
/// <returns>The newly created Sql Database</returns>
public Database CreateNewDatabase(
string databaseName,
int? databaseMaxSize,
string databaseCollation,
DatabaseEdition databaseEdition)
{
this.clientRequestId = SqlDatabaseManagementHelper.GenerateClientTracingId();
ISqlDatabaseManagement channel = GetManagementChannel();
SqlDatabaseInput input = new SqlDatabaseInput();
input.Name = databaseName;
input.CollationName = databaseCollation ?? string.Empty;
//determine the edition
if (databaseEdition != DatabaseEdition.None)
{
input.Edition = databaseEdition.ToString();
}
else
{
input.Edition = string.Empty;
}
//determine the maximum size
if (databaseMaxSize.HasValue)
{
input.MaxSizeGB = databaseMaxSize.ToString();
}
//create a new database on the server
SqlDatabaseResponse response =
channel.EndNewDatabase(
channel.BeginNewDatabase(this.subscriptionId, this.serverName, input, null, null));
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="databaseMaxSize">The new maximum size of the database</param>
/// <param name="databaseEdition">The new edition of the database</param>
/// <returns>The updated database</returns>
public Database UpdateDatabase(
string databaseName,
string newDatabaseName,
int? databaseMaxSize,
DatabaseEdition? databaseEdition,
ServiceObjective serviceObjective)
{
this.clientRequestId = SqlDatabaseManagementHelper.GenerateClientTracingId();
ISqlDatabaseManagement channel = GetManagementChannel();
Database database = this.GetDatabase(databaseName);
//make sure the database exists.
if (database == null)
{
throw new Exception(
"Error: Result of GetDatabase() in ServerDataServiceCertAuth.UpdateDatabase() is null");
}
SqlDatabaseInput input = new SqlDatabaseInput();
//Set the database ID and collation
input.Id = database.Id.ToString();
input.CollationName = database.CollationName;
if (serviceObjective != null)
{
input.ServiceObjectiveId = serviceObjective.Id.ToString();
}
//Determine what the new name for the database should be
if (!string.IsNullOrEmpty(newDatabaseName))
{
input.Name = newDatabaseName;
}
else
{
input.Name = database.Name;
}
//Determine what the new edition for the database should be
if (databaseEdition.HasValue && (databaseEdition != DatabaseEdition.None))
{
input.Edition = databaseEdition.ToString();
}
else
{
input.Edition = database.Edition;
}
//Determine what the new maximum size for the database should be.
if (databaseMaxSize.HasValue)
{
input.MaxSizeGB = databaseMaxSize.ToString();
}
else
{
input.MaxSizeGB = database.MaxSizeGB.ToString();
}
//Send the update request and wait for the response.
SqlDatabaseResponse response = channel.EndUpdateDatabase(
channel.BeginUpdateDatabase(
this.subscriptionId,
this.serverName,
databaseName,
input,
null,
null));
//Transform the response into a database object.
Database updatedDatabase = CreateDatabaseFromResponse(response);
return updatedDatabase;
}
/// <summary>
/// Creates a new sql database.
/// </summary>
/// <param name="databaseName">The name for the new database</param>
/// <param name="databaseMaxSizeInGB">The maximum size of the new database</param>
/// <param name="databaseCollation">The collation for the new database</param>
/// <param name="databaseEdition">The edition for the new database</param>
/// <returns>The newly created Sql Database</returns>
public Database CreateNewDatabase(
string databaseName,
int? databaseMaxSizeInGB,
string databaseCollation,
DatabaseEdition databaseEdition)
{
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Get the SQL management client
SqlManagementClient sqlManagementClient = this.subscription.CreateClient<SqlManagementClient>();
this.AddTracingHeaders(sqlManagementClient);
// Create the database
DatabaseCreateResponse response = sqlManagementClient.Databases.Create(
this.serverName,
new DatabaseCreateParameters()
{
Name = databaseName,
Edition = databaseEdition != DatabaseEdition.None ?
databaseEdition.ToString() : DatabaseEdition.Web.ToString(),
CollationName = databaseCollation ?? string.Empty,
MaximumDatabaseSizeInGB = databaseMaxSizeInGB ??
(databaseEdition == DatabaseEdition.Business ? 10 : 1),
});
// 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,
long? databaseMaxSizeInBytes,
DatabaseEdition? databaseEdition,
ServiceObjective serviceObjective)
{
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Get the SQL management client
SqlManagementClient sqlManagementClient = AzureSession.ClientFactory.CreateClient<SqlManagementClient>(this.profile, subscription, AzureEnvironment.Endpoint.ServiceManagement);
this.AddTracingHeaders(sqlManagementClient);
// Retrieve the specified database
DatabaseGetResponse database = sqlManagementClient.Databases.Get(
this.serverName,
databaseName);
DatabaseUpdateParameters parameters = new DatabaseUpdateParameters()
{
Name = !string.IsNullOrEmpty(newDatabaseName) ? newDatabaseName : database.Database.Name,
MaximumDatabaseSizeInGB = databaseMaxSizeInGB,
MaximumDatabaseSizeInBytes = databaseMaxSizeInBytes,
};
parameters.Edition = (database.Database.Edition ?? string.Empty);
if (databaseEdition.HasValue)
{
if (databaseEdition != DatabaseEdition.None)
{
parameters.Edition = databaseEdition.ToString();
}
}
parameters.ServiceObjectiveId = database.Database.ServiceObjectiveId;
if (serviceObjective != null)
{
parameters.ServiceObjectiveId = serviceObjective.Id.ToString();
}
// Update the database with the new properties
DatabaseUpdateResponse response = sqlManagementClient.Databases.Update(
this.serverName,
databaseName,
parameters
);
// Construct the resulting Database object
Database updatedDatabase = CreateDatabaseFromResponse(response);
return updatedDatabase;
}
/// <summary>
/// Creates a new sql database.
/// </summary>
/// <param name="databaseName">The name for the new database</param>
/// <param name="databaseMaxSizeInGB">The maximum size of the new database</param>
/// <param name="databaseCollation">The collation for the new database</param>
/// <param name="databaseEdition">The edition for the new database</param>
/// <returns>The newly created Sql Database</returns>
public Database CreateNewDatabase(
string databaseName,
int? databaseMaxSizeInGB,
long? databaseMaxSizeInBytes,
string databaseCollation,
DatabaseEdition databaseEdition,
ServiceObjective serviceObjective)
{
this.clientRequestId = SqlDatabaseCmdletBase.GenerateClientTracingId();
// Get the SQL management client
SqlManagementClient sqlManagementClient = AzureSession.ClientFactory.CreateClient<SqlManagementClient>(this.profile, subscription, AzureEnvironment.Endpoint.ServiceManagement);
this.AddTracingHeaders(sqlManagementClient);
DatabaseCreateParameters parameters = new DatabaseCreateParameters()
{
Name = databaseName,
Edition = databaseEdition != DatabaseEdition.None ?
databaseEdition.ToString() : null,
CollationName = databaseCollation ?? string.Empty,
MaximumDatabaseSizeInGB = databaseMaxSizeInGB,
MaximumDatabaseSizeInBytes = databaseMaxSizeInBytes,
ServiceObjectiveId = serviceObjective != null ? serviceObjective.Id.ToString() : null,
};
// Create the database
DatabaseCreateResponse response = sqlManagementClient.Databases.Create(
this.serverName,
parameters);
// 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;
}
/// <summary>
/// Creates a new Sql Database.
/// </summary>
/// <param name="databaseName">The name for the new database.</param>
/// <param name="databaseMaxSize">The max size for the database.</param>
/// <param name="databaseCollation">The collation for the database.</param>
/// <param name="databaseEdition">The edition for the database.</param>
/// <returns>The newly created Sql Database.</returns>
public Database CreateNewDatabase(
string databaseName,
int? databaseMaxSize,
string databaseCollation,
DatabaseEdition databaseEdition)
{
// Create a new request Id for this operation
this.clientRequestId = SqlDatabaseManagementHelper.GenerateClientTracingId();
// Create the new entity and set its properties
Database database = new Database();
database.Name = databaseName;
if (databaseMaxSize != null)
{
database.MaxSizeGB = (int)databaseMaxSize;
}
if (!string.IsNullOrEmpty(databaseCollation))
{
database.CollationName = databaseCollation;
}
if (databaseEdition != DatabaseEdition.None)
{
database.Edition = databaseEdition.ToString();
}
// Save changes
this.AddToDatabases(database);
try
{
this.SaveChanges(SaveChangesOptions.None);
// Re-Query the database for server side updated information
database = this.RefreshEntity(database);
if (database == null)
{
throw new ApplicationException(Resources.ErrorRefreshingDatabase);
}
}
catch
{
this.ClearTrackedEntity(database);
throw;
}
// Load the extra properties for this object.
database.LoadExtraProperties(this);
return database;
}
/// <summary>
/// Updates the property on the database with the name <paramref name="databaseName"/>.
/// </summary>
/// <param name="databaseName">The database to update.</param>
/// <param name="newDatabaseName">
/// The new database name, or <c>null</c> to not update.
/// </param>
/// <param name="databaseMaxSize">
/// The new database max size, or <c>null</c> to not update.
/// </param>
/// <param name="databaseEdition">
/// The new database edition, or <c>null</c> to not update.
/// </param>
/// <returns>The updated database object.</returns>
public Database UpdateDatabase(
string databaseName,
string newDatabaseName,
int? databaseMaxSize,
DatabaseEdition? databaseEdition)
{
// Find the database by name
Database database = GetDatabase(databaseName);
// Update the name if specified
if (newDatabaseName != null)
{
database.Name = newDatabaseName;
}
// Update the max size and edition properties
database.MaxSizeGB = databaseMaxSize;
database.Edition = databaseEdition == null ? null : databaseEdition.ToString();
database.IsRecursiveTriggersOn = null;
// Mark the database object for update and submit the changes
this.UpdateObject(database);
try
{
this.SaveChanges();
}
catch
{
this.RevertChanges(database);
throw;
}
return this.GetDatabase(database.Name);
}
/// <summary>
/// Process the request using a temporary connection context using certificate authentication
/// </summary>
/// <param name="databaseName">The name of the database to update</param>
/// <param name="maxSizeGb">the new size for the database or null</param>
/// <param name="edition">the new edition for the database or null</param>
private void ProcessWithServerName(string databaseName, int? maxSizeGb, DatabaseEdition? edition)
{
string clientRequestId = null;
try
{
// Get the current subscription data.
SubscriptionData subscriptionData = this.GetCurrentSubscription();
// Create a temporary context
ServerDataServiceCertAuth context =
ServerDataServiceCertAuth.Create(this.ServerName, subscriptionData);
clientRequestId = context.ClientRequestId;
// Remove the database with the specified name
Database database = context.UpdateDatabase(
databaseName,
this.NewDatabaseName,
maxSizeGb,
edition,
this.ServiceObjective);
// If PassThru was specified, write back the updated object to the pipeline
if (this.PassThru.IsPresent)
{
this.WriteObject(database);
}
if (this.MyInvocation.BoundParameters.ContainsKey("Database"))
{
this.Database.CopyFields(database);
}
}
catch (Exception ex)
{
SqlDatabaseExceptionHandler.WriteErrorDetails(
this,
clientRequestId,
ex);
}
}
