Procedura: utilizzare un sistema di rilevamento delle modifiche personalizzato

Articolo
10/17/2009

Numerose applicazioni richiedono che le modifiche vengano rilevate nel database server, in modo da poter essere recapitate ai client durante una sessione di sincronizzazione successiva. In questo argomento vengono descritti i requisiti per un sistema di rilevamento delle modifiche e viene illustrato come creare un sistema personalizzato che possa essere utilizzato da Sync Framework. Benché il rilevamento delle modifiche personalizzato sia appropriato in alcuni casi, è opportuno considerare che si tratta di un tipo di rilevamento complesso che può influire sulle prestazioni del database server. Se si utilizza SQL Server 2008, si consiglia di utilizzare la caratteristica di rilevamento delle modifiche di SQL Server. Per ulteriori informazioni, vedere Procedura: utilizzare SQL Server Change Tracking.

Requisiti del server per gli scenari di sincronizzazione

Sync Framework è progettato per avere un impatto minimo sul database server. I cambiamenti necessari per il rilevamento delle modifiche nel database server sono pertanto proporzionali al livello di funzionalità desiderato in un'applicazione. Tenere presenti le considerazioni seguenti:

A un'estremità dello spettro è presente uno snapshot di solo download dei dati. Non sono necessarie modifiche.
All'altra estremità vi è la sincronizzazione bidirezionale che utilizza il rilevamento completo delle modifiche e dei conflitti.

Nella tabella seguente sono riepilogate le possibili modalità di utilizzo di Sync Framework e vengono identificati i requisiti corrispondenti per il database server.

Scenario	Chiave primaria o colonna univoca 1	Tenere traccia del tempo di aggiornamento	Tenere traccia del tempo di inserimento	Tenere traccia del tempo di eliminazione	Tenere traccia dell'ID client per gli aggiornamenti	Tenere traccia dell'ID client per gli inserimenti	Tenere traccia dell'ID client per le eliminazioni
Scaricare uno snapshot di dati nel client.	No	No	No	No	No	No	No
Scaricare gli inserimenti e gli aggiornamenti incrementali nel client.	Sì	Sì	Sì2	No	No	No	No
Scaricare gli inserimenti, gli aggiornamenti e le eliminazioni incrementali nel client.	Sì	Sì	Sì2	Sì	No	No	No
Caricare gli inserimenti nel server.	Sì	No	No	No	No	No3	No
Caricare gli inserimenti e gli aggiornamenti nel server.	Sì	No	No	No	No3	No3	No
Caricare gli inserimenti, gli aggiornamenti e le eliminazioni nel server.	Sì	No	No	No	No3	No3	No3
Inserimenti e aggiornamenti bidirezionali con rilevamento dei conflitti.	Sì	Sì	Sì2	No	Sì4	Sì4	No
Inserimenti, aggiornamenti ed eliminazioni bidirezionali con rilevamento dei conflitti.	Sì	Sì	Sì2	Sì	Sì4	Sì4	Sì4

1 Le chiavi primarie devono essere univoche in tutti i nodi e non devono essere riutilizzate. Se viene eliminata una riga, la relativa chiave primaria non deve essere utilizzata per un'altra riga. Le colonne di identità non sono in genere appropriate per gli ambienti distribuiti. Per ulteriori informazioni sulle chiavi primarie, vedere Selezione di una chiave primaria appropriata per un ambiente distribuito.

2 Obbligatorio per la distinzione fra inserimenti e aggiornamenti. Per ulteriori informazioni, vedere "Determinazione delle modifiche ai dati da scaricare in un client" più avanti in questo argomento.

3 Obbligatorio se una riga può essere modificata da più client e se si desidera identificare il client responsabile della modifica. Per ulteriori informazioni, vedere "Identificazione del client responsabile di una modifica dei dati" più avanti in questo argomento.

4 Obbligatorio se non si desidera restituire le modifiche al client che le ha effettuate. Per ulteriori informazioni, vedere "Identificazione del client responsabile di una modifica dei dati" più avanti in questo argomento.

Nota

Oltre alle modifiche descritte precedentemente, è probabile che vengano create stored procedure per accedere ai dati. La maggior parte degli esempi contenuti in questa documentazione utilizza SQL inline in quanto è più semplice visualizzare cosa avviene nel codice. Nelle applicazioni di produzione, è necessario utilizzare stored procedure in quanto incapsulano codice, garantiscono prestazioni migliori e, se scritte correttamente, sono in grado di assicurare maggiore sicurezza rispetto a SQL inline.

