Rilevare le modifiche ai dati (SQL Server)

Articolo
11/19/2013

SQL Server 2012 fornisce due funzionalità che consentono di tener traccia delle modifiche ai dati in un database: Change Data Capture e Rilevamento modifiche. Tali funzionalità consentono alle applicazioni di determinare le modifiche DML (operazioni di inserimento, aggiornamento ed eliminazione) apportate alle tabelle utente in un database. Change Data Capture e Rilevamento modifiche possono essere abilitati sullo stesso database, non sono richieste considerazioni speciali. Per le edizioni di SQL Server che supportano Change Data Capture e Rilevamento modifiche, vedere Funzionalità supportate dalle edizioni di SQL Server 2012.

Vantaggi dall'utilizzo di Change Data Capture o Rilevamento modifiche

Un importante requisito per l'efficienza di alcune applicazioni è costituito dalla possibilità di eseguire query relative a dati che sono stati modificati in un database. In genere, per determinare le modifiche apportate ai dati, gli sviluppatori di applicazioni devono implementare un metodo di rilevamento personalizzato utilizzando una combinazione di trigger, colonne di tipo timestamp e tabelle aggiuntive. La creazione di queste applicazioni comporta in genere molte operazioni di implementazione e rende necessari aggiornamenti di schemi, provocando spesso un elevato overhead delle prestazioni.

L'utilizzo di Change Data Capture o Rilevamento nelle applicazioni per tenere traccia delle modifiche apportate a un database rispetto allo sviluppo di una soluzione personalizzata, presenta i vantaggi indicati di seguito.

Riduzione del tempo necessario per lo sviluppo. Poiché la funzionalità è disponibile in SQL Server 2012, non è necessario sviluppare una soluzione personalizzata.
Modifiche dello schema non necessarie. Non è necessario aggiungere colonne e trigger o creare una tabella laterale in cui tenere traccia delle righe eliminate o in cui archiviare informazioni sul rilevamento delle modifiche se alle tabelle utente non possono essere aggiunte colonne.
Meccanismo di pulizia predefinito. La pulizia relativa al rilevamento delle modifiche viene eseguita automaticamente in background. Non è più necessario eseguire una pulizia personalizzata per dati archiviati in una tabella laterale.
Le funzioni sono fornite per ottenere informazioni sulle modifiche.
Overhead basso per le operazioni DML. Al rilevamento delle modifiche sincrono è sempre associato un livello di overhead. L'utilizzo del rilevamento delle modifiche può consentire la riduzione dell'overhead, che risulterà in genere minore rispetto a quello relativo all'utilizzo di soluzioni alternative, soprattutto soluzioni per cui è necessario utilizzare i trigger.
Utilizzo delle transazioni di cui è stato eseguito il commit come base per il rilevamento delle modifiche. L'ordine delle modifiche si basa sull'ora in cui è stato eseguito il commit della transazione. In questo modo è possibile ottenere risultati affidabili quando sono presenti transazioni sovrapposte e con tempi di esecuzione prolungati. Per la gestione di questi scenari, è necessario progettare in modo specifico soluzioni personalizzate che utilizzano valori timestamp.
Sono disponibili strumenti standard che possono essere utilizzati per configurare e gestire. In SQL Server 2012 sono disponibili istruzioni DDL standard, SQL Server Management Studio, viste del catalogo e autorizzazioni di sicurezza.

Differenze tra le funzionalità di Change Data Capture e del rilevamento delle modifiche

Nella tabella seguente vengono elencate le differenze tra le funzionalità di Change Data Capture e del rilevamento delle modifiche. Il meccanismo di rilevamento in Change Data Capture prevede un'acquisizione asincrona delle modifiche dal log delle transazioni in modo che le modifiche siano disponibili dopo l'operazione DML. Nel rilevamento delle modifiche il meccanismo prevede la registrazione sincrona di modifiche in linea con le operazioni DML in modo che le informazioni sulle modifiche siano disponibili immediatamente.

Funzionalità	Change Data Capture	Rilevamento modifiche
Modifiche rilevate
Modifiche DML	Sì	Sì
Informazioni rilevate
Dati cronologici	Sì	No
Modifiche apportate a una colonna	Sì	Sì
Tipo DML	Sì	Sì

Change Data Capture

