Bagikan melalui

Mengonfigurasi transaksi terdistribusi untuk grup ketersediaan AlwaysOn

  • Artikel

Berlaku untuk: SQL Server

SQL Server 2017 (14.x) dan versi yang lebih baru 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 versi yang lebih baru memberikan dukungan penuh untuk transaksi terdistribusi dalam grup ketersediaan. Dalam Paket Layanan SQL Server 2016 (13.x) 1 dan versi yang lebih lama, 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.

Di SQL Server 2016 (13.x), langkah-langkah konfigurasinya sama seperti di SQL Server 2017 (14.x).

Dalam transaksi terdistribusi, aplikasi klien bekerja dengan Koordinator Transaksi Terdistribusi Microsoft (MSDTC 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 instans SQL Server replika utama baru mungkin tidak dapat mendapatkan hasil transaksi dari DTC. Untuk mengaktifkan instans SQL Server guna 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 antara replika dipertahankan oleh logika grup ketersediaan: Primer tidak menyelesaikan penerapan dan mengakui penerapan kepada pemanggil sampai sekunder mengakui bahwa ia mempertahankan catatan log dalam penyimpanan yang tahan lama. Hanya kemudian primer menyatakan transaksi selesai. Dalam mode asinkron, kita tidak menunggu sekunder untuk ack, dan secara eksplisit 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 versi yang lebih baru.

  • Grup ketersediaan harus berjalan pada Windows Server 2012 R2 atau versi yang lebih baru. Untuk Windows Server 2012 R2, Anda harus menginstal pembaruan di KB3090973.

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 di SQL Server 2016 (13.x) atau versi 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 versi yang lebih baru. Untuk mengubah grup ketersediaan untuk transaksi terdistribusi, sertakan DTC_SUPPORT = PER_DB ALTER AVAILABILITY GROUP dalam skrip. Contoh skrip mengubah grup ketersediaan untuk mendukung transaksi terdistribusi.

ALTER AVAILABILITY GROUP MyaAG
SET (DTC_SUPPORT = PER_DB);

Catatan

Di SQL Server 2016 (13.x) Paket Layanan 2 dan versi yang lebih baru, Anda dapat mengubah grup ketersediaan untuk transaksi terdistribusi. Untuk versi SQL Server 2016 (13.x) sebelum Paket Layanan 2, Anda perlu menghilangkan, 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 MSDTC.

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 jauh sama dengan transaksi lokal. Pada akhir transaksi, aplikasi meminta transaksi untuk diterapkan atau digulung balik. Manajer transaksi harus mengelola penerapan terdistribusi secara berbeda, untuk meminimalkan risiko bahwa kegagalan jaringan dapat mengakibatkan beberapa manajer sumber daya berhasil melakukan, sementara yang lain membatalkan 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 disiram ke disk. Ketika setiap manajer sumber daya menyelesaikan fase persiapan, ia mengembalikan keberhasilan atau kegagalan fase persiapan ke manajer transaksi.

  • Fase Penerapan

    Jika manajer transaksi menerima persiapan yang berhasil dari semua manajer sumber daya, manajer sumber daya 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.

  1. Instans SQL Server mendaftarkan 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.
  2. Klien melakukan beberapa pekerjaan dalam instans SQL Server di bawah transaksi DTC.
  3. Klien mengeluarkan penerapan atau membatalkan transaksi DTC.
    • Jika masalah klien dibatalkan, transaksi akan segera dibatalkan.
    • Jika masalah klien diterapkan, DTC memulai protokol penerapan dua fase dengan meminta semua manajer sumber daya dalam transaksi untuk menyiapkan transaksi.
  4. DTC memberi tahu 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 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 unik (RMID) 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 - mungkin 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 resource manager untuk transaksi DTC.

Penting

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 couldn't register with Microsoft Distributed Transaction Coordinator (MSDTC) as a resource manager for this transaction. The transaction might have been stopped by the client or the resource manager.

Untuk detail selengkapnya tentang transaksi terdistribusi di 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 RMID SQL Server yang digunakan untuk mendaftar dan RMID SQL Server 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 (MSDTC)
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, lakukan 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 ragu 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 ragu dari pesan kesalahan sebelumnya, dan jalankan:

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

Setelah 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:

ALTER DATABASE [DB1] SET ONLINE;

Untuk informasi selengkapnya tentang mengatasi transaksi yang ragu, lihat Menyelesaikan Transaksi Secara Manual.

Konten terkait