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