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