Panduan Sinkronisasi Data di Microsoft SQL Server

Berbagi informasi terkait di antara sistem yang terisolasi menjadi semakin penting bagi organisasi, karena memungkinkan mereka untuk meningkatkan kualitas dan ketersediaan data. Ada banyak situasi di mana berguna untuk memiliki kumpulan data yang tersedia dan konsisten di lebih dari satu server direktori. Itulah mengapa mengetahui metode umum untuk melakukan sinkronisasi data SQL Server adalah penting.

Ketersediaan dan konsistensi data dapat dicapai dengan proses replikasi data dan sinkronisasi data. Replikasi data adalah proses membuat satu atau lebih salinan database yang berlebihan untuk tujuan toleransi kesalahan atau peningkatan aksesibilitas. Sinkronisasi data adalah proses membangun konsistensi data antara dua atau lebih database, dan pembaruan berkelanjutan berikutnya untuk menjaga konsistensi tersebut.

Di banyak organisasi, melakukan sinkronisasi data di berbagai sistem sangat diinginkan dan menantang. Kami dapat menemukan banyak kasus penggunaan di mana kami perlu melakukan sinkronisasi data:

• Migrasi basis data
• Sinkronisasi reguler antar sistem informasi
• Mengimpor data dari satu sistem informasi ke sistem informasi lainnya
• Memindahkan kumpulan data di antara berbagai tahapan atau lingkungan
• Mengimpor data dari sumber non-database

Tidak ada cara unik atau metode yang disepakati bersama untuk sinkronisasi data. Tugas ini berbeda dari kasus ke kasus, dan bahkan sinkronisasi data yang seharusnya sederhana pada pandangan pertama bisa menjadi rumit, karena kompleksitas struktur data. Dalam skenario nyata, sinkronisasi data terdiri dari banyak tugas kompleks, yang dapat memakan waktu lama untuk dilakukan. Ketika persyaratan baru muncul, spesialis database biasanya harus mengimplementasikan kembali seluruh proses sinkronisasi. Karena tidak ada cara standar untuk melakukan ini, selain replikasi, implementasi sinkronisasi data jarang optimal. Hal ini menyebabkan pemeliharaan yang sulit dan biaya yang lebih tinggi. Implementasi dan pemeliharaan sinkronisasi data adalah proses yang memakan waktu, dapat menjadi pekerjaan penuh waktu dengan sendirinya.

Kami dapat menerapkan arsitektur untuk tugas sinkronisasi data secara manual, mungkin menggunakan Microsoft Sync Framework, atau kami dapat memanfaatkan solusi yang telah dibuat dalam alat untuk mengelola Microsoft SQL Server. Kami akan mencoba menjelaskan metode dan alat paling umum yang dapat digunakan untuk menyelesaikan sinkronisasi data pada database Microsoft SQL Server dan mencoba memberikan beberapa rekomendasi.

Berdasarkan struktur sumber dan tujuan (misalnya, database, tabel) kita dapat membedakan use case ketika strukturnya serupa atau berbeda.

Sumber dan Tujuan Memiliki Struktur yang Sangat Mirip

Ini sangat sering terjadi ketika kami menggunakan data dalam berbagai tahap siklus hidup pengembangan perangkat lunak. Misalnya, struktur data dalam lingkungan pengujian dan produksi sangat mirip. Persyaratan umum adalah membandingkan data antara database pengujian dan produksi dan mengimpor data dari produksi ke database pengujian.

Sumber dan Tujuan Memiliki Struktur Yang Berbeda

Jika strukturnya berbeda, sinkronisasi lebih rumit. Ini juga merupakan tugas yang lebih sering berulang. Kasus umum adalah mengimpor dari satu database ke database lain. Kasus yang paling umum adalah ketika suatu perangkat lunak perlu mengimpor data dari perangkat lunak lain yang dikelola oleh perusahaan lain. Biasanya, impor perlu dijalankan secara otomatis berdasarkan jadwal.