Determinazione delle modifiche ai dati da scaricare in un client

Nella sincronizzazione di solo download e bidirezionale, è necessario rilevare le modifiche nel server in modo da consentire a Sync Framework di determinare quali modifiche scaricare nei client. Nonostante Sync Framework non definisca in modo specifico come supportare il rilevamento delle modifiche, questa operazione può essere eseguita tramite un approccio comune. È possibile utilizzare l'approccio seguente per ogni tabella da sincronizzare:

Aggiungere una colonna che tenga traccia del momento di inserimento di una riga nel database server.
Aggiungere una colonna, e in alcuni casi un trigger, che tenga traccia del momento dell'ultimo aggiornamento di una riga nel database server.
Aggiungere una tabella per la rimozione definitiva e un trigger che tenga traccia del momento dell'eliminazione di una riga dal database server. Se non si desidera eliminare dati dal server pur inviando le eliminazioni al client, è possibile tenere traccia delle eliminazioni logiche nella tabella di base. Utilizzare una colonna, in genere di tipo bit, per indicare l'eliminazione di una riga e un'altra colonna per tenere traccia del momento dell'eliminazione.

Le colonne e le tabelle per la rimozione definitiva vengono utilizzate insieme ad ancoraggi per determinare quali inserimenti, aggiornamenti ed eliminazioni scaricare. Un ancoraggio rappresenta un momento specifico utilizzato per definire un set di modifiche da sincronizzare. Considerare le query seguenti:

La query specificata per la proprietà SelectIncrementalInsertsCommand. La query scarica gli inserimenti incrementali dalla tabella Sales.Customer nel database di esempio Sync Framework come indicato di seguito.
```
SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @sync_last_received_anchor
AND InsertTimestamp <= @sync_new_received_anchor
```
Per ulteriori informazioni su questa proprietà e su altre proprietà associate ai comandi di sincronizzazione, vedere Procedura: specificare la sincronizzazione snapshot, di download, di caricamento e bidirezionale.
La query specificata per la proprietà SelectNewAnchorCommand. La query recupera un valore temporale specifico. La colonna InsertTimestamp archivia i valori di timestamp. Pertanto, per recuperare un valore di timestamp dal database server, la query utilizza la funzione Transact-SQL MIN_ACTIVE_ROWVERSION, introdotta in SQL Server 2005 Service Pack 2, nel modo seguente:
```
SELECT @sync_new_received_anchor = MIN_ACTIVE_ROWVERSION - 1
```
MIN_ACTIVE_ROWVERSION restituisce il valore attivo più basso di timestamp (noto anche come rowversion) nel database corrente. Una valore di timestamp è attivo se utilizzato in una transazione di cui non è stato ancora eseguito il commit. In mancanza di valori attivi nel database, MIN_ACTIVE_ROWVERSION restituisce lo stesso valore di @@DBTS + 1. MIN_ACTIVE_ROWVERSION è utile per diversi scenari, tra cui la sincronizzazione dei dati che utilizza i valori di timestamp per raggruppare set di modifiche. Se un'applicazione utilizza @@DBTS al posto di MIN_ACTIVE_ROWVERSION nei comandi di ancoraggio, potrebbero venire trascurate le modifiche attive in fase di sincronizzazione.

Quando la tabella Sales.Customer viene sincronizzata per la prima volta, si verifica il processo seguente:

Viene eseguito il nuovo comando di ancoraggio. Il comando restituisce il valore 0x0000000000000D49. Il valore viene archiviato nel database client. La tabella non è mai stata sincronizzata, pertanto non esiste alcun valore di ancoraggio archiviato nel database client da una sincronizzazione precedente. In questo caso, Sync Framework utilizza il valore più basso disponibile per il tipo di dati SQL Server timestamp: 0x0000000000000000. Di seguito viene riportata la query eseguita da Sync Framework. Questa query scarica lo schema e tutte le righe dalla tabella.
```
exec sp_executesql N'SELECT CustomerId, CustomerName, SalesPerson,
CustomerType FROM Sales.Customer WHERE (InsertTimestamp >
@sync_last_received_anchor AND InsertTimestamp <=
@sync_new_received_anchor)',N'@sync_last_received_anchor timestamp,
@sync_new_received_anchor timestamp',
@sync_last_received_anchor=0x0000000000000000,
@sync_new_received_anchor=0x0000000000000D49
```
Durante la seconda sincronizzazione, viene eseguito il nuovo comando di ancoraggio. Dall'ultima sincronizzazione sono state inserite nuove righe, pertanto il comando restituisce il valore 0x0000000000000D4C. La tabella è già stata sincronizzata in precedenza, pertanto Sync Framework può recuperare il valore di ancoraggio 0x0000000000000D49 archiviato nel database client dalla sincronizzazione precedente. Di seguito è riportata la query che viene eseguita per scaricare dalla tabella solo le righe inserite tra i due valori di ancoraggio.
```
exec sp_executesql N'SELECT CustomerId, CustomerName, SalesPerson,
CustomerType FROM Sales.Customer WHERE (InsertTimestamp >
@sync_last_received_anchor AND InsertTimestamp <=
@sync_new_received_anchor)', N'@sync_last_received_anchor timestamp,
@sync_new_received_anchor timestamp',
@sync_last_received_anchor=0x0000000000000D49,
@sync_new_received_anchor=0x0000000000000D4C
```

Per esempi dei comandi di aggiornamento ed eliminazione, vedere Procedura: scaricare le modifiche incrementali ai dati in un client e Procedura: scambiare le modifiche incrementali bidirezionali ai dati tra un client e un server.

Come accennato in precedenza, il comando utilizzato per recuperare i valori di ancoraggio varia in base al tipo di dati delle colonne di rilevamento nel database server. Per gli esempi di questa documentazione viene utilizzato SQL Servertimestamp, noto anche come rowversion. Per utilizzare una colonna SQL Server datetime, la query per il nuovo comando di ancoraggio deve essere analoga alla seguente:

SELECT @sync_new_received_anchor = GETUTCDATE()

Per determinare quale tipo di dati utilizzare per un ancoraggio, è necessario valutare i requisiti dell'applicazione e considerare la flessibilità di cui si può disporre per modificare lo schema del database server. Se il database è in fase di sviluppo, è possibile specificare esattamente quali colonne e trigger aggiungere. Se il database è in fase di produzione, le opzioni disponibili potrebbero essere più limitate. Considerare le linee guida seguenti:

Tutte le tabelle in un gruppo di sincronizzazione devono utilizzare lo stesso tipo di dati e il nuovo comando di ancoraggio. Se possibile, utilizzare lo stesso tipo di dati e lo stesso comando per tutti i gruppi.
Il tipo di dati datetime è facile da comprendere, inoltre spesso le tabelle sono già dotate di una colonna che tiene traccia del momento in cui una riga è stata modificata. Questo tipo di dati può però presentare problemi se i client si trovano in fusi orari diversi. Con questo tipo di dati infatti, se si selezionano le modifiche incrementali, alcune transazioni potrebbero essere tralasciate.
Il tipo di dati timestamp presenta una maggiore precisione e non dipende dall'ora locale. Ogni tabella di un database SQL Server può però contenere una sola colonna di questo tipo di dati, pertanto, se è necessario distinguere fra inserimenti e aggiornamenti, è possibile aggiungere una colonna di un tipo di dati diverso, ad esempio binary(8), in cui archiviare i valori di timestamp. Per un esempio, vedere Impostare script per le procedure relative ai provider di database. Il tipo di dati timestamp potrebbe rappresentare un problema se il database server viene ripristinato da un backup. Per ulteriori informazioni, vedere Oggetti di database supportati da Sync Framework. Come accennato in precedenza, è consigliabile utilizzare MIN_ACTIVE_ROWVERSION nel comando che seleziona un nuovo ancoraggio.

Identificazione del client responsabile di una modifica dei dati

Può essere necessario identificare il client responsabile di una modifica dei dati per due motivi principali:

Supportare il rilevamento e la risoluzione dei conflitti nella sincronizzazione di solo caricamento e bidirezionale.

Se le modifiche a una riga possono essere apportate dal server e dal client o da più client, potrebbe essere utile identificare quali di questi ha apportato la modifica. Tali informazioni consentono, ad esempio, di scrivere codice in grado di classificare in ordine di priorità una modifica rispetto a un'altra. Senza queste informazioni, verrà mantenuta l'ultima modifica apportata alla riga.
Impedire la ripetizione delle modifiche nel client durante la sincronizzazione bidirezionale.

