SQL Server 2016 – Como arquivar tabelas históricas no Azure com o Stretch Database

Uma forma muito simples de implementar o Stretch Database e começar a armazenar suas tabelas na nuvem da Microsoft Azure é utilizando a interface do SQL Server Management Studio (SSMS). Eu gosto muito de utilizar o Wizard para implementar o Stretch Database, especialmente na primeira tabela, pois ele já permite criar os databases e as chaves de criptografia (Always Encrypted) sem precisar nem abrir o Portal do Azure.

O primeiro passo, é selecionar a tabela que você gostaria de armazenar na nuvem através do “Object Explorer”, clicar com o botão direito e selecionar o menu “Stretch Database” e clicar na opção “Enable”.

A primeira tela do Wizard do Stretch Database exibe um resumo de como será a configuração desse recurso.

Caso a sua tabela possua algum recurso que seja uma limitação do Stretch Database, você irá se deparar com essa tela.

No caso da tabela SalesOrderHeader, as limitações que estão impedindo o uso do Stretch Database são constraints de check, default e foreign keys. Para contornar isso, vou criar uma nova tabela (SalesOrderHeader2) com o conteúdo da tabela original (SalesOrderHeader), mas sem as contraints.

Agora, vamos tentar ativar o Stretch Database na nova tabela recém criada. Reparem que a opção padrão é “Entire Table”, ou seja, toda a tabela será armazenada na nuvem. Caso você queira manter uma parte dos dados na infra local, até para ter uma performance melhor nas consultas mais frequentes, clique nesse link para abrir a tela de configuração da migração para a nuvem.

Caso você queira personalizar a forma de identificar os dados “frios” (dados pouco usados – armazenados na nuvem) e dados “quentes” (dados usados com frequência – armazenados na infra local), você pode selecionar a opção “Choose Rows”, definir um nome para esse filtro e escolher a coluna e a expressão que será utilizada para filtrar os dados, conforme exemplo abaixo:

E assim fica a tela de configuração do Stretch Database após criar um filtro para selecionar as linhas que serão migradas:

Nesta tela, devemos configurar a conta do Azure que irá armazenar os dados “frios” da tabela de origem.

Já nesta tela, vamos configurar uma senha forte para a master key (DMK) do banco de dados, que será utilizada para criptografar os dados na nuvem (utilizando Always Encrypted, tudo de forma transparente para você)

E aqui, podemos configurar o Firewall desse database, seja utilizando o seu IP atual ou fornecendo uma faixa de IP’s para permitir acesso. Essa configuração depende muito da sua rede, então pode ser alterada de acordo com o seu cenário. Caso você queira alterar essas configurações de firewall após a ativação do Stretch Database, isso pode ser feito através do Portal do Azure.

Resumo das ações que serão realizadas:

Migração dos dados realizada com sucesso! Agora é só esperar a migração dos dados finalizar em segundo-plano (background), de forma totalmente transparente. Para acompanhar o andamento da transferência dos dados, utilize a DMV sys.dm_db_rda_migration_status:

Notem que após criar o Stretch Database em pelo menos uma tabela, o ícone do database é alterado na tela do Object Explorer do SSMS.

Além disso, 2 novos objetos são criados: 1 External Data Source (Stretch Server) e 1 função de classificação (fncUltimos_4_Anos)

Demonstração em vídeo

Após demonstrar como implementar o Stretch Database utilizando a interface do SSMS, agora vou compartilhar com vocês como fazer a mesma coisa, utilizando apenas comandos T-SQL.

O primeiro passo, é habilitar o Stretch Database na instância:

Para que seja possível habilitar o Stretch Database em tabelas individuais, você precisa habilitá-lo no banco de dados. Habilitar o Stretch Database em um banco de dados ou uma tabela exige permissões db_owner e CONTROL DATABASE.

Você precisará se logar manualmente no Portal Azure para criar um novo servidor de Stretch Database (caso ainda não tenha nenhum). Lembre-se de configurar as regras de firewall para conseguir acessá-lo.

Agora é a hora que devemos configurar a criptografia dos dados que serão enviados para o Stretch Database:

E vamos habilitar o Stretch Database no database:

E vamos habilitar o Stretch Database para a tabela:

Caso você queira filtrar as linhas que irá subir para o Stretch Database ao invés de armazenar a tabela toda no Azure, pode utilizar um filtro de predicado:

Vale ressaltar que apenas funções determinísticas podem ser utilizadas no filtro do Stretch Database, ou seja, você não conseguirá utilizar um GETDATE(), por exemplo, para fazer um filtro de data dinâmico. Caso você precise disso, terá que criar novas funções, alterando o filtro, e aplicando um ALTER TABLE para alterar a função de filtro:

Você também pode criar a tabela já utilizando o Stretch Database:

Se você analisar o plano de execução de uma consulta antes de você ativar o Stretch nessa tabela, verá que o plano não terá o operador “Remote Query” na leitura dos dados das tabelas (a não ser que você utilize dados externos, como Linked Server), apenas operadores de dados locais, como Index Seek, Table Scan, Index Scan, etc..

Entretanto, quando ativamos o Stretch nessa tabela, vemos que ao consultar os dados que estão no servidor remoto, o operador “Remote Query” passa a fazer parte do nosso plano de execução. Isso ocorre tanto quando você consulta a tabela inteira quanto quando você consulta dados que estão apenas no servidor remoto.

Caso o resultado da sua consulta esteja armazenado apenas localmente, o operador “Remote Query” não irá aparecer no plano de execução.

No caso do exemplo acima, foi utilizado a coluna “Discontinued” como filtro para determinar os dados que devem ser migrados ou não (1 = Migra, 0 = Permanece local), ou seja, caso não seja realizado nenhum filtro (trazendo a tabela toda) ou algum outro filtro que retorne dados com essa flag tanto com os valores 0 e 1 ou apenas o valor 1, o operador “Remote Query” estará presente, pois uma parte dos dados estará no servidor remoto.

Caso uma consulta retorne apenas os dados com essa flag = 0, o operador “Remote Query” não irá fazer parte do plano de execução, já que serão retornados apenas dados que estão na infra local.

Para pausar ou retomar a migração de dados no Azure, escolha Stretch para uma tabela no SQL Server Management Studio e escolha Pausar para pausar a migração de dados ou Retomar para retomar a migração de dados. Você também pode usar o Transact-SQL para pausar ou retomar a migração de dados.

Como pausar a migração dos dados

Para pausar a migração dos dados, utilize o comando abaixo:

Como resumir/continuar/remotar a migração dos dados

Para resumir/retomar/continuar a migração dos dados, utilize o comando abaixo:

Como desativar a migração dos dados para uma tabela

Para desativar a migração dos dados e trazer os dados migrados para a nuvem de volta para a sua infraestrutura local (a cópia dos dados remotos para a tabela do Azure de volta para o SQL Server gera custos de transferência de dados), utilize o comando abaixo:

Para desativar a migração dos dados e abandonar os migrados para a nuvem, utilize o comando abaixo:

Lembre-se de Pausar a migração de dados em tabelas individuais quando quiser solucionar problemas no servidor local ou para maximizar a largura de banda de rede disponível. Além disso, desabilitar o Stretch Database de uma tabela ou de um banco de dados não exclui o objeto remoto. Se você quiser excluir a tabela remota ou o banco de dados remoto, descarte-o(a) usando o Portal de Gerenciamento do Azure. Os objetos remotos continuam incorrendo em custos do Azure até que você os exclua.

Como desativar o Stretch Database para um banco de dados

Para desativar o Stretch Database para um banco de dados, você precisará antes desativar o Stretch de todas as tabelas individuais que estão ativadas neste database. Utilize a view sys.remote_data_archive_tables para saber quais são essas tabelas.

Após desativar o Stretch de todas as tabelas, utilize o comando abaixo para desativar o Stretch do banco de dados:

Lembre-se que desabilitar o Stretch Database de um banco de dados não exclui o banco de dados remoto. Se você quiser excluir o banco de dados remoto, descarte-o usando o Portal de Gerenciamento do Azure. O banco de dados remoto continua a gerar custos do Azure até você excluí-lo.

Uma forma simples para monitorar o andamento da migração dos dados no Stretch Database é utilizando o SQL Server Management Studio (SSMS), ao clicar no banco desejado, clicar com o botão direito, selecionar a opção Tasks > Stretch > Monitor, conforme imagem abaixo:

Dessa forma, será aberta um relatório onde você consegue visualizar detalhes da quantidade de linhas elegíveis para migração e quantas já foram migradas, bem como as cargas (lotes de até 9.999 registros) realizadas:

Uma forma de conseguir acompanhar o andamento da migração dos dados utilizando Transact-SQL (T-SQL), é consultando a view sys.dm_db_rda_migration_status, que vai retornar as informações de cada lote de carga:

Para consultar as informações de quais databases estão com Stretch Database habilitado, consulte a view sys.remote_data_archive_databases

ou a view sys.remote_data_archive_tables para consultar as tabelas que possuem o Stretch Database:

Caso você ative o Stretch Database no seu ambiente, é comum surgirem dúvidas de como ficam as rotinas de backup/restore para essas bases/tabelas que possuem dados locais e também na nuvem. Pois bem, quanto ao backup, o processo não muda nada.

Backup de bases com Stretch Database habilitado

O backup dos dados locais (dados “quentes”), ou seja, que não estão elegíveis para a migração ainda e estão armazenados na sua infraestrutura local, continuam sendo feitos normalmente, utilizando comandos de BACKUP DATABASE e automatizados através de jobs, junto com as bases/tabelas que estão 100% locais e da mesma forma que você faz backup das bases/tabelas que não possuem o Stretch Database, inclusive, dos dados que são elegíveis mas ainda não foram migrados ainda no momento em que o backup é gerado.

Os dados que já foram migrados e estão na nuvem da Microsoft, fazem parte da rotina de backup automática do Azure, feito a cada 8h (no mínimo) nas tabelas de Staging e com retenção de dados de 7 dias (point-in-time recovery), ou seja, a Azure já faz o backup dos dados “frios” pra você.

Restore de bases com Stretch Database habilitado

Quando falamos em restore dos dados de bancos/tabelas que possuem Stretch Database habilitado, a situação é um pouco diferente. Caso os dados que precisam ser restaurados sejam os dados não elegíveis, ou seja, os dados que estão na sua infraestrutura local (dados “quentes”), você irá realizar o restore dos dados da forma tradicional, utilizando o comando RESTORE DATABASE, da mesma forma como você restaura os dados de databases sem o Stretch ativado.

Após concluir o processo de restore, você precisará executar o comando abaixo, para restabelecer a conexão entre a base e a base remota no Azure:

Obs: É possível procurar o nome da credencial na view sys.database_scoped_credentials.

Para restaurar os dados que estão no Azure, caso o problema tenha sido nos dados que já foram migrados, você precisará realizar o restore database no Portal do Azure. Caso você queira se conectar a um banco de dados Azure restaurado com um nome diferente ou em uma região diferente, pode utilizar a SP sys.sp_rda_deauthorize_db (exige permissões db_owner) para remover a conexão autenticada entre um banco de dados local habilitado para Stretch e o banco de dados remoto do Azure, muito útil também quando o servidor remoto está inconsistente ou indisponível.

Depois de executar sp_rda_deauthorize_db, todas as consultas em tabelas e bancos de dados habilitados para Stretch irão falhar, pois o modo de consulta é definido para DISABLED (desativado). Para voltar a utilizar essas consultas, você deve escolher uma das 2 opções abaixo:

• Utilizar a sys.sp_rda_reauthorize_db para reconectar-se ao banco de dados do Azure remoto. Esta operação redefine automaticamente o modo de consulta para LOCAL_AND_REMOTE, que é o comportamento padrão para o Stretch Database. Ou seja, consultas retornam resultados de dados locais e remotos.
• Executar a sp_rda_set_query_mode com o argumento LOCAL_ONLY para permitir que consultas continuam em execução em relação a apenas os dados locais, ignorando os dados remotos.

Recuperar um banco de dados dinâmico do Azure

O serviço SQL Server Stretch Database no Azure tira instantâneos de todos os dados dinâmicos com uma frequência mínima de intervalos de 8 horas usando o Azure Storage Snapshots. Esses snapshots são mantidos por 7 dias. Isso permite que você restaure os dados em, no mínimo, um dos 21 pontos específicos dos últimos 7 dias até a hora em que o último snapshot foi tirado.