Metode yang digunakan tergantung pada preferensi pribadi dan kompleksitas masalah yang perlu Anda pecahkan.

Terlepas dari seberapa mirip strukturnya, kita dapat memilih empat cara berbeda untuk menyelesaikan sinkronisasi data:

• Sinkronisasi menggunakan skrip SQL yang dibuat secara manual
• Sinkronisasi menggunakan metode perbandingan data (hanya dapat digunakan jika sumber dan target memiliki struktur yang mirip)
• Sinkronisasi menggunakan skrip SQL yang dibuat secara otomatis - membutuhkan produk komersial

Sumber dan Tujuan Memiliki Struktur yang Sama atau Sangat Mirip

Menggunakan Skrip SQL yang Dibuat Secara Manual

Solusi paling mudah dan membosankan adalah menulis skrip SQL secara manual untuk sinkronisasi.

Keuntungan

• Dapat dilakukan dengan alat gratis dan open source (FOSS).
• Jika tabel memiliki indeks, itu sangat cepat.
• Skrip SQL dapat disimpan ke dalam prosedur tersimpan, atau dijalankan secara berkala sebagai tugas untuk SQL Server.
• Dapat digunakan sebagai impor otomatis, bahkan pada data yang terus diubah.

Kekurangan

• Membuat skrip SQL seperti itu cukup membosankan, karena biasanya diperlukan tiga skrip untuk setiap tabel: INSERT , UPDATE , dan DELETE .
• Anda hanya dapat menyinkronkan data yang tersedia melalui kueri SQL, sehingga Anda tidak dapat mengimpor dari sumber seperti file CSV dan XML.
• Sulit untuk mempertahankannya—ketika struktur database diubah, perlu untuk memodifikasi dua atau tiga skrip ( INSERT , UPDATE , dan terkadang juga DELETE ).

Contoh

Kami akan melakukan sinkronisasi antara tabel Source , dengan kolom ID dan Value , dan tabel Target , dengan kolom yang sama.

Jika tabel memiliki kunci utama yang sama, dan tabel target tidak memiliki kunci utama (identitas) peningkatan otomatis, Anda dapat menjalankan skrip sinkronisasi berikut.

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

Menggunakan Metode Perbandingan Data

Dalam metode ini, kita dapat menggunakan alat untuk membandingkan antara data sumber dan target. Proses perbandingan menghasilkan skrip SQL yang menerapkan perbedaan dari database sumber ke database target.

Ada sejumlah program untuk perbandingan dan sinkronisasi data. Program-program ini sebagian besar menggunakan pendekatan yang sama. Pengguna memilih sumber dan database target, tetapi alternatif lain dapat berupa cadangan DB, folder dengan skrip SQL, atau bahkan koneksi ke sistem kontrol sumber.

Di bawah ini adalah alat paling populer yang menggunakan pendekatan perbandingan data:

• Perbandingan Data dbForge untuk SQL Server
• Perbandingan Data SQL RedGate
• Perbedaan Data SQL Puncak

Pada langkah pertama, data dibaca, atau hanya checksum data yang lebih besar dari sumber dan dari target dibaca. Kemudian dilakukan proses perbandingan.

Alat ini juga menawarkan pengaturan tambahan untuk sinkronisasi.

Kami perlu menyiapkan opsi konfigurasi berikut yang diperlukan untuk sinkronisasi data:

Kunci Sinkronisasi

Secara default, kunci utama atau batasan UNIQUE digunakan. Jika tidak ada kunci utama, Anda dapat memilih kombinasi kolom. Kunci Sync digunakan untuk memasangkan baris sumber dengan baris target.

Pasangan Meja

Secara default, tabel dipasangkan berdasarkan nama. Anda dapat mengubah ini, dan memasangkannya sesuai dengan kebutuhan Anda sendiri. Dalam perangkat lunak dbForge Data Compare, Anda dapat memilih kueri SQL sebagai sumber atau tujuan.

Proses Sinkronisasi

Setelah mengonfirmasi, alat membandingkan data sumber dan target. Seluruh proses terdiri dari mengunduh semua sumber dan data target dan membandingkannya berdasarkan kriteria yang ditentukan. Secara default, nilai dari tabel dan kolom dengan nama yang sama dibandingkan. Semua alat mendukung pemetaan kolom dan nama tabel. Juga, ada kemungkinan untuk mengecualikan kolom IDENTITY (peningkatan otomatis) atau melakukan beberapa transformasi sebelum membandingkan nilai (tipe float bulat, mengabaikan huruf besar-kecil, memperlakukan NULL sebagai string kosong, dll.) Pengunduhan data dioptimalkan. Jika volume data besar, hanya checksum yang diunduh. Pengoptimalan ini sangat membantu dalam banyak kasus, tetapi persyaratan waktu untuk melakukan operasi meningkat seiring dengan volume data.

Pada langkah selanjutnya, ada skrip SQL dengan migrasi yang dihasilkan. Script ini dapat disimpan atau dijalankan secara langsung. Agar aman, kita bahkan bisa membuat backup database sebelum menjalankan script ini. Alat ApexSQL Data Diff dapat membuat program yang dapat dieksekusi yang menjalankan skrip pada database yang dipilih. Script ini berisi data yang perlu diubah, bukan logika bagaimana mengubahnya. Ini berarti bahwa skrip tidak dapat dijalankan secara otomatis untuk memberikan impor berulang. Itulah kelemahan terbesar dari pendekatan ini.

Keuntungan

• Pengetahuan lanjutan tentang SQL tidak diperlukan, dan dapat dilakukan melalui GUI.
• Anda memiliki kemampuan untuk memeriksa perbedaan antara database secara visual sebelum sinkronisasi.

Kekurangan

• Ini adalah fitur lanjutan dari produk komersial.
• Performa menurun saat mentransfer volume data yang sangat besar.
• Skrip SQL yang dihasilkan hanya berisi perbedaan, dan karenanya tidak dapat digunakan kembali untuk menyinkronkan data di masa mendatang secara otomatis.

Di bawah ini Anda dapat melihat UI khas alat ini.

Sinkronkan dengan SQL yang Dihasilkan Secara Otomatis

Metode ini sangat mirip dengan metode perbandingan data. Satu-satunya perbedaan dibandingkan dengan metode sebelumnya adalah tidak ada perbandingan data, dan skrip SQL yang dihasilkan tidak mengandung perbedaan data, tetapi logika sinkronisasi. Skrip yang dihasilkan dapat dengan mudah disimpan ke dalam prosedur tersimpan, dan dapat dijalankan secara berkala (misalnya, setiap malam). Metode ini berguna untuk impor otomatis antar database. Performa metode ini jauh lebih baik daripada metode pembanding data.

Sinkronisasi dengan SQL yang dibuat secara otomatis hanya disediakan oleh SQL Database Studio.

SQL Database Studio menyediakan antarmuka yang mirip dengan metode perbandingan data. Kita perlu memilih sumber dan target (database atau tabel). Kemudian kita perlu mengatur opsi (kunci sinkronisasi, pemasangan dan pemetaan). Ada fitur pembuat kueri grafis untuk menyiapkan semua parameter.

Keuntungan

• Pengetahuan lanjutan tentang SQL tidak diperlukan.
• Anda dapat mengatur semuanya dalam GUI dengan cukup cepat.
• Skrip SQL yang dihasilkan dapat disimpan ke dalam prosedur tersimpan.
• Dapat digunakan sebagai impor otomatis - sebagai pekerjaan untuk SQL Server.

Kekurangan

• Ini adalah fitur lanjutan dari produk komersial.
• Perbedaan tidak dapat diperiksa secara manual sebelum sinkronisasi, karena seluruh proses dijalankan dalam satu langkah.

Tolok Ukur Kinerja

Kasus cobaan

