Explore o desempenho e a segurança

Concluído

O ecossistema do Azure oferece várias opções de desempenho e segurança para a instância do SQL Server na máquina virtual do Azure. Cada opção fornece vários recursos, como diferentes tipos de disco que atendem aos requisitos de capacidade e desempenho de sua carga de trabalho.

Considerações sobre armazenamento

O SQL Server requer um bom desempenho de armazenamento para oferecer um desempenho robusto do aplicativo, seja uma instância local ou instalada em uma VM do Azure. O Azure fornece uma ampla variedade de soluções de armazenamento para atender às necessidades de sua carga de trabalho. Embora o Azure ofereça vários tipos de armazenamento (blob, arquivo, fila, tabela), na maioria dos casos, as cargas de trabalho do SQL Server usarão discos gerenciados do Azure. As exceções são que uma Instância de Cluster de Failover pode ser criada no armazenamento de arquivos e os backups usarão armazenamento de blob. Os discos gerenciados pelo Azure atuam como um dispositivo de armazenamento em nível de bloco que é apresentado à sua VM do Azure. Os discos gerenciados oferecem vários benefícios, incluindo 99,999% de disponibilidade, implantação escalável (você pode ter até 50.000 discos VM por assinatura por região) e integração com conjuntos e zonas de disponibilidade para oferecer níveis mais altos de resiliência em caso de falha.

Todos os discos gerenciados pelo Azure oferecem dois tipos de criptografia. A criptografia do lado do servidor do Azure é fornecida pelo serviço de armazenamento e atua como criptografia em repouso fornecida pelo serviço de armazenamento. A Criptografia de Disco do Azure usa o BitLocker no Windows e o DM-Crypt no Linux para fornecer criptografia de sistema operacional e de disco de dados dentro da VM. Ambas as tecnologias se integram ao Azure Key Vault e permitem que você traga sua própria chave de criptografia.

Cada VM terá pelo menos dois discos associados a ela:

• Disco do sistema operacional – Cada máquina virtual exigirá um disco do sistema operacional que contenha o volume de inicialização. Este disco seria a unidade C: no caso de uma máquina virtual da plataforma Windows, ou /dev/sda1 no Linux. O sistema operacional será instalado automaticamente no disco do sistema operacional.

• Disco temporário – Cada máquina virtual incluirá um disco usado para armazenamento temporário. Esse armazenamento destina-se a ser usado para dados que não precisam ser duráveis, como arquivos de paginação ou arquivos de permuta. Como o disco é temporário, você não deve usá-lo para armazenar informações críticas, como banco de dados ou arquivos de log de transações, pois eles serão perdidos durante a manutenção ou uma reinicialização da máquina virtual. Esta unidade será montada como D:\ no Windows e /dev/sdb1 no Linux.

Além disso, você pode e deve adicionar discos de dados adicionais às suas VMs do Azure que executam o SQL Server.

• Discos de dados – O termo disco de dados é usado no portal do Azure, mas, na prática, esses são apenas discos gerenciados adicionais adicionados a uma VM. Esses discos podem ser agrupados para aumentar as IOPs disponíveis e a capacidade de armazenamento, usando Espaços de Armazenamento no Windows ou Gerenciamento de Volume Lógico no Linux.

Além disso, cada disco pode ser de vários tipos:

Caraterística	Discos Ultra	SSD Premium	SSD Standard	HDD Standard
Tipo de disco	SSD	SSD	SSD	HDD
Melhor para	Carga de trabalho intensiva de IO	Carga de trabalho sensível ao desempenho	Cargas de trabalho leves	Backups, cargas de trabalho não críticas
Tamanho máximo do disco	65 536 GiB	32,767 GiB	32,767 GiB	32,767 GiB
Débito máximo	2.000 MB/s	900 MB/s	750 MB/s	500 MB/s
IOPS Máximo	160 000	20 000	6000	2.000

As práticas recomendadas para o SQL Server no Azure recomendam o uso de Discos Premium agrupados para aumentar as IOPs e a capacidade de armazenamento. Os arquivos de dados devem ser armazenados em seu próprio pool com cache de leitura nos discos do Azure.

