Memecahkan Masalah Konfigurasi Grup Ketersediaan AlwaysOn (SQL Server)

Berlaku untuk: SQL Server

Topik ini menyediakan informasi untuk membantu Anda memecahkan masalah umum dengan mengonfigurasi instans server untuk grup ketersediaan AlwaysOn. Masalah konfigurasi umum termasuk grup ketersediaan AlwaysOn dinonaktifkan, akun salah dikonfigurasi, titik akhir pencerminan database tidak ada, titik akhir tidak dapat diakses (Kesalahan SQL Server 1418), akses jaringan tidak ada, dan perintah database gabungan gagal (Kesalahan SQL Server 35250).

Catatan

Pastikan Anda memenuhi prasyarat grup ketersediaan AlwaysOn. Untuk informasi selengkapnya, lihat Prasyarat, Pembatasan, dan Rekomendasi untuk Grup Ketersediaan AlwaysOn (SQL Server).

Dalam Topik ini:

Bagian	Deskripsi
Grup Ketersediaan AlwaysOn Tidak Diaktifkan	Jika instans SQL Server tidak diaktifkan untuk grup ketersediaan AlwaysOn, instans tidak mendukung pembuatan grup ketersediaan dan tidak dapat menghosting replika ketersediaan apa pun.
Account	Membahas persyaratan untuk mengonfigurasi akun dengan benar di mana SQL Server berjalan.
Titik Akhir	Membahas cara mendiagnosis masalah dengan titik akhir pencerminan database instans server.
Akses jaringan	Mendokumentasikan persyaratan bahwa setiap instans server yang menghosting replika ketersediaan harus dapat mengakses port masing-masing instans server lainnya melalui TCP.
Listener	Dokumen cara membuat alamat IP dan port pendengar dan memastikannya berjalan dan mendengarkan koneksi masuk
Akses Titik Akhir (Kesalahan SQL Server 1418)	Berisi informasi tentang pesan kesalahan SQL Server ini.
Gabungan Database Gagal (Kesalahan SQL Server 35250)	Membahas kemungkinan penyebab dan resolusi kegagalan untuk menggabungkan database sekunder ke grup ketersediaan karena koneksi ke replika utama tidak aktif.
Perutean Baca-Saja Tidak Berfungsi Dengan Benar
Tugas Terkait	Berisi daftar topik berorientasi tugas di SQL Server Books Online yang relevan dengan pemecahan masalah konfigurasi grup ketersediaan.
Isi Terkait	Berisi daftar sumber daya relevan yang berada di luar SQL Server Books Online.

Grup Ketersediaan AlwaysOn Tidak Diaktifkan

Fitur grup ketersediaan AlwaysOn harus diaktifkan pada setiap instans SQL Server.

Jika fitur Grup Ketersediaan AlwaysOn tidak diaktifkan, Anda akan mendapatkan pesan kesalahan ini saat mencoba membuat grup Ketersediaan di SQL Server.

The Always On Availability Groups feature must be enabled for server instance 'SQL1VM' before you can create an availability group on this instance. To enable this feature, open the SQL Server Configuration Manager, select SQL Server Services, right-click on the SQL Server service name, select Properties, and use the Always On Availability Groups tab of the Server Properties dialog. Enabling Always On Availability Groups may require that the server instance is hosted by a Windows Server Failover Cluster (WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)

Pesan kesalahan dengan jelas menunjukkan bahwa fitur AG tidak diaktifkan dan juga mengarahkan Anda cara mengaktifkannya. Ada dua skenario di mana Anda bisa mendapatkan dalam keadaan ini selain yang jelas di mana AG tidak diaktifkan di tempat pertama.

1. Jika SQL Server diinstal dan fitur Grup Ketersediaan AlwaysOn diaktifkan sebelum Anda menginstal fitur Pengklusteran Failover Windows, Anda mungkin mendapatkan kesalahan ini saat mencoba membuat Always On AG.
2. Jika Anda menghapus fitur Pengklusteran Failover Windows yang ada dan membangunnya kembali saat SQL Server masih mengonfigurasi Always On, ketika Anda mencoba menggunakan AG lagi, kesalahan ini mungkin terjadi.

Dalam kasus seperti itu, Anda dapat mengambil langkah-langkah berikut untuk mengatasinya:

1. Menonaktifkan fitur AG
2. Mulai ulang layanan SQL Server
3. Mengaktifkan kembali fitur AG
4. Sekali lagi mulai ulang Layanan SQL

Untuk informasi selengkapnya, lihat Mengaktifkan dan Menonaktifkan Grup Ketersediaan AlwaysOn (SQL Server).

Akun

Akun tempat SQL Server berjalan harus dikonfigurasi dengan benar.

1. Apakah akun memiliki izin yang benar?

   1. Jika mitra berjalan di bawah akun domain yang sama, login pengguna yang benar ada secara otomatis di kedua database master . Ini menyederhanakan konfigurasi keamanan dan disarankan.

   2. Jika dua instans server berjalan di bawah akun yang berbeda, maka setiap akun harus dibuat di master pada instans server jarak jauh, dan prinsipal server tersebut harus diberikan izin CONNECT untuk terhubung ke titik akhir pencerminan database instans server tersebut. Untuk informasi selengkapnya, lihat Menyiapkan Akun Masuk untuk Pencerminan Database atau Grup Ketersediaan AlwaysOn (SQL Server). Anda dapat menggunakan kueri berikut pada setiap instans untuk memeriksa apakah login memiliki izin CONNECT:

   SELECT 
     perm.class_desc,
     prin.name,
     perm.permission_name,
     perm.state_desc,
     prin.type_desc as PrincipalType,
     prin.is_disabled
   FROM sys.server_permissions perm
     LEFT JOIN sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
     LEFT JOIN sys.tcp_endpoints tep ON perm.major_id = tep.endpoint_id
   WHERE 
     perm.class_desc = 'ENDPOINT'
     AND perm.permission_name = 'CONNECT'
     AND tep.type = 4    
   

2. Jika SQL Server berjalan di bawah akun bawaan, seperti Sistem Lokal, Layanan Lokal, atau Layanan Jaringan, atau akun nondomain, Anda harus menggunakan sertifikat untuk autentikasi titik akhir. Jika akun layanan Anda menggunakan akun domain di domain yang sama, Anda dapat memilih untuk memberikan akses CONNECT untuk setiap akun layanan di semua lokasi replika atau Anda dapat menggunakan sertifikat. Untuk informasi selengkapnya, lihat Menggunakan Sertifikat untuk Titik Akhir Pencerminan Database (Transact-SQL).

Titik akhir

Titik akhir harus dikonfigurasi dengan benar.

1. Pastikan bahwa setiap instans SQL Server yang akan menghosting replika ketersediaan (setiap lokasi replika) memiliki titik akhir pencerminan database. Untuk menentukan apakah titik akhir pencerminan database ada pada instans server tertentu, gunakan tampilan katalog sys.database_mirroring_endpoints :

   SELECT name, state_desc FROM sys.database_mirroring_endpoints  
   

   Untuk informasi selengkapnya tentang membuat titik akhir, lihat Membuat Titik Akhir Pencerminan Database untuk Autentikasi Windows (Transact-SQL) atau Izinkan Titik Akhir Pencerminan Database menggunakan Sertifikat untuk Koneksi Keluar (Transact-SQL).

2. Periksa apakah nomor port sudah benar.

   Untuk mengidentifikasi port yang saat ini terkait dengan titik akhir pencerminan database instans server, gunakan pernyataan Transact-SQL berikut:

   SELECT type_desc, port FROM sys.tcp_endpoints;  
   GO  
   

3. Untuk masalah penyiapan grup ketersediaan AlwaysOn yang sulit dijelaskan, kami sarankan Anda memeriksa setiap instans server untuk menentukan apakah grup ketersediaan mendengarkan pada port yang benar.

4. Pastikan titik akhir dimulai (STATE=STARTED). Pada setiap instans server, gunakan pernyataan Transact-SQL berikut:

   SELECT state_desc FROM sys.database_mirroring_endpoints  
   

   Untuk informasi selengkapnya tentang kolom state_desc , lihat sys.database_mirroring_endpoints (Transact-SQL).

   Untuk memulai titik akhir, gunakan pernyataan Transact-SQL berikut:

   ALTER ENDPOINT Endpoint_Mirroring   
   STATE = STARTED   
   AS TCP (LISTENER_PORT = <port_number>)  
   FOR database_mirroring (ROLE = ALL);  
   GO  
   

   Untuk informasi selengkapnya, lihat ALTER ENDPOINT (Transact-SQL).

   Catatan

   Dalam beberapa kasus, jika titik akhir dimulai tetapi replika AG tidak berkomunikasi, Anda dapat mencoba menghentikan dan memulai ulang titik akhir. Anda dapat menggunakan ALTER ENDPOINT [Endpoint_Mirroring] STATE = STOP diikuti oleh ALTER ENDPOINT [Endpoint_Mirroring] STATE = STARTED

5. Pastikan bahwa login dari server lain memiliki izin CONNECT. Untuk menentukan siapa yang memiliki izin CONNECT untuk titik akhir, pada setiap instans server gunakan pernyataan Transact-SQL berikut:

   SELECT 'Metadata Check';  
   SELECT EP.name, SP.STATE,   
      CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))   
         AS GRANTOR,   
      SP.TYPE AS PERMISSION,  
      CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))   
         AS GRANTEE   
      FROM sys.server_permissions SP , sys.endpoints EP  
      WHERE SP.major_id = EP.endpoint_id  
      ORDER BY Permission,grantor, grantee;   
   

6. Pastikan nama server yang benar digunakan di URL titik akhir

   Untuk nama server dalam URL titik akhir, disarankan untuk menggunakan nama domain yang sepenuhnya memenuhi syarat (FQDN), meskipun Anda dapat menggunakan nama apa pun yang secara unik mengidentifikasi komputer. Alamat server dapat berupa nama Netbios (jika sistem berada di domain yang sama), nama domain yang sepenuhnya memenuhi syarat (FQDN), atau alamat IP (sebaiknya, alamat IP statis). Menggunakan nama domain yang sepenuhnya memenuhi syarat adalah opsi yang direkomendasikan.

   Jika Anda telah menentukan URL Titik Akhir, Anda bisa mengkuerinya dengan menggunakan:

   select endpoint_url from sys.availability_replicas
   

   Selanjutnya, bandingkan output endpoint_url dengan nama server (nama NetBIOS atau FQDN). Untuk mengkueri nama server, jalankan perintah berikut di PowerShell pada replika secara lokal:

   $env:COMPUTERNAME
   [System.Net.Dns]::GetHostEntry([string]$env:computername).HostName
   

   Untuk memvalidasi nama server pada komputer jarak jauh, jalankan perintah ini dari PowerShell.

   $servername_from_endpoint_url = "server_from_endpoint_url_output"
   
   Test-NetConnection -ComputerName $servername_from_endpoint_url
   

   Untuk informasi selengkapnya, lihat Menentukan URL Titik Akhir Saat Menambahkan atau Memodifikasi Replika Ketersediaan (SQL Server).

Catatan

Untuk menggunakan autentikasi Kerberos untuk komunikasi antara titik akhir grup ketersediaan (AG), daftarkan Nama Perwakilan Layanan untuk Koneksi Kerberos untuk titik akhir pencerminan database yang digunakan oleh AG.

Akses Jaringan

Setiap instans server yang menghosting replika ketersediaan harus dapat mengakses port masing-masing instans server lainnya melalui TCP. Ini sangat penting jika instans server berada di domain yang berbeda yang tidak mempercayai satu sama lain (domain yang tidak tepercaya). Periksa apakah Anda bisa menyambungkan ke titik akhir dengan mengikuti langkah-langkah berikut:

• Gunakan Test-NetConnection (setara dengan Telnet) untuk memvalidasi konektivitas. Berikut adalah contoh perintah yang dapat Anda gunakan:

  $server_name = "your_server_name"
  $IP_address = "your_ip_address"
  $port_number = "your_port_number"
  
  Test-NetConnection -ComputerName $server_name -Port $port_number
  Test-NetConnection -ComputerName $IP_address -Port $port_number
  

• Jika Titik Akhir mendengarkan dan koneksi berhasil, Anda akan melihat "TcpTestSucceededed : True". Jika tidak, Anda akan menerima "TcpTestSucceededed : False".

