private static void CreateXmlReport(string srcConString, SqlConnectionStringBuilder TargetCon, DacServices TargetdacServices, DacPackage dacpac)
{
var deployReport = TargetdacServices.GenerateDeployReport(dacpac, TargetCon.InitialCatalog);
var outReportPath = GetDacFileName(srcConString) + "_DeployReport.xml";
System.IO.File.WriteAllText(outReportPath, deployReport);
Console.WriteLine("DeployReport.{0}", deployReport);
}
private static string MainExec(string sourceDacFilePath, string sourceConnectionString, string targerConnectionString, string username, string password, DacDeployOptions options = null, CancellationTokenSource C_Token = null)
{
using (var impersonator = new ImpersonateIt())
{
impersonator.Impersonate(username, password);
//if (!System.IO.File.Exists(sourceDacFilePath))
//{
// Console.WriteLine("source dac file does not exists, Creating new file. ");
// if (string.IsNullOrWhiteSpace(sourceConnectionString))
// {
// Console.Error.WriteLine("Source Connection string is required for creating a bac file.");
// return string.Empty;
// }
//}
Export(sourceConnectionString, @"C:\Temp\Source_dacFile.dacpac");
Export(targerConnectionString, @"C:\Temp\Target_dacFile.dacpac");
var TargetCon = new SqlConnectionStringBuilder(targerConnectionString);
var TargetdacServices = new DacServices(TargetCon.ConnectionString);
TargetdacServices.Message += ((s, e) => { Console.WriteLine(e?.Message.ToString()); });
TargetdacServices.ProgressChanged += ((s, e) => { Console.WriteLine("Status:{0}, Message:{1}", e?.Status, e?.Message.ToString()); });
if (options == null)
{
options = new DacDeployOptions();
}
using (DacPackage dacpac = DacPackage.Load(sourceDacFilePath, DacSchemaModelStorageType.Memory))
{
// Script then deploy, to support debugging of the generated plan
// string script = dacServices.GenerateDeployScript(dacpac, dbName, options);
var deployReport = TargetdacServices.GenerateDeployReport(dacpac, TargetCon.InitialCatalog);
var deployScript = TargetdacServices.GenerateDeployScript(dacpac, TargetCon.InitialCatalog);
var DiffReport = TargetdacServices.GenerateDriftReport(TargetCon.InitialCatalog);
var outReportPath = Path.Combine(@"C:\Temp\", "DeployReport_" + DateTime.Now.ToString("yyyyMMMdd HHmmsstt") + ".sql");
System.IO.File.WriteAllText(outReportPath, deployReport);
var outScriptPath = Path.Combine(@"C:\Temp\", "DeployScript_" + DateTime.Now.ToString("yyyyMMMdd HHmmsstt") + ".sql");
System.IO.File.WriteAllText(outScriptPath, deployScript);
var outDiffReport = Path.Combine(@"C:\Temp\", "DeployDiff_" + DateTime.Now.ToString("yyyyMMMdd HHmmsstt") + ".sql");
System.IO.File.WriteAllText(outDiffReport, DiffReport);
Console.WriteLine("output Report and script generated.");
Console.WriteLine("DeployReport.{0}", deployReport);
Console.WriteLine("DiffReport.{0}", DiffReport);
Console.WriteLine("DeployScript.{0}", deployScript);
return("Done.");
}
}
return("");
}
public string GenerateReport()
{
VerifyProperties("GenerateReport");
// Get the database deployment options.
DacDeployOptions deployOptions = GetDeployOptions();
// Use the package from the SourcePackage property.
// But if that is null, then create a temporary package.
// Use try/finally to ensure that the temporary file gets deleted.
DacPackage tempSourcePackage = SourcePackage;
FileInfo tempSourcePackageFile = null;
string deployReport;
try
{
if (tempSourcePackage == null)
{
tempSourcePackageFile = new FileInfo(Path.GetTempFileName());
tempSourcePackage = ExtractSource(tempSourcePackageFile);
}
// Generate the deploy report.
if (this.TargetPackage == null)
{
// If the target package is not specified, then create a DacServices instance
// and use the GenerateDeployReport instance method that takes a target database name.
DacServices targetServices = GetTargetDacServices();
deployReport = targetServices.GenerateDeployReport(tempSourcePackage, TargetDatabaseName, deployOptions);
}
else
{
// Otherwise, since the target package is specified, use the DacServices.GenerateDeployReport
// static method that takes a target package.
deployReport = DacServices.GenerateDeployReport(tempSourcePackage, TargetPackage, TargetDatabaseName ?? TargetPackage.Name, deployOptions);
}
}
finally
{
if (tempSourcePackageFile != null)
{
tempSourcePackageFile.Delete();
}
}
return(deployReport);
}
/// <summary>
/// Deploys the database.
/// </summary>
/// <param name="connection"></param>
/// <param name="databaseName"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public async Task DeployAsync(SqlConnection connection, string databaseName, DacProfile profile, CancellationToken cancellationToken)
{
if (File.Exists(source) == false)
{
throw new FileNotFoundException($"Missing DACPAC '{source}'. Ensure project has been built successfully.", source);
}
// will use to identify instance
var instanceName = await connection.GetServerInstanceName(cancellationToken);
// check that existing database does not already exist with tag
if (await IsOutOfDate(connection, databaseName, cancellationToken) == false)
{
logger.LogInformation("Database {Name} is up to date.", databaseName);
return;
}
try
{
// lock database for deployment
connection.ChangeDatabase("master");
if (await connection.GetAppLock($"DATABASE::{databaseName}", timeout: (int)TimeSpan.FromMinutes(5).TotalMilliseconds) < 0)
{
throw new SqlDeploymentException($"Unable to acquire database lock on '{databaseName}'.");
}
// load up the DAC services
using var dac = LoadDacPackage(source);
var svc = new DacServices(connection.ConnectionString);
svc.Message += (s, a) => LogDacServiceMessage(instanceName, databaseName, a);
svc.ProgressChanged += (s, a) => LogDacServiceProgress(instanceName, databaseName, a);
var prf = profile ?? new DacProfile();
var opt = prf.DeployOptions;
// will specifically drop these
opt.DoNotAlterReplicatedObjects = false;
// check if database exists
if (await connection.ExecuteScalarAsync((string)$"SELECT db_id('{databaseName}')") is short dbid)
{
connection.ChangeDatabase(databaseName);
// some items are replicated
var helpDbReplicationOption = await connection.ExecuteSpHelpReplicationDbOptionAsync(databaseName, cancellationToken);
if (helpDbReplicationOption.TransactionalPublish ||
helpDbReplicationOption.MergePublish)
{
if ((int)await connection.ExecuteScalarAsync("SELECT COUNT(*) FROM sysarticles", null, cancellationToken) > 0)
{
var reportTxt = svc.GenerateDeployReport(dac, databaseName, opt, cancellationToken);
var reportXml = XDocument.Parse(reportTxt);
foreach (var operation in reportXml.Root.Elements(dacRptNs + "Operations").Elements(dacRptNs + "Operation"))
{
if ((string)operation.Attribute("Name") == "TableRebuild" ||
(string)operation.Attribute("Name") == "Drop" ||
(string)operation.Attribute("Name") == "Alter" ||
(string)operation.Attribute("Name") == "Rename")
{
foreach (var item in operation.Elements(dacRptNs + "Item"))
{
if ((string)item.Attribute("Type") == "SqlTable" ||
(string)item.Attribute("Type") == "SqlSimpleColumn")
{
await DropReplicatedTableAsync(connection, (string)item.Attribute("Value"), cancellationToken);
}
}
}
}
}
}
}
// deploy database
connection.ChangeDatabase("master");
logger.LogInformation("Publishing {DacPacFile} to {Database} at {InstanceName}.", source, databaseName, instanceName);
svc.Deploy(dac, databaseName, true, opt, cancellationToken);
// generate files for file groups
connection.ChangeDatabase(databaseName);
foreach (var group in await GetFileGroupsWithMissingFiles(connection, cancellationToken))
{
await CreateDefaultFilesForFileGroup(connection, databaseName, group, cancellationToken);
}
// record that the version we just deployed
await SetDacTag(connection, databaseName, GetDacTag(source), cancellationToken);
}
catch (SqlException e)
{
logger.LogError(e, "Exception deploying DACPAC to {Name} at {InstanceName}.", databaseName, instanceName);
throw;
}
finally
{
try
{
// we might have been closed as part of the error
if (connection.State == ConnectionState.Open)
{
connection.ChangeDatabase("master");
await connection.ReleaseAppLock($"DATABASE::{databaseName}");
}
}
catch (SqlException e)
{
logger.LogError(e, "Unable to release database lock on {Name}.", databaseName);
throw;
}
}
}
