Copiare i dati da Amazon RDS per SQL Server usando Azure Data Factory o Azure Synapse Analytics

Questo articolo illustra come usare l'attività Copy in Azure Data Factory e pipeline Azure Synapse per copiare dati da un database Amazon RDS per SQL Server. Per altre informazioni, leggere l'articolo introduttivo per Azure Data Factory o Azure Synapse Analytics.

Funzionalità supportate

Questo connettore Amazon RDS per SQL Server è supportato per le funzionalità seguenti:

① Azure Integration Runtime ② Runtime di integrazione self-hosted

Per un elenco degli archivi dati supportati come origini o sink dall'attività di copia, vedere la tabella relativa agli archivi dati supportati.

In particolare, il connettore Amazon RDS per SQL Server supporta:

• SQL Server versione 2005 e successive.
• La copia dei dati tramite l'autenticazione di SQL o di Windows.
• Come origine, il recupero dati tramite una query SQL o una stored procedure. È anche possibile scegliere di eseguire la copia parallela dall'origine di Amazon RDS per SQL Server. Per informazioni dettagliate, vedere la sezione Copia parallela dal database SQL.

Local DB di SQL Server Express non è supportato.

Prerequisiti

Se l'archivio dati si trova all'interno di una rete locale, una rete virtuale di Azure o un cloud privato virtuale di Amazon, è necessario configurare un runtime di integrazione self-hosted per connettersi.

Se l'archivio dati è un servizio dati del cloud gestito, è possibile usare Azure Integration Runtime. Se l'accesso è limitato solo agli indirizzi IP approvati nelle regole del firewall, è possibile aggiungere IP di Azure Integration Runtime nell'elenco Consentiti.

È anche possibile usare la funzionalitàruntime di integrazione della rete virtuale gestita in Azure Data Factory per accedere alla rete locale senza installare e configurare un runtime di integrazione self-hosted.

Per altre informazioni sui meccanismi di sicurezza di rete e sulle opzioni supportate da Data Factory, vedere strategie di accesso ai dati.

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 il servizio collegato di Amazon RDS per SQL Server tramite l’interfaccia utente

Usare la procedura seguente per creare un servizio collegato di Amazon RDS per SQL Server 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 Amazon RDS per SQL Server e selezionare il connettore Amazon RDS per SQL Server.

   

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

   

Dettagli di configurazione del connettore

Le sezioni seguenti forniscono informazioni dettagliate sulle proprietà usate per definire entità delle pipeline di Data Factory e Synapse specifiche per il connettore del database Amazon RDS per SQL Server.

Proprietà del servizio collegato

Il connettore Amazon RDS per SQL Server nella versione consigliata supporta TLS 1.3. Fare riferimento a questa sezione per aggiornare la versione del connettore Amazon RDS per SQL Server 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 di Amazon RDS per SQL Server 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: usare l’autenticazione SQL

