Mengonfigurasi skala baca untuk grup ketersediaan Always On

Artikel
04/26/2023

Berlaku untuk:SQL Server

Anda dapat mengonfigurasi grup ketersediaan AlwaysOn SQL Server untuk beban kerja skala baca di Windows. Ada dua jenis arsitektur untuk grup ketersediaan:

Arsitektur untuk ketersediaan tinggi yang menggunakan manajer kluster untuk memberikan peningkatan kelangsungan bisnis dan yang dapat mencakup replika sekunder yang dapat dibaca. Untuk membuat arsitektur ketersediaan tinggi ini, lihat Membuat dan mengonfigurasi grup ketersediaan di Windows.
Arsitektur yang hanya mendukung beban kerja skala baca.

Artikel ini menjelaskan cara membuat grup ketersediaan tanpa manajer kluster untuk beban kerja skala baca. Arsitektur ini hanya menyediakan skala baca. Ini tidak memberikan ketersediaan tinggi.

Catatan

Grup ketersediaan dengan CLUSTER_TYPE = NONE dapat mencakup replika yang dihosting di berbagai platform sistem operasi. Ini tidak dapat mendukung ketersediaan tinggi. Untuk sistem operasi Linux, lihat Mengonfigurasi grup ketersediaan SQL Server untuk skala baca di Linux.

Prasyarat

Sebelum membuat grup ketersediaan, Anda perlu:

Atur lingkungan Anda sehingga semua server yang akan menghosting replika ketersediaan dapat berkomunikasi.
Instal SQL Server. Lihat Menginstal SQL Server untuk detailnya.

Aktifkan Grup Ketersediaan AlwaysOn dan hidupkan ulang mssql-server

Catatan

Perintah berikut menggunakan cmdlet dari modul sqlserver yang diterbitkan di Galeri PowerShell. Anda dapat menginstal modul ini dengan menggunakan perintah Install-Module.

Aktifkan grup ketersediaan AlwaysOn pada setiap replika yang menghosting instans SQL Server. Kemudian mulai ulang layanan SQL Server. Jalankan perintah berikut untuk mengaktifkan lalu mulai ulang layanan SQL Server:

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

Mengaktifkan sesi peristiwa AlwaysOn_health

Untuk membantu diagnosis akar penyebab saat memecahkan masalah grup ketersediaan, Anda dapat secara opsional mengaktifkan sesi peristiwa yang diperluas grup ketersediaan AlwaysOn (XEvents). Untuk melakukannya, jalankan perintah berikut pada setiap instans SQL Server:

ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

Untuk informasi selengkapnya tentang sesi XEvents ini, lihat Peristiwa yang diperluas grup ketersediaan AlwaysOn.

Autentikasi titik akhir pencerminan database

Agar sinkronisasi berfungsi dengan baik, replika yang terlibat dalam grup ketersediaan skala baca perlu mengautentikasi melalui titik akhir. Dua skenario utama yang dapat Anda gunakan untuk autentikasi tersebut tercakup di bagian berikutnya.

Akun layanan

Di lingkungan Direktori Aktif tempat semua replika sekunder bergabung ke domain yang sama, SQL Server dapat mengautentikasi dengan menggunakan akun layanan. Anda harus secara eksplisit membuat login untuk akun layanan pada setiap instans SQL Server:

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

Di lingkungan di mana replika sekunder mungkin tidak digabungkan ke domain Direktori Aktif, Anda harus menggunakan autentikasi SQL. Skrip Transact-SQL berikut membuat login bernama dbm_login dan pengguna bernama dbm_user. Perbarui skrip dengan kata sandi yang kuat. Untuk membuat pengguna titik akhir pencerminan database, jalankan perintah berikut pada semua instans SQL Server:

CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_Password!@#>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

Autentikasi sertifikat

Jika Anda menggunakan replika sekunder yang memerlukan autentikasi dengan autentikasi SQL, gunakan sertifikat untuk mengautentikasi antara titik akhir pencerminan.

Skrip Transact-SQL berikut membuat kunci master dan sertifikat. Kemudian mencadangkan sertifikat dan mengamankan file dengan kunci privat. Perbarui skrip dengan kata sandi yang kuat. Jalankan skrip pada instans SQL Server utama untuk membuat sertifikat:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
   WITH PRIVATE KEY (
       FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
       ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
   );

Pada titik ini, replika SQL Server utama Anda memiliki sertifikat di c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer dan kunci privat di c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk. Salin kedua file ini ke lokasi yang sama di semua server yang akan menghosting replika ketersediaan.

Pada setiap replika sekunder, pastikan bahwa akun layanan untuk instans SQL Server memiliki izin untuk mengakses sertifikat.

Membuat sertifikat di server sekunder

Skrip Transact-SQL berikut membuat kunci master dan sertifikat dari cadangan yang Anda buat di replika SQL Server utama. Perintah ini juga mengotorisasi pengguna untuk mengakses sertifikat. Perbarui skrip dengan kata sandi yang kuat. Kata sandi dekripsi adalah kata sandi yang sama dengan yang Anda gunakan untuk membuat file .pvk di langkah sebelumnya. Untuk membuat sertifikat, jalankan skrip berikut pada semua replika sekunder:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
    );

Membuat titik akhir pencerminan database di semua replika

Titik akhir pencerminan database menggunakan Protokol Kontrol Transmisi (TCP) untuk mengirim dan menerima pesan antara instans server yang berpartisipasi dalam sesi pencerminan database atau replika ketersediaan host. Titik akhir pencerminan database mendengarkan nomor port TCP yang unik.

Skrip Transact-SQL berikut membuat titik akhir mendengarkan bernama Hadr_endpoint untuk grup ketersediaan. Ini memulai titik akhir dan memberikan izin koneksi ke akun layanan atau login SQL yang Anda buat di langkah sebelumnya. Sebelum Anda menjalankan skrip, ganti nilai di antara **< ... >**. Secara opsional Anda dapat menyertakan alamat IP, LISTENER_IP = (0.0.0.0). Alamat IP pendengar harus berupa alamat IPv4. Anda juga dapat menggunakan 0.0.0.0.

Perbarui skrip Transact-SQL berikut untuk lingkungan Anda di semua instans SQL Server:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = **<5022>**)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

Port TCP pada firewall harus terbuka untuk port pendengar.

Untuk informasi selengkapnya, lihat Titik akhir pencerminan database (SQL Server).

Membuat grup ketersediaan

Membuat grup ketersediaan. Set CLUSTER_TYPE = NONE. Selain itu, atur setiap replika dengan FAILOVER_MODE = NONE. Aplikasi klien yang menjalankan analitik atau melaporkan beban kerja dapat langsung terhubung ke database sekunder. Anda juga dapat membuat daftar perutean baca-saja. Koneksi ke replika utama meneruskan permintaan koneksi baca ke setiap replika sekunder dari daftar perutean dengan cara round-robin.

Skrip Transact-SQL berikut membuat grup ketersediaan bernama ag1. Skrip mengonfigurasi replika grup ketersediaan dengan SEEDING_MODE = AUTOMATIC. Pengaturan ini menyebabkan SQL Server secara otomatis membuat database di setiap server sekunder setelah ditambahkan ke grup ketersediaan.

Perbarui skrip berikut untuk lingkungan Anda. <node1> Ganti nilai dan <node2> dengan nama instans SQL Server yang menghosting replika. <5022> Ganti nilai dengan port yang Anda tetapkan untuk titik akhir. Jalankan skrip Transact-SQL berikut pada replika SQL Server utama:

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    ),
        N'<node2>' WITH (
		    ENDPOINT_URL = N'tcp://<node2>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
		    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Menggabungkan instans SQL Server sekunder ke grup ketersediaan

Skrip Transact-SQL berikut menggabungkan server ke grup ketersediaan bernama ag1. Perbarui skrip untuk lingkungan Anda. Untuk bergabung dengan grup ketersediaan, jalankan skrip Transact-SQL berikut pada setiap replika SQL Server sekunder:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Menambahkan database ke Grup Ketersediaan

Pastikan database yang Anda tambahkan ke grup ketersediaan berada dalam model pemulihan penuh dan memiliki cadangan log yang valid. Jika database adalah database pengujian atau database yang baru dibuat, ambil cadangan database. Untuk membuat dan mencadangkan database yang disebut db1, jalankan skrip Transact-SQL berikut pada instans SQL Server utama:

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

Untuk menambahkan database yang dipanggil db1 ke grup ketersediaan yang disebut ag1, jalankan skrip Transact-SQL berikut pada replika SQL Server utama:

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

Memverifikasi bahwa database dibuat di server sekunder

Untuk melihat apakah db1 database dibuat dan disinkronkan, jalankan kueri berikut pada setiap replika SQL Server sekunder:

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

Grup ketersediaan ini bukan konfigurasi ketersediaan tinggi. Jika Anda memerlukan ketersediaan tinggi, ikuti instruksi di Mengonfigurasi grup ketersediaan AlwaysOn untuk SQL Server di Linux atau Pembuatan dan Konfigurasi grup ketersediaan di Windows.

Koneksi ke replika sekunder baca-saja

Anda dapat terhubung ke replika sekunder baca-saja dengan salah satu dari dua cara:

Aplikasi dapat terhubung langsung ke instans SQL Server yang menghosting replika sekunder dan mengkueri database. Untuk informasi selengkapnya, lihat Replika sekunder yang dapat dibaca.
Aplikasi juga dapat menggunakan perutean baca-saja, yang memerlukan pendengar. Jika Anda menyebarkan skenario skala baca tanpa manajer kluster, Anda masih dapat membuat pendengar yang menunjuk ke alamat IP replika utama saat ini dan port yang sama dengan yang didengarkan SQL Server. Anda harus membuat ulang pendengar untuk menunjuk ke alamat IP utama baru setelah failover. Untuk informasi selengkapnya, lihat Perutean baca-saja.

Failover replika utama pada grup ketersediaan skala baca

Setiap grup ketersediaan hanya memiliki satu replika utama. Replika utama memungkinkan baca dan tulis. Untuk mengubah replika mana yang utama, Anda dapat melakukan failover. Dalam grup ketersediaan umum, manajer kluster mengotomatiskan proses failover. Dalam grup ketersediaan dengan jenis kluster NONE, proses failover manual.

Ada dua cara untuk melakukan failover pada replika utama dalam grup ketersediaan dengan jenis kluster NONE:

Failover manual tanpa kehilangan data
Failover manual paksa dengan kehilangan data

Failover manual tanpa kehilangan data

Gunakan metode ini saat replika utama tersedia, tetapi Anda perlu mengubah sementara atau secara permanen instans mana yang menghosting replika utama. Untuk menghindari potensi kehilangan data, sebelum Anda mengeluarkan failover manual, pastikan bahwa replika sekunder target sudah diperbarui.

Untuk melakukan failover secara manual tanpa kehilangan data:

Buat replika SYNCHRONOUS_COMMITsekunder primer dan target saat ini .

ALTER AVAILABILITY GROUP [AGRScale] 
     MODIFY REPLICA ON N'<node2>' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

Untuk mengidentifikasi bahwa transaksi aktif diterapkan ke replika utama dan setidaknya satu replika sekunder sinkron, jalankan kueri berikut:

SELECT ag.name, 
   drs.database_id, 
   drs.group_id, 
   drs.replica_id, 
   drs.synchronization_state_desc, 
   ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

Replika sekunder disinkronkan ketika synchronization_state_desc adalah SYNCHRONIZED.

Perbarui REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT ke 1.

Skrip berikut diatur REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT ke 1 pada grup ketersediaan bernama ag1. Sebelum Anda menjalankan skrip berikut, ganti ag1 dengan nama grup ketersediaan Anda:
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
```
Pengaturan ini memastikan bahwa setiap transaksi aktif diterapkan ke replika utama dan setidaknya satu replika sekunder sinkron.

Catatan

Pengaturan ini tidak spesifik untuk failover dan harus ditetapkan berdasarkan persyaratan lingkungan.
Atur replika utama dan replika sekunder yang tidak berpartisipasi dalam failover offline untuk mempersiapkan perubahan peran:
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```

Promosikan replika sekunder target ke primer.

ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS;

Perbarui peran primer lama dan sekunder lainnya ke SECONDARY, jalankan perintah berikut pada instans SQL Server yang menghosting replika utama lama:
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (ROLE = SECONDARY); 
```
Catatan

Untuk menghapus grup ketersediaan, gunakan DROP AVAILABILITY GROUP. Untuk grup ketersediaan yang dibuat dengan jenis kluster NONE atau EXTERNAL, jalankan perintah pada semua replika yang merupakan bagian dari grup ketersediaan.
Lanjutkan pergerakan data, jalankan perintah berikut untuk setiap database dalam grup ketersediaan pada instans SQL Server yang menghosting replika utama:
```
ALTER DATABASE [db1]
     SET HADR RESUME
```
Buat ulang listener apa pun yang Anda buat untuk tujuan skala baca dan yang tidak dikelola oleh manajer kluster. Jika pendengar asli menunjuk ke primer lama, letakkan dan buat ulang untuk menunjuk ke primer baru.

Failover manual paksa dengan kehilangan data

Jika replika utama tidak tersedia dan tidak dapat segera dipulihkan, maka Anda perlu memaksa failover ke replika sekunder dengan kehilangan data. Namun, jika replika utama asli pulih setelah failover, replika tersebut akan mengasumsikan peran utama. Untuk menghindari setiap replika berada dalam status yang berbeda, hapus primer asli dari grup ketersediaan setelah failover paksa dengan kehilangan data. Setelah primer asli kembali online, hapus grup ketersediaan darinya sepenuhnya.

Untuk memaksa failover manual dengan kehilangan data dari replika utama N1 ke replika sekunder N2, ikuti langkah-langkah berikut:

Pada replika sekunder (N2), mulai failover paksa:

ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Pada replika utama baru (N2), hapus primer asli (N1):

ALTER AVAILABILITY GROUP [AGRScale]
REMOVE REPLICA ON N'N1';

Validasi bahwa semua lalu lintas aplikasi diarahkan ke pendengar dan/atau replika utama baru.
Jika primer asli (N1) online, segera ambil grup ketersediaan AGRScale offline pada primer asli (N1):
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
Jika ada data atau perubahan yang tidak disinkronkan, pertahankan data ini melalui cadangan atau opsi replikasi data lain yang sesuai dengan kebutuhan bisnis Anda.
Selanjutnya, hapus grup ketersediaan dari primer asli (N1):
```
DROP AVAILABILITY GROUP [AGRScale];
```
Hilangkan database grup ketersediaan pada replika utama asli (N1):
```
USE [master]
GO
DROP DATABASE [AGDBRScale]
GO
```
(Opsional) Jika diinginkan, Anda sekarang dapat menambahkan N1 kembali sebagai replika sekunder baru ke grup ketersediaan AGRScale.

Perhatikan bahwa jika Anda menggunakan pendengar untuk terhubung, Anda harus membuat ulang pendengar setelah melakukan failover.

Mengonfigurasi skala baca untuk grup ketersediaan Always On

Prasyarat

Aktifkan Grup Ketersediaan AlwaysOn dan hidupkan ulang mssql-server

Mengaktifkan sesi peristiwa AlwaysOn_health

Autentikasi titik akhir pencerminan database

Akun layanan

Autentikasi sertifikat

Membuat sertifikat di server sekunder

Membuat titik akhir pencerminan database di semua replika

Membuat grup ketersediaan

Menggabungkan instans SQL Server sekunder ke grup ketersediaan

Menambahkan database ke Grup Ketersediaan

Memverifikasi bahwa database dibuat di server sekunder

Koneksi ke replika sekunder baca-saja

Failover replika utama pada grup ketersediaan skala baca

Failover manual tanpa kehilangan data

Failover manual paksa dengan kehilangan data

Langkah berikutnya

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

Mengonfigurasi skala baca untuk grup ketersediaan Always On

Prasyarat

Aktifkan Grup Ketersediaan AlwaysOn dan hidupkan ulang mssql-server

Mengaktifkan sesi peristiwa AlwaysOn_health

Autentikasi titik akhir pencerminan database

Akun layanan

Autentikasi masuk SQL

Autentikasi sertifikat

Membuat sertifikat di server sekunder

Membuat titik akhir pencerminan database di semua replika

Membuat grup ketersediaan

Menggabungkan instans SQL Server sekunder ke grup ketersediaan

Menambahkan database ke Grup Ketersediaan

Memverifikasi bahwa database dibuat di server sekunder

Koneksi ke replika sekunder baca-saja

Failover replika utama pada grup ketersediaan skala baca

Failover manual tanpa kehilangan data

Failover manual paksa dengan kehilangan data

Langkah berikutnya

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan: