Gerenciar dados históricos em tabelas temporais com política de retenção

Aplica-se a:Banco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Fabric

As tabelas temporais podem aumentar o tamanho do banco de dados mais do que as tabelas regulares, especialmente se você retiver dados históricos por um período de tempo mais longo. Portanto, a política de retenção de dados históricos é um aspeto importante do planejamento e do gerenciamento do ciclo de vida de cada tabela temporal. As tabelas temporais no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure vêm com um mecanismo de retenção fácil de usar que ajuda você a realizar essa tarefa.

A retenção do histórico temporal pode ser configurada no nível da tabela individual, o que permite que os usuários criem políticas flexíveis de envelhecimento. A aplicação da retenção temporal é simples: requer que apenas um parâmetro seja definido durante a criação da tabela ou a alteração do esquema.

Depois de definir a política de retenção, o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure começam a verificar regularmente se há linhas históricas qualificadas para limpeza automática de dados. A identificação de linhas correspondentes e sua remoção da tabela de histórico ocorrem de forma transparente, na tarefa em segundo plano agendada e executada pelo sistema. A condição de idade para as linhas da tabela de histórico é verificada com base na coluna que representa o fim do SYSTEM_TIME período. Se o período de retenção, por exemplo, for definido como seis meses, as linhas da tabela elegíveis para limpeza satisfazem a seguinte condição:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

No exemplo anterior, assumimos que a ValidTo coluna corresponde ao fim do SYSTEM_TIME período.

Como configurar a política de retenção

Antes de configurar a política de retenção para uma tabela temporal, verifique primeiro se a retenção histórica temporal está habilitada no nível do banco de dados.

SELECT is_temporal_history_retention_enabled, [name]
FROM sys.databases;

O sinalizador is_temporal_history_retention_enabled de banco de dados é definido como ON por padrão, mas os usuários podem alterá-lo com ALTER DATABASE instrução. Isto também é automaticamente definido como OFF após a execução da operação de restauração a um ponto no tempo. Para habilitar a remoção de retenção de histórico temporal para seu banco de dados, execute a seguinte instrução:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

A política de retenção é configurada durante a criação da tabela especificando o valor para o parâmetro HISTORY_RETENTION_PERIOD:

CREATE TABLE dbo.WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH
 (
     SYSTEM_VERSIONING = ON
     (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
     )
 );

O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure permitem especificar o período de retenção usando unidades de tempo diferentes: DAYS, WEEKS, MONTHSe YEARS. Se HISTORY_RETENTION_PERIOD não for especificado, assume-se uma retenção infinita. Você também pode usar INFINITE a palavra-chave explicitamente.

Em alguns cenários, convém configurar a retenção após a criação da tabela ou alterar o valor configurado anteriormente. Nesse caso, use a instrução ALTER TABLE.

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Para revisar o estado atual da política de retenção, use a seguinte consulta que une o sinalizador de ativação de retenção temporal no nível do banco de dados com períodos de retenção para tabelas individuais:

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2;

Como as linhas antigas são excluídas

O processo de limpeza depende do layout de índice da tabela de histórico. É importante observar que apenas as tabelas de histórico com um índice clusterizado (árvore B ou columnstore) podem ter a política de retenção finita configurada. Uma tarefa em segundo plano é criada para executar a limpeza de dados antigos para todas as tabelas temporais com período de retenção finito. A lógica de limpeza para o índice clusterizado rowstore (B-tree) exclui linhas envelhecidas em blocos menores (até 10K), minimizando a pressão no log do banco de dados e no subsistema de entrada/saída. Embora a lógica de limpeza utilize o índice B-tree necessário, a ordem das exclusões para as linhas anteriores ao período de retenção não pode ser assegurada com firmeza. Portanto, não confie na ordem de limpeza nos seus aplicativos.

A tarefa de limpeza para o columnstore clusterizado remove grupos de linhas inteiros de uma só vez (normalmente contêm 1 milhão de linhas cada), o que é muito eficiente, especialmente quando os dados históricos são gerados a um ritmo alto.

A excelente compactação de dados e a limpeza eficiente da retenção tornam o índice columnstore clusterizado uma escolha perfeita para cenários em que a carga de trabalho gera rapidamente uma grande quantidade de dados históricos. Esse padrão é típico para cargas de trabalho de processamento transacional intensivo que usam tabelas temporais para controle e auditoria de alterações, análise de tendências ou ingestão de dados de IoT.

Index considerations

A tarefa de limpeza para tabelas com índice clusterizado rowstore requer que o índice comece com a coluna correspondente ao final do SYSTEM_TIME período. Se esse índice não existir, não será possível configurar um período de retenção finito:

Mensagem 13765, Nível 16, Estado 1

A configuração do período de retenção finito falhou na tabela temporal com versão do sistema 'temporalstagetestdb.dbo.WebsiteUserInfo' porque a tabela de histórico 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' não contém o índice clusterizado necessário. Considere a criação de um índice columnstore clusterizado ou um índice B-tree começando com a coluna que corresponde ao final do período de SYSTEM_TIME, na tabela do histórico.

/*Create B-tree ordered by the end of period column*/
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory (ValidTo)
WITH (DROP_EXISTING = ON);
GO
/*Re-create clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON