Copiare e trasformare dati in Istanza gestita di SQL di Azure usando Azure Data Factory o Synapse Analytics

SI APPLICA A: Azure Data Factory Azure Synapse Analytics

Questo articolo illustra come usare l'attività Copy per copiare dati da e in Istanza gestita di SQL di Azure e usare il flusso di dati per trasformare i dati in Istanza gestita di SQL di Azure. Per altre informazioni, vedere gli articoli introduttivi per Azure Data Factory e Synapse Analytics.

Funzionalità supportate

Questo connettore di Istanza gestita di SQL di Azure è supportato per le funzionalità seguenti:

① Runtime di integrazione di Azure ② Runtime di integrazione self-hosted

Per l'attività Copy, questo connettore di database SQL di Azure supporta queste funzioni:

• La copia di dati tramite l'autenticazione SQL e l'autenticazione token dell'applicazione Microsoft Entra con un'entità servizio o identità gestite per le risorse di Azure.
• Come origine, il recupero dati tramite una query SQL o una stored procedure. È anche possibile scegliere di eseguire la copia parallela dall'origine dell'istanza gestita di SQL. Per informazioni dettagliate, vedere la sezione Copia parallela dall'istanza gestita di SQL.
• Come sink, la creazione automatica della tabella di destinazione se non esistente in base allo schema di origine; l’accodamento di dati a una tabella o il richiamo di una stored procedure con logica personalizzata durante la copia.

Prerequisiti

Per accedere all'endpoint pubblico di Istanza gestita di SQL, è possibile usare un runtime di integrazione di Azure gestito. Assicurarsi di abilitare l'endpoint pubblico e consentire anche il traffico degli endpoint pubblici nel gruppo di sicurezza di rete in modo che il servizio possa connettersi al database. Per altre informazioni, vedere questa guida.

Per accedere all'endpoint privato di Istanza gestita di SQL, configurare un runtime di integrazione self-hosted in grado di accedere al database. Se si effettua il provisioning del runtime di integrazione self-hosted nella stessa rete virtuale dell'istanza gestita, verificare che il computer del runtime di integrazione si trovi in una subnet diversa da quella dell'istanza gestita. Se si effettua il provisioning del runtime di integrazione self-hosted in una rete virtuale diversa da quella dell'istanza gestita, è consigliabile usare un peering di rete virtuale o stabilire una connessione tra reti virtuali. Per altre informazioni, vedere Connettere l'applicazione a Istanza gestita di SQL di Azure.

Operazioni preliminari

Per eseguire l'attività di copia con una pipeline, è possibile usare uno degli strumenti o SDK seguenti:

• Strumento Copia dati
• Il portale di Azure
• .NET SDK
• SDK di Python
• Azure PowerShell
• API REST
• Modello di Azure Resource Manager

Creare un servizio collegato a un'istanza gestita di SQL di Azure usando l'interfaccia utente

Usare la procedura seguente per creare un servizio collegato a un'istanza gestita di SQL nell'interfaccia utente del portale di Azure.

1. Passare alla scheda Gestisci nell'area di lavoro di Azure Data Factory o Synapse e selezionare Servizi collegati, quindi fare clic su Nuovo:

   • Azure Data Factory
   • Azure Synapse

   

2. Cercare SQL e selezionare il connettore Istanza gestita di SQL Server di Azure.

   

3. Configurare i dettagli del servizio, testare la connessione e creare il nuovo servizio collegato.

   

Dettagli di configurazione del connettore

Le sezioni seguenti riportano informazioni dettagliate sulle proprietà usate per definire entità di Azure Data Factory specifiche del connettore Istanza gestita di SQL.

Proprietà del servizio collegato

La versione consigliata del connettore Istanza gestita di SQL di Azure supporta TLS 1.3. Fare riferimento a questa sezione per aggiornare la versione del connettore di Istanza gestita di SQL di Azure da quella Legacy. Per informazioni dettagliate sulla proprietà, vedere le sezioni corrispondenti.

• Versione consigliata
• Versione legacy

Versione consigliata

Queste proprietà generiche sono supportate per un servizio collegato Istanza gestita di SQL di Azure quando si applica la versione consigliata:

Per altre proprietà di connessione, vedere la tabella seguente:

Autenticazione SQL

Per usare l’autenticazione SQL, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti:

Esempio 1: usare l'autenticazione SQL

{
    "name": "AzureSqlMILinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Esempio 2: usare l'autenticazione SQL con una password in Azure Key Vault

{
    "name": "AzureSqlMILinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Esempio 3: usare l'autenticazione SQL con Always Encrypted

{
    "name": "AzureSqlMILinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "alwaysEncryptedSettings": {
            "alwaysEncryptedAkvAuthType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticazione dell'entità servizio

Per usare l'autenticazione dell'entità servizio, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti

È anche necessario seguire la procedura seguente:

1. Seguire la procedura per Effettuare il provisioning di un amministratore di Microsoft Entra per l'istanza gestita.

2. Creare un'applicazione Microsoft Entra dal portale di Azure. Prendere nota del nome dell'applicazione e dei valori seguenti che definiscono il servizio collegato:

   • ID applicazione
   • Chiave applicazione
   • ID tenant

3. Creare login per l'entità servizio. In SQL Server Management Studio (SSMS), connettersi all'istanza gestita con un account SQL Server di tipo sysadmin. Eseguire il codice T-SQL seguente nel database master:

   CREATE LOGIN [your application name] FROM EXTERNAL PROVIDER
   

4. Creare utenti del database indipendente per l'entità servizio. Connettersi al database da cui o in cui si vuole copiare i dati ed eseguire il codice T-SQL seguente:

   CREATE USER [your application name] FROM EXTERNAL PROVIDER
   

5. Concedere all'entità servizio le autorizzazioni necessarie, come si fa di norma per utenti SQL o altri utenti. Eseguire il codice seguente. Per altre opzioni, vedere questo documento.

   ALTER ROLE [role name e.g. db_owner] ADD MEMBER [your application name]
   

6. Configurare un servizio collegato Istanza gestita di SQL.

Esempio: usare l'autenticazione dell'entità servizio

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "hostNameInCertificate": "<host name>",
            "authenticationType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalCredential": {
                "type": "SecureString",
                "value": "<application key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticazione dell'identità gestita assegnata dal sistema

Una data factory o un'area di lavoro di Synapse può essere associata a un'identità gestita assegnata dal sistema per risorse di Azure che rappresenta il servizio per l'autenticazione ad altri servizi di Azure. È possibile usare questa identità gestita per l'autenticazione di Istanza gestita di SQL. Il servizio specificato può accedere e copiare i dati dal database o nel database usando questa identità.

Per usare l'autenticazione dell'identità gestita assegnata dal sistema, specificare le proprietà generiche descritte nella sezione precedente e seguire questa procedura.

1. Seguire la procedura per Effettuare il provisioning di un amministratore di Microsoft Entra per l'istanza gestita.

2. Creare login per l'identità gestita assegnata dal sistema. In SQL Server Management Studio (SSMS), connettersi all'istanza gestita con un account SQL Server di tipo sysadmin. Eseguire il codice T-SQL seguente nel database master:

   CREATE LOGIN [your_factory_or_workspace_ name] FROM EXTERNAL PROVIDER
   

3. Creare utenti di database indipendenti per l'identità gestita assegnata dal sistema. Connettersi al database da cui o in cui si vuole copiare i dati ed eseguire il codice T-SQL seguente:

   CREATE USER [your_factory_or_workspace_name] FROM EXTERNAL PROVIDER
   

4. Concedere le autorizzazioni necessarie per l'identità gestita assegnata dal sistema come si fa normalmente per utenti SQL e altri utenti. Eseguire il codice seguente. Per altre opzioni, vedere questo documento.

   ALTER ROLE [role name e.g. db_owner] ADD MEMBER [your_factory_or_workspace_name]
   

5. Configurare un servizio collegato Istanza gestita di SQL.

Esempio: usa l'autenticazione dell'identità gestita assegnata dal sistema

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SystemAssignedManagedIdentity"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticazione dell'identità gestita assegnata dall'utente

Una data factory o un'area di lavoro di Synapse può essere associata a un’identità gestita assegnata dall'utente che rappresenta il servizio per l'autenticazione ad altri servizi di Azure. È possibile usare questa identità gestita per l'autenticazione di Istanza gestita di SQL. Il servizio specificato può accedere e copiare i dati dal database o nel database usando questa identità.

Per usare l'autenticazione dell'identità gestita assegnata dall'utente, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti:

È anche necessario seguire la procedura seguente:

1. Seguire la procedura per Effettuare il provisioning di un amministratore di Microsoft Entra per l'istanza gestita.

2. Creare login per l'identità gestita assegnata dall'utente. In SQL Server Management Studio (SSMS), connettersi all'istanza gestita con un account SQL Server di tipo sysadmin. Eseguire il codice T-SQL seguente nel database master:

   CREATE LOGIN [your_factory_or_workspace_ name] FROM EXTERNAL PROVIDER
   

3. Creare utenti di database indipendenti per l'identità gestita assegnata dall'utente. Connettersi al database da cui o in cui si vuole copiare i dati ed eseguire il codice T-SQL seguente:

   CREATE USER [your_factory_or_workspace_name] FROM EXTERNAL PROVIDER
   

4. Creare una o più identità gestite assegnate dall'utente e concedere le autorizzazioni necessarie per l'identità gestita assegnata dall'utente, come si fa di norma per utenti SQL e altri utenti. Eseguire il codice seguente. Per altre opzioni, vedere questo documento.

   ALTER ROLE [role name e.g. db_owner] ADD MEMBER [your_factory_or_workspace_name]
   

5. Assegnare una o più identità gestite assegnate dall'utente alla data factory e creare le credenziali per ogni identità gestita assegnata dall'utente.

6. Configurare un servizio collegato Istanza gestita di SQL.

Esempio: usa l'autenticazione dell'identità gestita assegnata dall'utente

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "UserAssignedManagedIdentity",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Versione legacy

Queste proprietà generiche sono supportate per un servizio collegato Istanza gestita di SQL di Azure quando si applica la versione Legacy:

Per altri tipi di autenticazione, vedere le sezioni seguenti relative rispettivamente a proprietà e prerequisiti specifici:

• Autenticazione SQL per la versione legacy
• Autenticazione dell'entità servizio per la versione legacy
• Autenticazione dell'identità gestita assegnata dal sistema per la versione legacy
• Autenticazione dell'identità gestita assegnata dall'utente per la versione legacy

Autenticazione SQL per la versione legacy

Per usare l'autenticazione SQL, specificare le proprietà generiche descritte nella sezione precedente.

Autenticazione dell'entità servizio per la versione legacy

Per usare l'autenticazione dell'entità servizio, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti:

È anche necessario seguire la procedura descritta in Autenticazione dell'entità servizio per concedere l'autorizzazione corrispondente.

Autenticazione dell'identità gestita assegnata dal sistema per la versione legacy

Per usare l'autenticazione dell'identità gestita assegnata dal sistema, seguire lo stesso passaggio per la versione consigliata in Autenticazione identità gestita assegnata dal sistema.

Autenticazione dell'identità gestita assegnata dall'utente per la versione legacy

Per usare l'autenticazione dell'identità gestita assegnata dall'utente, seguire lo stesso passaggio per la versione consigliata in Autenticazione identità gestita assegnata dall'utente.

Proprietà del set di dati

Per un elenco completo delle sezioni e delle proprietà disponibili per definire set di dati, vedere l'articolo sui set di dati. In questa sezione viene fornito un elenco delle proprietà supportate dal set di dati di Istanza gestita di SQL.

Per copiare dati da e verso Istanza gestita di SQL, sono supportate le proprietà seguenti:

Esempio

{
    "name": "AzureSqlMIDataset",
    "properties":
    {
        "type": "AzureSqlMITable",
        "linkedServiceName": {
            "referenceName": "<SQL Managed Instance linked service name>",
            "type": "LinkedServiceReference"
        },
        "schema": [ < physical schema, optional, retrievable during authoring > ],
        "typeProperties": {
            "schema": "<schema_name>",
            "table": "<table_name>"
        }
    }
}

Proprietà dell'attività di copia

Per un elenco completo delle sezioni e delle proprietà disponibili per definire le attività, vedere l'articolo sulle pipeline. Questa sezione fornisce un elenco delle proprietà supportate dall'origine e dal sink di Istanza gestita di SQL.

Istanza gestita di SQL come origine

Per copiare dati da Istanza gestita di SQL, nella sezione origine dell'attività di copia sono supportate le proprietà seguenti:

Tenere presente quanto segue:

• Se sqlReaderQuery è specificato per SqlMISource, l'attività di copia esegue questa query sull'origine dell'istanza gestita di SQL per ottenere i dati. In alternativa, è possibile specificare una stored procedure indicando i parametri sqlReaderStoredProcedureName e storedProcedureParameters, se la stored procedure accetta parametri.
• Quando si usa la stored procedure nell'origine per recuperare dati, tenere presente che se la stored procedure è progettata per restituire schemi diversi quando viene passato un valore di parametro diverso, è possibile che si verifichi un errore o un risultato imprevisto durante l'importazione dello schema dall'interfaccia utente o quando si copiano dati nel database SQL con la creazione automatica della tabella.

Esempio: usare una query SQL

"activities":[
    {
        "name": "CopyFromAzureSqlMI",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SQL Managed Instance input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlMISource",
                "sqlReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Esempio: usare una stored procedure

"activities":[
    {
        "name": "CopyFromAzureSqlMI",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SQL Managed Instance input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlMISource",
                "sqlReaderStoredProcedureName": "CopyTestSrcStoredProcedureWithParameters",
                "storedProcedureParameters": {
                    "stringData": { "value": "str3" },
                    "identifier": { "value": "$$Text.Format('{0:yyyy}', <datetime parameter>)", "type": "Int"}
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Definizione della stored procedure

CREATE PROCEDURE CopyTestSrcStoredProcedureWithParameters
(
    @stringData varchar(20),
    @identifier int
)
AS
SET NOCOUNT ON;
BEGIN
    select *
    from dbo.UnitTestSrcTable
    where dbo.UnitTestSrcTable.stringData != stringData
    and dbo.UnitTestSrcTable.identifier != identifier
END
GO

Istanza gestita di SQL come sink

Per copiare dati in Istanza gestita di SQL, nella sezione sink dell'attività di copia sono supportate le proprietà seguenti:

Esempio 1: accodare dati

"activities":[
    {
        "name": "CopyToAzureSqlMI",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Managed Instance output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlMISink",
                "tableOption": "autoCreate",
                "writeBatchSize": 100000
            }
        }
    }
]

Esempio 2: richiamare una stored procedure durante la copia

Per altre informazioni, vedere Richiamare una stored procedure da un sink di Istanza gestita di SQL.

"activities":[
    {
        "name": "CopyToAzureSqlMI",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Managed Instance output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlMISink",
                "sqlWriterStoredProcedureName": "CopyTestStoredProcedureWithParameters",
                "storedProcedureTableTypeParameterName": "MyTable",
                "sqlWriterTableType": "MyTableType",
                "storedProcedureParameters": {
                    "identifier": { "value": "1", "type": "Int" },
                    "stringData": { "value": "str1" }
                }
            }
        }
    }
]

Esempio 3: Upsert dei dati

"activities":[
    {
        "name": "CopyToAzureSqlMI",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Managed Instance output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlMISink",
                "tableOption": "autoCreate",
                "writeBehavior": "upsert",
                "upsertSettings": {
                    "useTempDB": true,
                    "keys": [
                        "<column name>"
                    ]
                },            
            }
        }
    }
]

Copia parallela dall'istanza gestita di SQL

Il connettore Istanza gestita di SQL di Azure nell'attività di copia fornisce il partizionamento dei dati predefinito per copiare i dati in parallelo. È possibile trovare le opzioni di partizionamento dei dati nella tabella Origine dell'attività di copia.

Quando si abilita la copia partizionata, l'attività di copia esegue query parallele sull'origine dell'Istanza gestita di SQL per caricare dati in base alle partizioni. Il grado di parallelismo è controllato dall'impostazione parallelCopies sull'attività di copia. Se, ad esempio, si imposta parallelCopies su quattro, il servizi genera ed esegue simultaneamente quattro query in base all'opzione partizione e alle impostazioni specificate, e ogni query recupera una porzione di dati dal database dell’Istanza gestita di database SQL.

È consigliabile abilitare la copia parallela con partizionamento dei dati, soprattutto quando si caricano grandi quantità di dati dall'istanza gestita di SQL. Di seguito sono riportate le configurazioni consigliate per i diversi scenari: Quando si copiano dati in un archivio dati basato su file, è consigliabile scrivere in una cartella come file multipli (specificare solo il nome della cartella); in tal caso, le prestazioni risultano migliori rispetto alla scrittura in un singolo file.

Procedure consigliate per il caricamento di dati con opzione partizione:

1. Scegliere una colonna distintiva come colonna partizione (ad esempio, chiave primaria o chiave univoca) per evitare l'asimmetria dei dati.
2. Se la tabella include una partizione predefinita, usare l'opzione di partizione "Partizioni fisiche della tabella" per ottenere prestazioni migliori.
3. Se si usa Azure Integration Runtime per copiare i dati, è possibile impostare "Unità di integrazione dati (DIU)" (>4) perché utilizzi più risorse di calcolo. Controllare gli scenari applicabili.
4. “Grado di parallelismo copia” controlla i numeri partizione; se si imposta per questo numero un valore troppo grande, le prestazioni potrebbero talvolta risentirne. È preferibile impostare questo numero come (DIU o numero di nodi del runtime di integrazione self-hosted) * (2-4).

Esempio: caricamento completo da una tabella di grandi dimensioni con partizioni fisiche

"source": {
    "type": "SqlMISource",
    "partitionOption": "PhysicalPartitionsOfTable"
}

Esempio: query con partizione a intervalli dinamici

"source": {
    "type": "SqlMISource",
    "query": "SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>",
    "partitionOption": "DynamicRange",
    "partitionSettings": {
        "partitionColumnName": "<partition_column_name>",
        "partitionUpperBound": "<upper_value_of_partition_column (optional) to decide the partition stride, not as data filter>",
        "partitionLowerBound": "<lower_value_of_partition_column (optional) to decide the partition stride, not as data filter>"
    }
}

Query di esempio per controllare la partizione fisica

SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, pf.name AS PartitionFunctionName, c.name AS ColumnName, iif(pf.name is null, 'no', 'yes') AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id 
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id 
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id 
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id 
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id 
WHERE s.name='[your schema]' AND t.name = '[your table name]'

Se la tabella ha una partizione fisica, viene visualizzato "HasPartition" come "sì", come illustrato di seguito.

Procedura consigliata per il caricamento di dati in Istanza gestita di SQL

Quando si copiano dati in Istanza gestita di SQL, potrebbe essere necessario un comportamento di scrittura diverso:

• Accodamento: i dati di origine hanno solo nuovi record.
• Upsert: i dati di origine hanno sia inserimenti che aggiornamenti.
• Sovrascrittura: si desidera ricaricare ogni volta l'intera tabella dimensioni.
• Scrittura con logica personalizzata: è necessaria un'elaborazione aggiuntiva prima dell'inserimento finale nella tabella di destinazione.

Per informazioni sulla modalità di configurazione e per procedure consigliate, vedere le sezioni pertinenti.

Accodare dati

L'aggiunta di dati è il comportamento predefinito del connettore sink di Istanza gestita di SQL. Il servizio esegue un inserimento bulk per scrivere nella tabella in modo efficiente. È possibile configurare opportunamente l'origine e il sink nell'attività di copia.

Eseguire l'upsert dei dati

L'attività di copia supporta ora il caricamento nativo dei dati in una tabella temporanea del database e quindi l'aggiornamento dei dati nella tabella sink, se la chiave è esistente; in caso contrario, inserisce nuovi dati. Per altre informazioni sulle impostazioni upsert nelle attività di copia, vedere Istanza gestita di SQL come sink.

Sovrascrivere l'intera tabella

È possibile configurare la proprietà preCopyScript in un sink dell'attività di copia. In questo caso, per ogni attività di copia eseguita, il servizio esegue prima lo script. Quindi, esegue la copia per inserire i dati. Ad esempio, per sovrascrivere l'intera tabella con i dati più recenti, specificare uno script per eliminare tutti i record prima del caricamento bulk dei nuovi dati dall'origine.

Scrivere dati con logica personalizzata

I passaggi per scrivere dati con una logica personalizzata sono simili a quelli descritti nella sezione Upsert dei dati. Quando si necessita di applicare un'elaborazione aggiuntiva prima dell'inserimento finale dei dati di origine nella tabella di destinazione, è possibile caricare in una tabella di staging e quindi richiamare un'attività stored procedure, o richiamare una stored procedure nel sink dell'attività di copia per applicare i dati.

Richiamare una stored procedure da un sink SQL

Quando si copiano dati in Istanza gestita di SQL, è anche possibile configurare e richiamare una stored procedure specificata dall'utente con parametri aggiuntivi in ogni batch della tabella di origine. La funzionalità di stored procedure sfrutta i parametri valutati a livello di tabella.

È possibile usare una stored procedure quando non si possono usare i meccanismi di copia predefiniti. Ad esempio, quando si desidera applicare un'elaborazione aggiuntiva prima dell'inserimento finale dei dati di origine nella tabella di destinazione. Alcuni esempi di elaborazione aggiuntivi sono: quando si desidera unire colonne, cercare valori aggiuntivi e inserire in più tabelle.

Nell'esempio seguente viene illustrato come usare una stored procedure per eseguire un'operazione upsert in una tabella del database SQL Server. Si presuppone che i dati di input e la tabella Marketing del sink abbiano tre colonne: ProfileID, Stato e Categoria. Eseguire l'operazione upsert nella colonna ProfileID e applicarla solo a una categoria specifica denominata “ProductA”.

1. Nel database, definire il tipo di tabella con lo stesso nome di sqlWriterTableType. Lo schema del tipo di tabella è identico allo schema restituito dai dati di input.

   CREATE TYPE [dbo].[MarketingType] AS TABLE(
       [ProfileID] [varchar](256) NOT NULL,
       [State] [varchar](256) NOT NULL,
       [Category] [varchar](256) NOT NULL
   )
   

2. Nel database, definire la stored procedure con lo stesso nome di sqlWriterStoredProcedureName. che gestisce i dati di input dell'origine specificata e li unisce nella tabella di output. Il nome del parametro del tipo di tabella nella stored procedure deve essere identico a tableName definito nel set di dati.

   CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY, @category varchar(256)
   AS
   BEGIN
   MERGE [dbo].[Marketing] AS target
   USING @Marketing AS source
   ON (target.ProfileID = source.ProfileID and target.Category = @category)
   WHEN MATCHED THEN
       UPDATE SET State = source.State
   WHEN NOT MATCHED THEN
       INSERT (ProfileID, State, Category)
       VALUES (source.ProfileID, source.State, source.Category);
   END
   

3. Nella pipeline, definire la sezione sink di istanza gestita di SQL nell'attività di copia come indicato di seguito:

   "sink": {
       "type": "SqlMISink",
       "sqlWriterStoredProcedureName": "spOverwriteMarketing",
       "storedProcedureTableTypeParameterName": "Marketing",
       "sqlWriterTableType": "MarketingType",
       "storedProcedureParameters": {
           "category": {
               "value": "ProductA"
           }
       }
   }
   

Proprietà del flusso di dati per mapping

Quando si trasformano dati in flusso di dati per mapping, è possibile leggere e scrivere nelle tabelle da Istanza gestita di SQL di Azure. Per altre informazioni, vedere la trasformazione origine e la trasformazione sink nei flussi di dati per mapping.

Trasformazione origine

La tabella seguente elenca le proprietà supportate dall'origine di Istanza gestita di SQL di Azure. È possibile modificare queste proprietà nella scheda Opzioni origine.

CREATE PROC CTESP @query nvarchar(max)
AS
BEGIN
EXECUTE sp_executesql @query;
END

Esempio di script di origine di Istanza gestita di SQL di Azure

Quando si usa Istanza gestita di SQL di Azure come tipo di origine, lo script del flusso di dati associato è:

source(allowSchemaDrift: true,
    validateSchema: false,
    isolationLevel: 'READ_UNCOMMITTED',
    query: 'select * from MYTABLE',
    format: 'query') ~> SQLMISource

Trasformazione sink

La tabella seguente elenca le proprietà supportate dal sink di Istanza gestita di SQL di Azure. È possibile modificare queste proprietà nella scheda Opzioni sink.

Esempio di script sink di Istanza gestita di SQL di Azure

Quando si usa Istanza gestita di SQL di Azure come tipo di sink, lo script del flusso di dati associato è:

IncomingStream sink(allowSchemaDrift: true,
    validateSchema: false,
    deletable:false,
    insertable:true,
    updateable:true,
    upsertable:true,
    keys:['keyColumn'],
    format: 'table',
    skipDuplicateMapInputs: true,
    skipDuplicateMapOutputs: true) ~> SQLMISink

Proprietà dell'attività Lookup

Per altre informazioni sulle proprietà, vedere Attività Lookup.

Proprietà dell'attività GetMetadata

Per altre informazioni sulle proprietà, vedere Attività GetMetadata

Mapping dei tipi di dati per Istanza gestita di SQL

Quando i dati vengono copiati da e verso Istanza gestita di SQL usando l'attività Copy, i mapping seguenti vengono usati dai tipi di dati di Istanza gestita di SQL ai tipi di dati provvisori usati all’interno del servizio. Per informazioni su come l'attività di copia esegue il mapping dello schema e del tipo di dati di origine al sink, vedere Mapping dello schema e del tipo di dati.

Uso di Always Encrypted

Quando si copiano dati da/a Istanza gestita di SQL con Always Encrypted, seguire questa procedura:

1. Archiviare la chiave master della colonna (CMK) in un Azure Key Vault. Altre informazioni su come configurare Always Encrypted usando Azure Key Vault

2. Assicurarsi di avere accesso ottimale all'insieme di credenziali delle chiavi in cui è archiviata la chiave master della colonna (CMK) . Per le autorizzazioni necessarie, fare riferimento a questo articolo.

3. Creare un servizio collegato per connettersi al database SQL e abilitare la funzione "Always Encrypted" usando l'identità gestita o l'entità servizio.

Change Data Capture nativo

Azure Data Factory può supportare funzionalità Change Data Capture native per SQL Server, il database di Azure SQL e l'istanza gestita di Azure SQL. I dati modificati, inclusi l’inserimento, l’aggiornamento e l’eliminazione di righe in archivi SQL possono essere rilevati ed estratti automaticamente dal flusso di dati per mapping di ADF. Senza alcuna esperienza di coding nel flusso di dati per mapping, gli utenti possono ottenere facilmente uno scenario di replica dei dati da archivi SQL accodando un database come archivio di destinazione. Inoltre, gli utenti possono anche comporre qualsiasi logica di trasformazione dati intermedia per ottenere uno scenario ETL incrementale dagli archivi SQL.

Accertarsi di mantenere invariato il nome della pipeline e dell'attività, in modo che il checkpoint possa essere registrato da ADF per ottenere automaticamente i dati modificati dall'ultima esecuzione. Se si modifica il nome della pipeline o il nome dell'attività, il checkpoint verrà reimpostato, il che porta a ricominciare dall'inizio o a ottenere le modifiche da subito nella successiva esecuzione. Se si desidera modificare il nome della pipeline o il nome dell'attività mantenendo comunque il checkpoint per ottenere automaticamente i dati modificati dall'ultima esecuzione, usare la propria chiave checkpoint nell'attività del flusso di dati per ottenere tale risultato.

Quando si esegue il debug della pipeline, questa funzionalità funziona allo stesso modo. Tenere presente che il checkpoint verrà reimpostato quando si aggiorna il browser durante l'esecuzione del debug. Quando il risultato della pipeline dall'esecuzione del debug è soddisfacente, è possibile procedere alla pubblicazione e all'attivazione della pipeline. Al momento della prima attivazione della pipeline pubblicata, la pipeline viene riavviata automaticamente dall'inizio o ottiene le modifiche da tale momento in avanti.

Nella sezione di monitoraggio, è sempre possibile eseguire nuovamente una pipeline. Quando si esegue questa operazione, i dati modificati vengono sempre acquisiti dal checkpoint precedente dell'esecuzione della pipeline selezionata.

Esempio 1:

Quando si concatena direttamente una trasformazione di origine che fa riferimento al set di dati abilitato per SQL CDC, con una trasformazione sink che fa riferimento a un database in un flusso di dati per mapping, le modifiche apportate all'origine SQL verranno applicate automaticamente al database di destinazione, in modo da ottenere facilmente uno scenario di replica dei dati tra database. È possibile usare il metodo di aggiornamento nella trasformazione sink per selezionare se si desideri consentire l'inserimento, l'aggiornamento o l'eliminazione nel database di destinazione. Lo script di esempio nel flusso di dati di mapping è il seguente.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 sink(allowSchemaDrift: true,
	validateSchema: false,
	deletable:true,
	insertable:true,
	updateable:true,
	upsertable:true,
	keys:['id'],
	format: 'table',
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true,
	errorHandlingOption: 'stopOnFirstError') ~> sink1

Esempio 2:

Se si desidera abilitare lo scenario ETL anziché la replica dei dati tra database tramite SQL CDC, è possibile usare espressioni nel flusso di dati per mapping isInsert(1), isUpdate(1) e isDelete(1) per distinguere le righe con tipi di operazione diversi. Di seguito è riportato uno degli script di esempio per il flusso di dati per mapping sulla derivazione di una colonna con il valore: 1 per indicare le righe inserite, 2 per indicare le righe aggiornate e 3 per indicare le righe eliminate per le trasformazioni downstream per l’elaborazione dei dati delta.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 derive(operationType = iif(isInsert(1), 1, iif(isUpdate(1), 2, 3))) ~> derivedColumn1
derivedColumn1 sink(allowSchemaDrift: true,
	validateSchema: false,
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true) ~> sink1

Limitazione nota:

• Solo modifiche nette da SQL CDC verranno caricate da ADF tramite cdc.fn_cdc_get_net_changes_.

Aggiornare la versione di Istanza gestita di SQL di Azure

Per aggiornare la versione di Istanza gestita di SQL di Azure, nella pagina Modifica servizio collegato selezionare Consigliato in Versione e configurare il servizio collegato facendo riferimento alle Proprietà del servizio collegato per la versione consigliata.

Differenze tra la versione consigliata e la versione legacy

La tabella seguente illustra le differenze tra Istanza gestita di SQL di Azure con uso della versione consigliata e della versione legacy.

Contenuto correlato

Per un elenco degli archivi dati supportati come origini e sink dall'attività di copia, vedere Archivi dati supportati.