• Jika koneksi Test-NetConnection (Telnet) ke alamat IP berfungsi tetapi ke ServerName tidak, kemungkinan ada masalah resolusi DNS atau nama

• Jika koneksi berfungsi oleh ServerName dan bukan dengan alamat IP, mungkin ada lebih dari satu titik akhir yang ditentukan pada server tersebut (instans SQL lain mungkin) yang mendengarkan di port tersebut. Meskipun status titik akhir pada instans yang dimaksud menunjukkan "STARTED", instans lain mungkin benar-benar memiliki port yang terikat dan mencegah instans yang benar mendengarkan dan membuat koneksi TCP.

• Jika Test-NetConnection gagal terhubung, cari firewall dan/atau perangkat lunak Anti-virus yang mungkin memblokir port titik akhir yang dimaksud. Periksa pengaturan firewall untuk melihat apakah memungkinkan komunikasi port titik akhir antara instans server yang menghosting replika utama dan replika sekunder (port 5022 secara default). Jalankan skrip PowerShell berikut untuk memeriksa aturan lalu lintas masuk yang dinonaktifkan

• Jika Anda menjalankan SQL Server di Azure VM, selain itu Anda harus memastikan Network Security Group (NSG) memungkinkan lalu lintas ke port titik akhir. Periksa pengaturan firewall (dan NSG, untuk Azure VM) untuk melihat apakah memungkinkan komunikasi port titik akhir antara instans server yang menghosting replika utama dan replika sekunder (port 5022 secara default)

  Get-NetFirewallRule -Action Block -Enabled True -Direction Inbound |Format-Table
  

• Ambil output dari cmdlet Get-NetTCPConnection (setara dengan NETSTAT -a) dan verifikasi statusnya adalah LISTENING atau ESTABLISHED pada IP:Port untuk titik akhir yang ditentukan

  Get-NetTCPConnection 
  

Listener

Untuk konfigurasi pendengar Grup Ketersediaan yang benar, ikuti "Konfigurasikan pendengar untuk grup ketersediaan AlwaysOn"

1. Setelah pendengar dikonfigurasi, Anda dapat memvalidasi alamat IP dan port yang didengarkannya dengan menggunakan kueri berikut:

   $server_name = $env:computername  #replace this with your sql instance "server\instance"
   
   sqlcmd -E -S$server_name -Q"SELECT dns_name AS AG_listener_name, port, ip_configuration_string_from_cluster 
   FROM sys.availability_group_listeners"
   

