O SQL Server agora possui suporte para a Autenticação Azure Active Directory para sistemas operacionais Windows e Linux

É ótimo ver que agora o SQL Server dá suporte a vários métodos de autenticação com o Azure AD, incluindo:

• senha do Azure AD,
• Azure AD integrado,
• Azure AD universal com autenticação multifator
• Token de acesso do Azure AD.

Também é importante observar que, para usar a autenticação do Azure AD com o SQL Server, o SQL Server e o host Windows ou Linux em que ele é executado devem ser registrados no Azure Arc, e o Agente do Azure Arc e a extensão do Azure para SQL Server devem estar instalados .

Em relação aos métodos de autenticação, o Azure AD Password permite especificar o nome de usuário e a senha para o cliente e o driver, mas é recomendável evitar isso, pois requer o envio de senhas pela rede.

O Azure AD integrado usa as credenciais do Windows do usuário para autenticação do Azure AD quando o domínio do Windows é sincronizado com o Azure AD e o usuário está conectado ao domínio do Windows.

O Azure AD Universal com autenticação multifator é o método interativo padrão com opção de autenticação multifator para contas do Azure AD, e o token de acesso do Azure AD é usado por alguns clientes não GUI, como Invoke-sqlcmd.

Vale a pena observar que apenas o SQL Server 2022 (16.x) local com um sistema operacional Windows ou Linux compatível, ou o SQL Server 2022 em VMs do Windows Azure, é compatível com a autenticação do Azure AD.

Além disso, a conta do Azure AD usada para conectar o SQL Server ao Azure Arc deve ter as permissões necessárias, incluindo associação ao grupo de integração de máquinas conectadas ao Azure ou função de colaborador no grupo de recursos, associação à função Administrador de recursos de máquinas conectadas do Azure no grupo de recursos e associação na função Leitor no grupo de recursos.

Grupos de disponibilidade Always On no SQL Server são projetados para fornecer recursos de alta disponibilidade e recuperação de desastres para um grupo de bancos de dados de usuários que precisam operar como um grupo coordenado.

Esses bancos de dados são replicados em alguns nós em um cluster e são mantidos sincronizados em todas as réplicas do grupo de disponibilidade.

Em um grupo de disponibilidade Always On, há uma réplica primária e uma ou mais réplicas secundárias.

A réplica primária é a instância do SQL Server que hospeda a cópia primária do banco de dados de disponibilidade e é responsável por processar todas as operações de gravação no banco de dados.

As réplicas secundárias são cópias somente leitura do banco de dados de disponibilidade que são mantidas em sincronia com a réplica primária.

Quando há uma falha no nó ou na integridade do SQL Server no nó que hospeda a cópia primária, o grupo de disponibilidade pode fazer failover para uma das réplicas secundárias.

Durante um failover, todos os bancos de dados do usuário no grupo de disponibilidade são movidos como uma unidade para o novo nó de réplica, garantindo que permaneçam sincronizados e disponíveis.

Os bancos de dados do usuário em um grupo de disponibilidade podem ser mantidos sincronizados em todas as réplicas no modo síncrono ou assíncrono.

No modo síncrono, as transações são confirmadas em todas as réplicas antes que a transação seja considerada confirmada na réplica primária, garantindo que os dados sejam sempre consistentes em todas as réplicas.

No modo assíncrono, as transações são confirmadas primeiro na réplica primária e, em seguida, replicadas de forma assíncrona para as réplicas secundárias. Embora o modo assíncrono possa fornecer maior desempenho, existe o risco de perda de dados em caso de failover.

Os grupos de disponibilidade contidos no SQL Server são projetados para manter as configurações do ambiente de execução consistentes nas réplicas de um grupo de disponibilidade. Cada grupo de disponibilidade independente tem seus próprios bancos de dados de sistema master e msdb, nomeados de acordo com o nome do grupo de disponibilidade. Esses bancos de dados do sistema são propagados automaticamente para novas réplicas e as atualizações são replicadas para esses bancos de dados como qualquer outro banco de dados em um grupo de disponibilidade.

