private double ExecuteScripts(IndigoInfo targetIndigo, ScriptTypes scriptType, bool useTransaction) { var scripts = targetIndigo.IndigoVersion.GetScripts(scriptType); double total = ExecuteNonQuery(targetIndigo, null, useTransaction, scripts); return(total); }
private void btnCreateDatabase_Click(object sender, EventArgs e) { try { if (_dbServer.Exists(cmbTargetDatabase.Text)) { _loggingWindow.WriteFormat("{0} already exists on server.", cmbTargetDatabase.Text); MessageBox.Show("Database already exists on server!", "Database Exists", MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } var targetIndigo = new IndigoInfo( cmbTargetDatabase.Text, cmbTargetCollation.Text, tbMasterKey.Text, new System.IO.DirectoryInfo(tbExportPath.Text), ckbExportKeys.Checked, new System.IO.DirectoryInfo(tbKeyExportPath.Text), tbExportKey.Text, true); var confirmResult = MessageBox.Show(String.Format("This will create new database {0} on {1}, are you sure?", targetIndigo.DatabaseName, _dbServer.ServerName), "Confirm Create Database", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (confirmResult == DialogResult.No) { return; } _parentForm.Disbale(); var ui = TaskScheduler.FromCurrentSynchronizationContext(); CancellationTokenSource cts = new CancellationTokenSource(); Task.Factory.StartNew(() => TaskProgress(cts.Token)); Task.Factory.StartNew(() => _dbServer.CreateNewDatabase(targetIndigo)) .ContinueWith(task => { _parentForm.Enable(); cts.Cancel(); progressBar.Value = 0; cmbTargetDatabase.Items.Clear(); var dbs = _dbServer.DatabaseList().ToArray(); cmbTargetDatabase.Items.AddRange(dbs); }, ui); } catch (Exception ex) { _loggingWindow.Write(ex.ToString()); } }
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 void btnStartMigration_Click(object sender, EventArgs e) { //_source.Bulk(); try { if (!_source.Exists(cmbTargetDatabase.Text)) { _loggingWindow.WriteFormat("{0} does not exists on server.", cmbTargetDatabase.Text); MessageBox.Show("Database doesnt exist on server!", "Database doesnt exist", MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } var targetIndigo = new IndigoInfo(((ComboBoxItem)cmbTargetVersion.SelectedItem).Value as IndigoVersionInfo, cmbTargetDatabase.Text, _source.DatabaseCollation(cmbTargetDatabase.Text), tbMasterKey.Text, new System.IO.DirectoryInfo(tbExportPath.Text), ckbExportKeys.Checked, new System.IO.DirectoryInfo(tbKeyExportPath.Text), tbExportKey.Text, new System.IO.DirectoryInfo(tbPostScripts.Text), false); var sourceIndigo = new IndigoInfo(((ComboBoxItem)cmbSourceVersion.SelectedItem).Value as IndigoVersionInfo, cmbSourceDbs.Text, tbSourceCollation.Text, String.Empty, null, false, null, String.Empty, null, false); var confirmResult = MessageBox.Show(String.Format("This will migrate database {0} to {1} on {2}, are you sure?", sourceIndigo.DatabaseName, targetIndigo.DatabaseName, tbSourceServer.Text), "Confirm Migration", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (confirmResult == DialogResult.No) { return; } gbMigrationOptions.Enabled = gbSourceServer.Enabled = gbCreateDB.Enabled = false; //btnStartMigration.Enabled = false; var ui = TaskScheduler.FromCurrentSynchronizationContext(); CancellationTokenSource cts = new CancellationTokenSource(); Task.Factory.StartNew(() => TaskProgress(cts.Token)); Task.Factory.StartNew(() => _source.MigrateData(targetIndigo, sourceIndigo, CommandTimeout, MigrationBatchSize)) .ContinueWith(task => { gbMigrationOptions.Enabled = gbSourceServer.Enabled = gbCreateDB.Enabled = true; cts.Cancel(); }, ui); } catch (Exception ex) { tbLogWindow.Text = ex.ToString(); } }
private void btnCreateDatabase_Click(object sender, EventArgs e) { try { if (_source.Exists(cmbTargetDatabase.Text)) { _loggingWindow.WriteFormat("{0} already exists on server.", cmbTargetDatabase.Text); MessageBox.Show("Database already exists on server!", "Database Exists", MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } var targetIndigo = new IndigoInfo(((ComboBoxItem)cmbTargetVersion.SelectedItem).Value as IndigoVersionInfo, cmbTargetDatabase.Text, cmbTargetCollation.Text, tbMasterKey.Text, new System.IO.DirectoryInfo(tbExportPath.Text), ckbExportKeys.Checked, new System.IO.DirectoryInfo(tbKeyExportPath.Text), tbExportKey.Text, null, chkCreateEnterprise.Checked); var confirmResult = MessageBox.Show(String.Format("This will create new database {0} on {1}, are you sure?", targetIndigo.DatabaseName, tbSourceServer.Text), "Confirm Create Database", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (confirmResult == DialogResult.No) { return; } gbMigrationOptions.Enabled = gbSourceServer.Enabled = gbCreateDB.Enabled = false; //btnStartMigration.Enabled = false; var ui = TaskScheduler.FromCurrentSynchronizationContext(); CancellationTokenSource cts = new CancellationTokenSource(); Task.Factory.StartNew(() => TaskProgress(cts.Token)); Task.Factory.StartNew(() => _source.CreateNewDatabase(targetIndigo)) .ContinueWith(task => { gbMigrationOptions.Enabled = gbSourceServer.Enabled = gbCreateDB.Enabled = true; cts.Cancel(); progressBar.Value = 0; cmbTargetDatabase.Items.Clear(); cmbSourceDbs.Items.Clear(); var dbs = _source.DatabaseList().ToArray(); cmbTargetDatabase.Items.AddRange(dbs); cmbSourceDbs.Items.AddRange(dbs); }, ui); } catch (Exception ex) { tbLogWindow.Text = ex.ToString(); } }
private double ExecuteNonQuery(IndigoInfo targetIndigo, IndigoInfo sourceIndigo, bool useTransaction, Dictionary <int, Tuple <ScriptFunction, FileInfo> > scripts) { if (scripts.Count <= 0) { throw new Exception("No setup scripts in directory."); } var keys = scripts.Keys.ToList(); keys.Sort(); if (useTransaction && !scripts.Values.Any(a => a.Item1 == ScriptFunction.C)) //If any of the scripts is Create we cant use transaction { _server.ConnectionContext.BeginTransaction(); } double total = 0; Stopwatch sw = new Stopwatch(); Tuple <ScriptFunction, FileInfo> script = null; try { foreach (var key in keys) { script = scripts[key]; string scriptText = String.Empty; using (var reader = script.Item2.OpenText()) scriptText = reader.ReadToEnd(); if (scriptText.Contains("{DATABASE_NAME}")) { scriptText = scriptText.Replace("{DATABASE_NAME}", targetIndigo.DatabaseName); } if (scriptText.Contains("{SOURCE_DATABASE_NAME}")) { scriptText = scriptText.Replace("{SOURCE_DATABASE_NAME}", sourceIndigo.DatabaseName); } if (scriptText.Contains("{MASTER_KEY}")) { scriptText = scriptText.Replace("{MASTER_KEY}", targetIndigo.MasterKey); } if (scriptText.Contains("{EXPITY_DATE}")) { scriptText = scriptText.Replace("{EXPITY_DATE}", DateTime.Now.AddYears(10).ToString("yyyyMMdd")); } //Do we need to Run this in its own transaction if (script.Item1 == ScriptFunction.Q) { _server.ConnectionContext.BeginTransaction(); } _loggingWindow.WriteFormat("Start Executing Script: {0}", script.Item2.Name); sw.Restart(); var results = _server.ConnectionContext.ExecuteNonQuery(scriptText); sw.Stop(); if (script.Item1 == ScriptFunction.Q) { _server.ConnectionContext.CommitTransaction(); } total += sw.Elapsed.TotalMilliseconds; _loggingWindow.WriteFormat("End Executing Script, elapsed time: {0}", sw.Elapsed.ToString(@"hh\:mm\:ss\.fff")); } } catch { if (script.Item1 == ScriptFunction.Q) { _server.ConnectionContext.RollBackTransaction(); } if (useTransaction && !scripts.Values.Any(a => a.Item1 == ScriptFunction.C)) { _server.ConnectionContext.RollBackTransaction(); } throw; } if (useTransaction && !scripts.Values.Any(a => a.Item1 == ScriptFunction.C)) { _server.ConnectionContext.CommitTransaction(); } return(total); }
public void MigrateData(IndigoInfo targetIndigo, IndigoInfo sourceIndigo, int?commandTimeout, int?migrationBatchSize) { if (sourceIndigo == null) { throw new ArgumentNullException("sourceIndigo"); } if (!targetIndigo.Collation.Equals(sourceIndigo.Collation)) { throw new Exception("Database collations do not match."); } _loggingWindow.Write("---------------------------------------- Migration ------------------------------------------"); bool doPostScripts = true; double total = 0; Stopwatch sw = new Stopwatch(); try { // Do setup _loggingWindow.Write("------------------------------------- Migration Setup --------------------------------------"); var setupScripts = targetIndigo.IndigoVersion.GetScripts(ScriptTypes.MigrationSetup, sourceIndigo.IndigoVersion); total += ExecuteNonQuery(targetIndigo, sourceIndigo, 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.IndigoVersion.GetScripts(ScriptTypes.MigrationBulkCopy, sourceIndigo.IndigoVersion); 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}", sourceIndigo.DatabaseName); 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; if (commandTimeout != null) { cmd.CommandTimeout = commandTimeout.Value; } using (var reader = cmd.ExecuteReader()) { using (SqlBulkCopy sbc = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity, transaction)) { if (commandTimeout != null) { sbc.BulkCopyTimeout = commandTimeout.Value; } //sbc.BulkCopyTimeout = 300; sbc.ColumnMappings.Clear(); sbc.SqlRowsCopied += Sbc_SqlRowsCopied; sbc.NotifyAfter = 50000; if (migrationBatchSize != null) { sbc.BatchSize = migrationBatchSize.Value; } else { sbc.BatchSize = 10000; } 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()); } } } } if (doPostScripts) { //Run PostMigration Scripts _loggingWindow.Write("---------------------------------- Post Migration Scripts ---------------------------------"); if (targetIndigo.PostMigrationScriptsPath != null) { var postScripts = targetIndigo.IndigoVersion.GetScripts(targetIndigo.PostMigrationScriptsPath); total += ExecuteNonQuery(targetIndigo, sourceIndigo, true, postScripts); _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.IndigoVersion.GetScripts(ScriptTypes.MigrationCleanup, sourceIndigo.IndigoVersion); total += ExecuteNonQuery(targetIndigo, sourceIndigo, false, cleanupScripts); } catch (Exception ex) { _loggingWindow.Write(ex.ToString()); } _loggingWindow.WriteFormat("TOTAL MIGRATION TIME: {0}", TimeSpan.FromMilliseconds(total).ToString(@"hh\:mm\:ss\.fff")); }
public bool CreateNewDatabase(IndigoInfo targetIndigo) { _loggingWindow.Write("------------------------------------------- Create Database -------------------------------------------"); var isDone = false; try { Database targetNewDB = new Database(_server, targetIndigo.DatabaseName); targetNewDB.Collation = targetIndigo.Collation; targetNewDB.Create(); double total = ExecuteScripts(targetIndigo, ScriptTypes.Create, false); _loggingWindow.WriteFormat("Done creating schema, total time: {0}.", TimeSpan.FromMilliseconds(total).ToString(@"hh\:mm\:ss\.fff")); _loggingWindow.Write("Validating Schema."); _server.Databases.Refresh(); var newDB = _server.Databases[targetIndigo.DatabaseName]; if (newDB != null) { StringBuilder sb = new StringBuilder(); var dir = Directory.CreateDirectory(Path.Combine(targetIndigo.ExportPath.FullName, DateTime.Now.ToString("yyyyMMddhhmmss"))); //Tables foreach (Table table in newDB.Tables) { sb.AppendLine(table.Name); } sb.AppendLine("Total: " + newDB.Tables.Count); File.WriteAllText(Path.Combine(dir.FullName, "tables.txt"), sb.ToString()); //Views sb.Clear(); int viewCount = 0; foreach (View view in newDB.Views) { if (view.Schema == "dbo") { viewCount++; sb.AppendLine(view.Name); } } sb.AppendLine("Total: " + viewCount); File.WriteAllText(Path.Combine(dir.FullName, "views.txt"), sb.ToString()); //SPs int spCount = 0; sb.Clear(); foreach (StoredProcedure sp in newDB.StoredProcedures) { if (sp.Schema != "sys") { spCount++; sb.AppendLine(sp.Name); } } sb.AppendLine("Total: " + spCount); File.WriteAllText(Path.Combine(dir.FullName, "storedProcs.txt"), sb.ToString()); sb.Clear(); //MasterKey sb.Clear(); sb.AppendLine("Master Key: created " + newDB.MasterKey.CreateDate); //SymmetricKeys foreach (SymmetricKey key in newDB.SymmetricKeys) { sb.AppendLine("SymmetricKey: " + key.Name + " " + key.EncryptionAlgorithm + " " + key.KeyLength); } sb.AppendLine("Total SymmetricKeys: " + newDB.SymmetricKeys.Count); //Certificates foreach (Certificate cert in newDB.Certificates) { sb.AppendLine("Certificate:" + cert.Name + " " + cert.ExpirationDate + " " + cert.PrivateKeyEncryptionType); if (targetIndigo.ExportEncryption) { cert.Export(Path.Combine(targetIndigo.ExportEncryptionPath.FullName, String.Format("{0}_{1}.crt", targetIndigo.DatabaseName, cert.Name)), Path.Combine(targetIndigo.ExportEncryptionPath.FullName, String.Format("{0}_{1}_pvtKey", targetIndigo.DatabaseName, cert.Name)), targetIndigo.ExportEncryptionPassword); } } sb.AppendLine("Total Certificates: " + newDB.Certificates.Count); File.WriteAllText(Path.Combine(dir.FullName, "encryption.txt"), sb.ToString()); sb.Clear(); string MasterKeyFile = Path.Combine(targetIndigo.ExportEncryptionPath.FullName, String.Format("{0}_MaskterKey", targetIndigo.DatabaseName)); //BackupKeys if (targetIndigo.ExportEncryption) { newDB.MasterKey.Export(MasterKeyFile, targetIndigo.ExportEncryptionPassword); } //Load Defaults _loggingWindow.Write("Loading lookup data."); total = ExecuteScripts(targetIndigo, ScriptTypes.LoadLookup, true); _loggingWindow.WriteFormat("Done loading lookup data, elapsed time {0}ms", TimeSpan.FromMilliseconds(total).ToString(@"hh\:mm\:ss\.fff")); //Create Enterprise if (targetIndigo.CreateEnterprise) { _loggingWindow.Write("Create enterprise data."); total = ExecuteScripts(targetIndigo, ScriptTypes.CreateEnterprise, true); _loggingWindow.WriteFormat("Done creating enterprise data, elapsed time {0}ms", TimeSpan.FromMilliseconds(total).ToString(@"hh\:mm\:ss\.fff")); } //if (newDB.Tables.Count == 155 && // spCount == 355 && // viewCount == 13 && // newDB.SymmetricKeys.Count == 3 && // newDB.Certificates.Count == 3) // isDone = true; isDone = true; } } catch (Exception ex) { _loggingWindow.Write(ex.ToString()); } if (isDone) { _loggingWindow.WriteFormat("Done creating {0}", targetIndigo.DatabaseName); } else { _loggingWindow.WriteFormat("Failed creating {0}", targetIndigo.DatabaseName); } //var completionSource = new TaskCompletionSource<bool>(); //completionSource.SetResult(isDone); //return completionSource.Task; return(isDone); }