Bagikan melalui

Salin dan ubah data ke dan dari SQL Server dengan menggunakan Azure Data Factory atau Azure Synapse Analytics

  • Artikel

BERLAKU UNTUK: Azure Data Factory Azure Synapse Analytics

Tip

Cobalah Data Factory di Microsoft Fabric, solusi analitik all-in-one untuk perusahaan. Microsoft Fabric mencakup semuanya mulai dari pergerakan data hingga ilmu data, analitik real time, kecerdasan bisnis, dan pelaporan. Pelajari cara memulai uji coba baru secara gratis!

Artikel ini menguraikan cara menggunakan aktivitas penyalinan di Azure Data Factory dan Azure alur Synapse untuk menyalin data dari dan ke database SQL Server dan menggunakan Aliran Data untuk mengubah data di database SQL Server. Untuk mempelajari selengkapnya, baca artikel pengantar untuk Azure Data Factory atau Azure Synapse Analytics.

Kemampuan yang didukung

Konektor SQL Server ini didukung untuk kemampuan berikut ini:

Kemampuan yang didukung IR
Salin aktivitas (sumber/sink) (1) (2)
Memetakan aliran data (sumber/sink) (1)
Aktivitas pencarian (1) (2)
Aktivitas GetMetadata (1) (2)
Aktivitas skrip (1) (2)
Aktivitas prosedur tersimpan (1) (2)

① Runtime integrasi Azure ② Runtime integrasi yang dihost sendiri

Untuk daftar penyimpanan data yang didukung sebagai sumber atau sink oleh aktivitas salin, lihat tabel Penyimpanan data yang didukung.

Secara khusus, konektor SQL Server ini mendukung:

  • SQL Server versi 2005 ke atas.
  • Menyalin data dengan menggunakan SQL atau NTLM.
  • Sebagai sumber, mengambil data dengan menggunakan kueri SQL atau prosedur tersimpan. Anda juga dapat memilih untuk salin paralel dari sumber SQL Server, lihat bagian Salinan paralel dari database SQL untuk detailnya.
  • Sebagai sink, secara otomatis membuat tabel tujuan jika tidak ada berdasarkan skema sumber; menambahkan data ke tabel atau memanggil prosedur tersimpan dengan logika kustom selama salinan.

SQL Server Express LocalDB tidak didukung.

Penting

Sumber data harus mendukung jenis data NVARCHAR karena memengaruhi pengodean data saat pengkodean non-universal diterapkan pada data.

Prasyarat

Jika penyimpanan data Anda terletak di dalam jaringan lokal, jaringan virtual Azure, atau Amazon Virtual Private Cloud, Anda harus mengonfigurasi runtime integrasi yang dihosting sendiri untuk menghubungkannya.

Jika penyimpanan data Anda adalah layanan data cloud terkelola, Anda dapat menggunakan Azure Integration Runtime. Jika akses dibatasi untuk IP yang disetujui dalam aturan firewall, Anda dapat menambahkan IP Azure Integration Runtime ke daftar izinkan.

Anda juga dapat menggunakan fitur runtime integrasi jaringan virtual terkelola di Azure Data Factory untuk mengakses jaringan lokal tanpa menginstal dan mengonfigurasi runtime integrasi yang dihosting sendiri.

Untuk informasi selengkapnya tentang mekanisme dan opsi keamanan jaringan yang didukung oleh Data Factory, lihat Strategi akses data.

Memulai

Untuk melakukan aktivitas Salin dengan alur, Anda dapat menggunakan salah satu alat atau SDK berikut:

Membuat layanan SQL Server tertaut menggunakan UI

Gunakan langkah-langkah berikut untuk membuat layanan SQL Server tertaut di UI portal Azure.

  1. Telusuri ke tab Kelola di ruang kerja Azure Data Factory atau Synapse Anda dan pilih Layanan Tertaut, lalu klik Baru:

  2. Cari SQL dan pilih konektor SQL Server.

    Tangkapan layar konektor SQL Server.

  3. Konfigurasikan detail layanan, uji koneksi, dan buat layanan tertaut baru.

    Cuplikan layar konfigurasi untuk layanan SQL Server tertaut.

Detail konfigurasi konektor

Bagian berikut ini menyediakan detail tentang properti yang digunakan untuk menentukan entitas alur Synapse dan Data Factory khusus untuk konektor database SQL Server.

Properti layanan tertaut

Versi SQL Server Recommended mendukung TLS 1.3. Lihat bagian ini untuk meningkatkan layanan tertaut SQL Server Anda jika Anda menggunakan versi Warisan. Untuk detail properti, lihat bagian yang sesuai.

Tip

Jika Anda menemui kesalahan dengan kode kesalahan "UserErrorFailedToConnectToSqlServer" dan pesan seperti "Batas sesi untuk database adalah XXX dan telah tercapai," tambahkan Pooling=false ke string koneksi Anda dan coba lagi.

Properti generik ini didukung untuk layanan tertaut server SQL saat Anda menerapkan versi yang Direkomendasikan :

Properti Deskripsi Wajib
jenis Properti jenis harus diatur ke SqlServer. Ya
server Nama atau alamat jaringan instans server SQL yang ingin Anda sambungkan. Ya
database Nama database. Ya
authenticationType Jenis yang digunakan untuk autentikasi. Nilai yang diizinkan adalah SQL (default), Windows dan UserAssignedManagedIdentity (hanya untuk SQL Server di Azure VM). Buka bagian autentikasi yang relevan pada properti dan prasyarat tertentu. Ya
alwaysEncryptedSettings Tetapkan informasi alwaysencryptedsettings yang diperlukan untuk mengaktifkan Always Encrypted untuk melindungi data sensitif yang disimpan di server SQL dengan menggunakan identitas terkelola atau prinsip layanan. Untuk informasi selengkapnya, lihat contoh JSON mengikuti tabel dan bagian Menggunakan Always Encrypted. Jika tidak ditentukan, pengaturan default selalu dienkripsi akan dinonaktifkan. No
mengenkripsi Menunjukkan apakah enkripsi TLS diperlukan untuk semua data yang dikirim antara klien dan server. Opsi: wajib (untuk true, default)/opsional (untuk false)/strict. No
trustServerCertificate Tunjukkan apakah saluran akan dienkripsi saat melewati rantai sertifikat untuk memvalidasi kepercayaan. No
hostNameInCertificate Nama host yang digunakan saat memvalidasi sertifikat server untuk koneksi. Ketika tidak ditentukan, nama server digunakan untuk validasi sertifikat. No
connectVia Runtime integrasi ini digunakan untuk menyambungkan ke penyimpanan data. Pelajari selengkapnya dari bagian Prasyarat. Jika tidak ditentukan, runtime integrasi Azure default digunakan. No

Untuk properti koneksi tambahan, lihat tabel di bawah ini:

Properti Deskripsi Wajib diisi
applicationIntent Jenis beban kerja aplikasi saat menyambungkan ke server. Nilai yang diizinkan adalah ReadOnly dan ReadWrite. No
connectTimeout Lamanya waktu (dalam detik) untuk menunggu koneksi ke server sebelum mengakhiri upaya dan menghasilkan kesalahan. No
connectRetryCount Jumlah koneksi ulang yang dicoba setelah mengidentifikasi kegagalan koneksi diam. Nilai harus berupa bilangan bulat antara 0 dan 255. No
connectRetryInterval Jumlah waktu (dalam detik) antara setiap upaya koneksi ulang setelah mengidentifikasi kegagalan koneksi diam. Nilai harus berupa bilangan bulat antara 1 dan 60. No
loadBalanceTimeout Waktu minimum (dalam detik) agar koneksi hidup di kumpulan koneksi sebelum koneksi dihancurkan. No
commandTimeout Waktu tunggu default (dalam detik) sebelum mengakhiri upaya untuk menjalankan perintah dan menghasilkan kesalahan. No
integratedSecurity Nilai yang diizinkan adalah true atau false. Saat menentukan false, tunjukkan apakah userName dan kata sandi ditentukan dalam koneksi. Saat menentukan true, menunjukkan apakah kredensial akun Windows saat ini digunakan untuk autentikasi. No
failoverPartner Nama atau alamat server mitra yang akan disambungkan jika server utama tidak berfungsi. No
maxPoolSize Jumlah maksimum koneksi yang diizinkan dalam kumpulan koneksi untuk koneksi tertentu. No
minPoolSize Jumlah minimum koneksi yang diizinkan dalam kumpulan koneksi untuk koneksi tertentu. No
multipleActiveResultSets Nilai yang diizinkan adalah true atau false. Saat Anda menentukan true, aplikasi dapat mempertahankan beberapa kumpulan hasil aktif (MARS). Ketika Anda menentukan false, aplikasi harus memproses atau membatalkan semua kumpulan hasil dari satu batch sebelum dapat menjalankan batch lain pada koneksi tersebut. No
multiSubnetFailover Nilai yang diizinkan adalah true atau false. Jika aplikasi Anda tersambung ke grup ketersediaan AlwaysOn (AG) pada subnet yang berbeda, atur properti ini untuk true memberikan deteksi dan koneksi yang lebih cepat ke server yang saat ini aktif. No
packetSize Ukuran dalam byte paket jaringan yang digunakan untuk berkomunikasi dengan instans server. No
Penggabungan Nilai yang diizinkan adalah true atau false. Saat Anda menentukan true, koneksi akan dikumpulkan. Ketika Anda menentukan false, koneksi akan dibuka secara eksplisit setiap kali koneksi diminta. No

Autentikasi SQL

Untuk menggunakan autentikasi SQL, selain properti generik yang dijelaskan di bagian sebelumnya, tentukan properti berikut:

Properti Deskripsi Wajib diisi
userName Nama pengguna yang akan digunakan saat menyambungkan ke server. Ya
kata sandi Kata sandi untuk nama pengguna. Tandai bidang ini sebagai SecureString untuk menyimpannya dengan aman. Atau, Anda dapat mereferensikan rahasia yang disimpan di Azure Key Vault. No

Contoh: Menggunakan autentikasi SQL

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "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"
        }
    }
}

Contoh: Menggunakan autentikasi SQL dengan kata sandi di Azure Key Vault

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "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"
        }
    }
}

Contoh: Gunakan Always Encrypted

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "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"
        }
    }
}

Autentikasi Windows

Untuk menggunakan autentikasi Windows, selain properti generik yang dijelaskan di bagian sebelumnya, tentukan properti berikut:

Properti Deskripsi Wajib diisi
userName Tentukan nama pengguna. Contohnya adalah domainname\username. Ya
kata sandi Tentukan kata sandi untuk akun pengguna yang Anda tentukan untuk nama pengguna. Tandai bidang ini sebagai SecureString untuk menyimpannya dengan aman. Atau, Anda dapat mereferensikan rahasia yang disimpan di Azure Key Vault. Ya

Catatan

Autentikasi Windows tidak didukung dalam aliran data.

Contoh: Menggunakan autentikasi Windows

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "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"
        }
    }
}

Contoh: Menggunakan autentikasi Windows dengan kata sandi di Azure Key Vault

{
    "name": "SqlServerLinkedService",
    "properties": {
        "annotations": [],
        "type": "SqlServer",
        "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": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                },
                "secretName": "<secretName>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autentikasi identitas terkelola yang ditetapkan pengguna

Catatan

Autentikasi identitas terkelola yang ditetapkan pengguna hanya berlaku untuk SQL Server di Azure VM.

Pabrik data atau ruang kerja Synapse dapat dikaitkan dengan identitas terkelola yang ditetapkan pengguna yang mewakili layanan saat mengautentikasi ke sumber daya lain di Azure. Anda dapat menggunakan identitas terkelola ini untuk SQL Server pada autentikasi Azure VM . Pabrik yang ditunjuk atau ruang kerja Synapse dapat mengakses dan menyalin data dari atau ke database Anda dengan menggunakan identitas ini.

Untuk menggunakan autentikasi identitas terkelola yang ditetapkan pengguna, selain properti generik yang dijelaskan di bagian sebelumnya, tentukan properti berikut ini:

Properti Deskripsi Wajib diisi
informasi masuk Tentukan identitas terkelola yang ditetapkan pengguna sebagai objek kredensial. Ya

Anda juga harus mengikuti langkah-langkah di bawah:

  1. Berikan izin ke identitas terkelola yang ditetapkan pengguna Anda.

  2. Aktifkan autentikasi Microsoft Entra ke SQL Server Anda di Azure VM.

  3. Buat pengguna database mandiri untuk identitas terkelola yang ditetapkan pengguna. Sambungkan ke database dari atau tempat Anda ingin menyalin data dengan menggunakan alat seperti SQL Server Management Studio, dengan identitas Microsoft Entra yang memiliki setidaknya izin UBAH PENGGUNA APA PUN. Jalankan T-SQL berikut:

    CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;

  4. Buat satu atau beberapa identitas terkelola yang ditetapkan pengguna dan berikan izin yang diperlukan identitas terkelola yang ditetapkan pengguna seperti yang biasa Anda lakukan untuk pengguna SQL dan lainnya. Jalankan kode berikut. Untuk opsi selengkapnya, lihat dokumen ini.

    ALTER ROLE [role name] ADD MEMBER [your_resource_name];

  5. Tetapkan satu atau beberapa identitas terkelola yang ditetapkan pengguna ke pabrik data Anda dan buat info masuk untuk setiap identitas terkelola yang ditetapkan pengguna.

  6. Mengonfigurasi layanan tertaut SQL Server.

Contoh

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "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"
        }
    }
}