2. Anda juga dapat menemukan informasi pendengar bersama dengan port SQL Server menggunakan kueri ini:

   $server_name = $env:computername      #replace this with your sql instance "server\instance"
   
   sqlcmd -E -S($server_name) -Q("SELECT  convert(varchar(32), SERVERPROPERTY ('servername')) servername, convert(varchar(32),ip_address) ip_address, port, type_desc,state_desc, start_time 
   FROM sys.dm_tcp_listener_states 
   WHERE ip_address not in ('127.0.0.1', '::1') and type <> 2")
   

3. Jika Anda perlu membangun konektivitas ke pendengar dan mencurigai port diblokir, Anda dapat melakukan pengujian menggunakan cmdlet PowerShell Test-NetConnection (setara dengan telnet).

   $listener_name = "your_ag_listener"
   $IP_address = "your_ip_address"
   $port_number = "your_port_number"
   
   Test-NetConnection -ComputerName $listener_name -Port $port_number
   Test-NetConnection -ComputerName $IP_address -Port $port_number
   

4. Terakhir, periksa apakah pendengar mendengarkan pada port yang ditentukan:

   $port_number = "your_port_number"
   
   Get-NetTCPConnection -LocalPort $port_number -State Listen
   

Akses Titik Akhir (Kesalahan SQL Server 1418)

Pesan SQL Server ini menunjukkan bahwa alamat jaringan server yang ditentukan dalam URL titik akhir tidak dapat dijangkau atau tidak ada, dan disarankan agar Anda memverifikasi nama alamat jaringan dan mengeluarkan kembali perintah.

Gabungan Database Gagal (Kesalahan SQL Server 35250)

Bagian ini membahas kemungkinan penyebab dan resolusi kegagalan untuk menggabungkan database sekunder ke grup ketersediaan karena koneksi ke replika utama tidak aktif. Ini adalah pesan kesalahan lengkap:

Msg 35250 The connection to the primary replica is not active. The command cannot be processed.

Resolusi:

Ringkasan langkah-langkah diuraikan di bawah ini.

Untuk instruksi langkah demi langkah terperinci , lihat Kesalahan mesin MSSQLSERVER_35250

1. Pastikan titik akhir dibuat dan dimulai.
2. Periksa apakah Anda dapat tersambung ke titik akhir melalui Telnet dan memastikan tidak ada aturan firewall yang memblokir konektivitas
3. Periksa kesalahan dalam sistem. Anda bisa mengkueri sys.dm_hadr_availability_replica_states untuk last_connect_error_number yang dapat membantu Anda mendiagnosis masalah gabungan.
4. Pastikan titik akhir ditentukan sehingga cocok dengan IP/port yang digunakan AG dengan benar.
5. Periksa apakah akun layanan jaringan memiliki izin CONNECT ke titik akhir.
6. Periksa kemungkinan masalah resolusi nama
7. Pastikan SQL Server Anda menjalankan build terbaru (sebaiknya build terbaru untuk dilindungi agar tidak mengalami masalah tetap.

Perutean Baca-Saja Tidak Berfungsi Dengan Benar

1. Pastikan Anda telah menyiapkan perutean baca-saja dengan mengikuti Mengonfigurasi dokumen perutean baca-saja.

2. Pastikan Dukungan Driver Klien

   Aplikasi klien harus menggunakan penyedia klien yang mendukung ApplicationIntent parameter. Lihat Dukungan konektivitas driver dan klien untuk grup ketersediaan

   Catatan

   Jika Anda menyambungkan ke Listener nama jaringan terdistribusi (DNN), penyedia juga harus mendukung MultiSubnetFailover parameter

3. Pastikan properti string koneksi diatur dengan benar

   Agar perutean baca-saja berfungsi dengan baik, aplikasi klien Anda harus menggunakan properti ini di string koneksi:

   • Nama database milik AG
   • Nama pendengar grup ketersediaan
     • Jika Anda menggunakan DNN, Anda harus menentukan nama pendengar DNN dan nomor port DNN <DNN name,DNN port>
   • ApplicationIntent diatur ke ReadOnly
   • MultiSubnetFailover yang diatur ke true diperlukan untuk Nama jaringan terdistribusi (DNN)

   Contoh

   Contoh ini mengilustrasikan string koneksi untuk penyedia .NET System.Data.SqlClient untuk pendengar nama jaringan virtual (VNN):

   Server=tcp:VNN_AgListener,1433;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
   

   Ini menggambarkan string koneksi untuk penyedia .NET System.Data.SqlClient untuk pendengar nama jaringan terdistribusi (DNN):

   Server=tcp:DNN_AgListener,DNN_Port;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
   

   Catatan

   Jika Anda menggunakan program baris perintah seperti SQLCMD, pastikan Anda menentukan sakelar yang benar untuk nama server. Misalnya, di SQLCMD Anda harus menggunakan sakelar huruf besar -S yang menentukan nama server, bukan sakelar huruf kecil -s yang digunakan untuk pemisah kolom.
   Contoh: sqlcmd -S AG_Listener,port -E -d AgDb1 -K ReadOnly -M

4. Pastikan bahwa pendengar grup ketersediaan sedang online. Untuk memastikan bahwa pendengar grup ketersediaan secara online menjalankan kueri berikut pada replika utama:

   SELECT * FROM sys.dm_tcp_listener_states;
   

   Jika Anda menemukan pendengar offline, Anda dapat mencoba membuatnya online menggunakan perintah seperti ini:

   ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'AG_Listener';
   

5. Pastikan READ_ONLY_ROUTING_LIST diisi dengan benar. Pada Replika utama, pastikan bahwa READ_ONLY_ROUTING_LIST hanya berisi instans server yang menghosting replika sekunder yang dapat dibaca.

   Untuk melihat properti setiap replika, Anda dapat menjalankan kueri ini dan memeriksa titik akhir konektivitas (URL) dari replika baca saja.

   SELECT replica_id, replica_server_name, secondary_role_allow_connections_desc, read_only_routing_url 
   FROM sys.availability_replicas;   
   

   Untuk melihat daftar perutean baca-saja dan membandingkan dengan URL titik akhir:

   SELECT * FROM sys.availability_read_only_routing_lists;
   

   Untuk mengubah daftar perutean baca-saja, Anda bisa menggunakan kueri seperti ini:

   ALTER AVAILABILITY GROUP [AG1]   
   MODIFY REPLICA ON  
   N'COMPUTER02' WITH   
   (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
   

   Untuk informasi selengkapnya, lihat Mengonfigurasi perutean baca-saja untuk grup ketersediaan - SQL Server Always On

6. Periksa apakah port READ_ONLY_ROUTING_URL terbuka. Pastikan firewall Windows tidak memblokir port READ_ONLY_ROUTING_URL. Konfigurasikan Windows Firewall untuk akses mesin database pada setiap replika di read_only_routing_list dan apa pun untuk klien yang akan terhubung ke replika tersebut.

   Catatan

   Jika Anda menjalankan SQL Server di Azure VM, Anda harus mengambil langkah-langkah konfigurasi tambahan. Pastikan bahwa kelompok keamanan jaringan (NSG) dari setiap replika VM memungkinkan lalu lintas ke port titik akhir dan port DNN, jika Anda menggunakan pendengar DNN. Jika Anda menggunakan pendengar VNN, Anda harus memastikan load balancer dikonfigurasi dengan benar.

7. Pastikan bahwa READ_ONLY_ROUTING_URL (TCP://system-address:port) berisi nama domain yang sepenuhnya memenuhi syarat (FQDN) dan nomor port yang benar. Lihat:

   • Menghitung read_only_routing_url untuk AlwaysOn
   • sys.availability_replicas (T-SQL)
   • UBAH GRUP KETERSEDIAAN (Transact-SQL)

8. Pastikan konfigurasi Jaringan SQL Server yang tepat di Pengelola Konfigurasi SQL Server.

   Verifikasi pada setiap replika di read_only_routing_list bahwa:

   • Konektivitas jarak jauh SQL Server diaktifkan
   • TCP/IP diaktifkan
   • Alamat IP dikonfigurasi dengan benar

   Catatan

   Anda dapat dengan cepat memverifikasi semua ini dikonfigurasi dengan benar jika Anda dapat terhubung dari komputer jarak jauh ke nama instans SQL Server replika sekunder target menggunakan TCP:SQL_Instance sintaksis.

Lihat: Mengonfigurasi Server untuk Mendengarkan di Port TCP Tertentu (Pengelola Konfigurasi SQL Server) dan Melihat atau Mengubah Properti Server (SQL Server)

Tugas Terkait

• Pembuatan dan Konfigurasi Grup Ketersediaan (SQL Server)

• Membuat Titik Akhir Pencerminan Database untuk Autentikasi Windows (Transact-SQL)

• Tentukan URL Titik Akhir Saat Menambahkan atau Memodifikasi Replika Ketersediaan (SQL Server)

• Menyiapkan Database Sekunder secara Manual untuk Grup Ketersediaan (SQL Server)

• Memecahkan Masalah Operasi Add-File yang Gagal (Grup Ketersediaan AlwaysOn)

• Manajemen Login dan Pekerjaan untuk Database Grup Ketersediaan (SQL Server)

• Mengelola Metadata Saat Membuat Database Tersedia di Instans Server Lain (SQL Server)

Konten Terkait

• Melihat Peristiwa dan Log untuk Kluster Failover

• Cmdlet Kluster Failover Get-ClusterLog

• Blog SQL Server Always On Team: Blog resmi SQL Server Always On Team

Lihat Juga

Keamanan Transportasi untuk Pencerminan Database dan Grup Ketersediaan AlwaysOn (SQL Server)
Konfigurasi Jaringan Klien
Prasyarat, Pembatasan, dan Rekomendasi untuk Grup Ketersediaan AlwaysOn (SQL Server)