Dua database (A dan B), masing-masing berisi satu tabel dengan 2.000.000 baris. Tabel berada di dua database yang berbeda pada SQL Server yang sama. Tes ini mencakup dua kasus ekstrim: 1) Tabel sumber berisi semua 2.000.000 baris dan tabel target kosong. Sinkronisasi perlu menyediakan banyak INSERTS . 2) Tabel sumber dan target berisi 2.000.000 baris. Perbedaannya hanya pada satu baris. Sinkronisasi hanya perlu menyediakan satu UPDATE .

Perbandingan Data RedGate membutuhkan 3 langkah:

• Membandingkan
• Buat skrip
• Jalankan skrip pada basis data target

Perbedaan Data ApexSQL membutuhkan 2 langkah:

• Membandingkan
• Hasilkan skrip dan jalankan skrip dalam satu langkah

SQL Database Studio melakukan seluruh sinkronisasi dalam satu langkah. Di bawah ini adalah waktu sinkronisasi, dalam detik. Di kolom berlabel "langkah individual" adalah durasi langkah sinkronisasi yang tercantum di atas.

Lebih rendah lebih baik.

Tes yang sama, tetapi database berada di server SQL yang berbeda, yang tidak terhubung melalui server yang ditautkan.

Lebih rendah lebih baik.

Ringkasan

Dari hasil terlihat bahwa RedGate dan Apex tidak peduli jika database berada pada SQL server yang sama, karena algoritma sinkronisasi tidak bergantung pada SQL Server. SQL Database Studio menggunakan fungsi asli SQL Server; oleh karena itu, hasilnya lebih baik ketika database berada di server yang sama.

Sumber dan tujuan memiliki struktur yang berbeda

Ada juga situasi ketika satu tabel lebar harus disinkronkan ke banyak tabel terkait kecil.

Contoh ini terdiri dari satu tabel lebar SourceData yang perlu disinkronkan ke dalam tabel kecil Continent , Country , dan City . Skema diberikan di bawah ini.

Data di SourceData bisa seperti yang ada pada gambar di bawah ini.

Menggunakan skrip SQL yang dibuat secara manual

Script Sinkronisasi Tabel Benua

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

Script Sinkronisasi Tabel Kota

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

Skrip ini lebih rumit. Itu karena catatan dalam tabel Country dan Continent harus ditemukan. Skrip ini menyisipkan catatan yang hilang ke City dan mengisi ContryId dengan benar.

Skrip UPDATE dan DELETE juga dapat ditulis dengan cara yang sama jika diperlukan.

Keuntungan

• Anda tidak memerlukan produk komersial apa pun.
• Skrip SQL dapat disimpan ke dalam prosedur tersimpan atau dijalankan secara berkala sebagai tugas untuk SQL Server.

Kekurangan

• Membuat skrip SQL seperti itu sulit dan rumit (untuk setiap tabel, tiga skrip— INSERT , UPDATE , dan DELETE —biasanya diperlukan).
• Sangat sulit untuk mempertahankannya.

Menggunakan alat eksternal

Sinkronisasi semacam ini (tabel lebar menjadi banyak tabel terkait) tidak dapat dilakukan dengan metode perbandingan data, karena difokuskan pada kasus penggunaan yang berbeda. Karena metode perbandingan data menghasilkan skrip SQL dengan data yang akan disisipkan, metode ini tidak memiliki kemampuan langsung untuk mencari referensi di tabel terkait. Oleh karena itu, aplikasi yang menggunakan metode ini tidak dapat digunakan (dbForge Data Compare for SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).

Namun, SQL Database Studio dapat membantu Anda membuat skrip sinkronisasi secara otomatis. Pada gambar di bawah ini, ada elemen yang disebut Editor untuk Sinkronisasi Data di SQL Database Studio.

