public void ValidateMigrateData(IndigoInfo targetIndigo, int?commandTimeout, int?migrationBatchSize) { _loggingWindow.Write("---------------------------------------- Validation ------------------------------------------"); bool doPostScripts = true; double total = 0; //Stopwatch sw = new Stopwatch(); try { // Do setup _loggingWindow.Write("------------------------------------- Validation Setup -------------------------------------"); var setupScripts = targetIndigo.GetScripts(ScriptTypes.MigrationSetup, targetIndigo.SourceIndigoVersion); total += ExecuteNonQuery(targetIndigo, false, setupScripts); _loggingWindow.Write("---------------------------------- Migration Data Validation ---------------------------------"); var validationScripts = targetIndigo.GetScripts(ScriptTypes.MigrationValidation, targetIndigo.SourceIndigoVersion); List <string> failedValidation; total += ExecuteValidationQuery(targetIndigo, false, validationScripts, out failedValidation); if (failedValidation.Count > 0) { _loggingWindow.Write("*******************************************************************************************"); _loggingWindow.WriteFormat("Total Validations Failed {0}", failedValidation.Count); foreach (var item in failedValidation) { _loggingWindow.WriteFormat("Validation Failed For: {0}", item); } _loggingWindow.Write("*******************************************************************************************"); } } catch (Exception ex) { _loggingWindow.Write(ex.ToString()); } try { //Do Cleanup _loggingWindow.Write("------------------------------------- Validation Clean Up -------------------------------------"); var cleanupScripts = targetIndigo.GetScripts(ScriptTypes.MigrationCleanup, targetIndigo.SourceIndigoVersion); total += ExecuteNonQuery(targetIndigo, false, cleanupScripts); } catch (Exception ex) { _loggingWindow.Write(ex.ToString()); } _loggingWindow.WriteFormat("TOTAL VALIDATION TIME: {0}", TimeSpan.FromMilliseconds(total).ToString(@"hh\:mm\:ss\.fff")); }
private double ExecuteScripts(IndigoInfo targetIndigo, ScriptTypes scriptType, bool useTransaction) { var scripts = targetIndigo.GetScripts(scriptType); double total = ExecuteNonQuery(targetIndigo, useTransaction, scripts); return(total); }
public void MigrateData(IndigoInfo targetIndigo, int?commandTimeout, int?migrationBatchSize) { _loggingWindow.Write("---------------------------------------- Migration ------------------------------------------"); bool doPostScripts = true; double total = 0; Stopwatch sw = new Stopwatch(); try { // Do setup _loggingWindow.Write("------------------------------------- Migration Setup -------------------------------------"); var setupScripts = targetIndigo.GetScripts(ScriptTypes.MigrationSetup, targetIndigo.SourceIndigoVersion); total += ExecuteNonQuery(targetIndigo, false, setupScripts); _loggingWindow.Write("------------------------------------ Migration BulkCopy -----------------------------------"); using (SqlConnection con = new SqlConnection(_server.ConnectionContext.ConnectionString)) { using (SqlConnection destinationConnection = new SqlConnection(_server.ConnectionContext.ConnectionString)) { con.Open(); destinationConnection.Open(); using (SqlTransaction transaction = destinationConnection.BeginTransaction()) { try { //Run PreMigration Scripts //Migration has 5 parts, setup, PreMigration, BulkCopy, PostMigration and cleanup var scripts = targetIndigo.GetScripts(ScriptTypes.MigrationBulkCopy, targetIndigo.SourceIndigoVersion); if (scripts.Count <= 0) { throw new Exception("No migration scripts in directory."); } var keys = scripts.Keys.ToList(); keys.Sort(); byte[] barr = new byte[] { (byte)36 }; foreach (var key in keys) { var script = scripts[key]; bool firstLine = true; string tableName = String.Empty; StringBuilder scriptText = new StringBuilder(); //Extract table name and script text using (var fileReader = script.Item2.OpenText()) { while (fileReader.Peek() >= 0) { string line = fileReader.ReadLine() .Replace("{DATABASE_NAME}", targetIndigo.DatabaseName) .Replace("{SOURCE_DATABASE_NAME}", targetIndigo.SourceDatabaseName); if (firstLine) { tableName = line; firstLine = false; } else { scriptText.AppendLine(line); } } } _loggingWindow.WriteFormat("Start Bulk Copy: {0}", script.Item2.Name); sw.Restart(); SqlCommand cmd = new SqlCommand(scriptText.ToString().Trim(), con); //cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 0; //if (commandTimeout != null) // cmd.CommandTimeout = commandTimeout.Value; using (var reader = cmd.ExecuteReader()) { using (SqlBulkCopy sbc = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.TableLock, transaction)) { //if (commandTimeout != null) // sbc.BulkCopyTimeout = commandTimeout.Value; sbc.BulkCopyTimeout = 0; sbc.ColumnMappings.Clear(); sbc.SqlRowsCopied += Sbc_SqlRowsCopied; sbc.NotifyAfter = 50000; if (migrationBatchSize != null) { sbc.BatchSize = migrationBatchSize.Value; } else { sbc.BatchSize = 1000; } for (int i = 0; i < reader.FieldCount; i++) { //columns.Add(reader.GetName(i)); sbc.ColumnMappings.Add(reader.GetName(i), reader.GetName(i)); } sbc.DestinationTableName = tableName; sbc.WriteToServer(reader); } } transaction.Save("save_mig_" + key); sw.Stop(); total += sw.Elapsed.TotalMilliseconds; _loggingWindow.WriteFormat("End Bulk Copy, elapsed time: {0}", sw.Elapsed.ToString(@"hh\:mm\:ss\.fff")); } transaction.Commit(); } catch (Exception ex) { doPostScripts = false; sw.Stop(); total += sw.Elapsed.TotalMilliseconds; transaction.Rollback(); _loggingWindow.WriteFormat("Failed after {0}", sw.Elapsed.ToString(@"hh\:mm\:ss\.fff")); _loggingWindow.Write(ex.ToString()); } } } } _loggingWindow.Write("---------------------------------- Migration Data Validation ---------------------------------"); var validationScripts = targetIndigo.GetScripts(ScriptTypes.MigrationValidation, targetIndigo.SourceIndigoVersion); List <string> failedValidation; total += ExecuteValidationQuery(targetIndigo, false, validationScripts, out failedValidation); if (failedValidation.Count > 0) { _loggingWindow.Write("*******************************************************************************************"); _loggingWindow.WriteFormat("Total Validations Failed {0}", failedValidation.Count); foreach (var item in failedValidation) { _loggingWindow.WriteFormat("Validation Failed For: {0}", item); } _loggingWindow.Write("*******************************************************************************************"); } _loggingWindow.WriteFormat("End Post Migration Scripts, elapsed time: {0}", sw.Elapsed.ToString(@"hh\:mm\:ss\.fff")); if (doPostScripts && failedValidation.Count == 0) { //Run PostMigration Scripts _loggingWindow.Write("---------------------------------- Post Migration Scripts ---------------------------------"); if (targetIndigo.PostMigrationScriptsPath != null && targetIndigo.PostMigrationScriptsPath.Exists) { var postScripts = targetIndigo.GetScripts(targetIndigo.PostMigrationScriptsPath); if (postScripts != null && postScripts.Count > 0) { total += ExecuteNonQuery(targetIndigo, true, postScripts); } else { _loggingWindow.WriteFormat("No post scripts found in directory."); } _loggingWindow.WriteFormat("End Post Migration Scripts, elapsed time: {0}", sw.Elapsed.ToString(@"hh\:mm\:ss\.fff")); } else { _loggingWindow.Write("No post scripts directory given, skipping..."); } } } catch (Exception ex) { _loggingWindow.Write(ex.ToString()); } try { //Do Cleanup _loggingWindow.Write("------------------------------------- Migration Clean Up -------------------------------------"); var cleanupScripts = targetIndigo.GetScripts(ScriptTypes.MigrationCleanup, targetIndigo.SourceIndigoVersion); total += ExecuteNonQuery(targetIndigo, false, cleanupScripts); } catch (Exception ex) { _loggingWindow.Write(ex.ToString()); } _loggingWindow.WriteFormat("TOTAL MIGRATION TIME: {0}", TimeSpan.FromMilliseconds(total).ToString(@"hh\:mm\:ss\.fff")); }