public static GetMasterConnectionString ( string connectionString ) : string | ||
connectionString | string | |
return | string |
public override void ExecuteCommand() { using (var masterDbConnection = new SqlConnection(Util.GetMasterConnectionString(ConnectionString.ConnectionString))) using (var masterDbExecutor = new SqlExecutor(masterDbConnection)) { masterDbConnection.Open(); var restoreDbName = CopyDatabaseForRestore( masterDbExecutor); using (var restoreDbConnection = new SqlConnection(Util.GetConnectionString(ConnectionString.ConnectionString, restoreDbName))) using (var restoreDbExecutor = new SqlExecutor(restoreDbConnection)) { restoreDbConnection.Open(); PrepareDataForRestore( restoreDbExecutor); RenameLiveDatabase( masterDbExecutor); RenameDatabaseBackup( masterDbExecutor, restoreDbName); } } }
public override void ExecuteCommand() { var dbServer = ConnectionString.DataSource; var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString); Log.Trace("Deleting old warehouse backups for server '{0}':", dbServer); using (var sqlConnection = new SqlConnection(masterConnectionString)) { sqlConnection.Open(); using (var dbExecutor = new SqlExecutor(sqlConnection)) { var dbs = dbExecutor.Query <Db>( "SELECT name FROM sys.databases WHERE name LIKE 'WarehouseBackup_%' AND state = @state", new { state = Util.OnlineState }); foreach (var db in dbs) { var timestamp = Util.GetDatabaseNameTimestamp(db); var date = Util.GetDateTimeFromTimestamp(timestamp); if (DateTime.UtcNow.Subtract(TimeSpan.FromDays(7)) > date) { DeleteDatabaseBackup(db, dbExecutor); } } } } }
public override void ExecuteCommand() { var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString); using (SqlConnection connection = new SqlConnection(masterConnectionString)) { connection.Open(); SqlCommand command = new SqlCommand("SELECT [state] FROM sys.databases WHERE [name] = @BackupName", connection); command.Parameters.AddWithValue("BackupName", BackupName); SqlDataReader reader = command.ExecuteReader(); int count = 0; while (reader.Read()) { State = int.Parse(reader.GetValue(0).ToString()); count++; } if (count < 1) { State = -1; // Not present. } if (count > 1) { throw new InvalidOperationException("Please provide a specific database name"); } Log.Info("'{0}' State: {1}", BackupName, State); } }
public override void ExecuteCommand() { var dbServer = ConnectionString.DataSource; var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString); Log.Info("Listing backups for server '{0}':", dbServer); using (var sqlConnection = new SqlConnection(masterConnectionString)) using (var dbExecutor = new SqlExecutor(sqlConnection)) { sqlConnection.Open(); var dbs = dbExecutor.Query <Db>( "SELECT name FROM sys.databases WHERE name LIKE 'Backup_%' AND state = @state", new { state = Util.OnlineState }); foreach (var db in dbs) { var timestamp = Util.GetDatabaseNameTimestamp(db); var date = Util.GetDateTimeFromTimestamp(timestamp); Log.Info("{0} ({1})", timestamp, date); } } }
private void CreateReportForDBCPUUsage() { List <Tuple <string, string> > usageDataPoints = new List <Tuple <string, string> >(); var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString); var currentDbName = Util.GetDbName(ConnectionString.ConnectionString); using (var sqlConnection = new SqlConnection(masterConnectionString)) { using (var dbExecutor = new SqlExecutor(sqlConnection)) { sqlConnection.Open(); List <DateTime> lastNTimeEntries = dbExecutor.Query <DateTime>(string.Format("select distinct Top(5) time from sys.resource_usage where database_name = '{0}' order by time desc", currentDbName.ToString())).ToList(); foreach (DateTime time in lastNTimeEntries) { Console.WriteLine("Time ..................." + time.ToString()); var usageSeconds = dbExecutor.Query <Int32>(string.Format("select Sum(usage_in_seconds) from sys.resource_usage where time = '{0}' AND database_name = '{1}'", time.ToString(), currentDbName)).SingleOrDefault(); usageDataPoints.Add(new Tuple <string, string>(String.Format("{0:HH:mm}", time.ToLocalTime()), usageSeconds.ToString())); } } usageDataPoints.Reverse(); //reverse it as the array returned will have latest hour as first entry. JArray reportObject = ReportHelpers.GetJson(usageDataPoints); ReportHelpers.CreateBlob(StorageAccount, "DBCPUTime" + string.Format("{0:MMdd}", DateTime.Now) + ".json", ContainerName, "application/json", ReportHelpers.ToStream(reportObject)); } }
protected SqlConnection OpenMasterConnection() { var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString); var c = new SqlConnection(cstr); c.Open(); return(c); }
public override void ExecuteCommand() { Log.Trace("Connecting to server '{0}' to back up database '{1}'.", ConnectionString.InitialCatalog, Util.GetDatabaseServerName(ConnectionString)); _startedBackup = false; var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString); using (var connection = new SqlConnection(cstr)) using (var db = new SqlExecutor(connection)) { connection.Open(); if (!Force) { Log.Trace("Checking for a backup in progress."); if (Util.BackupIsInProgress(db, BackupNamePrefix)) { Log.Trace("Found a backup in progress; exiting."); return; } Log.Trace("Found no backup in progress."); Log.Trace("Getting last backup time."); var lastBackupTime = Util.GetLastBackupTime(db, BackupNamePrefix); if (lastBackupTime >= DateTime.UtcNow.Subtract(TimeSpan.FromMinutes(IfOlderThan))) { Log.Info("Skipping Backup. Last Backup was less than {0} minutes ago", IfOlderThan); return; } Log.Trace("Last backup time is more than {0} minutes ago. Starting new backup.", IfOlderThan); } else { Log.Trace("Forcing new backup"); } // Generate a backup name var timestamp = Util.GetTimestamp(); _backupName = BackupNamePrefix + timestamp; if (!WhatIf) { db.Execute(string.Format("CREATE DATABASE {0} AS COPY OF {1}", _backupName, ConnectionString.InitialCatalog)); _startedBackup = true; } Log.Info("Started Copy of '{0}' to '{1}'", ConnectionString.InitialCatalog, _backupName); } }
private void CreateReportForDataBaseEvents() { var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString); var currentDbName = Util.GetDbName(ConnectionString.ConnectionString); using (var sqlConnection = new SqlConnection(masterConnectionString)) { using (var dbExecutor = new SqlExecutor(sqlConnection)) { sqlConnection.Open(); var usageSeconds = dbExecutor.Query <DatabaseEvent>(string.Format("select start_time, end_time,event_type,event_count,description from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and severity = 2", DateTime.UtcNow.AddHours(-LastNHours).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName)); var json = new JavaScriptSerializer().Serialize(usageSeconds); ReportHelpers.CreateBlob(StorageAccount, "DBDetailed" + LastNHours.ToString() + "Hour.json", ContainerName, "application/json", ReportHelpers.ToStream(json)); var throttlingEventCount = dbExecutor.Query <Int32>(string.Format("select count(*) from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and (event_type Like 'throttling%' or event_type Like 'deadlock')", DateTime.UtcNow.AddHours(-1).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName)).SingleOrDefault(); var additional_data = dbExecutor.Query <string>(string.Format("select additional_data from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and (event_type Like 'throttling%' or event_type Like 'deadlock')", DateTime.UtcNow.AddHours(-1).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName)); AlertThresholds thresholdValues = new JavaScriptSerializer().Deserialize <AlertThresholds>(ReportHelpers.Load(StorageAccount, "Configuration.AlertThresholds.json", ContainerName)); StringBuilder sb = new StringBuilder(); foreach (string data in additional_data) { if (data != null) { sb.Append(data + "\n"); } } if (throttlingEventCount > thresholdValues.DatabaseThrottlingEventErrorThreshold && LastNHours == 1) { new SendAlertMailTask { AlertSubject = "Error: SQL Azure DB alert activated for throttling/deadlock event", Details = string.Format("Number of events exceeded threshold for DB throttling/deadlock events. Error Threshold count : {0}, events noticed in last hour : {1}, all additional data is {2}", thresholdValues.DatabaseThrottlingEventErrorThreshold, throttlingEventCount, sb.ToString()), AlertName = "Error: SQL Azure DB throttling/deadlock event", Component = "SQL Azure Database", Level = "Error" }.ExecuteCommand(); } else if (throttlingEventCount > thresholdValues.DatabaseThrottlingEventWarningThreshold && LastNHours == 1) { new SendAlertMailTask { AlertSubject = "Warning: SQL Azure DB alert activated for throttling/deadlock event", Details = string.Format("Number of events exceeded threshold for DB throttling/deadlock events. Warning Threshold count : {0}, events noticed in last hour : {1}, all additional data is {2}", thresholdValues.DatabaseThrottlingEventWarningThreshold, throttlingEventCount, sb.ToString()), AlertName = "Warning: SQL Azure DB throttling/deadlock event", Component = "SQL Azure Database", Level = "Warning" }.ExecuteCommand(); } } } }
public override void ExecuteCommand() { using (var destinationConnection = new SqlConnection(Util.GetMasterConnectionString(DestinationConnectionString.ConnectionString))) using (var destinationDbExecutor = new SqlExecutor(destinationConnection)) { string sourceDbServerName = Util.GetDatabaseServerName(SourceConnectionString); string destinationDbServerName = Util.GetDatabaseServerName(DestinationConnectionString); destinationConnection.Open(); var copyDbName = $"CopyOf{BackupName}"; var existingDatabaseBackup = Util.GetDatabase( destinationDbExecutor, copyDbName); if (existingDatabaseBackup != null && existingDatabaseBackup.State == Util.OnlineState) { Log.Info("Skipping {0}. It already exists on {1} and is online.", copyDbName, destinationDbServerName); return; } if (existingDatabaseBackup == null) { StartBackupCopy( destinationDbExecutor, sourceDbServerName, destinationDbServerName, BackupName, copyDbName); Log.Trace("Waiting 15 minutes for copy of {0} from {1} to {2} to complete.", BackupName, sourceDbServerName, destinationDbServerName); if (!WhatIf) { Thread.Sleep(15 * 60 * 1000); } } WaitForBackupCopy( destinationDbExecutor, destinationDbServerName, copyDbName); } }