Editor terlihat seperti pembuat kueri yang terkenal dan bekerja dengan cara yang sangat mirip. Setiap tabel perlu memiliki kunci sinkronisasi yang ditentukan, tetapi ada juga hubungan yang ditentukan antar tabel. Pada gambar di atas ada pemetaan untuk sinkronisasi juga. Pada daftar kolom (bagian bawah gambar) terdapat kolom tabel City (untuk tabel lain serupa).

kolom

• Id — Kolom ini tidak dipetakan karena merupakan kunci utama (dibuat secara otomatis).
• CountryId — Kolom ini didefinisikan sebagai referensi untuk tabel.
• Nama — Kolom ini diisi dari kolom Kota di tabel sumber (tabel lebar).

Kolom CountryId dan Name dipilih sebagai kunci sinkronisasi. Kunci sinkronisasi adalah kumpulan kolom yang secara unik mengidentifikasi baris dalam tabel sumber dan target. Anda tidak dapat menggunakan Id kunci utama sebagai kunci sinkronisasi karena ini tidak ada dalam tabel sumber.

Setelah sinkronisasi, inilah tampilan tabel:

Pada contoh di atas, ada satu tabel lebar sebagai sumber. Ada juga skenario umum ketika data sumber disimpan di beberapa tabel terkait. Hubungan di SQL Database Studio tidak ditentukan menggunakan kunci asing, tetapi nama kolom. Dengan cara ini juga dimungkinkan untuk mengimpor dari file CSV atau Excel (file dimuat ke tabel sementara, dan sinkronisasi dijalankan dari tabel itu). Ini praktik yang baik untuk memiliki nama kolom yang unik. Jika ini tidak memungkinkan, Anda dapat menentukan alias untuk kolom tersebut.

Keuntungan

• Mudah dan cepat untuk dibuat
• Mudah dirawat
• Dapat disimpan ke dalam prosedur tersimpan (prosedur tersimpan disimpan dengan data yang diperlukan untuk membuka sinkronisasi di editor nanti)

Kekurangan

• Solusi komersial

Membandingkan Solusi

Sinkronisasi data terdiri dari urutan perintah INSERT , UPDATE , atau DELETE . Ada beberapa cara untuk membuat urutan perintah ini. Pada artikel ini, kami melihat tiga opsi untuk membuat skrip SQL sinkronisasi. Opsi pertama adalah membuat semuanya secara manual. Itu layak (tetapi membutuhkan terlalu banyak waktu), itu membutuhkan pemahaman SQL yang kompleks, dan sulit untuk dibuat dan dipelihara. Opsi kedua adalah menggunakan alat komersial. Kami melihat alat-alat berikut:

• Perbandingan Data dbForge untuk SQL Server
• Perbandingan Data SQL RedGate
• Perbedaan Data SQL Puncak
• SQL Database Studio

Tiga alat pertama bekerja sangat mirip. Mereka membandingkan data, membiarkan pengguna menganalisis perbedaan, dan dapat menyinkronkan perbedaan yang dipilih (bahkan secara otomatis atau dari baris perintah). Mereka bermanfaat untuk skenario penggunaan ini:

• Basis data tidak sinkron karena berbagai kesalahan.
• Anda perlu menghindari replikasi saat mentransfer data antar lingkungan.
• Laporan perbandingan data dalam Excel atau HTML diperlukan.

Setiap alat disukai karena satu dan lain alasan: dbForge memiliki UI yang bagus dan banyak pilihan, ApexSQL berkinerja lebih baik daripada yang lain, dan RedGate adalah yang paling populer.

Alat keempat, SQL Database Studio, bekerja sedikit berbeda. Ini menghasilkan skrip SQL yang berisi logika sinkronisasi, bukan perubahan. Performanya juga bagus, karena semua pekerjaan dilakukan langsung di server database, jadi tidak diperlukan transfer data antara server database dan alat sinkronisasi. Alat ini berguna untuk kasus penggunaan berikut:

• Migrasi database otomatis di mana database memiliki struktur yang berbeda
• Impor ke beberapa tabel terkait
• Impor dari sumber eksternal XML, CSV, MS Excel