Sync Framework carica in primo luogo le modifiche nel server e quindi le scarica nel client. Se non si tiene traccia dell'identità del client che ha apportato la modifica, questa verrà caricata nel server e quindi scaricata di nuovo nel client durante la stessa sessione di sincronizzazione. In alcuni casi, la ripetizione delle modifiche è accettabile, ma in altri non lo è.

Come per il rilevamento delle modifiche, Sync Framework non definisce in modo specifico come supportare il rilevamento dell'identità. Questa operazione può essere eseguita tramite un approccio comune. È possibile utilizzare l'approccio seguente per ogni tabella da sincronizzare:

Aggiungere una colonna alla tabella di base che tenga traccia dell'autore di ogni inserimento.
Aggiungere una colonna alla tabella di base che tenga traccia dell'autore di ogni aggiornamento.
Aggiungere una colonna alla tabella per la rimozione definitiva che tenga traccia dell'autore di ogni eliminazione.

Queste colonne e tabelle vengono utilizzate con la proprietà ClientId per determinare quale client ha effettuato ogni inserimento, aggiornamento o eliminazione. La prima volta che una tabella viene sincronizzata utilizzando un metodo diverso dalla sincronizzazione snapshot, Sync Framework archivia un valore GUID nel client per identificarlo. L'ID viene passato a DbServerSyncProvider per poter essere utilizzato nelle query di selezione e aggiornamento in ogni SyncAdapter. Il valore ID è disponibile mediante la proprietà ClientId. Considerare la query Transact-SQL seguente:

SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @sync_last_received_anchor AND
InsertTimestamp <= @sync_new_received_anchor AND InsertId <>
@sync_client_id

La query è simile alla precedente e consente di tenere traccia degli inserimenti effettuati nel server. L'istruzione nella clausola WHERE garantisce che i soli inserimenti scaricati siano quelli non effettuati dal client attualmente in fase di sincronizzazione.

Sync Framework consente inoltre alle applicazioni di identificare i client utilizzando un valore intero nel server anziché un valore GUID. Per ulteriori informazioni, vedere Procedura: utilizzare le variabili di sessione.

Esempi di preparazione del server

Negli esempi seguenti viene mostrato come configurare la tabella Sales.Customer dal database di esempio Sync Framework con l'infrastruttura di rilevamento per gestire lo scenario di applicazioni più complesso: operazioni bidirezionali di inserimento, aggiornamento ed eliminazione con rilevamento dei conflitti. Gli scenari meno complessi non richiedono l'intera infrastruttura. Per ulteriori informazioni, vedere la sezione "Requisiti del server per gli scenari di sincronizzazione" descritta in precedenza in questo argomento. Per uno script completo utilizzabile per creare gli oggetti in questo esempio e oggetti aggiuntivi, vedere Impostare script per le procedure relative ai provider di database. Per ulteriori informazioni sull'utilizzo di questi oggetti, vedere Procedura: specificare la sincronizzazione snapshot, di download, di caricamento e bidirezionale.

Negli esempi contenuti in questa sezione vengono eseguiti i seguenti passaggi per la preparazione di un server:

Verificare lo schema Sales.Customer. Determinare se la tabella dispone di una chiave primaria e di colonne utilizzabili per il rilevamento delle modifiche.
Aggiungere colonne per tenere traccia del momento e della posizione in cui vengono effettuati inserimenti e aggiornamenti.
Creare una tabella per la rimozione definitiva e popolarla aggiungendo un trigger alla tabella Sales.Customer.

Verifica dello schema Sales.Customer

Nell'esempio di codice riportato di seguito viene illustrato lo schema della tabella Sales.Customer. La tabella dispone di una chiave primaria nella colonna CustomerId, ma non di colonne utilizzabili per il rilevamento delle modifiche.

CREATE TABLE SyncSamplesDb.Sales.Customer(
    CustomerId uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NEWID(), 
    CustomerName nvarchar(100) NOT NULL,
    SalesPerson nvarchar(100) NOT NULL,
    CustomerType nvarchar(100) NOT NULL)

Aggiunta di colonne per il rilevamento delle operazioni di inserimento e di aggiornamento