Os arquivos de log de transações não se beneficiarão desse cache, portanto, esses arquivos devem entrar em seu próprio pool sem cache. O TempDB pode, opcionalmente, entrar em seu próprio pool ou usar o disco temporário da VM, que oferece baixa latência, uma vez que está fisicamente conectado ao servidor físico onde as VMs estão sendo executadas. SSD Premium configurado corretamente verá latência em milissegundos de um dígito. Para cargas de trabalho de missão crítica que exigem latência menor do que isso, você deve considerar o Ultra SSD.

Considerações de segurança

Há vários regulamentos e padrões do setor com os quais o Azure está em conformidade que tornam possível criar uma solução compatível com o SQL Server em execução em uma máquina virtual.

Microsoft Defender para SQL

O Microsoft Defender for SQL fornece recursos de segurança da Central de Segurança do Azure, como avaliações de vulnerabilidade e alertas de segurança.

O Azure Defender for SQL pode ser usado para identificar e mitigar possíveis vulnerabilidades em sua instância e banco de dados do SQL Server. O recurso de avaliação de vulnerabilidade pode detetar riscos potenciais em seu ambiente do SQL Server e ajudá-lo a corrigi-los. Ele também fornece informações sobre seu estado de segurança e etapas acionáveis para resolver problemas de segurança.

Centro de Segurança do Azure

A Central de Segurança do Azure é um sistema de gerenciamento de segurança unificado que avalia e oferece oportunidades para melhorar vários aspetos de segurança do seu ambiente de dados. A Central de Segurança do Azure fornece uma visão abrangente da integridade da segurança de todos os seus ativos de nuvem híbrida.

Considerações de desempenho

A maioria dos recursos de desempenho do SQL Server locais existentes também está disponível em máquinas virtuais (VMs) do Azure. Entre as opções oferecidas está a compactação de dados, que pode melhorar o desempenho de cargas de trabalho intensivas de E/S enquanto diminui o tamanho do banco de dados. Da mesma forma, o particionamento de tabelas e índices pode melhorar o desempenho de consultas de tabelas grandes, ao mesmo tempo em que melhora o desempenho e a escalabilidade.

Criação de partições de tabela

O particionamento de tabelas oferece muitos benefícios, mas muitas vezes essa estratégia só é considerada quando a tabela se torna grande o suficiente para começar a comprometer o desempenho da consulta. Identificar quais tabelas são candidatas ao particionamento de tabelas é uma boa prática que pode levar a menos interrupções e intervenções. Quando você filtra seus dados usando sua coluna de partição, apenas um subconjunto dos dados é acessado, não a tabela inteira. Da mesma forma, as operações de manutenção em uma tabela particionada reduzirão a duração da manutenção, por exemplo, compactando dados específicos em uma partição específica ou reconstruindo partições específicas de um índice.

Há quatro etapas principais necessárias ao definir uma partição de tabela:

• A criação de grupos de arquivos, que define os arquivos envolvidos quando as partições são criadas.
• A criação da função de partição, que define as regras de partição com base na coluna especificada.
• A criação do esquema de partição, que define o grupo de arquivos de cada partição.
• A tabela a ser particionada.

O exemplo abaixo ilustra como criar uma função de partição de 1º de janeiro de 2021 a 1º de dezembro de 2021 e distribuir as partições em diferentes grupos de arquivos.

-- Partition function
CREATE PARTITION FUNCTION PartitionByMonth (datetime2)
    AS RANGE RIGHT
    -- The boundary values defined is the first day of each month, where the table will be partitioned into 13 partitions
    FOR VALUES ('20210101', '20210201', '20210301',
      '20210401', '20210501', '20210601', '20210701',
      '20210801', '20210901', '20211001', '20211101', 
      '20211201');

-- The partition scheme below will use the partition function created above, and assign each partition to a specific filegroup.
CREATE PARTITION SCHEME PartitionByMonthSch
    AS PARTITION PartitionByMonth
    TO (FILEGROUP1, FILEGROUP2, FILEGROUP3, FILEGROUP4,
        FILEGROUP5, FILEGROUP6, FILEGROUP7, FILEGROUP8,
        FILEGROUP9, FILEGROUP10, FILEGROUP11, FILEGROUP12);

-- Creates a partitioned table called Order that applies PartitionByMonthSch partition scheme to partition the OrderDate column  
CREATE TABLE Order ([Id] int PRIMARY KEY, OrderDate datetime2)  
    ON PartitionByMonthSch (OrderDate) ;  
GO  

Compressão de dados