Versi warisan

Properti generik ini didukung untuk layanan tertaut server SQL saat Anda menerapkan versi Warisan :

Properti Deskripsi Wajib
jenis Properti jenis harus diatur ke SqlServer. Ya
alwaysEncryptedSettings Tetapkan informasi alwaysencryptedsettings yang diperlukan untuk mengaktifkan Always Encrypted untuk melindungi data sensitif yang disimpan di server SQL dengan menggunakan identitas terkelola atau prinsip layanan. Untuk informasi selengkapnya, lihat bagian Menggunakan Always Encrypted. Jika tidak ditentukan, pengaturan default selalu dienkripsi akan dinonaktifkan. No
connectVia Runtime integrasi ini digunakan untuk menyambungkan ke penyimpanan data. Pelajari selengkapnya dari bagian Prasyarat. Jika tidak ditentukan, runtime integrasi Azure default digunakan. No

Konektor server SQL ini mendukung jenis autentikasi berikut. Lihat bagian terkait untuk detailnya.

Autentikasi SQL untuk versi warisan

Untuk menggunakan autentikasi SQL, selain properti generik yang dijelaskan di bagian sebelumnya, tentukan properti berikut:

Properti Deskripsi Wajib diisi
connectionString Tentukan informasi connectionString yang diperlukan untuk tersambung ke database SQL Server. Tentukan nama login sebagai nama pengguna Anda, dan pastikan database yang ingin Anda sambungkan dipetakan ke proses masuk ini. Ya
kata sandi Jika Anda ingin memasukkan kata sandi di Azure Key Vault, tarik konfigurasi password dari string koneksi. Untuk informasi selengkapnya, lihat Menyimpan kredensial di Azure Key Vault. No

Autentikasi Windows untuk versi warisan

Untuk menggunakan autentikasi Windows, selain properti generik yang dijelaskan di bagian sebelumnya, tentukan properti berikut:

Properti Deskripsi Wajib diisi
connectionString Tentukan informasi connectionString yang diperlukan untuk tersambung ke database SQL Server. Ya
userName Tentukan nama pengguna. Contohnya adalah domainname\username. Ya
kata sandi Tentukan kata sandi untuk akun pengguna yang Anda tentukan untuk nama pengguna. Tandai bidang ini sebagai SecureString untuk menyimpannya dengan aman. Atau, Anda dapat mereferensikan rahasia yang disimpan di Azure Key Vault. Ya

Properti himpunan data

Untuk daftar lengkap bagian dan properti yang tersedia untuk menentukan himpunan data, lihat artikel himpunan data. Bagian ini menyediakan daftar properti yang didukung oleh himpunan data SQL Server.

Untuk menyalin data dari dan ke database SQL Server, properti berikut ini didukung:

Properti Deskripsi Wajib
jenis Properti jenis himpunan data harus diatur ke SqlServerTable. Ya
skema Nama skema. Tidak untuk sumber, Ya untuk sink
tabel Nama tabel/tampilan. Tidak untuk sumber, Ya untuk sink
tableName Nama tabel/tampilan dengan skema. Properti ini didukung untuk kompatibilitas mundur. Untuk beban kerja baru, gunakan schema dan table. Tidak untuk sumber, Ya untuk sink

Contoh

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

Properti aktivitas salin

Untuk daftar lengkap bagian dan properti yang tersedia untuk digunakan untuk menentukan aktivitas, lihat artikel Alur. Bagian ini menyediakan daftar properti yang didukung oleh sumber dan sink SQL Server.

SQL Server sebagai sumber

Tip

Untuk memuat data dari SQL Server secara efisien dengan menggunakan partisi data, pelajari lebih lanjut dari Salin paralel dari database SQL.

Untuk menyalin data dari SQL Server, atur tipe sumber dalam aktivitas salin ke SqlSource. Berikut ini properti yang didukung di bagian sumber aktivitas salin:

Properti Deskripsi Wajib
jenis Properti jenis sumber aktivitas salin harus diatur ke SqlSource. Ya
sqlReaderQuery Gunakan kueri SQL kustom untuk membaca data. Contohnya select * from MyTable. No
sqlReaderStoredProcedureName Properti ini adalah nama prosedur tersimpan yang membaca data dari tabel sumber. Pernyataan SQL terakhir harus merupakan pernyataan SELECT dalam prosedur tersimpan. No
storedProcedureParameters Parameter ini untuk prosedur tersimpan.
Nilai yang diizinkan adalah pasangan nama atau nilai. Nama dan kapitalisasi parameter harus sesuai dengan nama dan kapitalisasi parameter prosedur tersimpan.		 No
isolationLevel Menentukan perilaku penguncian transaksi untuk sumber SQL. Nilai yang diperbolehkan adalah: ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Rekam jepret. Jika tidak ditentukan, tingkat isolasi default database digunakan. Lihat ke dokumen ini untuk detail selengkapnya. No
partitionOptions Menentukan opsi partisi data yang digunakan untuk memuat data dari SQL Server.
Nilai yang diizinkan adalah: Tidak ada (default), PhysicalPartitionsOfTable, dan DynamicRange.
Ketika opsi partisi diaktifkan (yaitu, bukan None ), tingkat paralelisme untuk secara bersamaan memuat data dari SQL Server dikendalikan oleh pengaturan parallelCopies pada aktivitas salin.		 No
partitionSettings Tentukan grup pengaturan untuk pemartisian data.
Terapkan saat opsi partisi bukan None.		 No
Di bawah partitionSettings:
partitionColumnName Tentukan nama kolom sumber dalam bilangan bulat atau jenis tanggal/waktu (int, smallint, bigint, date, smalldatetime, datetime, datetime2, atau datetimeoffset) yang akan digunakan oleh partisi rentang untuk salinan paralel. Jika tidak ditentukan, indeks atau kunci primer tabel terdeteksi secara otomatis dan digunakan sebagai kolom partisi.
Terapkan saat opsi partisi adalah DynamicRange. Jika Anda menggunakan kueri untuk mengambil data sumber, kaitkan ?DfDynamicRangePartitionCondition di klausul WHERE. Misalnya, lihat bagian Penyalinan paralel dari database SQL.		 No
partitionUpperBound Nilai maksimum kolom partisi untuk pemisahan rentang partisi. Nilai ini digunakan untuk menentukan langkah partisi, bukan untuk memfilter baris dalam tabel. Semua baris dalam tabel atau hasil kueri akan dipartisi dan disalin. Jika tidak ditentukan, aktivitas salin secara otomatis mendeteksi nilai.
Terapkan saat opsi partisi adalah DynamicRange. Misalnya, lihat bagian Penyalinan paralel dari database SQL.		 No
partitionLowerBound Nilai minimum kolom partisi untuk pemisahan rentang partisi. Nilai ini digunakan untuk menentukan langkah partisi, bukan untuk memfilter baris dalam tabel. Semua baris dalam tabel atau hasil kueri akan dipartisi dan disalin. Jika tidak ditentukan, aktivitas salin secara otomatis mendeteksi nilai.
Terapkan saat opsi partisi adalah DynamicRange. Misalnya, lihat bagian Penyalinan paralel dari database SQL.		 No