Quando você adiciona um objeto como um logon ou trabalho de agente enquanto estiver conectado ao grupo de disponibilidade contido, essas alterações serão replicadas para as outras réplicas no grupo de disponibilidade. Isso garante que quando o grupo de disponibilidade contido fizer failover para outra instância, você ainda verá os trabalhos do agente e poderá autenticar usando o logon criado no grupo de disponibilidade independente.

É importante observar que os grupos de disponibilidade contidos não representam um limite de segurança e não há limite que impeça uma conexão com um grupo de disponibilidade contido de acessar bancos de dados fora do grupo de disponibilidade.

Portanto, é importante garantir que medidas de segurança adequadas sejam implementadas para proteger dados confidenciais.

Quando um novo grupo de disponibilidade independente é criado, os bancos de dados do sistema são inicialmente modelos vazios sem nenhum dado.

As contas de administrador na instância que cria o grupo de disponibilidade contido são copiadas para o banco de dados mestre contido, o que permite que o administrador faça logon no grupo de disponibilidade independente e defina o restante da configuração. No entanto, quaisquer usuários locais ou configurações na instância não aparecerão automaticamente nos bancos de dados do sistema independente e devem ser recriados manualmente no contexto do grupo de disponibilidade independente.

Uma exceção a isso é que todos os logons na função sysadmin na instância pai são copiados para o novo banco de dados principal específico do grupo de disponibilidade.

Conectar-se ao ouvinte do grupo de disponibilidade contido garante que você esteja operando no contexto do grupo de disponibilidade contido e tenha acesso ao conteúdo encontrado nos bancos de dados do sistema contido do grupo de disponibilidade contido, como logons, trabalhos de agente e outras definições de configuração específico para o grupo de disponibilidade contido.

Por outro lado, a conexão direta com a instância ignora o ambiente do grupo de disponibilidade contido e você estará sujeito ao conteúdo encontrado nos bancos de dados do sistema da instância, que pode ser diferente do conteúdo encontrado nos bancos de dados do sistema contido do grupo de disponibilidade contido .

"Persist Security Info=False;
User ID=MyUser;Password=xxxx;
Initial Catalog=MyContainedDatabase;
Server=MyServer;"

diferenças entre conectar-se à instância e conectar-se ao grupo de disponibilidade independente:

• Quando conectados à instância, os usuários verão todos os bancos de dados nessa instância, incluindo bancos de dados do sistema e bancos de dados do usuário que não fazem parte do grupo de disponibilidade contido.
• Quando conectados ao grupo de disponibilidade contido, os usuários verão apenas os bancos de dados que fazem parte desse grupo, além do banco de dados tempdb.
• Os nomes dos bancos de dados mestre e msdb do AG contido são diferentes quando conectados à instância e quando conectados ao AG contido.
• Dentro do AG contido, seus nomes são simplesmente “master” e “msdb”, mas fora do AG eles são denominados “[contained AG]_master” e “[contained AG]_msdb”.
• O ID do banco de dados para o banco de dados mestre do AG contido é 1 quando conectado ao AG contido, mas pode ser diferente quando conectado à instância.
• Embora os usuários não vejam bancos de dados fora do AG contido na exibição sys.databases quando conectados em uma conexão de AG contido, eles ainda poderão acessar esses bancos de dados usando um nome de três partes ou usando o comando USE.
• As opções de configuração do servidor podem ser lidas a partir de uma conexão AG contida, mas só podem ser gravadas a partir de uma conexão em nível de instância usando o procedimento armazenado do sistema sp_configure.
• Embora um usuário sysadmin possa executar a maioria das operações no nível da instância quando conectado a um AG contido, a maioria das operações no nível do banco de dados, no nível do terminal ou no nível do AG só pode ser executada a partir de uma conexão no nível da instância.

Uma das funcionalidades que eu mais gostei do SQL Server 2022 é a possibilidade de ter seus dados replicados para uma instância gerenciada do SQL Server

Ela permite que os dados sejam replicados de um SQL Serve onPrem para um Azure SQL Server Managed Instance, seja por replicação ou para cenários de recuperação de desastres.

O recurso Managed Instance Link no Azure permite a extensão de grupos de disponibilidade Always On locais para Azure SQL Managed Instance usando grupos de disponibilidade distribuídos. Isso permite que você tenha uma implantação híbrida do SQL Server que pode ser dimensionada horizontalmente em ambientes locais e na nuvem, garantindo alta disponibilidade e recuperação de desastres.

Os dados são replicados quase em tempo real usando a replicação síncrona entre a réplica primária local e a instância gerenciada no Azure. Isso fornece um alto nível de proteção de dados e minimiza o risco de perda de dados em caso de desastre ou interrupção. Além disso, o recurso Managed Instance Link usa uma conexão segura e criptografada entre a rede local e a rede do Azure para garantir a segurança e a conformidade dos dados.

O recurso Managed Instance Link no Azure oferece suporte a instâncias do SQL Server de nó único sem grupos de disponibilidade existentes, bem como instâncias do SQL Server de vários nós com grupos de disponibilidade existentes.

Para instâncias do SQL Server de nó único, o recurso Managed Instance Link pode ser usado para conectar a instância a uma Azure SQL Managed Instance, permitindo que você aproveite os benefícios mais recentes do Azure sem a necessidade de migrar toda a propriedade de dados do SQL Server para o nuvem. Isso permite que você modernize seu ambiente do SQL Server e melhore a escalabilidade, a disponibilidade e os recursos de recuperação de desastres.

Para instâncias do SQL Server de vários nós com grupos de disponibilidade existentes, o recurso Managed Instance Link pode ser usado para estender o grupo de disponibilidade local para Azure SQL Managed Instance, fornecendo uma implantação híbrida do SQL Server que escala horizontalmente no local e na nuvem ambientes.

Isso permite que você obtenha uma solução perfeita de alta disponibilidade e recuperação de desastres em ambos os ambientes, além de aproveitar os benefícios mais recentes do Azure.

Em ambos os casos, o recurso Managed Instance Link fornece uma maneira segura e eficiente de conectar suas instâncias do SQL Server à Azure SQL Managed Instance, sem a necessidade de uma migração completa de sua propriedade de dados do SQL Server para a nuvem.

Como este link funciona?

O recurso Managed Instance Link para SQL Managed Instance cria um grupo de disponibilidade distribuído entre o SQL Server e a Azure SQL Managed Instance.

Para estabelecer o link, uma conectividade segura como VPN ou Azure ExpressRoute é usada entre uma rede local e o Azure. Se o SQL Server estiver hospedado em uma VM do Azure, o backbone interno do Azure poderá ser usado entre a VM e a instância gerenciada usando emparelhamento de rede virtual.

A confiança entre os dois sistemas é estabelecida por meio de autenticação baseada em certificado, na qual SQL Server e SQL Managed Instance trocam suas chaves públicas. Isso garante que apenas partes confiáveis possam estabelecer uma conexão e se comunicar com segurança.

O recurso Managed Instance Link permite até 100 links da mesma ou de várias fontes do SQL Server para uma única instância gerenciada do Azure SQL. No entanto, o número de links é limitado pelo número de bancos de dados que podem ser hospedados na instância gerenciada ao mesmo tempo.

Além disso, uma única instância do SQL Server pode estabelecer vários links paralelos de sincronização de banco de dados com várias instâncias gerenciadas em diferentes regiões do Azure em um relacionamento individual entre um banco de dados e uma instância gerenciada.

Isso permite escalabilidade aprimorada e recursos de recuperação de desastres em várias regiões.

Recuperação de Desastres

O SSMS tem capacidade de exibir o plano de execução ao vivo de uma consulta ativa. O Live Query Plan fornece informações em tempo real sobre o processo de execução da consulta, conforme a query vai sendo executada e são passados dados de um operador de plano de consulta para outro.

O Live Query Plan exibe o progresso geral da consulta e as estatísticas de tempo de execução do nível de operador, como o número de linhas produzido, tempo decorrido, progresso do operador, etc. Como esses dados estão disponíveis em tempo real sem a necessidade de aguardar a conclusão da consulta, essas estatísticas de execução são extremamente úteis para depurar problemas de desempenho de consulta. Este recurso está disponível a partir do SSMS versão 13.x em diante.

 Este recurso é usado principalmente para a solução de problemas e o seu uso pode diminuir moderadamente o desempenho geral da consulta, especialmente no SQL Server 2014 (12.x).

