Filter Berparameter - Filter Baris Berparameter

  • Artikel

Berlaku untuk:SQL Server

Filter baris berparameter memungkinkan partisi data yang berbeda dikirim ke Pelanggan yang berbeda tanpa mengharuskan beberapa publikasi dibuat (filter berparameter disebut sebagai filter dinamis di versi SQL Server sebelumnya). Partisi adalah subset baris dalam tabel; bergantung pada pengaturan yang dipilih saat membuat filter baris berparameter, setiap baris dalam tabel yang diterbitkan hanya dapat dimiliki oleh satu partisi (yang menghasilkan partisi yang tidak tumpang tindih) atau ke dua partisi atau lebih (yang menghasilkan partisi yang tumpang tindih).

Partisi yang tidak tumpang tindih dapat dibagikan di antara langganan atau dapat dibatasi sehingga hanya satu langganan yang menerima partisi tertentu. Pengaturan yang mengontrol perilaku partisi dijelaskan dalam "Menggunakan Opsi Pemfilteran yang Sesuai" nanti dalam topik ini. Dengan menggunakan pengaturan ini, Anda dapat menyesuaikan pemfilteran berparameter sesuai dengan persyaratan aplikasi dan performa. Secara umum, partisi yang tumpang tindih memungkinkan fleksibilitas yang lebih besar, dan partisi yang tidak tumpang tindih yang direplikasi ke satu langganan memberikan performa yang lebih baik.

Filter berparameter digunakan pada satu tabel dan biasanya dikombinasikan dengan filter gabungan untuk memperluas pemfilteran ke tabel terkait. Untuk informasi selengkapnya, lihat Menggabungkan Filter.

Untuk menentukan atau mengubah filter baris berparameter, lihat Menentukan dan Mengubah Filter Baris Berparameter untuk Artikel Penggabungan.

Cara Kerja Filter Berparameter

Filter baris berparameter menggunakan klausa WHERE untuk memilih data yang sesuai untuk diterbitkan. Daripada menentukan nilai harfiah dalam klausul (seperti yang Anda lakukan dengan filter baris statis), Anda menentukan salah satu atau kedua fungsi sistem berikut: SUSER_SNAME() dan HOST_NAME(). Fungsi yang ditentukan pengguna juga dapat digunakan, tetapi harus menyertakan SUSER_SNAME() atau HOST_NAME() dalam isi fungsi, atau mengevaluasi salah satu fungsi sistem ini (seperti MyUDF(SUSER_SNAME()). Jika fungsi yang ditentukan pengguna menyertakan SUSER_SNAME() atau HOST_NAME() dalam isi fungsi, Anda tidak dapat meneruskan parameter ke fungsi .

Fungsi sistem SUSER_SNAME() dan HOST_NAME() tidak spesifik untuk menggabungkan replikasi, tetapi digunakan oleh replikasi penggabungan untuk pemfilteran berparameter:

  • SUSER_SNAME() mengembalikan informasi masuk untuk koneksi yang dibuat ke instans SQL Server. Saat digunakan dalam filter berparameter, filter mengembalikan login yang digunakan oleh Agen Penggabungan untuk menyambungkan ke Publisher (Anda menentukan login saat Membuat langganan).

  • HOST_NAME() mengembalikan nama komputer yang tersambung ke instans SQL Server. Saat digunakan dalam filter berparameter, secara default ia mengembalikan nama komputer tempat Agen Penggabungan berjalan. Untuk langganan penarikan, itu adalah nama Pelanggan; untuk langganan push, itu adalah nama Distributor.

    Dimungkinkan juga untuk mengambil alih fungsi ini dengan nilai selain nama Pelanggan atau Distributor. Biasanya aplikasi mengambil alih fungsi ini dengan nilai yang lebih bermakna, seperti nama tenaga penjual atau ID tenaga penjual. Untuk informasi selengkapnya, lihat bagian "Mengganti nilai HOST_NAME() dalam topik ini.

Nilai yang dikembalikan oleh fungsi sistem dibandingkan dengan kolom yang Anda tentukan dalam tabel yang Anda filter, dan data yang sesuai diunduh ke Pelanggan. Perbandingan ini dibuat ketika langganan diinisialisasi (jadi hanya data yang sesuai yang terkandung dalam rekam jepret awal) dan setiap kali langganan disinkronkan. Secara default, jika perubahan di Publisher menghasilkan baris yang dipindahkan dari partisi, baris dihapus di Pelanggan (perilaku ini dikontrol menggunakan @allow_partition_realignment parameter sp_addmergepublication (Transact-SQL)).

Catatan

Ketika perbandingan dibuat untuk filter berparameter, kolatasi database selalu digunakan. Misalnya, jika kolase database tidak peka huruf besar/kecil, tetapi kolase tabel atau kolom peka huruf besar/kecil, perbandingannya akan tidak peka huruf besar/kecil.

Pemfilteran dengan SUSER_SNAME()

Pertimbangkan Tabel Karyawan dalam database sampel Adventure Works. Tabel ini mencakup kolom LoginID, yang berisi login untuk setiap karyawan dalam formulir 'domain\login'. Untuk memfilter tabel ini sehingga karyawan hanya menerima data yang terkait dengannya, tentukan klausa filter:

LoginID = SUSER_SNAME()

Misalnya, nilai untuk salah satu karyawan adalah 'adventure-works\john5'. Saat Agen Penggabungan tersambung ke Penerbit, Agen tersebut menggunakan login yang Anda tentukan saat membuat langganan (dalam hal ini 'adventure-works\john5'). Agen Penggabungan kemudian membandingkan nilai yang dikembalikan oleh SUSER_SNAME() dengan nilai dalam tabel dan hanya mengunduh baris yang berisi nilai 'adventure-works\john5' di kolom LoginID .

Pemfilteran dengan HOST_NAME()

Pertimbangkan tabel HumanResources.Employee. Misalkan tabel ini berisi kolom seperti ComputerName dengan nama komputer setiap karyawan dalam formulir 'name_computertype'. Untuk memfilter tabel ini sehingga karyawan hanya menerima data yang terkait dengannya, tentukan klausa filter:

ComputerName = HOST_NAME()

Misalnya, nilai untuk salah satu karyawan bisa menjadi 'john5_laptop'. Saat Agen Penggabungan tersambung ke Publisher, agen tersebut membandingkan nilai yang dikembalikan oleh HOST_NAME() dengan nilai dalam tabel dan hanya mengunduh baris yang berisi nilai 'john5_laptop' di kolom ComputerName .

Dimungkinkan juga untuk menggabungkan fungsi dalam filter. Misalnya, jika Anda ingin memastikan bahwa karyawan menerima data hanya jika mereka menggunakan login mereka di komputer mereka, klausa filter dapat berupa:

LoginID = SUSER_SNAME() AND ComputerName = HOST_NAME()

Kecuali Anda mengambil alih nilai HOST_NAME(), pemfilteran dengan HOST_NAME() biasanya hanya digunakan dengan langganan penarikan. Nilai yang dikembalikan oleh fungsi adalah nama komputer tempat Agen Penggabungan berjalan. Untuk langganan penarikan, nilainya berbeda untuk setiap langganan, tetapi untuk langganan push, nilainya sama (semua Agen Penggabungan berjalan di Distributor untuk langganan push).

Penting

Nilai untuk fungsi HOST_NAME() dapat ditimpa; oleh karena itu, tidak dimungkinkan untuk menggunakan filter yang mencakup HOST_NAME() untuk mengontrol akses ke partisi data. Untuk mengontrol akses ke partisi data, gunakan SUSER_SNAME(), SUSER_SNAME() dalam kombinasi dengan HOST_NAME(), atau gunakan filter baris statis.

Mengambil alih Nilai HOST_NAME()

Seperti disebutkan sebelumnya, HOST_NAME() secara default mengembalikan nama komputer yang tersambung ke instans SQL Server. Saat menggunakan filter berparameter, biasanya mengambil alih nilai ini dengan menyediakan nilai saat Anda membuat langganan. Fungsi HOST_NAME() kemudian mengembalikan nilai yang Anda tentukan daripada nama komputer.

Catatan

Jika Anda mengambil alih HOST_NAME(), semua panggilan ke fungsi HOST_NAME() akan mengembalikan nilai yang Anda tentukan. Pastikan aplikasi lain tidak bergantung pada HOST_NAME() yang mengembalikan nama komputer.

Pertimbangkan tabel HumanResources.Employee. Tabel ini menyertakan kolom EmployeeID. Untuk memfilter tabel ini sehingga setiap karyawan hanya menerima data yang terkait dengannya, tentukan klausa filter:

EmployeeID = CONVERT(int,HOST_NAME())

Misalnya, karyawan Pamela Ansman-Wolfe telah diberi ID karyawan sebesar 280. Tentukan nilai ID karyawan (280 dalam contoh kami) untuk nilai HOST_NAME() saat membuat langganan untuk karyawan ini. Saat Agen Penggabungan tersambung ke Publisher, itu membandingkan nilai yang dikembalikan oleh HOST_NAME() dengan nilai dalam tabel dan hanya mengunduh baris yang berisi nilai 280 di kolom EmployeeID .

Penting

Fungsi HOST_NAME() mengembalikan nilai nchar, jadi Anda harus menggunakan CONVERT jika kolom dalam klausa filter berjenis data numerik, seperti pada contoh di atas. Untuk alasan performa, sebaiknya Anda tidak menerapkan fungsi ke nama kolom dalam klausa filter baris berparameter, seperti CONVERT(nchar,EmployeeID) = HOST_NAME(). Sebagai gantinya, sebaiknya gunakan pendekatan yang ditunjukkan dalam contoh: EmployeeID = CONVERT(int,HOST_NAME()). Klausa ini dapat digunakan untuk parameter sp_addmergearticle, tetapi biasanya tidak dapat digunakan dalam Wizard Publikasi Baru (wizard menjalankan klausa filter untuk memvalidasinya, yang gagal karena nama komputer tidak dapat dikonversi ke int).@subset_filterclause Jika Anda menggunakan Panduan Publikasi Baru, sebaiknya tentukan CONVERT(nchar,EmployeeID) = HOST_NAME() dalam wizard lalu gunakan sp_changemergearticle untuk mengubah klausa sebelum EmployeeID = CONVERT(int,HOST_NAME()) membuat rekam jepret untuk publikasi.

Untuk mengambil alih nilai HOST_NAME()

Gunakan salah satu metode berikut untuk mengambil alih nilai HOST_NAME():

  • SQL Server Management Studio: tentukan nilai di halaman nilai HOST_NAME() wizard Langganan Baru. Untuk informasi selengkapnya tentang membuat langganan, lihat Berlangganan Publikasi.

  • Pemrograman Transact-SQL Replikasi: tentukan nilai untuk @hostname parameter sp_addmergesubscription (Transact-SQL) (untuk langganan push) atau sp_addmergepullsubscription_agent (Transact-SQL) (untuk langganan pull).

  • Agen Penggabungan: tentukan nilai untuk parameter -Hostname di baris perintah atau melalui profil agen. Untuk informasi selengkapnya tentang Agen Penggabungan, lihat Agen Penggabungan Replikasi. Untuk informasi selengkapnya tentang profil agen, lihat Profil Agen Replikasi.

Menginisialisasi Langganan ke Publikasi dengan Filter Berparameter

Saat filter baris berparameter digunakan dalam publikasi gabungan, replikasi menginisialisasi setiap langganan dengan rekam jepret dua bagian. Untuk informasi selengkapnya, lihat Rekam Jepret untuk Gabungkan Publikasi dengan Filter Berparameter.

Menggunakan Opsi Pemfilteran yang Sesuai

Ada dua area utama di mana Anda memiliki kontrol saat menggunakan filter berparameter:

  • Cara filter diproses dengan menggabungkan replikasi, yang dikontrol oleh salah satu dari dua pengaturan publikasi: gunakan grup partisi dan pertahankan perubahan partisi.

  • Bagaimana data dibagikan di antara Pelanggan, yang harus tercermin oleh opsi partisi pengaturan artikel.

Untuk mengatur opsi pemfilteran, lihat Mengoptimalkan Filter Baris Berparameter.

Mengatur 'gunakan grup partisi' dan 'pertahankan perubahan partisi'

Opsi gunakan grup partisi dan pertahankan perubahan partisi meningkatkan performa sinkronisasi untuk publikasi dengan artikel yang difilter dengan menyimpan metadata tambahan dalam database publikasi. Opsi gunakan grup partisi memberikan peningkatan performa yang lebih besar melalui penggunaan fitur partisi yang telah dikomputasi sebelumnya. Opsi ini diatur ke true secara default jika artikel dalam publikasi Anda mematuhi serangkaian persyaratan. Untuk informasi selengkapnya tentang persyaratan ini, lihat Mengoptimalkan Performa Filter Berparameter dengan Partisi yang Telah Dikomputasi. Jika artikel Anda tidak memenuhi persyaratan untuk menggunakan partisi yang telah dikomputasi sebelumnya, opsi pertahankan perubahan partisi ke diatur ke true.

Mengatur 'opsi partisi'

Anda menentukan nilai untuk properti opsi partisi saat membuat artikel, sesuai dengan cara data dalam tabel yang difilter akan dibagikan oleh Pelanggan. Properti dapat diatur ke salah satu dari empat nilai menggunakan sp_addmergearticle, sp_changemergearticle, dan kotak dialog Properti Artikel. Properti dapat diatur ke salah satu dari dua nilai menggunakan kotak dialog Tambahkan Filter atau Edit Filter , yang tersedia dari Panduan Publikasi Baru dan kotak dialog Properti Publikasi. Tabel berikut ini meringkas nilai yang tersedia:

Deskripsi Nilai di Tambahkan Filter dan Edit Filter Nilai dalam Properti Artikel Nilai dalam prosedur tersimpan
Data dalam partisi tumpang tindih, dan Pelanggan dapat memperbarui kolom yang dirujuk dalam filter berparameter. Baris dari tabel ini akan masuk ke beberapa langganan Tumpang tindih 0
Data dalam partisi tumpang tindih, dan Pelanggan tidak dapat memperbarui kolom yang dirujuk dalam filter berparameter. T/A* Tumpang tindih, melarang perubahan data di luar partisi 1
Data dalam partisi tidak tumpang tindih, dan data dibagikan antar langganan. Pelanggan tidak dapat memperbarui kolom yang dirujuk dalam filter berparameter. T/A* Tidak tumpang tindih, dibagikan antar langganan 2
Data dalam partisi tidak tumpang tindih, dan ada satu langganan per partisi. Pelanggan tidak dapat memperbarui kolom yang dirujuk dalam filter berparameter.** Baris dari tabel ini hanya akan masuk ke satu langganan Langganan tunggal yang tidak tumpang tindih 3

*Jika opsi pemfilteran yang mendasar diatur ke 0, atau 1, atau 2, kotak dialog Tambahkan Filter dan Edit Filter akan menampilkan Baris dari tabel ini akan masuk ke beberapa langganan.

**Jika Anda menentukan opsi ini, hanya ada satu langganan untuk setiap partisi data dalam artikel tersebut. Jika langganan kedua dibuat di mana kriteria pemfilteran langganan baru diselesaikan ke partisi yang sama dengan langganan yang ada, langganan yang ada akan dihilangkan.

Penting

Nilai opsi partisi harus diatur sesuai dengan cara data dibagikan oleh Pelanggan. Jika, misalnya, Anda menentukan bahwa partisi tidak tumpang tindih dengan satu langganan per partisi, tetapi data kemudian diperbarui di Pelanggan lain, Agen Penggabungan dapat gagal selama sinkronisasi dan non-konvergensi dapat terjadi.

Memilih Opsi Partisi yang Sesuai

Partisi yang tidak tumpang tindih bekerja bersama dengan partisi yang telah dikomputasi untuk meningkatkan performa dalam situasi di mana beberapa batasan fungsional dapat diterima. Partisi yang telah dikomputasi mempercepat unduhan ke Pelanggan, tetapi unggahan lambat. Partisi yang tidak tumpang tindih meminimalkan biaya unggahan yang terkait dengan partisi yang telah dikomputasi sebelumnya. Manfaat performa partisi yang tidak tumpang tindih lebih terlihat ketika filter berparameter dan filter gabungan yang digunakan lebih kompleks.

Pertimbangkan skenario berikut saat memutuskan opsi partisi mana yang akan digunakan dalam publikasi.

  • Adventure Works memiliki kekuatan penjualan seluler dengan setiap orang penjualan yang bertanggung jawab atas pelanggan dalam kode pos tertentu. Aplikasi ini mengharuskan kode pos diperbarui jika pelanggan berpindah dari satu wilayah penjualan ke wilayah penjualan lainnya, sehingga pelanggan ditetapkan ke orang penjualan yang berbeda. Filter berparameter didasarkan pada kode pos pelanggan, dan pembaruan menghapus kode pos dari satu partisi orang penjualan dan memasukkannya ke partisi orang penjualan lain. Ini memerlukan partisi yang tumpang tindih dengan kemampuan untuk memperbarui kolom yang dirujuk dalam filter berparameter. Opsi ini memaksimalkan fleksibilitas tetapi mungkin tidak berkinerja serta partisi yang tidak tumpang tindih.

  • Lembaga ketenagakerjaan memiliki data yang disuplai ke kantor regional di setiap wilayah negara bagian. Data tidak tumpang tindih; setiap baris dalam tabel di kantor pusat agensi hanya disertakan dalam satu partisi, tetapi partisi tersebut dikirim ke beberapa kantor di wilayah yang sama. Opsi partisi yang tidak tumpang tindih dengan partisi yang dibagikan antar langganan sesuai, memberikan peningkatan performa partisi yang tumpang tindih sambil memenuhi persyaratan aplikasi.

  • Jika Anda memiliki partisi yang tidak tumpang tindih dan hanya satu langganan yang menerima dan memperbarui data dalam partisi, manfaat performa lebih lanjut dapat diwujudkan. Skenario ini umum untuk sistem titik penjualan, dan aplikasi paksa bidang di mana data terutama dikumpulkan di Pelanggan dan diunggah ke Penerbit. Pertimbangkan tabel Paket dalam aplikasi pengiriman: karena setiap paket dimuat ke truk, status paket diubah dalam tabel Paket, dan perubahan direplikasi kembali ke kantor pusat. Driver tidak akan memperbarui status paket yang sama pada dua truk yang berbeda, sehingga tabel Paket adalah kandidat yang baik untuk partisi yang tidak tumpang tindih dengan satu langganan per partisi.

Pertimbangan untuk Partisi yang Tidak Tumpang Tindih

Ingatlah pertimbangan berikut saat menggunakan partisi yang tidak tumpang tindih.

Pertimbangan Umum

  • Publikasi harus menggunakan partisi yang telah dikomputasi sebelumnya.

  • Baris harus milik hanya satu partisi.

  • Artikel tidak dapat menjadi bagian dari catatan logis.

  • Mitra sinkronisasi alternatif tidak didukung (fitur ini tidak digunakan lagi).

  • Pelanggan tidak dapat memperbarui kolom yang dirujuk dalam filter berparameter.

  • Jika sisipan di Pelanggan bukan milik partisi, itu tidak dihapus. Namun, itu tidak akan direplikasi ke Pelanggan lain.

  • Dalam beberapa keadaan dengan partisi yang tumpang tindih, rentang identitas disesuaikan ketika Agen Penggabungan menyisipkan data. Dengan partisi yang tidak tumpang tindih, rentang hanya dapat disesuaikan selama penyisipan oleh pengguna yang memiliki izin untuk menyesuaikan rentang identitas dalam database langganan. Pengguna harus memiliki tabel, atau menjadi anggota peran server tetap sysadmin , peran database tetap db_owner , atau peran database tetap db_ddladmin .

Pertimbangan Tambahan untuk Partisi yang Tidak Tumpang Tindih dengan Satu Langganan per Partisi
Pertimbangan Tambahan untuk Filter Gabungan

  • Dalam hierarki filter gabungan, artikel dengan partisi yang tumpang tindih tidak dapat muncul di atas artikel dengan partisi yang tidak tumpang tindih. Dengan kata lain, artikel induk harus menggunakan partisi yang tidak tumpang tindih jika artikel anak melakukannya. Untuk informasi tentang filter gabungan, lihat Menggabungkan Filter.

  • Filter gabungan di mana partisi yang tidak tumpang tindih adalah anak harus memiliki properti kunci unik gabungan yang diatur ke 1. Untuk informasi selengkapnya, lihat Menggabungkan Filter.

  • Artikel ini seharusnya hanya memiliki satu filter berparameter atau filter gabungan. Memiliki filter berparameter dan menjadi induk dalam filter gabungan diizinkan. Memiliki filter berparameter dan menjadi anak dalam filter gabungan tidak diperbolehkan. Memiliki lebih dari satu filter gabungan juga tidak diperbolehkan.

  • Jika dua tabel di Publisher memiliki hubungan filter gabungan dan tabel anak memiliki baris yang tidak memiliki baris terkait dalam tabel induk, sisipan baris induk yang hilang tidak akan mengakibatkan baris terkait diunduh ke Pelanggan (baris akan diunduh dengan partisi yang tumpang tindih). Misalnya, jika tabel SalesOrderDetail memiliki baris tanpa baris terkait dalam tabel SalesOrderHeader , dan Anda menyisipkan baris yang hilang di SalesOrderHeader, baris diunduh ke Pelanggan, tetapi baris yang sesuai di SalesOrderDetail tidak.

Lihat Juga

Praktik Terbaik untuk Filter Baris Berbasis Waktu
Filter Data yang Diterbitkan
Filter Data yang Diterbitkan untuk Replikasi Penggabungan