Utilizzo di Sql Server Modifica Data Capture con uno schema che cambia frequentemente

Abbiamo anche recentemente iniziato a guardare CDC. Non sono un esperto in materia, ma penso di avere alcune risposte alle tue domande.

Per la maggior parte, CDC aiuta a raggiungere il tuo obiettivo di una storia completamente tracciabile, ma non penso che ti farà arrivare fino in fondo.

Primo:

we frequently make changes to the database schema ... Is there a mechanism to update the CDC table to the new schema

Ed è qui che penso che CDC ti mancherà. La documentazione MSDN nella sezione "Capire il sovraccarico del monitoraggio delle modifiche" è piuttosto chiara che non traccia le modifiche dello schema per te. Ad esempio, con Alter Table Add Column :

If a new column is added to the change tracked table, the addition of the column is not tracked. Only the updates and changes that are made to the new column are tracked.

Drop Column è un po 'più complesso.

Tuttavia, dovresti utilizzare gli script DB per modificare lo schema in modo da non dover necessariamente fare affidamento su CDC qui. Ciò ti consente di avere coerenza tra il tuo QA e gli schemi di produzione. E la modifica al controllo qualità deve essere eseguita dallo script in modo che le stesse modifiche identiche possano essere applicate a Prod. Non dovrebbe essere troppo difficile estrarre le modifiche allo schema da quegli script. Ciò potrebbe significare che la dimensione "tempo" della tua cronologia sia guidata dalla versione anziché dall'ora effettiva, ma il risultato finale sarà lo stesso.

Se non ne hai già uno, crea una tabella per tracciare la versione dello schema del tuo database. Quindi posiziona la tabella delle versioni dello schema del database in CDC in modo da poter allineare le modifiche macroscopiche allo schema rispetto alle modifiche microscopiche all'interno di una determinata tabella.

A mio parere, dovresti comunque vedere i dati aggiunti alle nuove colonne indipendentemente dal fatto che CDC non mostri la modifica dello schema. E la migrazione dei dati da una tabella all'altra dovrebbe essere rilevata da CDC.

are there any best practices to how you deal with captured data when migrating the database schema?

Trattalo come se dovessi trattare un audit. Devi capire che cosa stai esaminando, perché lo stai esaminando e per quanto tempo hai bisogno di mantenere queste informazioni in giro. Ambito e conservazione sono i due bugaboos più importanti quando si tratta di un'attività come questa.

Gli strumenti di reporting di CDC sono comprensibilmente austeri, quindi è necessario conoscere il contesto delle modifiche. È troppo facile dire "track tutto !" e finiscono con niente che sia utilizzabile come risultato. Allo stesso modo, potresti raddoppiare la dimensione del tuo database mantenendo una copia di ogni modifica. Su una tabella di abbandono con molti inserti e cancellazioni, finirai con una crescita astronomica. Non è male di per sé, ma è necessario budget per quella crescita e avere un mezzo per esaminare tutti i dati che vengono generati.

Quindi questo ti riporta indietro a capire perché sei stato spinto ad avere una tracciabilità completa. Ci sono certamente validi motivi per questo requisito. Ma non sarai in grado di strutturare il tuo controllo effettivo del database finché non saprai perché devi soddisfare tale requisito.

Sei in grado di tracciare l'aggiunta di colonne con trigger DDL.

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] |
EXTERNAL NAME < method specifier > [ ; ] }

Puoi utilizzare il gruppo eventi DDL_TABLE_EVENTS per attivare CREATE, DROP o ALTER di una tabella.

Tuttavia, potresti dare un'occhiata a Controllo SQL Server , se stai utilizzando enterprise > 2008, poiché può "combinare tutte le funzionalità di controllo in una specifica di controllo". Questo link msdn contiene un articolo dettagliato a riguardo: link .

CREATE SERVER AUDIT audit_name
TO { [ FILE (<file_options> [, ...n]) ] |
APPLICATION_LOG | SECURITY_LOG }
[ WITH ( <audit_options> [, ...n] ) ] }[ ; ]

<file_options>::=
{FILEPATH = 'os_file_path'
[, MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]
[, MAX_ROLLOVER_FILES = integer ]
[, RESERVE_DISK_SPACE = { ON | OFF } ] }

<audit_options>::=
{ [ QUEUE_DELAY = integer ]
[, ON_FAILURE = { CONTINUE | SHUTDOWN } ]
[, AUDIT_GUID = uniqueidentifier ]}

Quindi crei le specifiche del server o del database.

Simple-Talk contiene un ottimo articolo sull'acquisizione dei dati sulle modifiche e una guida molto dettagliata su CDC, nonché MSDN spiega i vari metodi in base ai requisiti. Ma entrambi possono aiutarti con le tue esigenze specifiche.