Mengonfigurasi transaksi terdistribusi untuk grup ketersediaan AlwaysOn

Artikel
04/26/2023

Berlaku untuk:SQL Server

SQL Server 2017 (14.x) mendukung semua transaksi terdistribusi termasuk database dalam grup ketersediaan. Artikel ini menjelaskan cara mengonfigurasi grup ketersediaan untuk transaksi terdistribusi

Untuk menjamin transaksi terdistribusi, grup ketersediaan harus dikonfigurasi untuk mendaftarkan database sebagai manajer sumber daya transaksi terdistribusi.

Catatan

SQL Server 2016 (13.x) Paket Layanan 2 dan yang lebih baru memberikan dukungan penuh untuk transaksi terdistribusi dalam grup ketersediaan. Dalam versi SQL Server 2016 (13.x) sebelum Paket Layanan 2, transaksi terdistribusi lintas database (yaitu transaksi menggunakan database pada instans SQL Server yang sama) yang melibatkan database dalam grup ketersediaan tidak didukung. SQL Server 2017 (14.x) tidak memiliki batasan ini.

Pada SQL Server 2016 (13.x) langkah-langkah konfigurasi sama seperti pada SQL Server 2017 (14.x).

Dalam transaksi terdistribusi, aplikasi klien bekerja dengan Koordinator Transaksi Terdistribusi Microsoft (MS DTC atau DTC) untuk menjamin konsistensi transaksi di beberapa sumber data. DTC adalah layanan yang tersedia pada sistem operasi berbasis Windows Server yang didukung. Untuk transaksi terdistribusi, DTC adalah koordinator transaksi. Biasanya, instans SQL Server adalah manajer sumber daya. Saat database berada dalam grup ketersediaan, setiap database harus menjadi manajer sumber dayanya sendiri.

SQL Server tidak mencegah transaksi terdistribusi untuk database dalam grup ketersediaan - bahkan ketika grup ketersediaan tidak dikonfigurasi untuk transaksi terdistribusi. Namun ketika grup ketersediaan tidak dikonfigurasi untuk transaksi terdistribusi, failover mungkin tidak berhasil dalam beberapa situasi. Khususnya replika utama baru SQL Server instans mungkin tidak bisa mendapatkan hasil transaksi dari DTC. Untuk mengaktifkan instans SQL Server untuk mendapatkan hasil transaksi yang ragu dari DTC setelah failover, konfigurasikan grup ketersediaan untuk transaksi terdistribusi.

DTC tidak terlibat dalam pemrosesan grup ketersediaan kecuali database juga merupakan anggota Kluster Failover. Dalam grup ketersediaan, konsistensi antar replika dipertahankan oleh logika grup ketersediaan: Primer tidak akan menyelesaikan penerapan dan mengakui penerapan kepada pemanggil sampai sekunder telah mengakui bahwa ia telah mempertahankan catatan log dalam penyimpanan yang tahan lama. Hanya dengan begitu Primer akan menyatakan transaksi selesai. Dalam mode asinkron, kita tidak menunggu sekunder untuk ack, dan secara eksplisit ada kemungkinan hilangnya sejumlah kecil data.

Prasyarat

Sebelum mengonfigurasi grup ketersediaan untuk mendukung transaksi terdistribusi, Anda harus memenuhi prasyarat berikut:

Semua instans SQL Server yang berpartisipasi dalam transaksi terdistribusi harus SQL Server 2016 (13.x) atau yang lebih baru.
Grup ketersediaan harus berjalan pada Windows Server 2012 R2 atau yang lebih baru. Untuk Windows Server 2012 R2, Anda harus menginstal pembaruan di KB3090973 yang tersedia di https://support.microsoft.com/kb/3090973.

Membuat grup ketersediaan untuk transaksi terdistribusi

Konfigurasikan grup ketersediaan untuk mendukung transaksi terdistribusi. Atur grup ketersediaan untuk memungkinkan setiap database mendaftar sebagai manajer sumber daya. Artikel ini menjelaskan cara mengonfigurasi grup ketersediaan sehingga setiap database dapat menjadi manajer sumber daya di DTC.

Anda dapat membuat grup ketersediaan untuk transaksi terdistribusi pada SQL Server 2016 (13.x) atau yang lebih baru. Untuk membuat grup ketersediaan untuk transaksi terdistribusi, sertakan DTC_SUPPORT = PER_DB dalam definisi grup ketersediaan. Skrip berikut membuat grup ketersediaan untuk transaksi terdistribusi.

