Otimizar o ambiente do SQL com a solução verificação do estado de funcionamento do SQL Server no Azure Monitor

  • Artigo

Símbolo de Verificação do Estado de Funcionamento do SQL

Pode utilizar a solução Verificação de Estado de Funcionamento do SQL para avaliar o risco e o estado de funcionamento dos ambientes do servidor num intervalo regular. Este artigo irá ajudá-lo a instalar a solução para que possa tomar medidas corretivas para potenciais problemas.

Esta solução fornece uma lista priorizada de recomendações específicas da sua infraestrutura de servidor implementada. As recomendações são categorizadas em seis áreas de foco que o ajudam a compreender rapidamente o risco e a tomar medidas corretivas.

As recomendações feitas baseiam-se no conhecimento e experiência obtidos por engenheiros da Microsoft a partir de milhares de visitas de clientes. Cada recomendação fornece orientações sobre o motivo pelo qual um problema pode ser importante para si e como implementar as alterações sugeridas.

Pode escolher as áreas de foco mais importantes para a sua organização e controlar o seu progresso na execução de um ambiente sem risco e em bom estado de funcionamento.

Depois de adicionar a solução e concluir uma avaliação, as informações de resumo das áreas de foco são apresentadas no dashboard Verificação do Estado de Funcionamento do SQL para a infraestrutura no seu ambiente. As secções seguintes descrevem como utilizar as informações no dashboard Verificação de Estado de Funcionamento do SQL, onde pode ver e, em seguida, efetuar ações recomendadas para a sua infraestrutura de SQL Server.

imagem do mosaico Verificação do Estado de Funcionamento do SQL

imagem do dashboard verificação do estado de funcionamento do SQL

Pré-requisitos

  • A solução Verificação de Estado de Funcionamento do SQL requer uma versão suportada do .NET Framework 4.6.2 instalada em cada computador que tenha o Microsoft Monitoring Agent (MMA) instalado. O agente MMA é utilizado pelo System Center 2016 – Operations Manager e Operations Manager 2012 R2 e pelo Azure Monitor.

  • A solução suporta SQL Server versão 2012, 2014, 2016, 2017 e 2019.

  • Uma área de trabalho do Log Analytics para adicionar a solução Verificação de Estado de Funcionamento do SQL do Azure Marketplace no portal do Azure. Para instalar a solução, tem de ser administrador ou contribuidor na subscrição do Azure.

    Nota

    Depois de adicionar a solução, o ficheiro AdvisorAssessment.exe é adicionado aos servidores com agentes. Os dados de configuração são lidos e, em seguida, enviados para o Azure Monitor na cloud para processamento. A lógica é aplicada aos dados recebidos e o serviço cloud regista os dados.

Para efetuar a verificação de estado de funcionamento nos servidores SQL Server, precisam de um agente e conectividade ao Azure Monitor através de um dos seguintes métodos suportados:

  1. Instale o Microsoft Monitoring Agent (MMA) se o servidor ainda não estiver monitorizado pelo System Center 2016 – Operations Manager ou Operations Manager 2012 R2.
  2. Se for monitorizado com o System Center 2016 – Operations Manager ou o Operations Manager 2012 R2 e o grupo de gestão não estiver integrado no Azure Monitor, o servidor pode ser multi-alojado com o Log Analytics para recolher dados e reencaminhar para o serviço e continuar a ser monitorizado pelo Operations Manager.
  3. Caso contrário, se o grupo de gestão do Operations Manager estiver integrado no serviço, terá de adicionar os controladores de domínio para recolha de dados pelo serviço ao seguir os passos em adicionar computadores geridos por agente depois de ativar a solução na área de trabalho.

O agente no seu SQL Server que reporta a um grupo de gestão do Operations Manager, recolhe dados, reencaminha para o respetivo servidor de gestão atribuído e, em seguida, é enviado diretamente de um servidor de gestão para o Azure Monitor. Os dados não são escritos nas bases de dados do Operations Manager.

Se o SQL Server for monitorizado pelo Operations Manager, terá de configurar uma conta Run As do Operations Manager. Veja Contas run as do Operations Manager para o Azure Monitor abaixo para obter mais informações.

Detalhes da recolha de dados da Verificação de Estado de Funcionamento do SQL

A Verificação de Estado de Funcionamento do SQL recolhe dados das seguintes origens com o agente que ativou:

  • Windows Management Instrumentation (WMI)
  • Registo
  • Contadores de desempenho
  • SQL Server resultados da vista de gestão dinâmica

Os dados são recolhidos no SQL Server e reencaminhados para o Log Analytics a cada sete dias.

Contas Run As do Operations Manager para o Log Analytics

O Log Analytics utiliza o agente do Operations Manager e o grupo de gestão para recolher e enviar dados para o serviço Do Log Analytics. O Log Analytics baseia-se em pacotes de gestão para cargas de trabalho para fornecer serviços de valor acrescentado. Cada carga de trabalho requer privilégios específicos da carga de trabalho para executar pacotes de gestão num contexto de segurança diferente, como uma conta de utilizador de domínio. Tem de fornecer informações de credenciais ao configurar uma conta Run As do Operations Manager.

Utilize as seguintes informações para definir a conta Run As do Operations Manager para a Verificação do Estado de Funcionamento do SQL.

Definir a conta Run As para a Verificação de Estado de Funcionamento do SQL

Se já estiver a utilizar o pacote de gestão SQL Server, deve utilizar essa configuração Run As.

Para configurar a conta Run As do SQL na Consola de operações

Nota

Por predefinição, os fluxos de trabalho no pacote de gestão são executados no contexto de segurança da conta do Sistema Local. Se estiver a utilizar o Microsoft Monitoring Agent ligado diretamente ao serviço em vez de reportar diretamente a um grupo de gestão do Operations Manager, ignore os passos 1 a 5 abaixo e execute o exemplo T-SQL ou PowerShell, especificando NT AUTHORITY\SYSTEM como o nome de utilizador.

  1. No Operations Manager, abra a Consola de operações e, em seguida, clique em Administração.

  2. Em Configuração Run As, clique em Perfis e abra Perfil Run As da Avaliação do SQL.

  3. Na página Contas Run As, clique em Adicionar.

  4. Selecione uma conta Run As do Windows que contenha as credenciais necessárias para SQL Server ou clique em Novo para criar uma.

    Nota

    O tipo de conta Run As tem de ser Windows. A conta Run As também tem de fazer parte do grupo Administradores Locais em todos os Servidores Windows que alojam SQL Server Instances.

  5. Clique em Guardar.

  6. Modifique e execute o seguinte exemplo de T-SQL em cada instância SQL Server para conceder as permissões mínimas necessárias para a Conta Run As efetuar a verificação de estado de funcionamento. No entanto, não precisa de o fazer se uma Conta Run As já fizer parte da função de servidor sysadmin nas instâncias SQL Server.

    ---
    -- Replace <UserName> with the actual user name being used as Run As Account.
    USE master

    -- Create login for the user, comment this line if login is already created.
    CREATE LOGIN [<UserName>] FROM WINDOWS

    -- Grant permissions to user.
    GRANT VIEW SERVER STATE TO [<UserName>]
    GRANT VIEW ANY DEFINITION TO [<UserName>]
    GRANT VIEW ANY DATABASE TO [<UserName>]

    -- Add database user for all the databases on SQL Server Instance, this is required for connecting to individual databases.
    -- NOTE: This command must be run anytime new databases are added to SQL Server instances.
    EXEC sp_msforeachdb N'USE [?]; CREATE USER [<UserName>] FOR LOGIN [<UserName>];'

Para configurar a conta Run As do SQL com Windows PowerShell

Abra uma janela do PowerShell e execute o seguinte script depois de o atualizar com as suas informações:

    import-module OperationsManager
    New-SCOMManagementGroupConnection "<your management group name>"

    $profile = Get-SCOMRunAsProfile -DisplayName "SQL Assessment Run As Profile"
    $account = Get-SCOMrunAsAccount | Where-Object {$_.Name -eq "<your run as account name>"}
    Set-SCOMRunAsProfile -Action "Add" -Profile $Profile -Account $Account

Compreender como é definida a prioridade das recomendações

Todas as recomendações feitas recebem um valor de ponderação que identifica a importância relativa da recomendação. São apresentadas apenas as dez recomendações mais importantes.

Método de cálculo das ponderações

As ponderações são valores agregados com base em três fatores principais:

  • A probabilidade de um problema identificado causar problemas. Uma maior probabilidade equivale a uma classificação geral maior para a recomendação.
  • O impacto do problema na sua organização se este causar um problema. Um impacto mais elevado equivale a uma classificação geral maior para a recomendação.
  • O esforço necessário para implementar a recomendação. Um esforço mais elevado equivale a uma classificação geral mais pequena para a recomendação.

A ponderação de cada recomendação é expressa como uma percentagem da classificação total disponível para cada área de foco. Por exemplo, se uma recomendação na área de foco Segurança e Conformidade tiver uma classificação de 5%, a implementação dessa recomendação aumentará a classificação geral de Segurança e Conformidade em 5%.

