private void OnSnapShot_Weekly(object sender, RoutedEventArgs e)
{
ExpandMask.DatabaseExpandSetting databaseExpandSetting = new ExpandMask.DatabaseExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_Database);
ExpandMask.InstanceExpandSetting instanceExpandSetting = new ExpandMask.InstanceExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_Instance);
SQLInformation.Helper.TakeSnapShot(instanceExpandSetting, databaseExpandSetting);
}
public static void LoadFromSMO(Data.ApplicationDataSet.InstancesDataTable instances,
ExpandMask.InstanceExpandSetting instanceExpandSetting,
ExpandMask.JobServerExpandSetting jobServerExpandSetting,
ExpandMask.DatabaseExpandSetting databaseExpandSetting)
{
#if TRACE
long startTicks = VNC.AppLog.Info("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
foreach (Data.ApplicationDataSet.InstancesRow instanceRow in instances)
{
if (instanceRow.IsMonitored && instanceExpandSetting.IsMonitored)
{
var updatedInstanceRow = GetInfoFromSMO(instanceRow, instanceExpandSetting, jobServerExpandSetting, databaseExpandSetting);
// Some information is not provided by SMO. Add it in.
updatedInstanceRow.ADDomain = instanceRow.ADDomain;
updatedInstanceRow.Environment = instanceRow.Environment;
updatedInstanceRow.SecurityZone = instanceRow.SecurityZone;
Helper.TakeInstanceSnapShot(updatedInstanceRow);
}
}
#if TRACE
VNC.AppLog.Info("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1, startTicks);
#endif
}
private void OnSnapShot_IntraDay(object sender, RoutedEventArgs e)
{
ExpandMask.InstanceExpandSetting instanceExpandSetting = new ExpandMask.InstanceExpandSetting(SQLInformation.Data.Config.ExpandSetting_IntraDay_Instance);
ExpandMask.JobServerExpandSetting jobServerExpandSetting = new ExpandMask.JobServerExpandSetting(SQLInformation.Data.Config.ExpandSetting_Daily_JobServer);
ExpandMask.DatabaseExpandSetting databaseExpandSetting = new ExpandMask.DatabaseExpandSetting(SQLInformation.Data.Config.ExpandSetting_IntraDay_Database);
SQLInformation.Helper.TakeSnapShot(instanceExpandSetting, jobServerExpandSetting, databaseExpandSetting);
}
public static void TakeWeeklySnapShot()
{
ExpandMask.InstanceExpandSetting instanceExpandSetting = new ExpandMask.InstanceExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_Instance);
ExpandMask.JobServerExpandSetting jobServerExpandSetting = new ExpandMask.JobServerExpandSetting(SQLInformation.Data.Config.ExpandSetting_Daily_JobServer);
ExpandMask.DatabaseExpandSetting databaseExpandSetting = new ExpandMask.DatabaseExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_Database);
SQLInformation.Helper.TakeSnapShot(instanceExpandSetting, jobServerExpandSetting, databaseExpandSetting);
}
private void OnSnapShot_Weekly(object sender, RoutedEventArgs e)
{
try
{
ExpandMask.InstanceExpandSetting instanceExpandSetting = new ExpandMask.InstanceExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_Instance);
ExpandMask.JobServerExpandSetting jobServerExpandSetting = new ExpandMask.JobServerExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_JobServer);
ExpandMask.DatabaseExpandSetting databaseExpandSetting = new ExpandMask.DatabaseExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_Database);
SQLInformation.Helper.TakeSnapShot(instanceExpandSetting, jobServerExpandSetting, databaseExpandSetting);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static void TakeWeekly()
{
try
{
ExpandMask.InstanceExpandSetting instanceExpandSetting = new ExpandMask.InstanceExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_Instance);
ExpandMask.JobServerExpandSetting jobServerExpandSetting = new ExpandMask.JobServerExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_JobServer);
ExpandMask.DatabaseExpandSetting databaseExpandSetting = new ExpandMask.DatabaseExpandSetting(SQLInformation.Data.Config.ExpandSetting_Weekly_Database);
SQLInformation.Helper.TakeSnapShot(instanceExpandSetting, jobServerExpandSetting, databaseExpandSetting);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static void LoadFromSMO(MSMO.Server server, Guid instanceID, ExpandMask.DatabaseExpandSetting databaseExpandSettings)
{
#if TRACE
long startTicks = VNC.AppLog.Trace2("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
long stopwatchFrequency = Stopwatch.Frequency;
MarkExistingItemsAsNotFound(instanceID); // This enables cleanup of items that once existed but were deleted.
foreach (MSMO.Database database in server.Databases)
{
long loopStartTicks = Stopwatch.GetTimestamp();
if (!database.IsAccessible)
{
VNC.AppLog.Warning(string.Format("Database: {0} is not accessible, skipping", database.Name), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
continue;
}
SQLInformation.Data.ApplicationDataSet.DatabasesRow databaseRow = GetInfoFromSMO(instanceID, server.Name, database);
databaseRow.NotFound = false;
try
{
if (databaseRow.IsMonitored && databaseExpandSettings.IsMonitored)
{
if (databaseRow.ExpandStoredProcedures && databaseExpandSettings.ExpandStoredProcedures)
{
StoredProcedure.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandTables && databaseExpandSettings.ExpandTables)
{
ExpandMask.TableExpandSetting tableExpandSetting = new ExpandMask.TableExpandSetting(databaseRow.DefaultTableExpandMask);
Table.LoadFromSMO(database, databaseRow.ID, tableExpandSetting);
}
if (databaseRow.ExpandViews && databaseExpandSettings.ExpandViews)
{
ExpandMask.ViewExpandSetting viewExpandSetting = new ExpandMask.ViewExpandSetting(databaseRow.DefaultViewExpandMask);
View.LoadFromSMO(database, databaseRow.ID, viewExpandSetting);
}
if (databaseRow.ExpandFileGroups && databaseExpandSettings.ExpandFileGroups)
{
FileGroup.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandLogFiles && databaseExpandSettings.ExpandLogFiles)
{
LogFile.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandRoles && databaseExpandSettings.ExpandRoles)
{
DatabaseRole.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandTriggers && databaseExpandSettings.ExpandTriggers)
{
DatabaseDdlTrigger.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandUserDefinedFunctions && databaseExpandSettings.ExpandUserDefinedFunctions)
{
UserDefinedFunction.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandUsers && databaseExpandSettings.ExpandUsers)
{
User.LoadFromSMO(database, databaseRow.ID, databaseRow.Name_Database);
}
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
}
databaseRow.SnapShotDuration = (Stopwatch.GetTimestamp() - loopStartTicks) / stopwatchFrequency;
Common.ApplicationDataSet.DatabasesTA.Update(Common.ApplicationDataSet.Databases);
}
#if TRACE
VNC.AppLog.Trace2("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3, startTicks);
#endif
}
private static SQLInformation.Data.ApplicationDataSet.DatabasesRow Add(Guid instanceID, string instanceName, MSMO.Database database)
{
SQLInformation.Data.ApplicationDataSet.DatabasesRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.Databases.NewDatabasesRow();
dataRow.ID = Guid.NewGuid();
dataRow.Name_Database = database.Name;
dataRow.CreateDate = database.CreateDate;
try
{
dataRow.DataBaseGuid = database.DatabaseGuid.ToString();
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
#endif
dataRow.DataBaseGuid = "<Not Available>";
}
dataRow.ID_DB = database.ID;
dataRow.Instance_ID = instanceID;
dataRow.Name_Instance = instanceName;
try
{
dataRow.DefaultFileGroup = database.DefaultFileGroup;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 8);
#endif
}
try
{
dataRow.DataSpaceUsage = database.DataSpaceUsage;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 9);
#endif
}
try
{
dataRow.IndexSpaceUsage = database.IndexSpaceUsage;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 10);
#endif
}
try
{
dataRow.Size = database.Size;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 11);
#endif
}
try
{
dataRow.SpaceAvailable = database.SpaceAvailable;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 12);
#endif
}
try
{
dataRow.LastBackupDate = (database.LastBackupDate > SQLMinDateTime ? database.LastBackupDate : SQLMinDateTime);
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 13);
#endif
}
try
{
dataRow.LastDifferentialBackupDate = (database.LastDifferentialBackupDate > SQLMinDateTime ? database.LastDifferentialBackupDate : SQLMinDateTime);
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 14);
#endif
}
try
{
dataRow.LastLogBackupDate = (database.LastLogBackupDate > SQLMinDateTime ? database.LastLogBackupDate : SQLMinDateTime);
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 15);
#endif
}
try
{
dataRow.Owner = database.Owner;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 16);
#endif
}
dataRow.RecoveryModel = database.RecoveryModel.ToString();
// TODO(crhodes): Think through how to set this.
ExpandMask.DatabaseExpandSetting expandSetting = new ExpandMask.DatabaseExpandSetting(instanceID);
dataRow.IsMonitored = expandSetting.IsMonitored;
dataRow.ExpandDataFiles = expandSetting.ExpandDataFiles;
dataRow.ExpandFileGroups = expandSetting.ExpandFileGroups;
dataRow.ExpandLogFiles = expandSetting.ExpandLogFiles;
dataRow.ExpandRoles = expandSetting.ExpandRoles;
dataRow.ExpandStoredProcedures = expandSetting.ExpandStoredProcedures;
dataRow.ExpandTables = expandSetting.ExpandTables;
dataRow.ExpandTriggers = expandSetting.ExpandTriggers;
dataRow.ExpandUserDefinedFunctions = expandSetting.ExpandUserDefinedFunctions;
dataRow.ExpandUsers = expandSetting.ExpandUsers;
dataRow.ExpandViews = expandSetting.ExpandViews;
// TODO(crhodes): Need to get this passed in.
dataRow.DefaultTableExpandMask = 0;
dataRow.DefaultViewExpandMask = 0;
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.Databases.AddDatabasesRow(dataRow);
Common.ApplicationDataSet.DatabasesTA.Update(Common.ApplicationDataSet.Databases);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 17);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
return(dataRow);
}
public static SQLInformation.Data.ApplicationDataSet.InstancesRow GetInfoFromSMO(Data.ApplicationDataSet.InstancesRow dataRow,
ExpandMask.InstanceExpandSetting instanceExpandSetting,
ExpandMask.JobServerExpandSetting jobServerExpandSetting,
ExpandMask.DatabaseExpandSetting databaseExpandSetting)
{
#if TRACE
long startTicks = VNC.AppLog.Info(
string.Format("Enter ({0}) IES:{1} JES:{2} DES:{3}",
dataRow.Name_Instance,
instanceExpandSetting.ExpandMask, jobServerExpandSetting.ExpandMask, databaseExpandSetting.ExpandMask),
LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
long stopwatchFrequency = Stopwatch.Frequency;
MSMO.Server serverInstance = null;
// First try to connect to the instance and retrieve a simple property.
// If cannot, don't bother doing more.
try
{
serverInstance = Server.GetFromSMO(dataRow.Name_Instance);
string s = serverInstance.NetName;
// If we got to the server make everything upper case.
dataRow.NetName = s.ToUpper();
dataRow.Name_Instance = dataRow.Name_Instance.ToUpper();
}
catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException)
{
dataRow.SnapShotDuration = (Stopwatch.GetTimestamp() - startTicks) / stopwatchFrequency;
// Make names lower case to indicate error.
dataRow.NetName = dataRow.NetName.ToLower();
dataRow.Name_Instance = dataRow.Name_Instance.ToLower();
dataRow.SnapShotError = "Connection Failure Exception";
Common.ApplicationDataSet.Instances_Update();
//UpdateDatabaseWithSnapShot(dataRow, "Connection Failure Exception");
VNC.AppLog.Warning(string.Format("Connection Failure Exception: {0}", dataRow.Name_Instance), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3);
return(dataRow);
}
catch (Exception ex)
{
//VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4);
dataRow.SnapShotDuration = (Stopwatch.GetTimestamp() - startTicks) / stopwatchFrequency;
dataRow.NetName = dataRow.NetName.ToLower();
dataRow.Name_Instance = dataRow.Name_Instance.ToLower();
ReportException(ex, dataRow, CLASS_BASE_ERRORNUMBER + 4);
//UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
return(dataRow);
}
try
{
Update(serverInstance, dataRow);
//UpdateDatabaseWithSnapShot(dataRow, "");
if (dataRow.ExpandJobServer && instanceExpandSetting.ExpandJobServer)
{
JobServer.LoadFromSMO(serverInstance.JobServer, dataRow.ID, dataRow.Name_Instance, jobServerExpandSetting);
}
if (dataRow.ExpandLinkedServers && instanceExpandSetting.ExpandLinkedServers)
{
LinkedServer.LoadFromSMO(serverInstance, dataRow.ID, dataRow.Name_Instance);
}
if (dataRow.ExpandLogins && instanceExpandSetting.ExpandLogins)
{
Login.LoadFromSMO(serverInstance, dataRow.ID, dataRow.Name_Instance);
}
if (dataRow.ExpandServerRoles && instanceExpandSetting.ExpandServerRoles)
{
ServerRole.LoadFromSMO(serverInstance, dataRow.ID, dataRow.Name_Instance);
}
if (dataRow.ExpandTriggers && instanceExpandSetting.ExpandTriggers)
{
ServerDdlTrigger.LoadFromSMO(serverInstance, dataRow.ID, dataRow.Name_Instance);
}
if ((dataRow.ExpandContent && instanceExpandSetting.ExpandContent) ||
(dataRow.ExpandStorage && instanceExpandSetting.ExpandStorage))
{
Database.LoadFromSMO(serverInstance, dataRow.ID, databaseExpandSetting);
}
// We now have all the information for the instance.
// This will get pushed to the snapshot by our caller.
UpdateTotalStorageUsed(dataRow);
dataRow.SnapShotDuration = (Stopwatch.GetTimestamp() - startTicks) / stopwatchFrequency;
Common.ApplicationDataSet.Instances_Update();
//Common.ApplicationDataSet.InstancesTA.Update(Common.ApplicationDataSet.Instances);
}
catch (Exception ex)
{
//VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
dataRow.SnapShotDuration = (Stopwatch.GetTimestamp() - startTicks) / stopwatchFrequency;
dataRow.NetName = dataRow.NetName.ToLower();
dataRow.Name_Instance = dataRow.Name_Instance.ToLower();
dataRow.SnapShotDate = DateTime.Now;
//dataRow.SnapShotError = "";
ReportException(ex, dataRow, CLASS_BASE_ERRORNUMBER + 5);
//Common.ApplicationDataSet.Instances_Update();
//UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
#if TRACE
VNC.AppLog.Info(string.Format("Exit ({0})", dataRow.Name_Instance), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6, startTicks);
#endif
return(dataRow);
}
