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);
        }
示例#2
0
        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);
        }