Change Data Capture fornisce informazioni cronologiche sulle modifiche per una tabella utente acquisendo l'esecuzione di modifiche DML e le modifiche effettive apportate ai dati. Le modifiche vengono acquisite utilizzando un processo asincrono che legge il log delle transazioni senza un impatto significativo sul sistema.

Come illustrato nella figura seguente, le modifiche apportate alle tabelle utente vengono acquisite nella tabella delle modifiche corrispondente. In tali tabelle è disponibile una vista cronologica delle modifiche nel tempo. Le funzioni di Change Data Capture disponibili in SQL Server consentono ai dati delle modifiche di essere utilizzati in modo semplice e sistematico.

Illustrazione concettuale di Change Data Capture

Modello di sicurezza

In questa sezione viene descritto il modello di sicurezza di Change Data Capture.

Configurazione e amministrazione
Per abilitare o disabilitare la funzionalità Change Data Capture per un database, il chiamante di sys.sp_cdc_enable_db (Transact-SQL) o sys.sp_cdc_disable_db (Transact-SQL) deve essere un membro del ruolo predefinito del server sysadmin. Per abilitare e disabilitare Change Data Capture a livello di tabella, è necessario che il chiamante di sys.sp_cdc_enable_table (Transact-SQL) e sys.sp_cdc_disable_table (Transact-SQL) sia un membro del ruolo sysadmin o del ruolo del database database db_owner.

L'utilizzo delle stored procedure per supportare l'amministrazione dei processi Change Data Capture è limitato ai membri del ruolo del server sysadmin e del ruolo database db_owner.
Enumerazione delle modifiche e query sui metadati
Per accedere ai dati delle modifiche associati a un'istanza di acquisizione, l'utente deve disporre dell'autorizzazione SELECT per l'accesso a tutte le colonne acquisite della tabella di origine associata. Se, inoltre, al momento della creazione dell'istanza di acquisizione viene specificato un ruolo di controllo, il chiamante deve essere anche un membro del ruolo di controllo specificato. Le altre funzioni generali di Change Data Capture per l'accesso ai metadati saranno accessibili a tutti gli utenti del database tramite il ruolo public, sebbene l'accesso ai metadati restituiti venga controllato in genere utilizzando anche l'autorizzazione SELECT per l'accesso alle tabelle di origine sottostanti e tramite l'appartenenza a qualsiasi ruolo di controllo definito.
Operazioni DDL nelle tabelle delle modifiche abilitate per Change Data Capture
Quando una tabella è abilitata per Change Data Capture, le operazioni DDL possono essere eseguite nella tabella solo da un membro del ruolo predefinito del server sysadmin, del ruolo database role db_owner o del ruolo database role db_ddladmin. Se un utente che dispone di autorizzazioni esplicite per eseguire operazioni DDL nella tabella tenta di eseguire tali operazioni, verrò restituito l'errore 22914.

Considerazioni sui tipi di dati per Change Data Capture

La funzionalità Change Data Capture supporta tutti i tipi di colonna di base. Nella tabella seguente vengono descritti il comportamento e i limiti per numerosi tipi di colonna.

Tipo di colonna	Modifiche acquisite nelle tabelle delle modifiche	Limitazioni
Colonne di tipo sparse	Sì	Non supporta l'acquisizione delle modifiche quando si utilizza un set di colonne.
Colonne calcolate	No	Le modifiche alle colonne calcolate non vengono rilevate. La colonna verrà visualizzata nella tabella delle modifiche con il tipo appropriato, ma avrà valore NULL.
XML	Sì	Le modifiche a singoli elementi XML non vengono rilevate.
Timestamp	Sì	Il tipo di dati nella tabella delle modifiche viene convertito in binario.
Tipi di dati BLOB	Sì	L'immagine precedente della colonna BLOB viene archiviata solo se viene modificata la colonna stessa.

Change Data Capture e altre funzionalità di SQL Server

In questa sezione viene descritta l'interazione tra Change Data Capture e le funzionalità seguenti:

Mirroring del database
Replica transazionale
Ripristino o collegamento del database

Mirroring del database

È possibile eseguire il mirroring di un database per il quale la funzionalità Change Data Capture è abilitata. Per assicurarsi che i processi di acquisizione e pulizia vengano eseguiti automaticamente nel database di mirroring, effettuare le operazioni seguenti:

Assicurarsi che l'Agente SQL Server sia in esecuzione nel database di mirroring.
Creare i processi di acquisizione e pulizia nel database di mirroring dopo che il database principale ha eseguito il failover sul database di mirroring. Per creare i processi, utilizzare la stored procedure sys.sp_cdc_add_job (Transact-SQL).

Per ulteriori informazioni sul mirroring del database, vedere Mirroring del database (SQL Server).

Replica transazionale

Le funzionalità Change Data Capture e replica transazionale possono coesistere nello stesso database, tuttavia il popolamento delle tabelle delle modifiche viene gestito in modo diverso se entrambe le funzionalità sono abilitate. Change Data Capture e la replica transazionale utilizzano sempre la stessa stored procedure, sp_replcmds, per leggere le modifiche dal log delle transazioni. Quando Change Data Capture è la sola funzionalità abilitata, SQL Server Agent chiama sp_replcmds. Quando entrambe le funzionalità sono abilitate sullo stesso database, l'agente di lettura log chiama sp_replcmds. Questo agente popola sia le tabelle delle modifiche sia le tabelle del database di distribuzione. Per ulteriori informazioni, vedere Agente lettura log repliche.

Si consideri uno scenario in cui la funzionalità Change Data Capture è abilitata nel database AdventureWorks2012 e due tabelle sono abilitate per l'acquisizione. Per popolare le tabelle delle modifiche, il processo di acquisizione chiama sp_replcmds. Il database viene abilitato per la replica transazionale e viene creata una pubblicazione. L'agente di lettura log viene creato per il database e il processo di acquisizione viene eliminato. L'agente di lettura log continua ad analizzare il log dall'ultimo numero di sequenza di cui è stato eseguito il commit nella tabella delle modifiche. In questo modo, viene assicurata la coerenza dei dati nelle tabelle delle modifiche. Se la replica transazionale è disabilitata in questo database, l'agente di lettura log viene rimosso e il processo di acquisizione viene ricreato.

[!NOTA]

Quando l'agente di lettura log viene utilizzato sia per Change Data Capture sia per la replica transazionale, le modifiche replicate vengono innanzitutto scritte nel database di distribuzione. Le modifiche acquisite vengono quindi scritte nelle tabelle delle modifiche. Il commit di entrambe le operazioni viene eseguito contemporaneamente. Se si verifica della latenza durante la scrittura nel database di distribuzione, si verificherà latenza prima che le modifiche vengano visualizzate nelle tabelle delle modifiche.

Ripristino o collegamento di un database abilitato per Change Data Capture

In SQL Server viene utilizzata la logica seguente per determinare se la funzionalità Change Data Capture rimane abilitata anche dopo il ripristino o il collegamento di un database:

Se un database viene ripristinato nello stesso server con lo stesso nome di database, la funzionalità Change Data Capture rimane abilitata.
Se un database viene ripristinato in un altro server, per impostazione predefinita la funzionalità Change Data Capture viene disabilitata e tutti i metadati correlati vengono eliminati.

Per mantenere abilitata la funzionalità Change Data Capture, utilizzare l'opzione KEEP_CDC durante il ripristino del database. Per ulteriori informazioni su questa opzione, vedere RESTORE.
Se un database viene scollegato e collegato allo stesso o a un altro server, la funzionalità Change Data Capture rimane abilitata.
Se un database viene collegato o ripristinato con l'opzione KEEP_CDC in qualsiasi edizione diversa da Enterprise, l'operazione viene bloccata perché Change Data Capture richiede SQL Server Enterprise Edition e viene visualizzato il messaggio di errore 932:

SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

È possibile utilizzare sys.sp_cdc_disable_db per rimuovere Change Data Capture da un database collegato o ripristinato.

Icona freccia utilizzata con il collegamento Torna all'inizio [Inizio pagina]

Rilevamento modifiche

Il rilevamento delle modifiche consente di acquisire l'esecuzione di modifiche alle righe di una tabella, ma non di acquisire i dati modificati. In questo modo le applicazioni sono in grado di determinare le righe che sono state modificate con gli ultimi dati delle righe ottenuti direttamente dalle tabelle utente. Di conseguenza, il rilevamento delle modifiche è più limitato nelle richieste relative alla cronologia cui è in grado di rispondere rispetto a Change Data Capture. Per le applicazioni che non richiedono le informazioni cronologiche, tuttavia, l'overhead di archiviazione relativo al rilevamento delle modifiche è notevolmente minore poiché i dati modificati non vengono acquisiti. Per tenere traccia delle modifiche, viene utilizzato un meccanismo di rilevamento sincrono appositamente progettato per consentire un overhead minimo per le operazioni DML.

Nella figura seguente viene illustrato uno scenario di sincronizzazione per cui sarebbe più vantaggioso utilizzare il rilevamento delle modifiche. In tale scenario un'applicazione richiede tutte le righe della tabella modificate dall'ultima sincronizzazione della tabella e solo i dati della riga correnti. Poiché per tenere traccia delle modifiche viene utilizzato un meccanismo sincrono, un'applicazione può eseguire una sincronizzazione bidirezionale e rilevare qualsiasi conflitto che potrebbe essersi verificato in modo affidabile.

Illustrazione concettuale del rilevamento delle modifiche

Rilevamento delle modifiche e Sync Services for ADO.NET

Sync Services for ADO.NET consente la sincronizzazione tra database fornendo un'API intuitiva e flessibile che permette di compilare applicazioni per scenari offline e di collaborazione. Sync Services for ADO.NET fornisce un'API per sincronizzare le modifiche, ma non tiene effettivamente traccia delle modifiche nel database peer o del server. È possibile creare un sistema di rilevamento delle modifiche personalizzato, ma questa operazione comporta in genere un overhead in termini di complessità e di prestazioni. Per tenere traccia delle modifiche eseguite in un database del server o peer, è consigliabile utilizzare il rilevamento delle modifiche disponibile in SQL Server 2012 poiché è semplice da configurare e caratterizzato da un elevato livello di prestazioni.

Per ulteriori informazioni sul rilevamento delle modifiche e Sync Services for ADO.NET, utilizzare i collegamenti seguenti:

Informazioni sul rilevamento delle modifiche (SQL Server)

Viene descritto il rilevamento delle modifiche, viene fornita una panoramica di alto livello del funzionamento del rilevamento delle modifiche e viene descritta l'interazione del rilevamento delle modifiche con le altre funzionalità del Motore di database di SQL Server.
Microsoft Sync Framework Developer Center

Fornisce una documentazione completa per Sync Framework e Sync Services. Nella documentazione per Sync Services, l'argomento "Procedura: Utilizzare il rilevamento delle modifiche di SQL Server" contiene informazioni dettagliate ed esempi di codice.

Icona freccia utilizzata con il collegamento Torna all'inizio [Inizio pagina]

Altre correlate (richieste)

Attività	Argomento
Fornisce una panoramica di Change Data Capture.	Informazioni su Change Data Capture (SQL Server)
Descrive come abilitare e disabilitare Change Data Capture in un database o una tabella.	Abilitare e disabilitare Change Data Capture (SQL Server)
Descrive come amministrare ed eseguire il monitoraggio di Change Data Capture.	Amministrare e monitorare Change Data Capture (SQL Server)
Descrive come utilizzare i dati di modifica disponibili agli utenti di Change Data Capture. In questo argomento viene illustrata la convalida dei limiti LSN, le funzioni di query e gli scenari delle funzioni di query.	Utilizzare i dati delle modifiche (SQL Server)
Fornisce una panoramica del rilevamento delle modifiche.	Informazioni sul rilevamento delle modifiche (SQL Server)
Descrive come abilitare e disabilitare il rilevamento delle modifiche in un database o una tabella.	Abilitare e disabilitare il rilevamento delle modifiche (SQL Server)
Descrive come gestire il rilevamento delle modifiche, configurare la sicurezza e determinare gli effetti sull'archiviazione e sulle prestazioni quando si utilizza il rilevamento delle modifiche.	Gestire il rilevamento delle modifiche (SQL Server)
Descrive come le applicazioni che utilizzano il rilevamento delle modifiche possano ottenere le modifiche registrate, applicare tali modifiche a un altro archivio dati e aggiornare il database di origine. In questo argomento viene inoltre descritto il ruolo svolto dal rilevamento delle modifiche quando si verifica un failover ed è necessario ripristinare un database da un backup.	Utilizzare il rilevamento delle modifiche (SQL Server)

Vedere anche

Condividi tramite