CREATE AVAILABILITY GROUP MyAG
   WITH (
      DTC_SUPPORT = PER_DB  
      )
   FOR DATABASE DB1, DB2
   REPLICA ON
      'Server1' WITH (
         ENDPOINT_URL = 'TCP://SERVER1.corp.com:5022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         ),
      'Server2' WITH (
         ENDPOINT_URL = 'TCP://SERVER2.corp.com:5022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         )

Catatan

Skrip sebelumnya adalah contoh sederhana dari grup ketersediaan dan tidak dirancang untuk lingkungan produksi tertentu.

Mengubah grup ketersediaan untuk transaksi terdistribusi

Anda dapat mengubah grup ketersediaan untuk transaksi terdistribusi pada SQL Server 2017 (14.x) atau yang lebih baru. Untuk mengubah grup ketersediaan untuk transaksi terdistribusi, sertakan DTC_SUPPORT = PER_DBALTER AVAILABILITY GROUP dalam skrip. Contoh skrip mengubah grup ketersediaan untuk mendukung transaksi terdistribusi.

ALTER AVAILABILITY GROUP MyaAG
   SET (
      DTC_SUPPORT = PER_DB  
      );

Catatan

Dimulai dengan SQL Server 2016 (13.x) Paket Layanan 2 Anda dapat mengubah grup ketersediaan untuk transaksi terdistribusi. Untuk versi SQL Server 2016 (13.x) sebelum Paket Layanan 2, Anda perlu menghapus, dan membuat ulang grup ketersediaan dengan DTC_SUPPORT = PER_DB pengaturan .

Untuk menonaktifkan transaksi terdistribusi, gunakan perintah Transact-SQL berikut:

ALTER AVAILABILITY GROUP MyaAG
   SET (
      DTC_SUPPORT = NONE  
      );

Transaksi terdistribusi - konsep teknis

Transaksi terdistribusi mencakup dua database atau lebih. Sebagai manajer transaksi, DTC mengoordinasikan transaksi antara instans SQL Server, dan sumber data lainnya. Setiap instans mesin database SQL Server dapat beroperasi sebagai manajer sumber daya. Saat grup ketersediaan dikonfigurasi dengan DTC_SUPPORT = PER_DB, database dapat beroperasi sebagai manajer sumber daya. Untuk informasi selengkapnya, lihat dokumentasi MS DTC.

Transaksi dengan dua database atau lebih dalam satu instans mesin database sebenarnya adalah transaksi terdistribusi. Instans mengelola transaksi terdistribusi secara internal; kepada pengguna, ia beroperasi sebagai transaksi lokal. SQL Server 2017 (14.x) mempromosikan semua transaksi lintas database ke DTC ketika database berada dalam grup ketersediaan yang dikonfigurasi dengan DTC_SUPPORT = PER_DB - bahkan dalam satu instans SQL Server.

Pada aplikasi, transaksi terdistribusi dikelola sama dengan transaksi lokal. Pada akhir transaksi, aplikasi meminta transaksi untuk diterapkan atau digulung balik. Penerapan terdistribusi harus dikelola secara berbeda oleh manajer transaksi untuk meminimalkan risiko bahwa kegagalan jaringan dapat mengakibatkan beberapa manajer sumber daya berhasil berkomitmen sementara yang lain mengembalikan transaksi. Hal ini dicapai dengan mengelola proses penerapan dalam dua fase (fase persiapan dan fase penerapan), yang dikenal sebagai penerapan dua fase.

Fase persiapan

Ketika manajer transaksi menerima permintaan penerapan, manajer transaksi mengirimkan perintah persiapan ke semua manajer sumber daya yang terlibat dalam transaksi. Setiap manajer sumber daya kemudian melakukan semua yang diperlukan untuk membuat transaksi tahan lama, dan semua buffer yang memegang gambar log untuk transaksi dibersihkan ke disk. Ketika setiap manajer sumber daya menyelesaikan fase persiapan, ia mengembalikan keberhasilan atau kegagalan persiapan ke manajer transaksi.
Fase penerapan

Jika manajer transaksi menerima persiapan yang berhasil dari semua manajer sumber daya, manajer transaksi mengirimkan perintah penerapan ke setiap manajer sumber daya. Manajer sumber daya kemudian dapat menyelesaikan penerapan. Jika semua manajer sumber daya melaporkan penerapan yang berhasil, manajer transaksi kemudian mengirim pemberitahuan keberhasilan ke aplikasi. Jika ada manajer sumber daya yang melaporkan kegagalan persiapan, manajer transaksi mengirimkan perintah putar kembali ke setiap manajer sumber daya dan menunjukkan kegagalan penerapan ke aplikasi.

Langkah terperinci

Daftar berikut menjelaskan cara kerja aplikasi dengan DTC untuk menyelesaikan transaksi terdistribusi.

Instans SQL Server mendaftar dalam transaksi DTC. Ini dapat terjadi ketika ada lebih dari satu manajer sumber daya dalam transaksi atau jika klien meminta transaksi dipromosikan ke transaksi DTC.
Klien melakukan beberapa pekerjaan dalam instans SQL Server di bawah transaksi DTC.
Masalah klien diterapkan atau dibatalkan untuk transaksi DTC.
- Jika masalah klien dibatalkan, transaksi akan segera dibatalkan.
- Jika masalah klien berkomitmen, DTC memulai protokol penerapan dua fase dengan meminta semua manajer sumber daya dalam transaksi untuk menyiapkan transaksi.
DTC menginformasikan semua manajer sumber daya untuk melakukan transaksi setelah semua manajer sumber daya berhasil mengakui fase persiapan. Jika ada yang mencegah pengakuan yang berhasil, DTC membatalkan transaksi.

Efek mengonfigurasi grup ketersediaan untuk transaksi terdistribusi

Setiap entitas yang berpartisipasi dalam transaksi terdistribusi disebut manajer sumber daya. Contoh manajer sumber daya meliputi:

Instans SQL Server.
Database dalam grup ketersediaan yang telah dikonfigurasi untuk transaksi terdistribusi.
Layanan DTC - juga dapat menjadi manajer transaksi.
Sumber data lainnya.

Untuk berpartisipasi dalam transaksi terdistribusi, instans SQL Server mendaftar dengan DTC. Biasanya instans SQL Server mendaftar dengan DTC di server lokal. Setiap instans SQL Server membuat manajer sumber daya dengan pengidentifikasi manajer sumber daya (RMID) yang unik dan mendaftarkannya dengan DTC. Dalam konfigurasi default, semua database pada instans SQL Server menggunakan RMID yang sama.

Saat database berada dalam grup ketersediaan, salinan baca-tulis database - atau replika utama - dapat berpindah ke instans SQL Server yang berbeda. Untuk mendukung transaksi terdistribusi selama pergerakan ini, setiap database harus bertindak sebagai manajer sumber daya terpisah dan harus memiliki RMID yang unik. Ketika grup ketersediaan memiliki DTC_SUPPORT = PER_DB, SQL Server membuat manajer sumber daya untuk setiap database dan mendaftar dengan DTC menggunakan RMID unik. Dalam konfigurasi ini, database adalah manajer sumber daya untuk transaksi DTC.

Penting

Perhatikan bahwa DTC memiliki batas 32 pendaftaran per transaksi terdistribusi. Karena setiap database dalam grup ketersediaan mendaftar dengan DTC secara terpisah, jika transaksi Anda melibatkan lebih dari 32 database, Anda mungkin mendapatkan kesalahan berikut saat SQL Server mencoba mendaftarkan database ke-33:

Enlist operation failed: 0x8004d101(XACT_E_TOOMANY_ENLISTMENTS). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.

Untuk detail selengkapnya tentang transaksi terdistribusi dalam SQL Server, lihat Transaksi terdistribusi

Mengelola transaksi yang belum terselesaikan

Hasil untuk transaksi aktif yang ada selama perubahan RMID tidak dapat dipulihkan setelah failover. Ini karena SQL Server RMID yang digunakan untuk mendaftar dan SQL Server RMID yang digunakan untuk memulihkan berbeda. Perubahan RMID dapat terjadi dalam kasus berikut:

Ubah DTC_SUPPORT untuk grup ketersediaan.
Menambahkan atau menghapus database dari grup ketersediaan.
Hilangkan grup ketersediaan.

Dalam kasus sebelumnya, jika replika utama gagal ke instans baru SQL Server, instans mencoba menghubungi DTC untuk mengidentifikasi hasil transaksi. DTC tidak dapat mengembalikan hasil karena RMID yang digunakan database untuk mendapatkan hasil untuk transaksi yang ragu selama pemulihan tidak terdaftar sebelumnya. Oleh karena itu database masuk ke status SUSPECT.

Log kesalahan SQL Server baru memiliki entri seperti contoh berikut:

Microsoft Distributed Transaction Coordinator (MS DTC) 
failed to reenlist citing that the database RMID does 
not match the RMID [xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx] 
associated with the transaction.  Please manually resolve
the transaction.
    
SQL Server detected a DTC/KTM in-doubt transaction with UOW 
{yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy}.Please resolve it 
following the guideline for Troubleshooting DTC Transactions.

Contoh sebelumnya menunjukkan bahwa DTC tidak dapat mendaftarkan ulang database dari replika utama baru dalam transaksi yang dibuat setelah failover. Instans SQL Server tidak dapat menentukan hasil transaksi terdistribusi sehingga menandai database sebagai tersangka. Transaksi ditandai sebagai unit kerja (UOW) dan disebut oleh GUID. Untuk memulihkan database, terapkan atau putar kembali transaksi secara manual.

Peringatan

Ketika Anda menerapkan atau memutar kembali transaksi secara manual, transaksi tersebut dapat memengaruhi aplikasi. Verifikasi bahwa tindakan penerapan atau pembatalan konsisten dengan persyaratan aplikasi Anda.

Jalankan hanya salah satu skrip berikut:

Untuk melakukan transaksi, perbarui dan jalankan skrip berikut - ganti yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy dengan UOW transaksi yang diragukan dari pesan kesalahan sebelumnya, dan jalankan:

KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH COMMIT

Untuk mengembalikan transaksi, perbarui dan jalankan skrip berikut - ganti yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy dengan UOW transaksi yang diragukan dari pesan kesalahan sebelumnya, dan jalankan:

KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH ROLLBACK

Setelah Anda menerapkan atau mengembalikan transaksi, Anda dapat menggunakan ALTER DATABASE untuk mengatur database secara online. Perbarui dan jalankan skrip berikut - atur nama database untuk nama database tersangka: