CDC – Enable Database

Já utiliza ou quer começar a utilizar o CDC (Change Data Capture)?

Para habilitar o CDC em uma Database é muito simples, basta utilizar o script abaixo:


USE [databaseName]

<span>EXEC sys.sp_cdc_enable_d</span>b

Identify and fix orphanated users

Já restaurou uma database em outro ambiente e precisou ajustar os usuários órfãos?

Os usuários órfãos existem para logins que são criados no nível de instância do SQL Server e portanto são armazenados na database master. Os usuários acabam se tornando órfãos quando você restaura essa database em outra instância e o login não existe no nível de instância, mas existe no nível de database.

Para identificar os usuários órfãos em uma determinada database você pode utilizar a consulta abaixo:


SELECT dp.type_desc, dp.SID, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
WHERE sp.SID IS NULL AND authentication_type_desc = 'INSTANCE'

Retorno:

Screen Shot 2019-07-30 at 20.39.34.png

Para corrigir um usuário órfão você pode usar as duas opções abaixo:

Opção 1:


CREATE LOGIN [loginName] WITH PASSWORD = 'loginPassword', SID = 0xF4E44137DEB63E4783109C6C06E80BBE

ALTER USER [userName] WITH Login = [loginName]

OBS: O valor do parâmetro SID deve ser preenchido com o valor capturado a partir da consulta anterior utilizada para identificar os usuários órfãos.

Opção 2:


EXECUTE sp_change_users_login AUTO_FIX, 'loginUsername'

Referências:

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql?view=sql-server-2017

Create login in log shipping secondary

Já precisou criar um login em uma database secundária de Log Shipping?

É simples.

Execute a consulta abaixo no servidor primário do Log Shipping e copie o valor do campo sid.

select name, sid from master..syslogins where name in ('loginUsername')

Execute o comando abaixo no servidor secundário do Log Shipping preenchendo o valor da variável @sid com o conteúdo copiado na consulta anterior:


exec sp_addlogin 'loginUsername', @passwd='loginPassword', @sid=0xC8F35F87336B2D49AA7900EED64D83FE

Referências:

https://support.microsoft.com/en-us/help/303722/how-to-grant-access-to-sql-logins-on-a-standby-database-when-the-guest

CDC – Tabelas ativas

Você precisa listar as tabelas com CDC habilitado?

Apenas utilize o script abaixo dentro da sua database com CDC habilitado.


select name
from sys.tables
where is_tracked_by_cdc = 1
order by name

Retorno:
9902

Espero que ajude!

CDC – Databases ativas

Você precisa listar as databases com CDC habilitado?

Apenas use o script abaixo.


SELECT
name,
is_cdc_enabled
FROM sys.databases
WHERE
database_id > 4 AND
is_cdc_enabled = 1
ORDER BY name

Retorno:

99

Espero que ajude!

CDC – View job configuration

Para visualizar a configuração dos jobs de captura (capture) e limpeza (cleanup) é simples, basta utilizar o comando abaixo:


EXECUTE sys.sp_cdc_help_jobs

O retorno trará informações sobre o tempo de retenção, quantidade de registros deletados a cada iteração, intervalo de polling, máximo de scans realizados no log de transação a cada ciclo de verificação, máximo de transações a serem processadas a cada ciclo de verificação.

09

CDC – Change Data Capture

O Change Data Capture ou mais conhecido como CDC é um recurso disponível no SQL Server que armazena todas as alterações (INSERT, UPDATE, DELETE) realizadas em uma determinada tabela.

Por padrão as databases vem com o CDC desabilitado. Caso seja necessário o CDC deve ser ativado para cada database. Assim que o CDC é ativado é criado um schema chamado cdc e também são criadas alguns objetos e estruturas de controle dentro deste schema, como por exemplo:

  • cdc.change_tables
  • cdc.ddl_history
  • cdc.index_columns
  • cdc.lsn_time_mapping

Mesmo que você ative o CDC para uma database específica por padrão as tabelas vem com o CDC desativado, portanto é preciso ativá-lo para cada tabela necessária. Assim que o CDC é ativado para uma tabela específica é criada uma tabela com a mesma estrutura e 5 colunas adicionais no schema cdc:

  • cdc.nomeSchema_nomeTabela

Também são criadas duas funções para extração dos dados:

  • cdc.fn_cdc_get_all_changes_nomeSchema_nomeTabela
  • cdc.fn_cdc_get_net_changes_nomeSchema_nomeTabela

A extração das alterações realizadas em uma determinada tabela é responsabilidade da instância de captura (capture instance). A origem de extração das alterações utilizada pela instância de captura é o arquivo do log de transação (transaction log) da database. A instância de captura é executada através de um job criado no SQL Server Agent:

  • cdc.nomeDatabase_capture

Também é criado um outro job no SQL Server Agent, que é o job de limpeza (cleanup). Este job de limpeza é responsável pela limpeza das tabelas do CDC mantendo por padrão 4320 minutos (3 dias) de alterações dentro de cada tabela do CDC e removendo 5000 linhas a cada iteração.

  • cdc_nomeDatabase_cleanup

A seguir podemos visualizar a arquitetura do Change Data Capture:

01

Referências:

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017