Perhatikan poin-poin berikut:

  • Jika sqlReaderQuery ditentukan untuk SqlSource, aktivitas salin menjalankan kueri ini terhadap sumber SQL Server untuk mendapatkan data. Anda juga dapat menentukan prosedur tersimpan dengan menentukan sqlReaderStoredProcedureName dan storedProcedureParameters jika prosedur yang disimpan mengambil parameter.
  • Saat menggunakan prosedur tersimpan di sumber untuk mengambil data, perhatikan apakah prosedur tersimpan dirancang sebagai mengembalikan skema yang berbeda ketika nilai parameter yang berbeda diteruskan, Anda mungkin mengalami kegagalan atau melihat hasil yang tidak terduga saat mengimpor skema dari antarmuka pengguna atau saat menyalin data ke database SQL dengan pembuatan tabel otomatis.

Contoh: Gunakan kueri SQL

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

Contoh: Gunakan prosedur tersimpan

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

Definisi prosedur tersimpan

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

SQL Server sebagai sink

Tip

Pelajari lebih lanjut tentang perilaku tulis, konfigurasi, dan praktik terbaik yang didukung dari Praktik terbaik untuk memuat data ke SQL Server.

Untuk menyalin data ke SQL Server, atur jenis sink dalam aktivitas salin ke SqlSink. Berikut adalah properti yang didukung di bagian sink aktivitas salin:

Properti Deskripsi Wajib
jenis Properti tipe sink aktivitas salin harus diatur ke SqlSink. Ya
preCopyScript Properti ini menentukan kueri SQL untuk dijalankan aktivitas salin sebelum menulis data ke SQL Server. Ini diaktifkan hanya sekali per eksekusi salinan. Anda dapat menggunakan properti ini untuk membersihkan data yang telah dimuat sebelumnya. No
tableOption Menentukan apakah akan membuat tabel sink secara otomatis jika tidak ada berdasarkan skema sumber. Pembuatan tabel otomatis tidak didukung saat sink menentukan prosedur tersimpan. Nilai yang diizinkan adalah: none (default), autoCreate. No
sqlWriterStoredProcedureName Nama prosedur tersimpan yang menentukan cara menerapkan data sumber ke dalam tabel target.
Prosedur tersimpan ini digunakan per batch. Untuk operasi yang hanya berjalan sekali dan tidak ada hubungannya dengan data sumber, misalnya, menghapus atau memotong, gunakan preCopyScript properti.
Lihat contoh dari Menggunakan prosedur tersimpan dari sink SQL.		 No
storedProcedureTableTypeParameterName Nama parameter jenis tabel yang ditentukan dalam prosedur tersimpan. No
sqlWriterTableType Tentukan nama jenis tabel yang akan digunakan dalam prosedur tersimpan. Aktivitas salin membuat data yang sedang dipindahkan tersedia dalam tabel sementara dengan jenis tabel ini. Kode prosedur tersimpan kemudian dapat menggabungkan data yang sedang disalin dengan data yang ada. No
storedProcedureParameters Parameter untuk prosedur tersimpan.
Nilai yang diizinkan adalah pasangan nama dan nilai. Nama dan casing parameter harus sesuai dengan nama dan casing parameter prosedur yang disimpan.		 No
writeBatchSize Jumlah baris yang akan disisipkan ke dalam tabel SQL per batch.
Nilai yang diizinkan untuk jumlah baris adalah bilangan bulat. Secara default, layanan secara dinamis menentukan ukuran batch yang sesuai berdasarkan ukuran baris.		 No
writeBatchTimeout Waktu tunggu untuk operasi sisipkan, upsert, dan prosedur tersimpan selesai sebelum waktu habis.
Nilai yang diperbolehkan adalah untuk rentang waktu. Contohnya adalah "00:30:00" untuk 30 menit. Jika tidak ada nilai yang ditentukan, batas waktu default ke "00:30:00".		 No
 maxConcurrentConnections Batas atas koneksi bersamaan yang ditetapkan ke penyimpanan data selama eksekusi aktivitas. Menentukan nilai hanya saat Anda ingin membatasi koneksi bersamaan.  Tanpa
WriteBehavior Tentukan perilaku tulis untuk aktivitas salin untuk memuat data ke dalam Database SQL Server.
Nilai yang diizinkan adalah Insert dan Upsert. Secara default, layanan menggunakan insert untuk memuat data.		 No
upsertSettings Tentukan grup pengaturan untuk perilaku penulisan.
Terapkan saat opsi WriteBehavior adalah Upsert.		 No
Di bawah upsertSettings:
useTempDB Tentukan apakah akan menggunakan tabel sementara global atau tabel fisik sebagai tabel sementara untuk upsert.
Secara default, layanan menggunakan tabel sementara global sebagai tabel interim. nilai adalah true.		 No
interimSchemaName Tentukan skema interim untuk membuat tabel interim jika tabel fisik digunakan. Catatan: pengguna harus memiliki izin untuk membuat dan menghapus tabel. Secara default, tabel interim akan berbagi skema yang sama dengan tabel sink.
Terapkan saat opsi useTempDB adalah False.		 No
kunci Tentukan nama kolom untuk identifikasi baris unik. Salah satu kunci atau serangkaian kunci dapat digunakan. Jika tidak ditentukan, kunci primer digunakan. No

Contoh 1: Menambahkan data

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

Contoh 2: Gunakan prosedur tersimpan selama salinan

Pelajari detail selengkapnya dari Gunakan prosedur tersimpan dari sink SQL.

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

Contoh 3: Data upsert

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

Salin paralel dari database SQL

Konektor SQL Server dalam aktivitas salin menyediakan partisi data bawaan untuk menyalin data secara paralel. Anda dapat menemukan opsi pemartisian data pada tab Sumber aktivitas salin.

Cuplikan layar opsi partisi

Saat Anda mengaktifkan salinan terpartisi, aktivitas salin menjalankan kueri paralel terhadap sumber SQL Server Anda untuk memuat data menurut partisi. Derajat paralel dikendalikan oleh pengaturan parallelCopies pada aktivitas salin. Misalnya, jika Anda mengatur parallelCopies ke empat, Data Factory secara bersamaan dan menjalankan empat kueri berdasarkan opsi dan pengaturan partisi yang ditentukan, dan setiap kueri yang mengambil sebagian data dari SQL Server Anda.

Sebaiknya Anda mengaktifkan salin paralel dengan partisi data terutama ketika Anda memuat sejumlah besar data dari SQL Server Anda. Berikut ini adalah konfigurasi yang disarankan untuk skenario yang berbeda. Saat menyalin data ke penyimpanan data berbasis file, disarankan untuk menulis ke folder sebagai beberapa file (hanya tentukan nama folder), dalam hal ini performanya lebih baik daripada menulis ke satu file.

Skenario Pengaturan yang disarankan
Pemuatan penuh dari tabel besar, dengan partisi fisik. Opsi partisi: Partisi fisik tabel.

Selama eksekusi, layanan secara otomatis mendeteksi partisi fisik, dan menyalin data berdasarkan partisi.

Untuk memeriksa apakah tabel Anda memiliki partisi fisik atau tidak, Anda dapat merujuk ke kueri ini.
Pemuatan penuh dari tabel besar, tanpa partisi fisik, sedangkan dengan bilangan bulat atau kolom tanggalwaktu untuk pemartisian data. Opsi partisi: Partisi rentang dinamis.
Kolom partisi (opsional): Menentukan kolom yang digunakan untuk mempartisi data. Jika belum ditentukan, kolom kunci primer digunakan.
Batas atas partisi dan batas bawah partisi (opsional): Menentukan apakah Anda ingin menentukan langkah partisi. Ini bukan untuk memfilter baris dalam tabel, semua baris dalam tabel akan dipartisi dan disalin. Jika tidak ditentukan, salin aktivitas otomatis mendeteksi nilai dan dapat memakan waktu lama tergantung pada nilai MIN dan MAX. Dianjurkan untuk memberikan batas atas dan batas bawah.

Misalnya, jika kolom partisi "ID" Anda memiliki rentang nilai dari 1 hingga 100, dan Anda menetapkan batas bawah sebagai 20 dan batas atas sebagai 80, dengan salinan paralel sebagai 4, layanan mengambil data dengan 4 partisi - ID dalam rentang <=20, [21, 50], [51, 80], dan >=81, masing-masing.
Memuat sejumlah besar data dengan menggunakan kueri kustom, tanpa partisi fisik, sedangkan dengan kolom bilangan bulat atau tanggal/tanggalwaktu untuk pemartisian data. Opsi partisi: Partisi rentang dinamis.
Kueri: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>.
Kolom partisi: Menentukan kolom yang digunakan untuk mempartisi data.
Batas atas partisi dan batas bawah partisi (opsional): Menentukan apakah Anda ingin menentukan langkah partisi. Ini bukan untuk memfilter baris dalam tabel, semua baris dalam hasil kueri akan dipartisi dan disalin. Jika tidak ditentukan, aktivitas salin secara otomatis mendeteksi nilai.

Misalnya, jika kolom partisi "ID" Anda memiliki rentang nilai dari 1 hingga 100, dan Anda menetapkan batas bawah sebagai 20 dan batas atas sebagai 80, dengan salinan paralel sebagai 4, layanan mengambil data dengan 4 partisi- ID dalam rentang <=20, [21, 50], [51, 80], dan >=81, secara berurutan.

Berikut adalah sampel kueri lainnya untuk skenario yang berbeda:
1. Kueri seluruh tabel:
SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition
2. Kueri dari tabel dengan pemilihan kolom dan filter where-clause tambahan:
SELECT <column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>
3. Kueri dengan subkueri:
SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>
4. Kueri dengan partisi dalam subkueri:
SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition) AS T

Praktik terbaik untuk memuat data dengan opsi partisi:

  1. Pilih kolom yang khas sebagai kolom partisi (seperti kunci primer atau kunci unik) untuk menghindari penyimpangan data.
  2. Jika tabel memiliki partisi bawaan, gunakan opsi partisi "Partisi fisik tabel" untuk mendapatkan performa yang lebih baik.
  3. Jika Anda menggunakan Azure Integration Runtime untuk menyalin data, Anda dapat mengatur "Unit Integrasi Data (DIU)" yang lebih besar (>4) untuk menggunakan lebih banyak sumber daya komputasi. Periksa skenario yang berlaku di sana.
  4. "Tingkat paralelisme penyalinan" mengontrol jumlah partisi, mengatur jumlah ini terlalu besar kadang menurunkan kinerja, rekomendasikan mengatur angka ini sebagai (DIU atau jumlah simpul Runtime integrasi yang dihost sendiri) * (2 hingga 4).

Contoh: pemuatan penuh dari tabel besar dengan partisi fisik

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

Contoh: kueri dengan partisi rentang dinamis

"source": {
    "type": "SqlSource",
    "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>"
    }
}

Sampel kueri untuk memeriksa partisi fisik

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]'

Jika tabel memiliki partisi fisik, Anda akan melihat "HasPartition" sebagai "ya" seperti berikut ini.

Hasil kueri Sql

Praktik terbaik untuk memuat data ke SQL Server

Ketika Anda menyalin data ke SQL Server, Anda mungkin memerlukan perilaku menulis yang berbeda:

  • Tambahkan: Data sumber saya hanya memiliki rekaman baru.
  • Upsert: Data sumber saya memiliki sisipan dan pembaruan.
  • Timpa: Saya ingin memuat ulang seluruh tabel dimensi setiap saat.
  • Menulis dengan logika kustom: Saya perlu pemrosesan ekstra sebelum penyisipan akhir ke dalam tabel tujuan.

Lihat bagian masing-masing untuk cara mengonfigurasi dan praktik terbaik.

Menambahkan data

Menambahkan data adalah perilaku default konektor sink SQL Server ini. Layanan melakukan penyisipan massal untuk menulis ke tabel Anda secara efisien. Anda dapat mengonfigurasi sumber dan sink sesuai dengan aktivitas salin.

Meng-upsert data

Aktivitas salin sekarang mendukung pemuatan data secara native ke tabel sementara database, lalu memperbarui data di tabel sink jika ada kunci dan jika tidak, memasukkan data baru. Untuk mempelajari lebih lanjut tentang pengaturan upsert dalam aktivitas salin, lihat SQL Server sebagai sink.

Timpa seluruh tabel

Anda dapat mengonfigurasi properti preCopyScript di sink aktivitas salin. Dalam hal ini, untuk setiap aktivitas penyalinan yang berjalan, layanan menjalankan skrip terlebih dahulu. Kemudian menjalankan penyalinan untuk menyisipkan data. Misalnya, untuk menimpa seluruh tabel dengan data terbaru, tentukan skrip untuk terlebih dahulu menghapus semua rekaman sebelum Anda memuat data baru secara massal dari sumbernya.

Menulis data dengan logika kustom

Langkah-langkah untuk menulis data dengan logika kustom mirip dengan yang dijelaskan di bagian Meng-upsert data. Ketika Anda perlu menerapkan pemrosesan ekstra sebelum penyisipan akhir data sumber ke dalam tabel tujuan, Anda dapat memuat ke tabel pentahapan lalu menggunakan aktivitas prosedur tersimpan, atau menggunakan prosedur tersimpan di sink aktivitas salin untuk menerapkan data.

Memanggil prosedur tersimpan dari sink SQL

Saat menyalin data ke database SQL Server, Anda juga dapat mengonfigurasi dan menggunakan prosedur tersimpan oleh pengguna dengan parameter tambahan pada setiap batch tabel sumber. Fitur prosedur tersimpan memanfaatkan parameter bernilai tabel. Perhatikan bahwa layanan secara otomatis mencakup prosedur tersimpan dalam transaksinya sendiri, sehingga setiap transaksi yang dibuat di dalam prosedur yang disimpan akan menjadi transaksi berlapis, dan dapat memiliki implikasi untuk penanganan pengecualian.

Anda dapat menggunakan prosedur tersimpan saat mekanisme salinan bawaan tidak berfungsi sebagai mana mestinya. Contohnya adalah ketika Anda ingin menerapkan pemrosesan ekstra sebelum penyisipan akhir data sumber ke dalam tabel tujuan. Beberapa contoh pemrosesan tambahan adalah saat Anda ingin menggabungkan kolom, mencari nilai tambahan, dan menyisipkan ke dalam lebih dari satu tabel.

Sampel berikut ini memperlihatkan cara menggunakan prosedur tersimpan untuk melakukan upsert ke dalam tabel dalam database SQL Server Database. Asumsikan bahwa data input dan tabel Pemasaran sink masing-masing memiliki tiga kolom: IDProfil, Status, dan Kategori. Lakukan upsert berdasarkan kolom IDProfil, dan hanya menerapkannya untuk kategori tertentu yang disebut "ProductA".

  1. Di database Anda, tentukan jenis tabel dengan nama yang sama dengan sqlWriterTableType. Skema jenis tabel sama dengan skema yang dikembalikan oleh data input Anda.

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

  2. Di database Anda, tentukan prosedur tersimpan dengan nama yang sama dengan sqlWriterStoredProcedureName. Ini menangani data input dari sumber yang Anda tentukan dan bergabung ke dalam tabel output. Nama parameter jenis tabel dalam prosedur tersimpan sama dengan tableName yang ditentukan dalam himpunan data.

    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. Tentukan bagian SQL sink dalam aktivitas penyalinan sebagai berikut:

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

Properti pemetaan aliran data

Saat melakukan transformasi data dalam aliran data pemetaan, Anda dapat membaca dan menulis ke tabel dari Database SQL Server. Untuk informasi selengkapnya, lihat transformasi sumber dan transformasi sink dalam aliran data pemetaan.

Catatan

Untuk mengakses SQL Server di lokasi, Anda perlu menggunakan Azure Data Factory atau ruang kerja Synapse Jaringan Virtual Terkelola menggunakan titik akhir privat. Lihat tutorial ini untuk langkah-langkah terperinci.

Transformasi sumber

Tabel di bawah ini mencantumkan properti yang didukung oleh sumber SQL Server. Anda bisa mengedit properti ini di tab opsi Sumber.

Nama Deskripsi Wajib diisi Nilai yang diizinkan Properti skrip aliran data
Tabel Jika Anda memilih Tabel sebagai input, aliran data mengambil semua data dari tabel yang ditentukan dalam himpunan data. No - -
Kueri Jika Anda memilih Kueri sebagai input, tentukan kueri SQL untuk mengambil data dari sumber, yang menggantikan tabel apa pun yang Anda tentukan dalam himpunan data. Menggunakan kueri adalah cara yang bagus untuk mengurangi baris untuk pengujian atau pencarian.

Klausa Urutkan Menurut tidak didukung, tetapi Anda dapat mengatur pernyataan SELECT FROM lengkap. Anda juga dapat menggunakan fungsi tabel yang ditentukan pengguna. pilih * dari udfGetData() adalah UDF di SQL yang menghasilkan tabel yang dapat Anda gunakan dalam aliran data.
Contoh kueri: Select * from MyTable where customerId > 1000 and customerId < 2000		 No String pertanyaan
Ukuran batch Menentukan ukuran batch untuk membagi data besar ke dalam pembacaan. No Bilangan bulat batchSize
Tingkat Isolasi Pilih salah satu tingkat isolasi berikut:
- Read Committed
- Read Uncommitted (default)
- Repeatable Read
- Serializable
- Tidak ada (abaikan tingkat isolasi)		 No READ_COMMITTED
READ_UNCOMMITTED
REPEATABLE_READ
SERIALIZABLE
NONE		 isolationLevel
Aktifkan ekstrak inkremental Gunakan opsi ini untuk memberi tahu ADF untuk hanya memproses baris yang telah berubah sejak terakhir kali alur dijalankan. No - -
Kolom tanggal inkremental Saat menggunakan fitur ekstrak inkremental, Anda harus memilih kolom tanggal/waktu yang ingin Anda gunakan sebagai marka air di tabel sumber Anda. No - -
Aktifkan pengambilan data perubahan asli (Pratinjau) Gunakan opsi ini untuk memberi tahu ADF untuk hanya memproses data delta yang diambil oleh SQL mengubah teknologi penangkapan data sejak terakhir kali alur dijalankan. Dengan opsi ini, data delta termasuk sisipan baris, pembaruan, dan penghapusan akan dimuat secara otomatis tanpa kolom tanggal tambahan yang diperlukan. Anda perlu mengaktifkan ubah pengambilan data di SQL Server sebelum menggunakan opsi ini di ADF. Untuk informasi selengkapnya tentang opsi ini di ADF, lihat pengambilan data perubahan asli. No - -
Mulai membaca dari awal Mengatur opsi ini dengan ekstrak inkremental akan menginstruksikan ADF untuk membaca semua baris pada eksekusi pertama alur dengan ekstrak inkremental diaktifkan. No - -