{
    "name": "AmazonSqlLinkedService",
    "properties": {
        "type": "AmazonRdsForSqlServer",
        "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: usare l'autenticazione di SQL con una password in Azure Key Vault

{
    "name": "AmazonSqlLinkedService",
    "properties": {
        "type": "AmazonRdsForSqlServer",
        "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: usare Always Encrypted

{
    "name": "AmazonSqlLinkedService",
    "properties": {
        "type": "AmazonRdsForSqlServer",
        "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 di Windows

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

Esempio: usare l’autenticazione di Windows

{
    "name": "AmazonSqlLinkedService",
    "properties": {
        "type": "AmazonRdsForSqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "Windows",
            "userName": "<domain\\username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Versione legacy

Queste proprietà generiche sono supportate per un servizio collegato di Amazon RDS per SQL Server quando si applica la versione Legacy:

Il connettore Amazon RDS per SQL Server supporta i tipi di autenticazione seguenti. Per informazioni dettagliate, vedere le sezioni corrispondenti.

• Autenticazione di SQL per la versione legacy
• Autenticazione di Windows per la versione legacy

Autenticazione di SQL per la versione legacy

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

Autenticazione di Windows per la versione legacy

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

Proprietà del set di dati

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione di set di dati, vedere l'articolo sui set di dati. Questa sezione fornisce un elenco delle proprietà supportate dal set di dati di Amazon RDS per SQL Server.

Per copiare dati da/in un database di Amazon RDS per SQL Server, sono supportate le proprietà seguenti:

Esempio

{
    "name": "AmazonRdsForSQLServerDataset",
    "properties":
    {
        "type": "AmazonRdsForSqlServerTable",
        "linkedServiceName": {
            "referenceName": "<Amazon RDS for SQL Server 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 di Amazon RDS per SQL Server.

Amazon RDS per SQL Server come origine

Per copiare dati da Amazon RDS per SQL Server, impostare il tipo di origine nell'attività di copia su AmazonRdsForSqlServerSource. Nella sezione source dell'attività di copia sono supportate le proprietà seguenti:

Tenere presente quanto segue:

• Se è specificato sqlReaderQuery per AmazonRdsForSqlServerSource, l’attività Copy esegue questa query nell’origine di Amazon RDS per SQL Server 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": "CopyFromAmazonRdsForSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Amazon RDS for SQL Server input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "AmazonRdsForSqlServerSource",
                "sqlReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Esempio: usare una stored procedure

"activities":[
    {
        "name": "CopyFromAmazonRdsForSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Amazon RDS for SQL Server input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "AmazonRdsForSqlServerSource",
                "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

Copia parallela dal database SQL

Il connettore Amazon RDS per SQL Server 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 di Amazon RDS per SQL Server per caricare i 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 servizio simultaneamente genera ed esegue quattro query in base all'opzione partizione specificata e alle impostazioni, e ogni query recupera una porzione di dati dal database di Amazon RDS per SQL Server.

È preferibile abilitare la copia parallela con il partizionamento dei dati specialmente quando si caricano notevoli quantità di dati dal database di Amazon RDS per SQL Server. 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": "AmazonRdsForSqlServerSource",
    "partitionOption": "PhysicalPartitionsOfTable"
}

Esempio: query con partizione a intervalli dinamici

"source": {
    "type": "AmazonRdsForSqlServerSource",
    "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.

Proprietà dell'attività Lookup

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

Proprietà dell'attività GetMetadata

Per altre informazioni sulle proprietà, vedere Attività GetMetadata

Uso di Always Encrypted

Quando si copiano dati da/in Amazon RDS per SQL Server 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. Accertarsi di concedere l'accesso 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.

Risolvere i problemi di connessione

1. Configurare l’istanza di Amazon RDS per SQL Server per accettare connessioni remote. Avviare Amazon RDS per SQL Server Management Studio, fare clic con il pulsante destro del mouse su server e selezionare Proprietà. Selezionare Connessioni dall'elenco, quindi selezionare la casella di controllo Consenti connessioni remote a questo server.

   

   Per la procedura dettagliata, vedere Configurare l'opzione di configurazione del server di accesso remoto.

2. Avviare Amazon RDS per Gestione configurazione SQL Server. Espandere Configurazione di rete Amazon RDS per SQL Server per l'istanza prevista e selezionare Protocolli per MSSQLSERVER. I protocolli vengono visualizzati nel riquadro destro. Abilitare TCP/IP facendo clic con il pulsante destro del mouse su TCP/IP e selezionando Abilita.

   

   Per informazioni dettagliate e modi alternativi per abilitare il protocollo TCP/IP, vedere Abilitare o disabilitare un protocollo di rete del server.

3. Nella stessa finestra, fare doppio clic su TCP/IP per aprire la finestra Proprietà TCP/IP.

4. Passare alla scheda Indirizzi IP . Scorrere in basso per vedere la sezione IPAll. Prendere nota della Porta TCP. Il valore predefinito è 1433.

5. Creare una regola per Windows Firewall nel computer per consentire il traffico in ingresso attraverso questa porta.

6. Verifica connessione: per connettersi ad Amazon RDS per SQL Server usando un nome completo, usare Amazon RDS per SQL Server Management Studio da un computer diverso. Un esempio è "<machine>.<domain>.corp.<company>.com,1433".

Aggiornare la versione di Amazon RDS per SQL Server

Per aggiornare la versione di Amazon RDS per SQL Server, nella pagina Modifica servizio collegato, selezionare Consigliato in Versione e configurare il servizio collegato facendo riferimento a Proprietà del servizio collegato per la versione consigliata.

Differenze tra la versione consigliata e la versione legacy

La tabella seguente illustra le differenze tra Amazon RDS per SQL Server usando la versione consigliata e la versione legacy.

Contenuto correlato

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