O SQL Server oferece diferentes opções para compactar dados. Embora o SQL Server ainda armazene dados compactados em páginas de 8 KB, quando os dados são compactados, mais linhas de dados podem ser armazenadas em uma determinada página, o que permite que a consulta leia menos páginas. Ler menos páginas tem um duplo benefício: reduz a quantidade de E/S físicas realizadas e permite que mais linhas sejam armazenadas no pool de buffers, fazendo um uso mais eficiente da memória. Recomendamos habilitar a compactação de página de banco de dados quando apropriado.

As compensações para a compactação são que ela requer uma pequena quantidade de sobrecarga de CPU, no entanto, na maioria dos casos, os benefícios de E/S de armazenamento superam em muito qualquer uso adicional do processador.

A imagem acima mostra esse benefício de desempenho. Estas tabelas têm os mesmos índices subjacentes; A única diferença é que os índices agrupados e não agrupados na tabela Production.TransactionHistory_Pagesão compactados de página. A consulta contra o objeto compactado de página executa 72% menos leituras lógicas do que a consulta que usa os objetos não compactados.

A compactação é implementada no SQL Server no nível do objeto. Cada índice ou tabela pode ser compactado individualmente, e você tem a opção de compactar partições dentro de uma tabela ou índice particionado. Você pode avaliar quanto espaço economizará usando o procedimento armazenado do sistema sp_estimate_data_compression_savings. Antes do SQL Server 2019, este procedimento não oferecia suporte a índices columnstore ou compactação de arquivo columnstore.

• Compactação de linha - A compactação de linha é bastante básica e não incorre em muita sobrecarga, no entanto, não oferece a mesma quantidade de compactação (medida pela redução percentual de espaço de armazenamento necessária) que a compactação de página pode oferecer. A compactação de linha basicamente armazena cada valor em cada coluna em uma linha na quantidade mínima de espaço necessária para armazenar esse valor. Ele usa um formato de armazenamento de comprimento variável para tipos de dados numéricos como inteiro, flutuante e decimal, e armazena cadeias de caracteres de comprimento fixo usando o formato de comprimento variável.

• Compactação de página - A compactação de página é um superconjunto de compactação de linha, pois todas as páginas serão inicialmente compactadas antes de aplicar a compactação de página. Em seguida, uma combinação de técnicas chamada compressão de prefixo e dicionário é aplicada aos dados. A compactação de prefixo elimina dados redundantes em uma única coluna, armazenando ponteiros de volta para o cabeçalho da página. Após essa etapa, a compactação de dicionário procura valores repetidos em uma página e os substitui por ponteiros, reduzindo ainda mais o armazenamento. Quanto mais redundância nos dados, maior a economia de espaço ao compactar os dados.

• Compactação de arquivo Columnstore - Os objetos Columnstore são sempre compactados, no entanto, eles podem ser compactados usando a compactação de arquivamento, que usa o algoritmo de compactação Microsoft XPRESS nos dados. Esse tipo de compactação é melhor usado para dados lidos com pouca frequência, mas que precisam ser retidos por motivos regulatórios ou comerciais. Embora esses dados sejam ainda mais compactados, o custo da CPU de descompactação tende a superar quaisquer ganhos de desempenho da redução de E/S.

Opções adicionais

Abaixo está uma lista de recursos e ações adicionais do SQL Server a serem considerados para cargas de trabalho de produção:

• Ativar a compressão da cópia de segurança
• Ative a inicialização instantânea de ficheiros para os ficheiros de dados
• Limite o aumento automático da base de dados
• Desativar autoshrink/autoclose para os bancos de dados
• Mover todos os bancos de dados para discos de dados, incluindo bancos de dados do sistema
• Mover o log de erros do SQL Server e rastrear diretórios de arquivos para discos de dados
• Definir limite máximo de memória do SQL Server
• Ativar as páginas de bloqueio na memória
• Habilite a otimização para cargas de trabalho adhoc para ambientes OLTP pesados
• Habilite o Repositório de Consultas.
• Agendar trabalhos do SQL Server Agent para executar DBCC CHECKDB, reorganizar o índice, reconstruir o índice e atualizar trabalhos de estatísticas
• Monitorar e gerenciar a integridade e o tamanho dos arquivos de log de transações

Para obter mais informações sobre práticas recomendadas de desempenho, consulte Práticas recomendadas para SQL Server em VMs do Azure.