Melhores práticas do SQL Server para otimizar o desempenho no Azure Stack Hub

Este artigo fornece as melhores práticas do SQL Server para otimizar SQL Server e melhorar o desempenho em máquinas virtuais (VMs) do Microsoft Azure Stack Hub. Ao executar SQL Server em VMs do Azure Stack Hub, utilize as mesmas opções de otimização do desempenho da base de dados aplicáveis aos SQL Server num ambiente de servidor no local. O desempenho de uma base de dados relacional numa cloud do Azure Stack Hub depende de muitos fatores, incluindo o tamanho familiar de uma VM e a configuração dos discos de dados.

Ao criar SQL Server imagens, considere aprovisionar as VMs no portal do Azure Stack Hub. Transfira a Extensão IaaS do SQL a partir da Gestão do Marketplace no portal de administrador do Azure Stack Hub e transfira a sua escolha de SQL Server imagens de VM. Estes incluem SQL Server 2016 SP1, SQL Server 2016 SP2 e SQL Server 2017.

Nota

Embora o artigo descreva como aprovisionar uma VM SQL Server com o portal do Azure global, a documentação de orientação também se aplica ao Azure Stack Hub com as seguintes diferenças: O SSD não está disponível para o disco do sistema operativo e existem pequenas diferenças na configuração do armazenamento.

Nas imagens da VM, por SQL Server, só pode utilizar BYOL (Bring Your Own License). Para o Windows Server, o modelo de licença predefinido é pay as you go (PAYG). Para obter informações detalhadas sobre o modelo de licença do Windows Server na VM, veja o artigo FAQ do Windows Server no Marketplace do Azure Stack Hub.

Obter o melhor desempenho para SQL Server em VMs do Azure Stack Hub é o foco deste artigo. Se a carga de trabalho for menos exigente, poderá não exigir todas as otimizações recomendadas. Considere as suas necessidades de desempenho e padrões de carga de trabalho à medida que avalia estas recomendações.

Nota

Para obter orientações de desempenho para SQL Server em VMs do Azure, veja este artigo.

Lista de verificação das melhores práticas do SQL Server

A seguinte lista de verificação destina-se ao desempenho ideal de SQL Server em VMs do Azure Stack Hub:

Área	Otimizações
Tamanho da VM	DS3 ou superior para SQL Server Enterprise edição. DS2 ou superior para SQL Server Standard edição e edição Web.
Armazenamento	Utilize uma família de VMs que suporte o armazenamento Premium.
Discos	Utilize um mínimo de dois discos de dados (um para ficheiros de registo e outro para o ficheiro de dados e TempDB) e escolha o tamanho do disco com base nas suas necessidades de capacidade. Defina as localizações de ficheiros de dados predefinidas para estes discos durante a instalação do SQL Server. Evite utilizar o sistema operativo ou discos temporários para o armazenamento ou registo de bases de dados. Liste vários discos de dados do Azure para obter um maior débito de E/S com os Espaços de Armazenamento. Formatar com tamanhos de alocação documentados.
E/S	Ative a inicialização instantânea de ficheiros para os ficheiros de dados. Limite o aumento automático nas bases de dados com incrementos fixos razoavelmente pequenos (64 MB-256 MB). Desative a redução automática na base de dados. Configure localizações de ficheiros de base de dados e cópia de segurança predefinidas em discos de dados e não no disco do sistema operativo. Ativar páginas bloqueadas. Aplique SQL Server service packs e atualizações cumulativas.
Específico da funcionalidade	Faça uma cópia de segurança diretamente para o armazenamento de blobs (se for suportado pela versão SQL Server em utilização).

Para obter mais informações sobre como e por que motivo fazer estas otimizações, veja os detalhes e as orientações fornecidos nas secções seguintes.

Documentação de orientação dos tamanhos de VM

Para aplicações sensíveis ao desempenho, são recomendados os seguintes tamanhos de VM :

• SQL Server Enterprise edição: DS3 ou superior

• edição SQL Server Standard e edição Web: DS2 ou superior

Com o Azure Stack Hub, não existe nenhuma diferença de desempenho entre o DS e DS_v2 série da família de VMs.

Orientações de armazenamento

As VMs da série DS (juntamente com a série DSv2) no Azure Stack Hub fornecem o débito máximo do disco do sistema operativo e do disco de dados (IOPS). Uma VM da série DS ou DSv2 fornece até 1000 IOPS para o disco do sistema operativo e até 2300 IOPS por disco de dados, independentemente do tipo ou tamanho do disco escolhido.

O débito do disco de dados é determinado exclusivamente com base na série da família de VMs. Pode consultar este artigo para identificar o débito do disco de dados por série de família de VMs.

Nota

Para cargas de trabalho de produção, selecione uma VM da série DS ou da série DSv2 para fornecer o IOPS máximo possível no disco do sistema operativo e nos discos de dados.

Ao criar uma conta de armazenamento no Azure Stack Hub, a opção de georreplicação não tem qualquer efeito porque esta capacidade não está disponível no Azure Stack Hub.

