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); } }