private bool CompareDatabaseSchemas(string databaseName1, string databaseName2)
{
var initialConnectionString = Environment.GetEnvironmentVariable("SqlServer:ConnectionString") ?? LocalConnectionString;
var testConnectionString1 = new SqlConnectionStringBuilder(initialConnectionString)
{
InitialCatalog = databaseName1
}.ToString();
var testConnectionString2 = new SqlConnectionStringBuilder(initialConnectionString)
{
InitialCatalog = databaseName2
}.ToString();
var source = new SchemaCompareDatabaseEndpoint(testConnectionString1);
var target = new SchemaCompareDatabaseEndpoint(testConnectionString2);
var comparison = new SchemaComparison(source, target)
{
Options = { IgnoreWhitespace = true, IgnoreComments = true },
};
SchemaComparisonResult result = comparison.Compare();
// These types were introduced in earlier schema versions but are no longer used in newer versions.
// They are not removed so as to no break compatibility with instances requiring an older schema version.
// Exclude them from the schema comparison differences.
(string type, string name)[] deprecatedObjectToIgnore =
/// <summary>
/// Handles request for schema compare generate deploy script
/// </summary>
/// <returns></returns>
public async Task HandleSchemaCompareGenerateScriptRequest(SchemaCompareGenerateScriptParams parameters, RequestContext <ResultStatus> requestContext)
{
SchemaCompareGenerateScriptOperation operation = null;
try
{
SchemaComparisonResult compareResult = schemaCompareResults.Value[parameters.OperationId];
operation = new SchemaCompareGenerateScriptOperation(parameters, compareResult);
SqlTask sqlTask = null;
TaskMetadata metadata = new TaskMetadata();
metadata.TaskOperation = operation;
metadata.TaskExecutionMode = parameters.TaskExecutionMode;
metadata.ServerName = parameters.TargetServerName;
metadata.DatabaseName = parameters.TargetDatabaseName;
metadata.Name = SR.GenerateScriptTaskName;
sqlTask = SqlTaskManagerInstance.CreateAndRun <SqlTask>(metadata);
await requestContext.SendResult(new ResultStatus()
{
Success = true,
ErrorMessage = operation.ErrorMessage
});
}
catch (Exception e)
{
Logger.Write(TraceEventType.Error, "Failed to generate schema compare script. Error: " + e);
await requestContext.SendResult(new ResultStatus()
{
Success = false,
ErrorMessage = operation == null ? e.Message : operation.ErrorMessage,
});
}
}
public async Task GivenTwoSchemaInitializationMethods_WhenCreatingTwoDatabases_BothSchemasShouldBeEquivalent()
{
// Create two databases, one where we apply the the maximum supported version's snapshot SQL schema file
SqlDataStoreTestsFixture snapshotFixture = new SqlDataStoreTestsFixture(SqlDataStoreTestsFixture.GenerateDatabaseName("SNAPSHOT"));
// And one where we apply .diff.sql files to upgrade the schema version to the maximum supported version.
SqlDataStoreTestsFixture diffFixture = new SqlDataStoreTestsFixture(SqlDataStoreTestsFixture.GenerateDatabaseName("DIFF"));
await snapshotFixture.InitializeAsync(forceIncrementalSchemaUpgrade : false);
await diffFixture.InitializeAsync(forceIncrementalSchemaUpgrade : true);
SchemaCompareDatabaseEndpoint snapshotEndpoint = new SchemaCompareDatabaseEndpoint(snapshotFixture.TestConnectionString);
SchemaCompareDatabaseEndpoint diffEndpoint = new SchemaCompareDatabaseEndpoint(diffFixture.TestConnectionString);
var comparison = new SchemaComparison(snapshotEndpoint, diffEndpoint);
SchemaComparisonResult result = comparison.Compare();
Assert.True(result.IsEqual);
// cleanup if succeeds
await snapshotFixture.DisposeAsync();
await diffFixture.DisposeAsync();
}
/// <summary>
/// Handles request for schema compare generate deploy script
/// </summary>
/// <returns></returns>
public async Task HandleSchemaCompareGenerateScriptRequest(SchemaCompareGenerateScriptParams parameters, RequestContext <ResultStatus> requestContext)
{
SchemaCompareGenerateScriptOperation operation = null;
try
{
SchemaComparisonResult compareResult = schemaCompareResults.Value[parameters.OperationId];
operation = new SchemaCompareGenerateScriptOperation(parameters, compareResult);
SqlTask sqlTask = null;
TaskMetadata metadata = new TaskMetadata();
metadata.TaskOperation = operation;
// want to show filepath in task history instead of server and database
metadata.ServerName = parameters.ScriptFilePath;
metadata.DatabaseName = string.Empty;
metadata.Name = SR.GenerateScriptTaskName;
sqlTask = SqlTaskManagerInstance.CreateAndRun <SqlTask>(metadata);
await requestContext.SendResult(new ResultStatus()
{
Success = true,
ErrorMessage = operation.ErrorMessage
});
}
catch (Exception e)
{
await requestContext.SendResult(new ResultStatus()
{
Success = false,
ErrorMessage = operation == null ? e.Message : operation.ErrorMessage,
});
}
}
/// <summary>
/// Handles request for exclude incude node in Schema compare result
/// </summary>
/// <returns></returns>
public async Task HandleSchemaCompareIncludeExcludeNodeRequest(SchemaCompareNodeParams parameters, RequestContext <ResultStatus> requestContext)
{
SchemaCompareIncludeExcludeNodeOperation operation = null;
try
{
SchemaComparisonResult compareResult = schemaCompareResults.Value[parameters.OperationId];
operation = new SchemaCompareIncludeExcludeNodeOperation(parameters, compareResult);
operation.Execute(parameters.TaskExecutionMode);
// update the comparison result if the include/exclude was successful
if (operation.Success)
{
schemaCompareResults.Value[parameters.OperationId] = operation.ComparisonResult;
}
await requestContext.SendResult(new SchemaCompareIncludeExcludeResult()
{
Success = operation.Success,
ErrorMessage = operation.ErrorMessage,
AffectedDependencies = operation.AffectedDependencies,
BlockingDependencies = operation.BlockingDependencies
});
}
catch (Exception e)
{
Logger.Write(TraceEventType.Error, "Failed to select compare schema result node. Error: " + e);
await requestContext.SendResult(new ResultStatus()
{
Success = false,
ErrorMessage = operation == null ? e.Message : operation.ErrorMessage,
});
}
}
/// <summary>
/// Handles request for exclude incude node in Schema compare result
/// </summary>
/// <returns></returns>
public async Task HandleSchemaCompareIncludeExcludeNodeRequest(SchemaCompareNodeParams parameters, RequestContext <ResultStatus> requestContext)
{
SchemaCompareIncludeExcludeNodeOperation operation = null;
try
{
SchemaComparisonResult compareResult = schemaCompareResults.Value[parameters.OperationId];
operation = new SchemaCompareIncludeExcludeNodeOperation(parameters, compareResult);
operation.Execute(parameters.TaskExecutionMode);
await requestContext.SendResult(new ResultStatus()
{
Success = true,
ErrorMessage = operation.ErrorMessage
});
}
catch (Exception e)
{
Logger.Write(TraceEventType.Error, "Failed to select compare schema result node. Error: " + e);
await requestContext.SendResult(new ResultStatus()
{
Success = false,
ErrorMessage = operation == null ? e.Message : operation.ErrorMessage,
});
}
}
/// <summary>
/// Handles request for schema compare publish changes script
/// </summary>
/// <returns></returns>
public async Task HandleSchemaComparePublishChangesRequest(SchemaComparePublishChangesParams parameters, RequestContext <ResultStatus> requestContext)
{
SchemaComparePublishChangesOperation operation = null;
try
{
SchemaComparisonResult compareResult = schemaCompareResults.Value[parameters.OperationId];
operation = new SchemaComparePublishChangesOperation(parameters, compareResult);
SqlTask sqlTask = null;
TaskMetadata metadata = new TaskMetadata();
metadata.TaskOperation = operation;
metadata.ServerName = parameters.TargetServerName;
metadata.DatabaseName = parameters.TargetDatabaseName;
metadata.Name = SR.PublishChangesTaskName;
sqlTask = SqlTaskManagerInstance.CreateAndRun <SqlTask>(metadata);
await requestContext.SendResult(new ResultStatus()
{
Success = true,
ErrorMessage = operation.ErrorMessage
});
}
catch (Exception e)
{
await requestContext.SendResult(new ResultStatus()
{
Success = false,
ErrorMessage = operation == null ? e.Message : operation.ErrorMessage,
});
}
}
public async Task HandleSchemaCompareIncludeExcludeNodeRequest(SchemaCompareNodeParams parameters, RequestContext <ResultStatus> requestContext)
{
SchemaCompareIncludeExcludeNodeOperation operation = null;
try
{
SchemaComparisonResult compareResult = schemaCompareResults.Value[parameters.OperationId];
operation = new SchemaCompareIncludeExcludeNodeOperation(parameters, compareResult);
SqlTask sqlTask = null;
TaskMetadata metadata = new TaskMetadata();
metadata.TaskOperation = operation;
metadata.Name = parameters.IncludeRequest ? SR.IncludeNodeTaskName : SR.ExcludeNodeTaskName;
sqlTask = SqlTaskManagerInstance.CreateAndRun <SqlTask>(metadata);
await requestContext.SendResult(new ResultStatus()
{
Success = true,
ErrorMessage = operation.ErrorMessage
});
}
catch (Exception e)
{
await requestContext.SendResult(new ResultStatus()
{
Success = false,
ErrorMessage = operation == null ? e.Message : operation.ErrorMessage,
});
}
}
static void Main(string[] args)
{
//Loop thru all the scmp from the directory. This set to max 2 thread that run parallel and update together
Parallel.ForEach(Directory.GetFiles(schemaDirectory), new ParallelOptions {
MaxDegreeOfParallelism = 2
}, (file) =>
{
try
{
// Load comparison from Schema Compare (.scmp) file
var comparison = new SchemaComparison(file);
Console.WriteLine("Processing " + Path.GetFileName(file));
Console.WriteLine("Comparing schema...");
SchemaComparisonResult comparisonResult = comparison.Compare();
// Publish the changes to the target database
Console.WriteLine("Publishing schema...");
SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget();
Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed.");
Console.WriteLine(" ");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
});
Console.ReadLine();
}
public SchemaCompareGenerateScriptOperation(SchemaCompareGenerateScriptParams parameters, SchemaComparisonResult comparisonResult)
{
Validate.IsNotNull("parameters", parameters);
Validate.IsNotNull("scriptFilePath", parameters.ScriptFilePath);
this.Parameters = parameters;
Validate.IsNotNull("comparisonResult", comparisonResult);
this.ComparisonResult = comparisonResult;
}
public void ScmFileBasedCompare(string srcConString, string targetConString)
{
var src = new SqlConnectionStringBuilder(srcConString);
var bacPacFile = GetDacFileName(srcConString) + ".dacpac";
var dtVersion = DateTime.Now.ToString("yyyy.MM.dd.HHmmss");
//if Bac file not exists create new
var dacService = new Microsoft.SqlServer.Dac.DacServices(srcConString);
dacService.Extract(bacPacFile, src.InitialCatalog, "Test Application", Version.Parse(dtVersion));
var sourceDacpac = new SchemaCompareDacpacEndpoint(bacPacFile);
var target = new SqlConnectionStringBuilder(targetConString);
var targetDatabase = new SchemaCompareDatabaseEndpoint(targetConString);
var comparison = new SchemaComparison(sourceDacpac, targetDatabase);
// Persist comparison file to disk in Schema Compare (.scmp) format
comparison.SaveToFile(@"C:\temp\mycomparison.scmp");
// Load comparison from Schema Compare (.scmp) file
comparison = new SchemaComparison(@"C:\temp\mycomparison.scmp");
SchemaComparisonResult comparisonResult = comparison.Compare();
foreach (var d in comparisonResult.Differences)
{
Console.WriteLine(d.SourceObject.GetScript());
}
// Find the change to table1 and exclude it.
//foreach (SchemaDifference difference in comparisonResult.Differences)
//{
// if (difference.TargetObject.Name != null &&
// difference.TargetObject.Name.HasName &&
// difference.TargetObject.Name.Parts[1] == "DbConnections")
// {
// comparisonResult.Exclude(difference);
// break;
// }
//}
// Publish the changes to the target database
//SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget();
var publishResult = comparisonResult.GenerateScript(".");
Console.WriteLine(publishResult.MasterScript);
Console.WriteLine(publishResult.Script);
Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed.");
}
//#r "C:\Program Files\Microsoft SQL Server\140\DAC\bin\Microsoft.SqlServer.Dac.Extensions.dll"
public void Compare()
{
var csb = new SqlConnectionStringBuilder
{
DataSource = ".",
InitialCatalog = "ICareMVCMaster",
IntegratedSecurity = true
};
var bacPacFile = @"C:\temp\icaremvcmaster.dacpac";
var s = new Microsoft.SqlServer.Dac.DacServices(csb.ConnectionString);
s.Extract(bacPacFile, "IcareMVCMaster", "Test Application", new Version("1.1.1.1"));
var sourceDacpac = new SchemaCompareDacpacEndpoint(bacPacFile);
var csb2 = new SqlConnectionStringBuilder
{
DataSource = ".",
InitialCatalog = "New_ICareMVCMaster",
IntegratedSecurity = true
};
var targetDatabase = new SchemaCompareDatabaseEndpoint(csb2.ToString());
var comparison = new SchemaComparison(sourceDacpac, targetDatabase);
// Persist comparison file to disk in Schema Compare (.scmp) format
comparison.SaveToFile(@"C:\temp\mycomparison.scmp");
// Load comparison from Schema Compare (.scmp) file
comparison = new SchemaComparison(@"C:\temp\mycomparison.scmp");
SchemaComparisonResult comparisonResult = comparison.Compare();
// Find the change to table1 and exclude it.
//foreach (SchemaDifference difference in comparisonResult.Differences)
//{
// if (difference.TargetObject.Name != null &&
// difference.TargetObject.Name.HasName &&
// difference.TargetObject.Name.Parts[1] == "DbConnections")
// {
// comparisonResult.Exclude(difference);
// break;
// }
//}
// Publish the changes to the target database
//SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget();
var publishResult = comparisonResult.GenerateScript(".");
Console.WriteLine(publishResult.MasterScript);
Console.WriteLine(publishResult.Script);
Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed.");
}
public async Task GivenTwoSchemaInitializationMethods_WhenCreatingTwoDatabases_BothSchemasShouldBeEquivalent()
{
// Create two databases, one where we apply the the maximum supported version's snapshot SQL schema file
SqlDataStoreTestsFixture snapshotFixture = new SqlDataStoreTestsFixture(SqlDataStoreTestsFixture.GenerateDatabaseName("SNAPSHOT"));
// And one where we apply .diff.sql files to upgrade the schema version to the maximum supported version.
SqlDataStoreTestsFixture diffFixture = new SqlDataStoreTestsFixture(SqlDataStoreTestsFixture.GenerateDatabaseName("DIFF"));
await snapshotFixture.InitializeAsync(forceIncrementalSchemaUpgrade : false);
await diffFixture.InitializeAsync(forceIncrementalSchemaUpgrade : true);
SchemaCompareDatabaseEndpoint snapshotEndpoint = new SchemaCompareDatabaseEndpoint(snapshotFixture.TestConnectionString);
SchemaCompareDatabaseEndpoint diffEndpoint = new SchemaCompareDatabaseEndpoint(diffFixture.TestConnectionString);
var comparison = new SchemaComparison(snapshotEndpoint, diffEndpoint);
SchemaComparisonResult result = comparison.Compare();
// filter our sproc bodyscript differences because of auto-generation
var actualDiffs = new List <SchemaDifference>();
if (!result.IsEqual)
{
foreach (var diff in result.Differences)
{
if (diff.Name == "SqlProcedure")
{
foreach (var childDiff in diff.Children)
{
if (childDiff.Name != "BodyScript")
{
actualDiffs.Add(diff);
break;
}
}
}
else
{
actualDiffs.Add(diff);
}
}
}
Assert.Empty(actualDiffs);
// cleanup if succeeds
await snapshotFixture.DisposeAsync();
await diffFixture.DisposeAsync();
}
public SchemaComparisonResult Compare(string compareFileLocation = null)
{
var compare = new SchemaComparison(_sourceDacPac, _enpointDb);
compare.Options.BlockOnPossibleDataLoss = false;
if (compareFileLocation != null)
{
compare.SaveToFile(compareFileLocation);
}
compare = new SchemaComparison(compareFileLocation);
_compareResult = compare.Compare();
return(_compareResult);
}
public bool CompareDatabaseSchemas(string databaseName1, string databaseName2)
{
var testConnectionString1 = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName1
}.ToString();
var testConnectionString2 = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName2
}.ToString();
var source = new SchemaCompareDatabaseEndpoint(testConnectionString1);
var target = new SchemaCompareDatabaseEndpoint(testConnectionString2);
var comparison = new SchemaComparison(source, target);
SchemaComparisonResult result = comparison.Compare();
return(result.IsEqual);
}
public bool CompareDatabaseSchemas(string databaseName1, string databaseName2)
{
var testConnectionString1 = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName1
}.ToString();
var testConnectionString2 = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName2
}.ToString();
var source = new SchemaCompareDatabaseEndpoint(testConnectionString1);
var target = new SchemaCompareDatabaseEndpoint(testConnectionString2);
var comparison = new SchemaComparison(source, target);
SchemaComparisonResult result = comparison.Compare();
// These types were introduced in earlier schema versions but are no longer used in newer versions.
// They are not removed so as to no break compatibility with instances requiring an older schema version.
// Exclude them from the schema comparison differences.
(string type, string name)[] deprecatedObjectToIgnore =
private void Start()
{
var src = new SqlConnectionStringBuilder(this.SourceConString);
var target = new SqlConnectionStringBuilder(this.TargetConString);
//if Bac file not exists create new
//var bacPacFile = Path.Combine(GetWorkingFolder(), src.InitialCatalog + ".dacpac");
//var dacService = new Microsoft.SqlServer.Dac.DacServices(this.SourceConString);
//dacService.Extract(bacPacFile, src.InitialCatalog, "Test Application", Version.Parse(AppVersion));
//var sourceDacpac = new SchemaCompareDacpacEndpoint(bacPacFile);
Console.WriteLine("Creating Database end point connections.");
var sourceEndPoint = new SchemaCompareDatabaseEndpoint(this.SourceConString);
var targetEndPoint = new SchemaCompareDatabaseEndpoint(this.TargetConString);
Console.WriteLine("Initializing Schema Comparision with given endpoints");
var comparison = new SchemaComparison(sourceEndPoint, targetEndPoint);
//// Persist comparison file to disk in Schema Compare (.scmp) format
//var scmpFile = GetDacFileName(src.ConnectionString) + ".scmp";
//comparison.SaveToFile(scmpFile);
//// Load comparison from Schema Compare (.scmp) file
//comparison = new SchemaComparison(scmpFile);
Console.WriteLine("Schema Comparision Setting Options...");
comparison.Options = new Microsoft.SqlServer.Dac.DacDeployOptions()
{
};
if (Program.CommandArgs.ContainsKey("excludedTypes"))
{
var stringSplitter = new string[] { ",", ";", "|" };
var excludedTypes = new List <Microsoft.SqlServer.Dac.ObjectType>();
foreach (var type in Program.CommandArgs["excludedTypes"].ToString().Split(stringSplitter, StringSplitOptions.RemoveEmptyEntries))
{
Microsoft.SqlServer.Dac.ObjectType outType;
if (Enum.TryParse(type, out outType))
{
Console.WriteLine(" Adding excluded type {0}", outType.ToString());
excludedTypes.Add(outType);
}
}
comparison.Options.ExcludeObjectTypes = excludedTypes.ToArray();
}
Console.WriteLine("Schema Comparision Starting...");
//Set Options
SchemaComparisonResult comparisonResult = comparison.Compare();
Console.WriteLine("Schema Comparision Compated.");
var sourceFolder = GetSourceWorkingFolder(this.SourceConString);
var targetFolder = GetSourceWorkingFolder(this.TargetConString);
Console.WriteLine("Creating diff object scripts...");
foreach (var d in comparisonResult.Differences)
{
string objectName = string.Join(".", d.SourceObject?.Name.Parts ?? d.TargetObject.Name.Parts);
string objType = d.SourceObject?.ObjectType?.Name ?? d.TargetObject?.ObjectType?.Name;
if (string.IsNullOrWhiteSpace(objectName))
{
Console.WriteLine("Object Name is empty {0}", objectName);
continue;
}
WriteToFile(sourceFolder, objType, objectName, d.SourceObject?.ToString());
WriteToFile(targetFolder, objType, objectName, d.TargetObject?.GetScript());
}
// Find the change to table1 and exclude it.
//foreach (SchemaDifference difference in comparisonResult.Differences)
//{
// if (difference.TargetObject.Name != null &&
// difference.TargetObject.Name.HasName &&
// difference.TargetObject.Name.Parts[1] == "DbConnections")
// {
// comparisonResult.Exclude(difference);
// break;
// }
//}
// Publish the changes to the target database
//SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget();
var publishResult = comparisonResult.GenerateScript(target.InitialCatalog);
WriteToFile(GetWorkingFolder(), "", "MasterChangeScript.sql", publishResult.MasterScript);
WriteToFile(GetWorkingFolder(), "", "ChangeScript.sql", publishResult.Script);
Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed.");
System.IO.Compression.ZipFile.CreateFromDirectory(GetWorkingFolder(), Path.Combine(_basePath, "Artifact" + DateTime.Now.ToString("ddMMMyyHHmmss") + ".zip"));
}
public SchemaCompareIncludeExcludeNodeOperation(SchemaCompareNodeParams parameters, SchemaComparisonResult comparisonResult)
{
Validate.IsNotNull("parameters", parameters);
this.Parameters = parameters;
Validate.IsNotNull("comparisonResult", comparisonResult);
this.ComparisonResult = comparisonResult;
}
public SchemaCompareScriptGenerationResult GenerateScript()
{
ScriptGenerationResult = SchemaComparisonResult.GenerateScript("Testing");
return(ScriptGenerationResult);
}
// Constructors
/// <summary>
/// Initializes a new instance of this class.
/// </summary>
/// <param name="result">The schema comparison result.</param>
public SchemaSynchronizationException(SchemaComparisonResult result)
: this(string.Format(Strings.ExExtractedSchemaIsNotEqualToTheTargetSchema_DetailsX, result))
{
ComparisonResult = result;
}
public SchemaComparePublishChangesOperation(SchemaComparePublishChangesParams parameters, SchemaComparisonResult comparisonResult)
{
Validate.IsNotNull("parameters", parameters);
this.Parameters = parameters;
Validate.IsNotNull("comparisonResult", comparisonResult);
this.ComparisonResult = comparisonResult;
}