Nell'esempio di codice riportato di seguito vengono aggiunte quattro colonne: UpdateTimestamp, InsertTimestamp, UpdateId e InsertId. La colonna UpdateTimestamp è di tipo SQL Server timestamp. La colonna viene aggiornata automaticamente a ogni aggiornamento della riga. Come accennato in precedenza, una tabella può avere solo una colonna timestamp, pertanto la colonna InsertTimestamp è di tipo binary(8) con un valore predefinito di @@DBTS + 1. Nell'esempio viene aggiunto il valore restituito da @@DBTS, in modo che le colonne UpdateTimestamp e InsertTimestamp presentino lo stesso valore in seguito a un inserimento. Se questa operazione non viene eseguita, sembrerà che ogni riga sia stata aggiornata dopo l'inserimento.

L'ID creato da Sync Framework per ogni client rappresenta un GUID, pertanto le due colonne ID sono di tipo uniqueidentifier. Il valore predefinito delle colonne è 00000000-0000-0000-0000-000000000000. Il valore indica che il server ha eseguito l'aggiornamento o l'inserimento. In un esempio successivo viene inclusa una colonna DeleteId nella tabella per la rimozione definitiva.

ALTER TABLE SyncSamplesDb.Sales.Customer 
    ADD UpdateTimestamp timestamp
ALTER TABLE SyncSamplesDb.Sales.Customer 
    ADD InsertTimestamp binary(8) DEFAULT @@DBTS + 1
ALTER TABLE SyncSamplesDb.Sales.Customer 
    ADD UpdateId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
ALTER TABLE SyncSamplesDb.Sales.Customer 
    ADD InsertId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'

Dopo l'aggiunta delle colonne, nel codice di esempio seguente vengono aggiunti gli indici. Nel codice di esempio vengono creati questi e altri indici nelle colonne in cui vengono eseguite delle query durante la sincronizzazione. Gli indici vengono aggiunti per enfatizzare la necessità di considerare gli indici al momento di implementare il rilevamento delle modifiche nel database server. Verificare di bilanciare le prestazioni del server con quelle della sincronizzazione.

CREATE NONCLUSTERED INDEX IX_Customer_UpdateTimestamp
ON Sales.Customer(UpdateTimestamp)

CREATE NONCLUSTERED INDEX IX_Customer_InsertTimestamp
ON Sales.Customer(InsertTimestamp)

CREATE NONCLUSTERED INDEX IX_Customer_UpdateId
ON Sales.Customer(UpdateId)

CREATE NONCLUSTERED INDEX IX_Customer_InsertId
ON Sales.Customer(InsertId)

Aggiunta di una tabella contrassegnata per la rimozione definitiva per il rilevamento delle operazioni di eliminazione

Nell'esempio di codice seguente viene creata una tabella per la rimozione definitiva con un indice cluster e un trigger per popolare la tabella. Quando si verifica un'operazione di eliminazione nella tabella Sales.Customer, il trigger inserisce una riga nella tabella Sales.Customer_Tombstone. Prima che il trigger esegua un'operazione di inserimento, controlla se la tabella Sales.Customer_Tombstone contiene già una riga con la chiave primaria di una riga eliminata. Questa situazione si verifica quando una riga viene eliminata, reinserita e quindi nuovamente eliminata da Sales.Customer. Se la stessa riga viene rilevata in Sales.Customer_Tombstone, il trigger la elimina per poi reinserirla. Anche la colonna DeleteTimestamp in Sales.Customer_Tombstone può essere aggiornata.

CREATE TABLE SyncSamplesDb.Sales.Customer_Tombstone(
    CustomerId uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED, 
    CustomerName nvarchar(100) NOT NULL,
    SalesPerson nvarchar(100) NOT NULL,
    CustomerType nvarchar(100) NOT NULL,
    DeleteId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
    DeleteTimestamp timestamp)

CREATE TRIGGER Customer_DeleteTrigger 
ON SyncSamplesDb.Sales.Customer FOR DELETE 
AS 
BEGIN 
    SET NOCOUNT ON
    DELETE FROM SyncSamplesDb.Sales.Customer_Tombstone 
        WHERE CustomerId IN (SELECT CustomerId FROM deleted)
    INSERT INTO SyncSamplesDb.Sales.Customer_Tombstone (CustomerId, CustomerName, SalesPerson, CustomerType) 
    SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM deleted
    SET NOCOUNT OFF
END

CREATE CLUSTERED INDEX IX_Customer_Tombstone_DeleteTimestamp
ON Sales.Customer_Tombstone(DeleteTimestamp)

CREATE NONCLUSTERED INDEX IX_Customer_Tombstone_DeleteId
ON Sales.Customer_Tombstone(DeleteId)

Vedere anche

Concetti

Rilevamento delle modifiche nel database server

Condividi tramite