Tip

Ekspresi tabel umum (CTE) dalam SQL tidak didukung dalam mode Kueri aliran data pemetaan, karena prasyarat penggunaan mode ini adalah kueri dapat digunakan dalam klausul kueri SQL FROM tetapi CTE tidak dapat melakukannya. Untuk menggunakan CTE, Anda perlu membuat prosedur tersimpan menggunakan kueri berikut:

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

Kemudian gunakan mode Prosedur tersimpan dalam transformasi sumber aliran data pemetaan dan atur @query seperti contoh with CTE as (select 'test' as a) select * from CTE. Kemudian Anda dapat menggunakan CTE seperti yang diharapkan.

Contoh skrip sumber SQL Server

Saat menggunakan SQL Server sebagai jenis sumber, skrip aliran data terkait adalah:

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

Transformasi sink

Tabel di bawah ini mencantumkan properti yang didukung oleh sink SQL Server. Anda dapat mengedit properti ini di tab opsi Sink.

Nama Deskripsi Wajib diisi Nilai yang diizinkan Properti skrip aliran data
Perbarui() metode Menentukan operasi apa yang diizinkan di tujuan database Anda. Defaultnya hanya mengizinkan operasi sisipan.
Untuk memperbarui, meningkatkan, atau menghapus baris, transformasi baris Alterdiperlukan untuk menandai baris untuk tindakan tersebut.		 Ya true atau false dapat dideletable
dapat disisipkan
dapat diperbarui
dapat dibuat upsert
Kolom kunci Untuk pembaruan, upsert, dan penghapusan, kolom kunci harus diatur untuk menentukan baris mana yang akan diubah.
Nama kolom yang Anda pilih sebagai kunci akan digunakan sebagai bagian dari pembaruan, upsert, penghapusan berikutnya. Oleh karena itu, Anda harus memilih kolom yang ada di pemetaan Sink.		 No Array kunci
Lompati penulisan kolom kunci Jika Anda ingin tidak menulis nilai ke kolom kunci, pilih "Lompati penulisan kolom kunci". No true atau false skipKeyWrites
Tindakan pada tabel Menentukan apakah akan membuat ulang atau menghapus semua baris dari tabel tujuan sebelum menulis.
- Tidak Ada: Tidak ada tindakan yang akan dilakukan pada tabel.
- Buat ulang: Tabel akan dihapus dan dibuat ulang. Diperlukan jika membuat tabel baru secara dinamis.
- Kosongkan: Semua baris dari tabel target akan dihapus.		 No true atau false recreate
Memotong
Ukuran batch Menentukan berapa banyak baris yang sedang ditulis di setiap batch. Ukuran batch yang lebih besar meningkatkan pemadatan dan pengoptimalan memori, tetapi berisiko kehabisan pengecualian memori saat penembolokan data. No Bilangan bulat batchSize
Skrip Pra dan Pasca SQL Menentukan skrip SQL multibaris yang akan dijalankan sebelum (prapemrosesan) dan setelah (pascapemrosesan) data ditulis ke database Sink Anda. No String preSQL
postSQLs

Tip

  1. Disarankan untuk memecah skrip batch tunggal dengan beberapa perintah menjadi beberapa batch.
  2. Hanya pernyataan Bahasa Definisi Data (DDL) dan Bahasa Manipulasi Data (DML) yang menampilkan jumlah pembaruan sederhana yang dapat dijalankan sebagai bagian dari batch. Pelajari selengkapnya dari Melakukan operasi batch

Contoh skrip sink SQL Server

Saat menggunakan SQL Server sebagai jenis sink, skrip aliran data terkait adalah:

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

Pemetaan tipe data untuk SQL Server

Saat Anda menyalin data dari dan ke SQL Server, pemetaan berikut ini digunakan dari tipe data SQL Server ke tipe data interim Azure Data Factory. Pipa sinapsis, yang mengimplementasikan Data Factory, menggunakan pemetaan yang sama. Untuk mempelajari bagaimana aktivitas salin memetakan skema sumber dan jenis data ke sink, lihat Pemetaan skema dan jenis data.

Tipe data SQL Server Jenis data sementara Data Factory
bigint Int64
biner Byte[]
bit Boolean
char String, Char[]
date DateTime
Tanggalwaktu DateTime
tanggalwaktu2 DateTime
Datetimeoffset DateTimeOffset
Decimal Decimal
FILESTREAM attribute (varbinary(max)) Byte[]
Float Laju
gambar Byte[]
int Int32
money Decimal
nchar String, Char[]
ntext String, Char[]
numeric Decimal
nvarchar String, Char[]
real Tunggal
rowversion Byte[]
smalldatetime DateTime
smallint Int16
smallmoney Decimal
aql_variant Objek
text String, Char[]
waktu TimeSpan
rentang waktu Byte[]
tinyint Int16
uniqueidentifier Guid
varbinary Byte[]
varchar String, Char[]
xml String

Catatan

Untuk tipe data yang di petakan ke jenis sementara Desimal, aktivitas Salin saat ini mendukung presisi hingga 28. Jika Anda memiliki data yang memerlukan presisi lebih besar dari 28, pertimbangkan untuk mengonversi ke string dalam kueri SQL.

Saat menyalin data dari SQL Server menggunakan Azure Data Factory, tipe data bit dipetakan ke tipe data sementara Boolean. Jika Anda memiliki data yang perlu disimpan sebagai tipe data bit, gunakan kueri dengan T-SQL CAST atau CONVERT.

Properti aktivitas pencarian

Untuk mempelajari detail tentang properti, lihat Aktivitas pencarian.

Properti aktivitas GetMetadata

Untuk mempelajari rincian tentang properti ini, periksa Aktivitas GetMetadata

Menggunakan Always Encrypted

Saat menyalin data dari/ke Server SQL dengan Always Encrypted, ikuti langkah-langkah berikut:

  1. Simpan Kunci Master Kolom (CMK) di Azure Key Vault. Pelajari selengkapnya tentang cara mengonfigurasi Always Encrypted menggunakan Azure Key Vault

  2. Pastikan untuk memberikan akses ke brankas kunci tempat Column Master Key (CMK) disimpan. Lihat artikel ini untuk izin yang diperlukan.

  3. Buat layanan tertaut untuk terhubung ke database SQL Anda dan aktifkan fungsi 'Always Encrypted' menggunakan identitas terkelola atau perwakilan layanan.

Catatan