Áreas em foco

Segurança e Conformidade – esta área de foco mostra recomendações para potenciais ameaças e violações de segurança, políticas empresariais e requisitos técnicos, legais e regulamentares de conformidade.

Disponibilidade e Continuidade do Negócio – esta área de foco mostra recomendações para disponibilidade do serviço, resiliência da sua infraestrutura e proteção empresarial.

Desempenho e Escalabilidade – esta área de foco mostra recomendações para ajudar a infraestrutura de TI da sua organização a crescer, garantir que o seu ambiente de TI cumpre os requisitos de desempenho atuais e consegue responder às necessidades de infraestrutura em mudança.

Atualização, Migração e Implementação – esta área de foco mostra recomendações para o ajudar a atualizar, migrar e implementar SQL Server na sua infraestrutura existente.

Operações e Monitorização – esta área de foco mostra recomendações para ajudar a simplificar as operações de TI, implementar a manutenção preventiva e maximizar o desempenho.

Gestão de Alterações e Configuração – esta área de foco mostra recomendações para ajudar a proteger as operações diárias, garantir que as alterações não afetam negativamente a sua infraestrutura, estabelecer procedimentos de controlo de alterações e controlar e auditar as configurações do sistema.

Deve visar uma pontuação de 100% em cada uma das áreas em foco?

Não necessariamente. As recomendações baseiam-se nos conhecimentos e experiências obtidos pelos engenheiros da Microsoft em milhares de visitas de clientes. No entanto, não existem duas infraestruturas de servidor iguais e recomendações específicas podem ser mais ou menos relevantes para si. Por exemplo, algumas recomendações de segurança poderão ser menos relevantes se as suas máquinas virtuais não estiverem expostas à Internet. Algumas recomendações de disponibilidade podem ser menos relevantes para serviços que fornecem relatórios e recolha de dados ad hoc de baixa prioridade. Os problemas importantes para uma empresa madura podem ser menos importantes para uma start-up. Poderá querer identificar quais as áreas de foco que são as suas prioridades e, em seguida, ver como as suas pontuações mudam ao longo do tempo.

Todas as recomendações incluem orientações sobre o motivo pelo qual é importante. Deve utilizar esta documentação de orientação para avaliar se a implementação da recomendação é adequada para si, dada a natureza dos seus serviços de TI e as necessidades empresariais da sua organização.

Utilizar as recomendações das áreas em foco da verificação de estado de funcionamento

Antes de poder utilizar uma solução de avaliação no Azure Monitor, tem de ter a solução instalada. Após a instalação, pode ver o resumo das recomendações com o mosaico Verificação do Estado de Funcionamento do SQL na página Descrição Geral do Azure Monitor no portal do Azure.

Veja as avaliações de conformidade resumidas da sua infraestrutura e, em seguida, analise as recomendações.

Para ver as recomendações de uma área de foco e tomar medidas corretivas

  1. Inicie sessão no portal do Azure em https://portal.azure.com.
  2. No portal do Azure, clique em Mais serviços, que se encontra no canto inferior esquerdo. Na lista de recursos, escreva Monitorizar. À medida que começa a escrever, a lista filtra com base na sua entrada. Selecione Monitorizar.
  3. Na secção Informações do menu, selecione Mais.
  4. Na página Descrição geral , clique no mosaico Verificação de Estado de Funcionamento do SQL .
  5. Na página Verificação de Estado de Funcionamento , reveja as informações de resumo numa das secções da área de foco e, em seguida, clique numa para ver as recomendações dessa área de detalhe.
  6. Em qualquer uma das páginas da área de foco, pode ver as recomendações priorizadas efetuadas para o seu ambiente. Clique numa recomendação em Objetos Afetados para ver os detalhes sobre o motivo pelo qual a recomendação é efetuada.

    imagem das recomendações de Verificação de Estado de Funcionamento do SQL
  7. Pode efetuar ações corretivas sugeridas em Ações Sugeridas. Quando o item tiver sido abordado, as avaliações posteriores registarão que foram efetuadas ações recomendadas e que a classificação de conformidade aumentará. Os itens corrigidos são apresentados como Objetos Passados.

Ignorar recomendações

Se tiver recomendações que pretende ignorar, pode criar um ficheiro de texto que o Azure Monitor irá utilizar para impedir que as recomendações sejam apresentadas nos resultados da avaliação.

