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
CDC – Enable Database
Reply
Author Archives: Felipe Lauffer
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:
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:
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:
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:
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:
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.
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:
Referências:
Resource Database
Conhece essa database?
Já viu ela no Management Studio?
Viu alguma vez um DatabaseID 32767?
A resource é uma Database somente leitura com DBID 32767, que contém todos os objetos de sistema do SQL Server, todos aqueles objetos que utilizamos do schema SYS, como por exemplo o objeto sys.indexes. Todos esses objetos são persistidos nessa base de dados, mas são logicamente visíveis em todas as databases através do schema SYS.
Uma curiosidade é que o máximo de databases que uma instância de SQL Server suporta é 32767 e este número é reservado para a resource database.
Não adianta tentar utilizar a função nativa DB_NAME() para retornar o nome da database pelo ID 32767, pois o retorno será NULL.
Referências:
https://blog.sqlauthority.com/2011/05/10/sql-server-resource-database-id-32767/
SQL Server Start Time
Para conferir o horário de início do serviço da sua instância de SQL Server basta utilizar um dos scripts abaixo.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info; SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1; SELECT create_date FROM sys.databases WHERE name = 'tempdb';
Gostou? Dúvidas? Críticas?
Deixe seu comentário!
TRACE FLAGS
As trace flags são utilizadas para mudar uma característica da instância de SQL Server ou um comportamento em particular.
Elas também são utilizadas em diagnósticos de problemas de desempenho, debug de stored procedures ou até mesmo podem ser recomendadas pelo suporte da Microsoft para corrigir um comportamento específico que esteja impactando negativamente o seu ambiente.
É uma boa prática sempre realizar o teste da trace flag em um ambiente não-produtivo, assim como é uma boa prática realizar o teste em ambiente não-produtivo de qualquer alteração de configuração na sua instância de SQL Server.
Existem 3 escopos para uma trace flag:
1. query: específicas para o contexto de uma consulta.
2. session: ativas para uma conexão específica e são visíveis apenas dentro desta sessão.
3. global: são ativas para toda a instância de SQL Server e visíveis para todas as conexões.
As trace flags de escopo de sessão (session) são ligadas e desligadas com os comandos abaixo:
Como ligar uma trace flag de sessão:
DBCC TRACEON (3604)
Como desligar uma trace flag de sessão:
DBCC TRACEOFF (3604)
Para ligarmos e desligarmos uma trace flag de escopo global utilizamos os mesmos comandos, mas com um parâmetro adicional, conforme abaixo.
Como ligar uma trace flag global:
DBCC TRACEON (1222, -1)
Como desligar uma trace flag global:
DBCC TRACEOFF (1222, -1)
Precisa listar todas as trace flags ativas no seu ambiente e sessão?
Listar TRACE FLAGS ativas
Você utiliza alguma trace flag no seu ambiente?
Comente as trace flags que você utiliza!
Referências:
Trace flags: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
DBCC TRACEON: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-transact-sql
DBCC TRACEOFF: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceoff-transact-sql
Felipe Lauffer 