private static int FileToDatabaseSync(F2dbOptions opts)
{
if (string.IsNullOrEmpty(opts.WorkingDirectory))
{
opts.WorkingDirectory = Environment.CurrentDirectory;
}
var db = opts.Database;
var pk = BacPackage.Load(opts.InputFile);
var spec = new DacAzureDatabaseSpecification
{
Edition = DacAzureEdition.Basic
};
using (var sqlConn = new SqlConnection(opts.OutputConnectionString))
using (var singleUserCmd = new SqlCommand($"IF db_id('{db}') is not null ALTER DATABASE [{db}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", sqlConn))
using (var dropCmd = new SqlCommand($"IF db_id('{db}') is not null DROP DATABASE [{db}]", sqlConn))
{
sqlConn.Open();
singleUserCmd.ExecuteNonQuery();
dropCmd.ExecuteNonQuery();
}
var local = new DacServices(opts.OutputConnectionString);
local.ProgressChanged += (sender, eventArgs) => { Console.WriteLine($"[{db}] {eventArgs.Message}"); };
local.ImportBacpac(pk, db, spec);
if (!string.IsNullOrEmpty(opts.LocalUser))
{
using (var sqlConn = new SqlConnection(opts.OutputConnectionString))
using (var loginCmd = new SqlCommand($"USE [{db}]; CREATE USER [{opts.LocalUser}] FOR LOGIN [{opts.LocalUser}]; USE [{db}]; ALTER ROLE [db_owner] ADD MEMBER [{opts.LocalUser}];", sqlConn))
{
sqlConn.Open();
try
{
loginCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"WARNING: Couldn't add user {opts.LocalUser} because: {ex.Message}");
}
}
}
Console.Write("done.");
Console.WriteLine();
return(0);
}
public static void SetDeployProperties(this DacDeployOptions deployOptions, string[] deployProperties, IConsole console = null)
{
foreach (var deployProperty in deployProperties.Where(p => string.IsNullOrWhiteSpace(p) == false))
{
var databaseProperty = DatabaseProperty.Create(deployProperty);
var propertyValue = deployOptions.SetDeployProperty(databaseProperty.Name, databaseProperty.Value);
if (console != null)
{
var parsedValue = propertyValue switch
{
ObjectType[] o => string.Join(',', o),
DacAzureDatabaseSpecification s => $"{s.Edition},{s.MaximumSize},{s.ServiceObjective}",
_ => propertyValue == null ? "null" : propertyValue.ToString()
};
console.WriteLine($"Setting property {databaseProperty.Name} to value {parsedValue}");
}
}
}
private void SQLDBBacPacImportFromFile(DacAzureEdition edition, bool importBlockOnPossibleDataLoss, string importDBConnectionString, string importDBName, string importFileFullPath)
{
DacDeployOptions dacOptions = new DacDeployOptions();
dacOptions.BlockOnPossibleDataLoss = importBlockOnPossibleDataLoss;
DacServices dacServiceInstance = new DacServices(importDBConnectionString);
// There are two events to get feed back during import.
//dacServiceInstance.Message += EventHandlerDACMessage;
//dacServiceInstance.ProgressChanged += EventHandlerDacServiceInstanceProgressChanged;
using (BacPackage dacpac = BacPackage.Load(importFileFullPath))
{
DacAzureDatabaseSpecification dbSpec = new DacAzureDatabaseSpecification();
dbSpec.Edition = edition;
dacServiceInstance.ImportBacpac(dacpac, importDBName);
dacpac.Dispose();
}
}
private static async Task PublishDatabases(string[] args, CancellationToken cancellation)
{
#region Arguments
PublisherOptions opt;
SqlConnectionStringBuilder adminConnBldr = null;
{
// Build the configuration root based on project user secrets
IConfiguration config = new ConfigurationBuilder()
.AddUserSecrets(typeof(Program).Assembly)
.AddCommandLine(args) // Higher precedence
.Build();
opt = config.Get <PublisherOptions>();
// DACPAC File (Required)
if (!opt.SkipPublish)
{
while (string.IsNullOrWhiteSpace(opt.DacpacFile))
{
Write("Enter path to DACPAC file: ");
opt.DacpacFile = ReadLine();
if (opt.DacpacFile == null)
{
throw new OperationCanceledException();
}
}
if (!opt.DacpacFile.EndsWith(".dacpac"))
{
throw new ArgumentException($"DACPAC file must have the \".dacpac\" extension");
}
else if (!File.Exists(opt.DacpacFile))
{
throw new ArgumentException($"No DACPAC found at \"{opt.DacpacFile}\"");
}
}
// Admin Connection (Required)
while (string.IsNullOrWhiteSpace(opt.AdminConnection))
{
Write("Enter the admin DB connection string: ");
opt.AdminConnection = ReadLine();
if (opt.AdminConnection == null)
{
throw new OperationCanceledException();
}
}
try
{
adminConnBldr = new SqlConnectionStringBuilder(opt.AdminConnection);
}
catch
{
throw new ArgumentException($"Invalid connection string \"{opt.AdminConnection}\"");
}
// Pre-Publish Script
if (!string.IsNullOrWhiteSpace(opt.PrePublishScript) && !File.Exists(opt.PrePublishScript))
{
throw new ArgumentException($"No Pre-Publish script found at \"{opt.PrePublishScript}\"");
}
// Backup Folder
string workingDirectory = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
opt.BackupFolder ??= Path.Combine(workingDirectory, "Backups");
// To avoid negative batch size
opt.BatchSize = Math.Max(opt.BatchSize, 1);
}
#endregion
#region Tenants
List <PublishWorkspace> workspaces;
{
var adminOpt = Options.Create(new AdminRepositoryOptions {
ConnectionString = adminConnBldr.ConnectionString
});
var logger = new NullLogger <AdminRepository>();
var repo = new AdminRepository(adminOpt, logger);
var ctx = new QueryContext(0);
WriteLine($"Loading tenants info from server \"{adminConnBldr.DataSource}\"...");
var databases = await repo.SqlDatabases
.Expand(nameof(SqlDatabase.Server))
.OrderBy(nameof(SqlDatabase.Id))
.ToListAsync(ctx, cancellation);
workspaces = databases.Select(db =>
{
var connInfo = AdminRepositoryConnectionResolver.ToConnectionInfo(
serverName: db.Server.ServerName,
dbName: db.DatabaseName,
userName: db.Server.UserName,
_: db.Server.PasswordKey,
adminConnBuilder: adminConnBldr);
var dbConnBldr = new SqlConnectionStringBuilder
{
DataSource = connInfo.ServerName,
InitialCatalog = connInfo.DatabaseName,
UserID = connInfo.UserName,
Password = connInfo.Password,
IntegratedSecurity = connInfo.IsWindowsAuth,
PersistSecurityInfo = false,
};
return(new PublishWorkspace
{
DbName = db.DatabaseName,
ConnectionString = dbConnBldr.ConnectionString
});
})
.ToList();
Write($"\u2713 ", ConsoleColor.Green);
Write($"Found DBs:");
WriteLine($" [{string.Join("], [", workspaces.Select(w => w.DbName))}]", ConsoleColor.Cyan);
}
#endregion
#region Confirmation
if (!opt.SkipConfirmation)
{
string confirmed = "Confirmed";
WriteLine();
Write($"Confirm going ahead with all tenant DBs by typing ");
Write($"\"{confirmed}\"");
Write($": ");
var answer = ReadLine();
if (answer == null)
{
throw new OperationCanceledException();
}
else if (answer.ToLower() != confirmed.ToLower())
{
Write($"X", ConsoleColor.Red);
Write($" Did not confirm");
WriteLine();
return;
}
else
{
Write($"\u2713 ", ConsoleColor.Green);
Write("Confirmation acquired.");
WriteLine();
}
}
#endregion
#region Backup Dir
DateTime now = DateTime.Now;
string backupsPath = null;
if (!opt.SkipBackup)
{
string nowString = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss");
backupsPath = Path.Combine(opt.BackupFolder, nowString);
Directory.CreateDirectory(backupsPath);
Write($"\u2713 ", ConsoleColor.Green);
Write($"Backup directory created at ");
Write(backupsPath, ConsoleColor.Cyan);
Write($".");
WriteLine();
}
#endregion
#region PrePublish Script
string prePublishScript = null;
if (!string.IsNullOrWhiteSpace(opt.PrePublishScript))
{
prePublishScript = await File.ReadAllTextAsync(opt.PrePublishScript, cancellation);
}
#endregion
DacPackage dacPackage = null;
try
{
#region DACPAC
// Get the DACPAC package
if (!opt.SkipPublish)
{
dacPackage = DacPackage.Load(opt.DacpacFile);
{
// Sanity check just in case
string expectedName = "Tellma.Database.Application";
if (dacPackage.Name != expectedName)
{
throw new ArgumentException($"The DACPAC file \"{opt.DacpacFile}\" does not have the name {expectedName}.");
}
Write($"\u2713 ", ConsoleColor.Green);
WriteLine($"DACPAC version {dacPackage.Version} loaded.");
}
}
#endregion
#region Backup and Publish
WriteLine();
WriteLine($"Operation started at {now:hh:mm:ss tt}...");
foreach (var workspace in workspaces)
{
workspace.Top = Console.CursorTop;
workspace.UpdateStatus("Getting ready");
WriteLine();
}
int skip = 0;
while (!cancellation.IsCancellationRequested)
{
var batch = workspaces.Skip(skip).Take(opt.BatchSize);
if (batch.Any())
{
await Task.WhenAll(batch.Select(async ws =>
{
try
{
#region DacService
var service = new DacServices(ws.ConnectionString);
service.Message += (object s, DacMessageEventArgs e) =>
{
ws.UpdateStatus(e.Message);
};
#endregion
#region Backup
if (!opt.SkipBackup)
{
// Export Package
string bacpacPath = Path.Combine(backupsPath, $"{ws.DbName}.bacpac");
await Task.Run(() => service.ExportBacpac(bacpacPath, ws.DbName, null, cancellation), cancellation);
}
#endregion
#region Pre-Publish Script
if (!string.IsNullOrWhiteSpace(opt.PrePublishScript))
{
ws.UpdateStatus("Executing Pre-Publish Script (Started)");
using var conn = new Microsoft.Data.SqlClient.SqlConnection(ws.ConnectionString);
await Task.Run(() =>
{
Server server = new(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(prePublishScript);
});
ws.UpdateStatus("Executing Pre-Publish Script (Completed)");
}
#endregion
if (!opt.SkipPublish)
{
#region DB Specs
DacAzureDatabaseSpecification specs = null;
{
ws.UpdateStatus("Retrieving DB Specs (Started)");
using var conn = new SqlConnection(ws.ConnectionString);
using var cmd = conn.CreateCommand();
cmd.CommandText = @$ "
IF OBJECT_ID(N'sys.database_service_objectives') IS NOT NULL
SELECT
[edition] AS [Edition],
public void SetProperty(string key, string value)
{
try
{
// Convert value into the appropriate type depending on the key
object propertyValue = key switch
{
"AdditionalDeploymentContributorArguments" => value,
"AdditionalDeploymentContributorPaths" => value,
"AdditionalDeploymentContributors" => value,
"AllowDropBlockingAssemblies" => bool.Parse(value),
"AllowIncompatiblePlatform" => bool.Parse(value),
"AllowUnsafeRowLevelSecurityDataMovement" => bool.Parse(value),
"BackupDatabaseBeforeChanges" => bool.Parse(value),
"BlockOnPossibleDataLoss" => bool.Parse(value),
"BlockWhenDriftDetected" => bool.Parse(value),
"CommandTimeout" => int.Parse(value),
"CommentOutSetVarDeclarations" => bool.Parse(value),
"CompareUsingTargetCollation" => bool.Parse(value),
"CreateNewDatabase" => bool.Parse(value),
"DatabaseLockTimeout" => int.Parse(value),
"DatabaseSpecification" => ParseDatabaseSpecification(value),
"DeployDatabaseInSingleUserMode" => bool.Parse(value),
"DisableAndReenableDdlTriggers" => bool.Parse(value),
"DoNotAlterChangeDataCaptureObjects" => bool.Parse(value),
"DoNotAlterReplicatedObjects" => bool.Parse(value),
"DoNotDropObjectTypes" => ParseObjectTypes(value),
"DropConstraintsNotInSource" => bool.Parse(value),
"DropDmlTriggersNotInSource" => bool.Parse(value),
"DropExtendedPropertiesNotInSource" => bool.Parse(value),
"DropIndexesNotInSource" => bool.Parse(value),
"DropObjectsNotInSource" => bool.Parse(value),
"DropPermissionsNotInSource" => bool.Parse(value),
"DropRoleMembersNotInSource" => bool.Parse(value),
"DropStatisticsNotInSource" => bool.Parse(value),
"ExcludeObjectTypes" => ParseObjectTypes(value),
"GenerateSmartDefaults" => bool.Parse(value),
"IgnoreAnsiNulls" => bool.Parse(value),
"IgnoreAuthorizer" => bool.Parse(value),
"IgnoreColumnCollation" => bool.Parse(value),
"IgnoreColumnOrder" => bool.Parse(value),
"IgnoreComments" => bool.Parse(value),
"IgnoreCryptographicProviderFilePath" => bool.Parse(value),
"IgnoreDdlTriggerOrder" => bool.Parse(value),
"IgnoreDdlTriggerState" => bool.Parse(value),
"IgnoreDefaultSchema" => bool.Parse(value),
"IgnoreDmlTriggerOrder" => bool.Parse(value),
"IgnoreDmlTriggerState" => bool.Parse(value),
"IgnoreExtendedProperties" => bool.Parse(value),
"IgnoreFileAndLogFilePath" => bool.Parse(value),
"IgnoreFilegroupPlacement" => bool.Parse(value),
"IgnoreFileSize" => bool.Parse(value),
"IgnoreFillFactor" => bool.Parse(value),
"IgnoreFullTextCatalogFilePath" => bool.Parse(value),
"IgnoreIdentitySeed" => bool.Parse(value),
"IgnoreIncrement" => bool.Parse(value),
"IgnoreIndexOptions" => bool.Parse(value),
"IgnoreIndexPadding" => bool.Parse(value),
"IgnoreKeywordCasing" => bool.Parse(value),
"IgnoreLockHintsOnIndexes" => bool.Parse(value),
"IgnoreLoginSids" => bool.Parse(value),
"IgnoreNotForReplication" => bool.Parse(value),
"IgnoreObjectPlacementOnPartitionScheme" => bool.Parse(value),
"IgnorePartitionSchemes" => bool.Parse(value),
"IgnorePermissions" => bool.Parse(value),
"IgnoreQuotedIdentifiers" => bool.Parse(value),
"IgnoreRoleMembership" => bool.Parse(value),
"IgnoreRouteLifetime" => bool.Parse(value),
"IgnoreSemicolonBetweenStatements" => bool.Parse(value),
"IgnoreTableOptions" => bool.Parse(value),
"IgnoreTablePartitionOptions" => bool.Parse(value),
"IgnoreUserSettingsObjects" => bool.Parse(value),
"IgnoreWhitespace" => bool.Parse(value),
"IgnoreWithNocheckOnCheckConstraints" => bool.Parse(value),
"IgnoreWithNocheckOnForeignKeys" => bool.Parse(value),
"IncludeCompositeObjects" => bool.Parse(value),
"IncludeTransactionalScripts" => bool.Parse(value),
"LongRunningCommandTimeout" => int.Parse(value),
"NoAlterStatementsToChangeClrTypes" => bool.Parse(value),
"PopulateFilesOnFileGroups" => bool.Parse(value),
"RegisterDataTierApplication" => bool.Parse(value),
"RunDeploymentPlanExecutors" => bool.Parse(value),
"ScriptDatabaseCollation" => bool.Parse(value),
"ScriptDatabaseCompatibility" => bool.Parse(value),
"ScriptDatabaseOptions" => bool.Parse(value),
"ScriptDeployStateChecks" => bool.Parse(value),
"ScriptFileSize" => bool.Parse(value),
"ScriptNewConstraintValidation" => bool.Parse(value),
"ScriptRefreshModule" => bool.Parse(value),
"SqlCommandVariableValues" => throw new ArgumentException("SQLCMD variables should be set using the --sqlcmdvar command line argument and not as a property."),
"TreatVerificationErrorsAsWarnings" => bool.Parse(value),
"UnmodifiableObjectWarnings" => bool.Parse(value),
"VerifyCollationCompatibility" => bool.Parse(value),
"VerifyDeployment" => bool.Parse(value),
_ => throw new ArgumentException($"Unknown property with name {key}", nameof(key))
};
PropertyInfo property = typeof(DacDeployOptions).GetProperty(key, BindingFlags.Public | BindingFlags.Instance);
property.SetValue(DeployOptions, propertyValue);
var parsedValue = propertyValue switch
{
ObjectType[] o => string.Join(',', o),
DacAzureDatabaseSpecification s => $"{s.Edition},{s.MaximumSize},{s.ServiceObjective}",
_ => propertyValue.ToString()
};
_console.WriteLine($"Setting property {key} to value {parsedValue}");
}
catch (FormatException)
{
throw new ArgumentException($"Unable to parse value for property with name {key}: {value}", nameof(value));
}
}