Para identificar as recomendações que irá ignorar

  1. No menu do Azure Monitor, clique em Registos.

  2. Utilize a seguinte consulta para listar recomendações que falharam para computadores no seu ambiente.

    SQLAssessmentRecommendation | where RecommendationResult == "Failed" | sort by Computer asc | project Computer, RecommendationId, Recommendation

    Eis uma captura de ecrã que mostra a consulta de registo:

    recomendações falhadas

  3. Selecione as recomendações que pretende ignorar. Irá utilizar os valores para RecommendationId no próximo procedimento.

Para criar e utilizar um ficheiro de texto IgnoreRecommendations.txt

  1. Crie um ficheiro com o nome IgnoreRecommendations.txt.
  2. Cole ou escreva cada RecommendationId para cada recomendação que pretende que o Azure Monitor ignore numa linha separada e, em seguida, guarde e feche o ficheiro.
  3. Coloque o ficheiro na seguinte pasta em cada computador onde pretende que o Azure Monitor ignore as recomendações.
    • Em computadores com o Microsoft Monitoring Agent (ligado diretamente ou através do Operations Manager) – SystemDrive:\Programas\Microsoft Monitoring Agent\Agent
    • No servidor de gestão do Operations Manager – SystemDrive:\Programas\Microsoft System Center 2012 R2\Operations Manager\Server
    • No servidor de gestão do Operations Manager 2016 – SystemDrive:\Programas\Microsoft System Center 2016\Operations Manager\Server

Para verificar se as recomendações são ignoradas

  1. Após a execução da próxima avaliação agendada, por predefinição a cada 7 dias, as recomendações especificadas são marcadas como Ignoradas e não serão apresentadas no dashboard de avaliação.

  2. Pode utilizar as seguintes consultas da Pesquisa de Registos para listar todas as recomendações ignoradas.

    SQLAssessmentRecommendation | where RecommendationResult == "Ignored" | sort by Computer asc | project Computer, RecommendationId, Recommendation

  3. Se decidir mais tarde que pretende ver recomendações ignoradas, remova quaisquer ficheiros IgnoreRecommendations.txt ou pode remover OsIDs de Recomendação dos mesmos.

Perguntas mais frequentes

Que verificações são efetuadas pela solução de Avaliação do SQL?

  • A seguinte consulta mostra uma descrição de todas as verificações atualmente efetuadas:
SQLAssessmentRecommendation
| distinct RecommendationId, FocusArea, ActionArea, Recommendation, Description
| sort by FocusArea,ActionArea, Recommendation

Em seguida, os resultados podem ser exportados para o Excel para revisão adicional.

Com que frequência é executada uma verificação de estado de funcionamento?

  • A verificação é executada a cada sete dias.

Existe alguma forma de configurar a frequência com que a verificação é executada?

  • Neste momento, não.

Se outro servidor for detetado depois de adicionar a solução de Verificação de Estado de Funcionamento do SQL, será verificado?

  • Sim, depois de ser detetado, é verificado a partir daí ativado, a cada sete dias.

Se um servidor for desativado, quando será removido da verificação de estado de funcionamento?

  • Se um servidor não submeter dados durante 3 semanas, este será removido.

Qual é o nome do processo que faz a recolha de dados?

  • AdvisorAssessment.exe

Quanto tempo demora a recolha de dados?

  • A recolha de dados real no servidor demora cerca de 1 hora. Pode demorar mais tempo em servidores com um grande número de instâncias ou bases de dados SQL.

Que tipo de dados são recolhidos?

  • São recolhidos os seguintes tipos de dados:
    • WMI
    • Registo
    • Contadores de desempenho
    • Vistas de gestão dinâmica do SQL (DMV).

Existe alguma forma de configurar quando os dados são recolhidos?

  • Neste momento, não.

Por que motivo tenho de configurar uma Conta Run As?

  • Para SQL Server, é executado um pequeno número de consultas SQL. Para que sejam executadas, tem de ser utilizada uma Conta Run As com permissões VIEW SERVER STATE para SQL. Além disso, para consultar a WMI, são necessárias credenciais de administrador local.

Porquê apresentar apenas as 10 principais recomendações?

  • Em vez de lhe dar uma lista exaustiva e esmagadora de tarefas, recomendamos que se concentre em abordar primeiro as recomendações prioritárias. Depois de os abordar, estarão disponíveis recomendações adicionais. Se preferir ver a lista detalhada, pode ver todas as recomendações com a pesquisa de registos do Log Analytics.

Existe alguma forma de ignorar uma recomendação?

Passos seguintes

  • Registar consultas para saber como analisar dados e recomendações detalhadas da Verificação de Estado de Funcionamento do SQL.