Documentação de orientação sobre discos

Existem três tipos de disco principais numa VM do Azure Stack Hub:

• Disco do sistema operativo: Quando cria uma VM do Azure Stack Hub, a plataforma anexa pelo menos um disco (identificado como unidade C ) à VM do disco do sistema operativo. Este disco é um VHD armazenado como um blob de páginas no armazenamento.

• Disco temporário: As VMs do Azure Stack Hub contêm outro disco chamado disco temporário (identificado como unidade D ). Este é um disco no nó que pode ser utilizado para espaço de rascunho.

• Discos de dados: Pode anexar discos adicionais à VM como discos de dados e estes discos são armazenados no armazenamento como blobs de páginas.

As secções seguintes descrevem recomendações para utilizar estes discos diferentes.

Disco do sistema operativo

Um disco do sistema operativo é um VHD que pode arrancar e montar como uma versão em execução de um sistema operativo e está etiquetado como unidade C .

Disco temporário

A unidade de armazenamento temporária, identificada como unidade D , não é persistente. Não armazene quaisquer dados que não esteja disposto a perder na unidade D . Isto inclui os ficheiros da base de dados de utilizador e os ficheiros de registo de transações do utilizador.

Recomendamos que armazene a TempDB num disco de dados, uma vez que cada disco de dados fornece um máximo de até 2300 IOPS por disco de dados.

Discos de dados

• Utilizar discos de dados para ficheiros de dados e de registo. Se não estiver a utilizar a repartição de discos, utilize dois discos de dados de uma VM que suporte o armazenamento Premium, em que um disco contém os ficheiros de registo e o outro contém os dados e os ficheiros TempDB. Cada disco de dados fornece uma série de IOPS consoante a família de VMs, conforme descrito em Tamanhos de VM suportados no Azure Stack Hub. Se estiver a utilizar uma técnica de repartição de discos, como Os Espaços de Armazenamento, coloque todos os dados e ficheiros de registo na mesma unidade (incluindo TempDB). Esta configuração dá-lhe o número máximo de IOPS disponíveis para SQL Server consumir, independentemente do ficheiro que necessite deles a qualquer momento.

Nota

Quando aprovisiona uma VM SQL Server no portal, tem a opção de editar a configuração de armazenamento. Dependendo da configuração, o Azure Stack Hub configura um ou mais discos. Vários discos são combinados num único agrupamento de armazenamento. Tanto os dados como os ficheiros de registo residem juntos nesta configuração.

• Repartição de discos: Para obter mais débito, pode adicionar discos de dados adicionais e utilizar a repartição de discos. Para determinar o número de discos de dados de que precisa, analise o número de IOPS necessário para os seus ficheiros de registo e para os seus dados e ficheiros TempDB. Repare que os limites de IOPS são por disco de dados com base na família de séries de VMs e não com base no tamanho da VM. No entanto, os limites de largura de banda de rede baseiam-se no tamanho da VM. Veja as tabelas em tamanhos de VM no Azure Stack Hub para obter mais detalhes. Utilize as seguintes diretrizes:

  • Para o Windows Server 2012 ou posterior, utilize os Espaços de Armazenamento com as seguintes diretrizes:

    1. Defina a intercalação (tamanho da faixa) como 64 KB (65 536 bytes) para cargas de trabalho de processamento de transações online (OLTP) e 256 KB (262 144 bytes) para cargas de trabalho de armazenamento de dados para evitar o impacto no desempenho devido ao desalinhamento da partição. Tem de ser definido com o PowerShell.

    2. Definir contagem de colunas = número de discos físicos. Utilize o PowerShell ao configurar mais de oito discos (não Gestor de Servidor IU).

       Por exemplo, o seguinte PowerShell cria um novo agrupamento de armazenamento com o tamanho de intercalação definido como 64 KB e o número de colunas como 2:

       $PoolCount = Get-PhysicalDisk -CanPool $True
       $PhysicalDisks = Get-PhysicalDisk | Where-Object {$_.FriendlyName -like "*2" -or $_.FriendlyName -like "*3"}
       
       New-StoragePool -FriendlyName "DataFiles" -StorageSubsystemFriendlyName "Storage Spaces*" -PhysicalDisks $PhysicalDisks | New-VirtualDisk -FriendlyName "DataFiles" -Interleave 65536 -NumberOfColumns 2 -ResiliencySettingName simple -UseMaximumSize |Initialize-Disk -PartitionStyle GPT -PassThru |New-Partition -AssignDriveLetter -UseMaximumSize |Format-Volume -FileSystem NTFS -NewFileSystemLabel "DataDisks" -AllocationUnitSize 65536 -Confirm:$false
       

• Determine o número de discos associados ao agrupamento de armazenamento com base nas expectativas de carga. Tenha em atenção que diferentes tamanhos de VM permitem diferentes números de discos de dados anexados. Para obter mais informações, veja Tamanhos de VM suportados no Azure Stack Hub.

• Para obter o IOPS máximo possível para discos de dados, a recomendação é adicionar o número máximo de discos de dados suportados pelo tamanho da VM e utilizar a repartição de discos.