Para exibir estatísticas de consulta em tempo real para uma consulta

1. Para exibir o plano de execução de consulta ao vivo, no menu Ferramentas, clique no ícone Incluir Estatísticas de Consulta em Tempo Real.
2. Também é possível acessar o plano de execução de consulta dinâmica clicando com o botão direito do mouse em uma consulta selecionada no Management Studio e, em seguida, clicando em Incluir Estatísticas de Consulta Dinâmica.
3. Agora execute a consulta. O plano de consulta dinâmico exibe o progresso geral da consulta e as estatísticas de tempo de execução (por exemplo, tempo decorrido, progresso, etc.) dos operadores do plano de consulta. As informações de andamento da consulta e as estatísticas de execução são atualizadas periodicamente enquanto a execução da consulta está em andamento. Use essas informações para entender o processo de execução geral da consulta e depurar consultas de longa execução, consultas executadas indefinidamente, consultas que causam estouro de TempDB e Timeouts.

Para exibir estatísticas de consulta em tempo real para qualquer consulta

O plano de execução em tempo real também pode ser acessado pelo Activity Monitor clicando com o botão direito do mouse em qualquer consulta na tabela Processes ou Active Expensive Queries.

Durante o evento Microsoft Ignite, a Microsoft anunciou o grande lançamento da nova versão do SQL Server!

Desde algumas versões passadas, a Microsoft já não chama mais o seu produto de banco de dados. O SQL Server 2022 veio para consolidar essa nova “Plataforma de Dados”, contendo funcionalidades para atender os mais variados ambientes e funcionalidades de médias e grandes empresas.

Veja as principais novidades:

• SQL Server Ledger – essa funcionalidade cria um controle de modificações dos dados com o passar do tempo.
• HA/DR com Azure SQL Managed Instance – essa funcionalidade permite um DAG (Distributed Availability Group) que vai manter cópias dos seus dados para o um SQL Managed Instance e ainda permitir leitura nesse SQL Managed Instance
• Integração com Azure Purview – essa funcionalidade permite uma melhor governança dos dados.
• Analytics real-time – essa funcionalidade captura todas as alterações no SQL Server e alimenta o Azure Synapse Analytics com um processo transacional híbrido

Além dessas novas funcionalidades, ainda existem uma série de melhorias nas funcionalidades existentes

• Query Store agora é ativado por padrão em novos databases
• Suporte ao Query Store nas réplicas de leitura
• Grandes melhorias no IQP (Intelligent Query Processing) como por exemplo o [Parameter Sensitive Plan] que gera vários planos para uma única instrução que tenha vários parâmetros
• Query Store melhorado para uma utilizar melhores planos sem a necessidade de modificação do código

Está disponível no youtube um vídeo das novas funcionalidades. Clique no link abaixo para assistir

O SQL Server 2022 ainda não está disponível para download. Apenas alguns poucos privilegiados tem a versão “Private Preview”.

Por enquanto é o que temos de informações públicas a respeito. Vamos aguardar mais novidades em breve.

O SSMS na versão 17.2 e posteriores incluem o Painel de Desempenho. Este painel foi projetado para fornecer insights rápidos sobre o estado de desempenho do SQL Server (no SQL Server 2008 em diante) e de Managed Instances do Banco de Dados SQL do Azure.

O Painel de Desempenho ajuda a identificar rapidamente se SQL Server ou Banco de Dados SQL do Azure está passando por algum gargalo de desempenho. Se for encontrado um gargalo, capture facilmente dados de diagnóstico adicionais que podem ser necessários para resolver o problema. Alguns problemas comuns de desempenho que o Painel de Desempenho pode ajudar a identificar incluem:

• Gargalos de CPU (e quais consultas estão consumindo a maior parte da CPU)
• Gargalos de I/O (e quais consultas estão realizando a maior parte da E/S)
• Recomendações de índice geradas pelo Query Optimizer (missing indexes)
• Bloqueios
• Resource Contention (incluindo Latch Contentions)

O Painel de Desempenho também ajuda a identificar consultas pesadas executadas e várias métricas estão disponíveis para definir o alto custo: CPU, Logical Reads, Logical Writes, Duração, Physical Reads e CLR time.

O painel de desempenho é dividido nas seções e sub-relatórios a seguir:

• System CPU Utilization
• Current Waiting Requests
• Current ActivityUser Requests
• User Sessions
• Cache Hit Ratio
• Historical InformationWaits
• Latches
• I/O Statistics
• Expensive Queries
• Miscellaneous InformationActive Traces
• Active xEvent Sessions
• Databases
• Missing Indexes

Como acessar o Performance dashboard

Nesta imagem acima não há recomendações de Missing Index, mas caso você veja esse item no seu Dashboard, avalie se as sugestões são realmente aplicáveis no seu caso. A MS recomenda que sejam avaliados como potenciais candidatos aqueles que tiverem um score maior que 100.000.

O processo de Ghost Cleanup é um processo em segundo plano que exclui registros de páginas que foram marcadas para exclusão. O artigo a seguir oferece uma visão geral desse processo.

Ghost Records

Os registros que foram excluídos de um nível folha de uma página de índice não são removidos fisicamente da página – ao invés disso, o registro é marcado como “a ser excluído”, ou fantasma. Isso significa que a linha continua na página, mas um bit é alterado no cabeçalho da linha para indicar que a linha é realmente um deletada. Isso melhora o desempenho durante uma operação de exclusão.

Ghost Record Cleanup Task

Registros que foram marcados para exclusão, ou fantasmas, são removidos pelo Background Ghost Cleanup Process. Esse processo em segundo plano será executado somente depois que a transação de exclusão for confirmada e removerá fisicamente registros fantasmas das páginas. O Ghost Cleanup process é executado automaticamente em um intervalo (a cada 5 segundos para o SQL Server 2012 e a cada 10 segundos para o SQL Server 2008/2008R2). Ele verifica se alguma página foi marcada com Ghost Records e se encontrar alguma, ele excluirá os registros marcados para exclusão, ou fantasmas, que tocam no máximo 10 páginas em cada execução.

Quando houverem Ghost Records, o banco de dados será marcado como tendo registros fantasmas, e o Ghost Cleanup Process examinará apenas esses bancos de dados. Ele também marcará o banco de dados como “não tendo nenhum registro fantasma”, assim que não houver mais nenhum registro fantasma e ignorará esse banco de dados na próxima execução.

A consulta abaixo pode identificar quantos Ghost Records existem em um banco de dados.

SELECT sum(ghost_record_count) total_ghost_records, db_name(database_id) 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED')
group by database_id
order by total_ghost_records desc

Desabilitando o Ghost Cleanup Task

Em sistemas de alta carga e com muitas exclusões, o Ghost Cleanup Process pode gerar um problema de desempenho impedindo a manutenção de páginas no pool de buffers e a geração de I/O. Assim sendo, é possível desabilitar esse processo com o uso da Trace Flag 661. Entretando iso não é recomendado por trazer problemas de performance neste caso.

Desabilitar o Ghost Cleanup Process pode fazer seu banco de dados crescer desnecessariamente e ocasionar problemas de desempenho. Como o Ghost Cleanup Process remove registros marcados como fantasmas, desabilitar o processo deixará esses registros na página, impedindo que o SQL Server reutilize esse espaço. Isso força o SQL Server a adicionar dados a novas páginas, gerando arquivos de banco de dados maiores e podendo ocasionar também Page Splits.

Com o Ghost Cleanup Process desabilitado, o processo de remoção de registros fantasmas deve ser executado manualmente. Uma opção é executar um rebuild de índice, que moverá os dados nas páginas. Outra opção é executar manualmente sp_clean_db_free_space (para limpar todos os arquivos de dados do banco de dados) ou sp_clean_db_file_free_space (para limpar um único arquivo de dados do banco de dados), que excluirá registros fantasmas.