Para restaurar um banco de dados dinâmico do Azure em um ponto específico anterior por meio do portal do Azure, siga o procedimento a seguir:

• Faça logon no portal do Azure.
• No lado esquerdo da tela, selecione “BROWSE” e “SQL Databases”.
• Navegue até o banco de dados e selecione-o.
• Na parte superior da folha do banco de dados, clique em “Restore”.
• Especifique um novo Nome de banco de dados, selecione um “Restore Point” e clique em “Create”.
• O processo de restauração de banco de dados será iniciado e poderá ser monitorado com “NOTIFICATIONS”.

Recuperar um banco de dados excluído do Azure

O serviço SQL Server Stretch Database no Azure tira um database snapshot antes que um banco de dados seja removido e o retém por 7 dias. Depois disso, ele não retém mais instantâneos do banco de dados. Isso permite restaurar um banco de dados excluído no ponto em que ele foi excluído.

Para restaurar um banco de dados do Azure excluído no ponto em que ele foi excluído usando o portal do Azure, siga os procedimentos a seguir:

• Faça logon no portal do Azure.
• No lado esquerdo da tela, selecione “BROWSE” e “SQL Servers”.
• Navegue até o servidor e selecione-o.
• Role para baixo até “Operations” na blade do servidor e clique no bloco “Deleted Databases”.
• Selecione o banco de dados excluído que você deseja restaurar.
• Especifique um novo Nome de banco de dados e clique em “Create”.
• O processo de restauração de banco de dados será iniciado e poderá ser monitorado com “NOTIFICATIONS”.

Limitações para tabelas habilitadas para o Stretch

• Exclusividade não é imposta para restrições UNIQUE e restrições PRIMARY KEY em uma tabela do Azure que contém os dados migrados.
• Você não pode executar o UPDATE ou DELETE de linhas que foram migradas ou linhas que são qualificadas para migração em uma tabela habilitada para Stretch ou em uma view que inclui tabelas habilitadas para Stretch.
• Você não pode executar o INSERT de linhas em uma tabela habilitada para o Stretch em um Linked Server.
• Não é possível criar um índice para uma view que inclui tabelas habilitadas para o Stretch.
• Filtros em índices não são propagados para a tabela remota.

Limitações que atualmente impedem que você habilite o Stretch para uma tabela

• Tabelas que têm mais de 1.023 colunas ou mais de 998 índices
• FileTables ou tabelas que contêm dados FILESTREAM
• Tabelas que são replicadas ou que estão ativamente utilizando o Change Tracking ou Change Data Capture (CDC)
• Tabelas com otimização de memória (In-Memory OLTP)
• Tipos de dados text, ntext e image, timestamp, sql_variant, XML, Tipos de dados CLR, incluindo geometry, geography, hierarchyid e tipos CLR definidos pelo usuário
• Colunas computadas
• Default constraints e check constraints
• Restrições de chave estrangeira que referenciam a tabela. Em uma relação pai-filho (por exemplo, Order e Order_Detail), você pode habilitar o Stretch para a tabela filho (Order_Detail), mas não para a tabela pai (Order).
• Índices Full-text, XML ou espaciais
• Views indexadas que fazem referência à tabela

Quando eu comecei a fazer esse artigo sobre o Stretch Database, não cheguei a pesquisar muito sobre o preço desse recurso, pois na minha concepção, seria cobrado apenas pelos dados trafegados e armazenados no Azure. Quando você está utilizando o Wizard do SSMS, ele até te mostra uma previsão de custo da sua utilização atual do Stretch Database:

Como vocês podem ver na imagem, o custo estimado é de 61 dólares a cada Terabyte, por mês. Entretanto, essa estimativa é apenas em relação aos dados transferidos. Quando você cria um servidor do Stretch Database, você é cobrado por esse servidor também, e, ao contrário desse estimativa, não é nada barato. Percebi isso quando recebi um e-mail, 4 dias depois dos meus testes do Stretch Database, dizendo que eu já havia consumido meus 1.000 reais de crédito no Azure (sendo que não havia utilizado nada ainda).

Após esse susto (kkkkk) acabei pesquisando melhor o preço desse recurso, que segue abaixo para que vocês fiquem cientes do custo desse serviço: