public static bool ProcessReplicas(ServerVersion version,
string targetConnection,
string repositoryConnection,
int snapshotid,
string server,
ServerType serverType)
{
Debug.Assert(version != ServerVersion.Unsupported);
Debug.Assert(version >= ServerVersion.SQL2012);
Debug.Assert(!String.IsNullOrEmpty(targetConnection));
Debug.Assert(!String.IsNullOrEmpty(repositoryConnection));
bool isOk = true;
targetConnection = SqlHelper.AppendDatabaseToConnectionString(targetConnection, MasterDatabaseName);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
List <int> epList = new List <int>();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = AvailabilityReplicas.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = AvailabilityReplicas.Create())
{
// Process each rule to collect the table objects.
// Query to get the objects.
using (SqlDataReader rdr = SqlHelper.ExecuteReader(target, null, CommandType.Text, GetAvailabiltyReplicas, null))
{
while (rdr.Read())
{
// Retrieve the object information.
DataRow dr = dataTable.NewRow();
dr[AvailabilityReplicas.ParamReplicaId] = rdr.GetGuid(AvailabilityReplicas.ColReplicaid);
dr[AvailabilityReplicas.ParamSnapshotid] = snapshotid;
dr[AvailabilityReplicas.ParamGroupid] = rdr.GetGuid(AvailabilityReplicas.ColGroupId);
dr[AvailabilityReplicas.ParamReplicaServerName] = rdr.GetSqlString(AvailabilityReplicas.ColReplicaServerName);
dr[AvailabilityReplicas.ParamOwnersid] = rdr.GetSqlBinary(AvailabilityReplicas.ColOwnersid);
dr[AvailabilityReplicas.ParamEndpointUrl] = rdr.GetSqlString(AvailabilityReplicas.ColEndpointUrl);
dr[AvailabilityReplicas.ParamAvailabilityMode] = rdr.GetSqlByte(AvailabilityReplicas.ColAvailabilityMode);
dr[AvailabilityReplicas.ParamAvailabilityModeDesc] = rdr.GetSqlString(AvailabilityReplicas.ColAvailabilityModeDesc);
dr[AvailabilityReplicas.ParamFailoverMode] = rdr.GetSqlByte(AvailabilityReplicas.ColFailoverMode);
dr[AvailabilityReplicas.ParamFailoverModeDesc] = rdr.GetSqlString(AvailabilityReplicas.ColFailoverModeDesc);
dr[AvailabilityReplicas.ParamCreateDate] = rdr.GetSqlDateTime(AvailabilityReplicas.ColCreateDate);
dr[AvailabilityReplicas.ParamModifyDate] = rdr.GetSqlDateTime(AvailabilityReplicas.ColModifyDate);
SqlInt32 replicaMetaDataId = rdr.GetSqlInt32(AvailabilityReplicas.ColReplicaMetadataId);
dr[AvailabilityReplicas.ParamReplicaMetadataId] = replicaMetaDataId;
if (!replicaMetaDataId.IsNull)
{
epList.Add(replicaMetaDataId.Value);
}
dataTable.Rows.Add(dr);
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server availability replicas failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server availability replicas failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
}
}
if (epList.Count != 0)
{
if (!ServerPermission.Process(targetConnection, repositoryConnection, snapshotid, SqlObjectType.AvailabilityGroup, epList, serverType))
{
logX.loggerX.Error("ERROR - error encountered in processing availability group permissions");
isOk = false;
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing sql server availability replicas failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
public static bool Process(
string targetConnection,
string repositoryConnection,
int snapshotid,
ref Dictionary <Sql.SqlObjectType, Dictionary <MetricMeasureType, uint> > metricsData,
ServerType serverType
)
{
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Stopwatch sw = new Stopwatch();
sw.Start();
uint numProcessedEndpoints = 0;
// Init return.
bool isOk = true;
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
// Process endpoints.
List <int> epList = new List <int>();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = EndPointDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = EndPointDataTable.Create())
{
// Create the query.
string query = createQuery();
Debug.Assert(!string.IsNullOrEmpty(query));
// Query to get the table objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve information.
SqlString name = rdr.GetSqlString(FieldName);
SqlInt32 id = rdr.GetInt32(FieldId);
SqlInt32 principalid = rdr.GetInt32(FieldPrincipalId);
SqlString state = rdr.GetSqlString(FieldState);
SqlString protocol = rdr.GetSqlString(FieldProtocol);
SqlString type = rdr.GetSqlString(FieldType);
SqlString isadminendpoint = rdr.GetSqlString(FieldIsAdminEndpoint);
// Add endpoint id to the list for permission procesing.
Debug.Assert(!id.IsNull);
epList.Add(id.Value);
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[EndPointDataTable.ParamSnapshotid] = snapshotid;
dr[EndPointDataTable.ParamPrincipalid] = principalid;
dr[EndPointDataTable.ParamEndpointid] = id;
dr[EndPointDataTable.ParamName] = name;
dr[EndPointDataTable.ParamType] = type;
dr[EndPointDataTable.ParamProtocol] = protocol;
dr[EndPointDataTable.ParamState] = state;
dr[EndPointDataTable.ParamIsadminendpoint] = isadminendpoint;
dr[EndPointDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
numProcessedEndpoints++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing endpoints";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
// Load endpoint permissions.
if (isOk)
{
if (!ServerPermission.Process(targetConnection, repositoryConnection, snapshotid, SqlObjectType.Endpoint, epList, serverType))
{
logX.loggerX.Error("ERROR - error encountered in processing end point permissions");
isOk = false;
}
}
uint oldMetricCount = 0;
uint oldMetricTime = 0;
// See if User is already in Endpoint Dictionary
// ----------------------------------------------
Dictionary <MetricMeasureType, uint> de;
if (metricsData.TryGetValue(SqlObjectType.Endpoint, out de))
{
de.TryGetValue(MetricMeasureType.Count, out oldMetricCount);
de.TryGetValue(MetricMeasureType.Time, out oldMetricTime);
}
else
{
de = new Dictionary <MetricMeasureType, uint>();
}
de[MetricMeasureType.Count] = numProcessedEndpoints + oldMetricCount;
de[MetricMeasureType.Time] = (uint)sw.ElapsedMilliseconds + oldMetricTime;
metricsData[SqlObjectType.Endpoint] = de;
return(isOk);
}