• Tamanho da unidade de alocação NTFS: Ao formatar o disco de dados, recomendamos que utilize um tamanho de unidade de alocação de 64 KB para ficheiros de dados e de registo, bem como TempDB.

• Práticas de gestão de discos: Ao remover um disco de dados, pare o serviço SQL Server durante a alteração. Além disso, não altere as definições de cache nos discos, uma vez que não fornece melhorias de desempenho.

Aviso

A falha ao parar o Serviço SQL durante estas operações pode causar danos na base de dados.

Orientação de E/S

• Considere ativar a inicialização instantânea de ficheiros para reduzir o tempo necessário para a alocação inicial de ficheiros. Para tirar partido da inicialização instantânea de ficheiros, conceda a conta de serviço SQL Server (MSSQLSERVER) com SE_MANAGE_VOLUME_NAME e adicione-a à política de segurança Executar Tarefas de Manutenção em Volume. Se estiver a utilizar uma imagem de plataforma SQL Server para o Azure, a conta de serviço predefinida (NT Service\MSSQLSERVER) não é adicionada à política de segurança Executar Tarefas de Manutenção em Volume. Por outras palavras, a inicialização instantânea de ficheiros não está ativada numa SQL Server imagem da plataforma do Azure. Depois de adicionar a conta de serviço SQL Server à política de segurança Executar Tarefas de Manutenção de Volume, reinicie o serviço SQL Server. Podem existir considerações de segurança para utilizar esta funcionalidade. Para obter mais informações, veja Inicialização de Ficheiros de Base de Dados.

• O aumento automático é uma contingência para um crescimento inesperado. Não faça a gestão dos seus dados e registe o crescimento no dia-a-dia com o aumento automático. Se for utilizado o aumento automático, pré-aumente o ficheiro com o comutador Tamanho .

• Certifique-se de que o autoshrink está desativado para evitar sobrecargas desnecessárias que possam afetar negativamente o desempenho.

• Configurar localizações de ficheiros de base de dados e cópia de segurança predefinidas. Utilize as recomendações neste artigo e efetue as alterações na janela Propriedades do servidor. Para obter instruções, consulte Ver ou Alterar as Localizações Predefinidas para Ficheiros de Dados e Registos (SQL Server Management Studio). A captura de ecrã seguinte mostra onde efetuar estas alterações:

  

• Ative páginas bloqueadas para reduzir a E/S e quaisquer atividades de paginação. Para obter mais informações, consulte Ativar a Opção Bloquear Páginas na Memória (Windows).

• Considere comprimir quaisquer ficheiros de dados ao transferir para dentro/para fora do Azure Stack Hub, incluindo cópias de segurança.

Documentação de orientação específica da funcionalidade

Algumas implementações podem obter benefícios de desempenho adicionais com técnicas de configuração mais avançadas. A lista seguinte destaca algumas funcionalidades SQL Server que podem ajudá-lo a alcançar um melhor desempenho:

• Faça uma cópia de segurança do armazenamento do Azure. Ao fazer cópias de segurança para SQL Server em execução em VMs do Azure Stack Hub, pode utilizar SQL Server Cópia de Segurança para URL. Esta funcionalidade está disponível a partir do SQL Server CU2 do SP1 de 2012 e recomendada para fazer uma cópia de segurança dos discos de dados anexados.

  Quando faz uma cópia de segurança ou restauro com o armazenamento do Azure, siga as recomendações fornecidas no SQL Server Cópia de Segurança para As Melhores Práticas do URL e Resolução de Problemas e Restauro de Cópias de Segurança Armazenadas no Microsoft Azure. Também pode automatizar estas cópias de segurança com a Cópia de Segurança Automatizada para SQL Server em VMs do Azure.

• Faça uma cópia de segurança do armazenamento do Azure Stack Hub. Pode fazer uma cópia de segurança do armazenamento do Azure Stack Hub de forma semelhante à cópia de segurança do Armazenamento do Azure. Quando cria uma cópia de segurança dentro de SQL Server Management Studio (SSMS), tem de introduzir manualmente as informações de configuração. Não pode utilizar o SSMS para criar o contentor de armazenamento ou a Assinatura de Acesso Partilhado. O SSMS só se liga a subscrições do Azure e não a subscrições do Azure Stack Hub. Em vez disso, tem de criar a conta de armazenamento, o contentor e a Assinatura de Acesso Partilhado no portal do Azure Stack Hub ou com o PowerShell.

  

  Nota

  A Assinatura de Acesso Partilhado é o token de SAS do portal do Azure Stack Hub, sem a "?" à esquerda na cadeia. Se utilizar a função copy do portal, tem de eliminar o '?' à esquerda para que o token funcione no SQL Server.

  Assim que tiver o Destino de Cópia de Segurança configurado e configurado no SQL Server, pode fazer uma cópia de segurança para o armazenamento de blobs do Azure Stack Hub.

Passos seguintes

Utilizar serviços ou criar aplicações para o Azure Stack Hub