bool IReplicationAnalyzer.AreTableSchemasReplicationCompliant(Table sourceTable, Table targetTable)
{
//All columns in source table has to contain target table
foreach (Column sourceColumn in sourceTable.Columns)
{
if (!targetTable.Columns.Any(c => c.Name == sourceColumn.Name && c.DataType == sourceColumn.DataType && c.IsPrimaryKey == sourceColumn.IsPrimaryKey))
{
_log.Debug($"AreTableSchemasReplicationCompliant - column not found | Table: {sourceTable.Name} | Column: {sourceColumn.Name} | Data type: {sourceColumn.DataType} | Is primary key: {sourceColumn.IsForeignKey}");
return false;
}
}
return true;
}
private static void InsertRow(Table sourceTable, Table targetTable, SqlDataReader reader, SqlConnection targetDatabaseConnection, SqlTransaction transaction, string identityInsertSetup, ISqlCommandFactory commandFactory)
{
SqlCommand insertCommand = commandFactory.CreateSqlCommand("", targetDatabaseConnection, transaction);
string insertCommandText = $@"USE [{targetTable.Database}]
{identityInsertSetup}
INSERT INTO {targetTable.Schema}.{targetTable.Name} ( ";
for (int i = 0; i < sourceTable.Columns.Length; i++)
insertCommandText += $"[{sourceTable.Columns[i].Name}] {((i < sourceTable.Columns.Length - 1) ? "," : ") VALUES (")}";
for (int i = 0; i < sourceTable.Columns.Length; i++)
{
string paramName = $"prm{i}";
insertCommandText += $"@{paramName} {((i < sourceTable.Columns.Length - 1) ? "," : ")")}";
insertCommand.Parameters.AddWithValue(paramName, reader[sourceTable.Columns[i].Name]);
}
insertCommand.CommandText = insertCommandText;
if (insertCommand.ExecuteNonQuery() != 1)
throw new ReplicationException("Replication error: Failed to insert row into target database");
}
public long GetColumnMaxValue(Table table, string columnName)
{
try
{
using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
{
sqlConnection.Open();
string commandText = string.Format(@"USE [{0}]
SELECT MAX([{1}]) FROM [{2}].[{3}]", table.Database, columnName, table.Schema, table.Name);
SqlCommand command = _sqlCommandFactory.CreateSqlCommand(commandText, sqlConnection);
object res = command.ExecuteScalar();
return res is DBNull ? -1 : Convert.ToInt64(res);
}
}
catch (Exception ex)
{
_log.Error("GetPrimaryKeyMaxValue exception", ex);
throw;
}
}
void CheckReplicationPrerequisities(IReplicationArticle article, Table sourceTable, Table targetTable)
{
//Replication strategy requires that the table contains primary key column of int data type and that the value of the column is incremented
if (!sourceTable.Columns.Any(c => c.IsPrimaryKey &&
(c.DataType == System.Data.SqlDbType.TinyInt ||
c.DataType == System.Data.SqlDbType.SmallInt ||
c.DataType == System.Data.SqlDbType.Int ||
c.DataType == System.Data.SqlDbType.BigInt)))
{
if (sourceTable.Columns.SingleOrDefault(c => c.IsForeignKey &&
(c.DataType == System.Data.SqlDbType.TinyInt ||
c.DataType == System.Data.SqlDbType.SmallInt ||
c.DataType == System.Data.SqlDbType.Int ||
c.DataType == System.Data.SqlDbType.BigInt)) == null)
throw new ReplicationException($"Table {sourceTable.Name} doesn't contain primary key or single foreign key column or the type of the key column is not TinyInt or SmallInt or Int or BigInt");
}
IReplicationAnalyzer replicationAnalyzer = new ReplicationAnalyzer(_log);
if (!replicationAnalyzer.AreTableSchemasReplicationCompliant(sourceTable, targetTable))
throw new ReplicationException($"Source and target table {sourceTable.Name} are not replication compliant (there are schema differences in those tables)");
}
public void AreTableSchemasReplicationCompliant_TestHappyPath()
{
IReplicationAnalyzer replicationAnalyzer = new ReplicationAnalyzer(_logMock.Object);
Table sourceTable = new Table()
{
Name = "TestTable",
Columns = new Column[]
{
new Column() {Name = "Column1", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int },
new Column() {Name = "Column2", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int }
}
};
Table targetTable = new Table()
{
Name = "TestTable",
Columns = new Column[]
{
new Column() {Name = "Column1", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int },
new Column() {Name = "Column2", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int }
}
};
Assert.True(replicationAnalyzer.AreTableSchemasReplicationCompliant(sourceTable, targetTable), "Happy path - the result must be true");
}
public void AreTableSchemasReplicationCompliant_TestDifferentColumnTypes()
{
IReplicationAnalyzer replicationAnalyzer = new ReplicationAnalyzer(_logMock.Object);
Table sourceTable = new Table()
{
Name = "TestTable",
Columns = new Column[]
{
new Column() {Name = "Column1", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int },
new Column() {Name = "Column2", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int }
}
};
Table targetTable = new Table()
{
Name = "TestTable",
Columns = new Column[]
{
new Column() {Name = "Column1", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int },
new Column() {Name = "Column2", IsPrimaryKey = false, DataType = System.Data.SqlDbType.BigInt }
}
};
Assert.True(replicationAnalyzer.AreTableSchemasReplicationCompliant(sourceTable, targetTable) == false, "Columns in source and target table with different type not recognized");
}
public void AreTableSchemasReplicationCompliant_TestNewColumnInSourceDatabase()
{
IReplicationAnalyzer replicationAnalyzer = new ReplicationAnalyzer(_logMock.Object);
Table sourceTable = new Table()
{
Name = "TestTable",
Columns = new Column[]
{
new Column() {Name = "Column1", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int },
new Column() {Name = "Column2", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int },
new Column() {Name = "Column3", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int }
}
};
Table targetTable = new Table()
{
Name = "TestTable",
Columns = new Column[]
{
new Column() {Name = "Column1", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int },
new Column() {Name = "Column2", IsPrimaryKey = false, DataType = System.Data.SqlDbType.Int }
}
};
Assert.True(replicationAnalyzer.AreTableSchemasReplicationCompliant(sourceTable, targetTable) == false, "New column in source table not recognized");
}
public long GetPrimaryKeyMaxValue(Table table)
{
Column primaryKeyColumn = table.Columns.Single(c => c.IsPrimaryKey);
return GetColumnMaxValue(table, primaryKeyColumn.Name);
}
private void Replicate(Table sourceTable, Table targetTable)
{
try
{
var scriptContainer = _foreignKeysDropCreateScriptProvider.GenerateScripts(targetTable.Database);
using (SqlConnection sourceDatabaseConnection = new SqlConnection(_sourceConnectionString))
{
sourceDatabaseConnection.Open();
SqlCommand command = _sqlCommandFactory.CreateSqlCommand($@"USE [{sourceTable.Database}]
SELECT * FROM {sourceTable.Schema}.{sourceTable.Name}", sourceDatabaseConnection);
using (SqlDataReader reader = command.ExecuteReader())
{
using (SqlConnection targetDatabaseConnection = new SqlConnection(_targetConnectionString))
{
targetDatabaseConnection.Open();
using (SqlTransaction transaction = targetDatabaseConnection.BeginTransaction())
{
int syncedRows = 0;
try
{
ExecuteNonQuerySqlCommand(scriptContainer.DropScript, targetDatabaseConnection, transaction, _sqlCommandFactory);
string truncateSql = $@"USE [{targetTable.Database}]
TRUNCATE TABLE {targetTable.Schema}.{targetTable.Name}";
ExecuteNonQuerySqlCommand(truncateSql, targetDatabaseConnection, transaction, _sqlCommandFactory);
string identityInsertSetup = targetTable.Columns.Any(c => c.IsIdentity) ? $"SET IDENTITY_INSERT {targetTable.Schema}.{targetTable.Name} ON" : "";
while (reader.Read())
{
InsertRow(sourceTable, targetTable, reader, targetDatabaseConnection, transaction, identityInsertSetup,_sqlCommandFactory);
syncedRows++;
}
ExecuteNonQuerySqlCommand(scriptContainer.CreateScript, targetDatabaseConnection, transaction, _sqlCommandFactory);
}
catch (Exception ex)
{
_log.Error("Replication exception in transaction", ex);
transaction.Rollback();
throw;
}
transaction.Commit();
_log.DebugFormat($"{sourceTable.Name} synced {syncedRows} rows");
}
}
}
}
}
catch (Exception ex)
{
_log.Error("Replication exception", ex);
throw new ReplicationException($"Snapshot replication failed see inner exception | table {sourceTable.Schema}.{sourceTable.Name}", ex);
}
}
void CheckReplicationPrerequisities(IReplicationArticle article, Table sourceTable, Table targetTable)
{
IReplicationAnalyzer replicationAnalyzer = new ReplicationAnalyzer(_log);
if (!replicationAnalyzer.AreTableSchemasReplicationCompliant(sourceTable, targetTable))
throw new ReplicationException($"Source and target table {sourceTable.Name} are not replication compliant (there are schema differences in those tables)");
}
private void Replicate(Table sourceTable, Table targetTable)
{
ITableValuesLoader tableValueLoader = new TableValuesLoader(_targetConnectionString, _log, _sqlCommandFactory);
Column replicationKeyColumn = sourceTable.Columns.FirstOrDefault(c => c.IsPrimaryKey);
if (replicationKeyColumn == null) //fallback to sigle foreign key
replicationKeyColumn = targetTable.Columns.Single(c => c.IsForeignKey);
long targetDatabasePrimaryKeyMaxValue = tableValueLoader.GetColumnMaxValue(targetTable, replicationKeyColumn.Name);
try
{
using (SqlConnection sourceDatabaseSqlConnection = new SqlConnection(_sourceConnectionString))
{
sourceDatabaseSqlConnection.Open();
SqlCommand command = _sqlCommandFactory.CreateSqlCommand(string.Format(@"USE [{0}]
SELECT * FROM [{1}].[{2}] WHERE [{3}] >= {4}",
sourceTable.Database,
sourceTable.Schema,
sourceTable.Name,
replicationKeyColumn.Name,
targetDatabasePrimaryKeyMaxValue),
sourceDatabaseSqlConnection);
using (SqlDataReader reader = command.ExecuteReader())
{
using (SqlConnection targetDatabaseSqlConnection = new SqlConnection(_targetConnectionString))
{
targetDatabaseSqlConnection.Open();
using (SqlTransaction transaction = targetDatabaseSqlConnection.BeginTransaction())
{
int syncedRows = 0;
try
{
string identityInsertSetup = targetTable.Columns.Any(c => c.IsIdentity) ? $"SET IDENTITY_INSERT {targetTable.Schema}.{targetTable.Name} ON" : "";
if (targetDatabasePrimaryKeyMaxValue != -1 && reader.Read())
{
SqlCommand updateCommand = _sqlCommandFactory.CreateSqlCommand("", targetDatabaseSqlConnection, transaction);
string updateCommandText = $@"USE [{targetTable.Database}]
UPDATE [{targetTable.Schema}].[{targetTable.Name}] SET ";
for (int i = 0; i < sourceTable.Columns.Length; i++)
{
if (sourceTable.Columns[i].IsIdentity) continue;
string paramName = $"prm{i}";
updateCommandText += $"{sourceTable.Columns[i].Name} = @{paramName}" + ((i < sourceTable.Columns.Length - 1) ? "," : " ");
updateCommand.Parameters.AddWithValue(paramName, reader[sourceTable.Columns[i].Name]);
}
updateCommandText += string.Format(" WHERE [{0}] = {1}", replicationKeyColumn.Name, targetDatabasePrimaryKeyMaxValue);
updateCommand.CommandText = updateCommandText;
if (updateCommand.ExecuteNonQuery() != 1)
throw new ReplicationException("Replication failed. Failed to update row in target database");
syncedRows++;
}
while (reader.Read())
{
SqlCommand insertCommand = _sqlCommandFactory.CreateSqlCommand("", targetDatabaseSqlConnection, transaction);
string insertCommandText = $@"USE [{targetTable.Database}]
{identityInsertSetup}
INSERT INTO [{targetTable.Schema}].[{targetTable.Name}] ( ";
for (int i = 0; i < sourceTable.Columns.Length; i++)
insertCommandText += string.Format("[{0}]" + ((i < sourceTable.Columns.Length - 1) ? "," : ") VALUES ("), sourceTable.Columns[i].Name);
for (int i = 0; i < targetTable.Columns.Length; i++)
{
string paramName = string.Format("prm{0}", i);
insertCommandText += string.Format("@" + paramName + ((i < sourceTable.Columns.Length - 1) ? "," : ")"));
insertCommand.Parameters.AddWithValue(paramName, reader[sourceTable.Columns[i].Name]);
}
insertCommand.CommandText = insertCommandText;
if (insertCommand.ExecuteNonQuery() != 1)
throw new ReplicationException("Replication failed. Unable to insert row into target database.");
syncedRows++;
}
}
catch (Exception ex)
{
_log.Error(string.Format("Replicate exception in transaction | table: {0}", sourceTable.Name), ex);
transaction.Rollback();
throw;
}
transaction.Commit();
_log.DebugFormat("{0} synced {1} rows", sourceTable.Name, syncedRows);
}
}
}
}
}
catch (Exception ex)
{
_log.Error(string.Format("Replicate exception | table: {0}", sourceTable.Name), ex);
if (ex is ReplicationException)
throw;
else
throw new ReplicationException($"Replication failed see inner exception | table {sourceTable.Schema}.{sourceTable.Name}", ex);
}
}