Always Encrypted Server SQL mendukung skenario di bawah:

  1. Baik penyimpanan data sumber atau sink menggunakan identitas terkelola atau perwakilan layanan sebagai jenis autentikasi penyedia kunci.
  2. Penyimpanan data sumber dan sink menggunakan identitas terkelola sebagai jenis autentikasi penyedia kunci.
  3. Penyimpanan data sumber dan sink menggunakan perwakilan layanan yang sama dengan jenis autentikasi penyedia kunci.

Catatan

Saat ini, SQL Server Always Encrypted hanya didukung untuk transformasi sumber dalam memetakan aliran data.

Pengambilan data perubahan asli

Azure Data Factory dapat mendukung kemampuan pengambilan data perubahan asli untuk SQL Server, Azure SQL DB, dan Azure SQL MI. Data yang diubah termasuk sisipan baris, pembaruan, dan penghapusan di penyimpanan SQL dapat secara otomatis dideteksi dan diekstrak oleh aliran data pemetaan ADF. Tanpa pengalaman kode dalam memetakan aliran data, pengguna dapat dengan mudah mencapai skenario replikasi data dari penyimpanan SQL dengan menambahkan database sebagai penyimpanan tujuan. Terlebih lagi, pengguna juga dapat menyusun logika transformasi data apa pun di antaranya untuk mencapai skenario ETL inkremental dari penyimpanan SQL.

Pastikan Anda menjaga agar nama alur dan aktivitas tidak berubah, sehingga titik pemeriksaan dapat direkam oleh ADF agar Anda mendapatkan data yang diubah dari yang terakhir dijalankan secara otomatis. Jika Anda mengubah nama alur atau nama aktivitas Anda, titik pemeriksaan akan diatur ulang, yang mengarahkan Anda untuk memulai dari awal atau mendapatkan perubahan mulai sekarang di eksekusi berikutnya. Jika Anda ingin mengubah nama alur atau nama aktivitas tetapi masih menyimpan titik pemeriksaan untuk mendapatkan data yang diubah dari eksekusi terakhir secara otomatis, gunakan kunci Titik Pemeriksaan Anda sendiri dalam aktivitas aliran data untuk mencapainya.

Saat Anda men-debug alur, fitur ini berfungsi sama. Ketahuilah bahwa pos pemeriksaan akan diatur ulang saat Anda menyegarkan browser selama eksekusi debug. Setelah Anda puas dengan hasil alur dari eksekusi debug, Anda dapat melanjutkan untuk menerbitkan dan memicu alur. Pada saat Anda pertama kali memicu alur yang diterbitkan, maka secara otomatis dimulai ulang dari awal atau mendapat perubahan mulai sekarang.

Di bagian pemantauan, Anda selalu memiliki kesempatan untuk menjalankan kembali alur. Saat Anda melakukannya, data yang diubah selalu diambil dari pos pemeriksaan sebelumnya dari eksekusi alur yang Anda pilih.

Contoh 1:

Ketika Anda langsung menautkan transformasi sumber yang direferensikan ke himpunan data yang diaktifkan SQL CDC dengan transformasi sink yang direferensikan ke database dalam aliran data pemetaan, perubahan yang terjadi pada sumber SQL akan secara otomatis diterapkan ke database target, sehingga Anda akan dengan mudah mendapatkan skenario replikasi data antar database. Anda dapat menggunakan metode pembaruan dalam transformasi sink untuk memilih apakah Anda ingin mengizinkan penyisipan, mengizinkan pembaruan, atau mengizinkan penghapusan pada database target. Contoh skrip dalam pemetaan aliran data adalah seperti di bawah ini.

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

Contoh 2:

Jika Anda ingin mengaktifkan skenario ETL alih-alih replikasi data antara database melalui SQL CDC, Anda dapat menggunakan ekspresi dalam memetakan aliran data termasuk isInsert(1), isUpdate(1) dan isDelete(1) untuk membedakan baris dengan jenis operasi yang berbeda. Berikut ini adalah salah satu contoh skrip untuk memetakan aliran data pada mendapatkan satu kolom dengan nilai: 1 untuk menunjukkan baris yang disisipkan, 2 untuk menunjukkan baris yang diperbarui dan 3 untuk menunjukkan baris yang dihapus untuk transformasi hilir untuk memproses data 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

Batasan yang diketahui:

Pemecahan masalah koneksi

  1. Konfigurasikan instans SQL Server Anda untuk menerima koneksi jarak jauh. Mulai SQL Server Management Studio, klik kanan server, dan pilih Properti. Pilih Koneksi dari daftar, dan pilih kotak centang Perbolehkan koneksi jarak jauh ke server.

    Fungsikan sambungan jarak jauh

    Untuk langkah-langkah yang detail, lihat Mengonfigurasi opsi konfigurasi server akses jarak jauh.

  2. Buka Pengelola Konfigurasi SQL Server. Perluas Konfigurasi Jaringan SQL Server untuk instans yang Anda inginkan, dan pilih Protokol untuk MSSQLSERVER. Protokol muncul di panel kanan. Aktifkan TCP/IP dengan mengklik kanan TCP/IP dan memilih Aktifkan.

    Mengaktifkan TCP/IP

    Untuk informasi selengkapnya dan cara alternatif mengaktifkan protokol TCP/IP, lihat Mengaktifkan atau menonaktifkan protokol jaringan server.

  3. Di jendela yang sama, klik ganda TCP/IP untuk meluncurkan jendela Properti TCP/IP.

  4. Beralih ke tab Alamat IP. Gulir ke bawah untuk melihat bagian IPAll. Tuliskan Port TCP. Defaultnya adalah 1433.

  5. Buat aturan untuk Windows Firewall pada mesin untuk mengizinkan lalu lintas masuk melalui port ini.

  6. Verifikasi koneksi: Untuk menyambungkan ke SQL Server dengan menggunakan nama yang sepenuhnya memenuhi syarat, gunakan SQL Server Management Studio dari mesin yang berbeda. Contohnya "<machine>.<domain>.corp.<company>.com,1433".

Meningkatkan versi SQL Server

Untuk meningkatkan versi SQL Server, di halaman Edit layanan tertaut, pilih Direkomendasikan di bawah Versi dan konfigurasikan layanan tertaut dengan merujuk ke Properti layanan tertaut untuk versi yang direkomendasikan.

Tabel di bawah ini memperlihatkan perbedaan antara SQL Server menggunakan versi yang direkomendasikan dan warisan.

Versi yang direkomendasikan Versi warisan
Dukung TLS 1.3 melalui encrypt sebagai strict. TLS 1.3 tidak didukung.

Konten terkait

Untuk daftar penyimpanan data yang didukung sebagai sumber dan sink oleh aktivitas salin, lihat Penyimpanan data yang didukung.