Bagikan melalui


Membuat grup ketersediaan AlwaysOn menggunakan Transact-SQL (T-SQL)

Berlaku untuk: SQL Server

Topik ini menjelaskan cara menggunakan Transact-SQL untuk membuat dan mengonfigurasi grup ketersediaan pada instans SQL Server tempat fitur grup ketersediaan AlwaysOn diaktifkan. Grup ketersediaan mendefinisikan sekumpulan database pengguna yang akan gagal sebagai satu unit dan sekumpulan mitra failover, yang dikenal sebagai replika ketersediaan, yang mendukung failover.

Catatan

Untuk pengenalan grup ketersediaan, lihat Gambaran Umum Grup Ketersediaan AlwaysOn (SQL Server).

Catatan

Sebagai alternatif untuk menggunakan Transact-SQL, Anda dapat menggunakan wizard Buat Grup Ketersediaan atau cmdlet PowerShell SQL Server. Untuk informasi selengkapnya, lihat Menggunakan Wizard Grup Ketersediaan (SQL Server Management Studio), Menggunakan Kotak Dialog Grup Ketersediaan Baru (SQL Server Management Studio), atau Membuat Grup Ketersediaan (SQL Server PowerShell).

Prasyarat, Pembatasan, dan Rekomendasi

  • Sebelum membuat grup ketersediaan, verifikasi bahwa instans SQL Server yang menghosting replika ketersediaan berada di node Windows Server Failover Clustering (WSFC) yang berbeda dalam kluster failover WSFC yang sama. Selain itu, verifikasi bahwa setiap instans server memenuhi semua prasyarat grup ketersediaan AlwaysOn lainnya. Untuk informasi selengkapnya, kami sangat menyarankan Anda membaca Prasyarat, Pembatasan, dan Rekomendasi untuk Grup Ketersediaan AlwaysOn (SQL Server).

Izin

Memerlukan keanggotaan dalam peran server tetap sysadmin dan izin BUAT server GRUP KETERSEDIAAN, UBAH izin GRUP KETERSEDIAAN APA PUN, atau izin SERVER KONTROL.

Menggunakan Transact-SQL untuk Membuat dan Mengonfigurasi Grup Ketersediaan

Ringkasan Tugas dan Pernyataan Transact-SQL yang Sesuai

Tabel berikut mencantumkan tugas dasar yang terlibat dalam membuat dan mengonfigurasi grup ketersediaan dan menunjukkan pernyataan Transact-SQL mana yang akan digunakan untuk tugas-tugas ini. Tugas grup ketersediaan AlwaysOn harus dilakukan secara berurutan di mana tugas tersebut disajikan dalam tabel.

Tugas Pernyataan T-SQL Tempat Melakukan Tugas*****
Membuat titik akhir pencerminan database (sekali per instans SQL Server) CREATE ENDPOINT endpointName ... UNTUK DATABASE_MIRRORING Jalankan pada setiap instans server yang tidak memiliki titik akhir pencerminan database.
Membuat grup ketersediaan MEMBUAT GRUP KETERSEDIAAN Jalankan pada instans server yang akan menghosting replika utama awal.
Bergabunglah dengan replika sekunder ke grup ketersediaan UBAH GABUNGAN GROUP_NAME GRUP KETERSEDIAAN Jalankan pada setiap instans server yang menghosting replika sekunder.
Menyiapkan database sekunder PENCADANGAN dan PEMULIHAN. Buat cadangan pada instans server yang menghosting replika utama.

Pulihkan cadangan pada setiap instans server yang menghosting replika sekunder, menggunakan RESTORE WITH NORECOVERY.
Mulai sinkronisasi data dengan menggabungkan setiap database sekunder ke grup ketersediaan ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = group_name Jalankan pada setiap instans server yang menghosting replika sekunder.

*Untuk melakukan tugas tertentu, sambungkan ke instans atau instans server yang ditunjukkan.

Menggunakan T-SQL

Catatan

Untuk contoh prosedur konfigurasi yang berisi contoh kode dari setiap pernyataan T-SQL ini, lihat Contoh: Mengonfigurasi Grup Ketersediaan yang Menggunakan Autentikasi Windows.

  1. Sambungkan ke instans server yang akan menghosting replika utama.

  2. Buat grup ketersediaan dengan menggunakan pernyataan CREATE AVAILABILITY GROUPTransact-SQL.

  3. Bergabunglah dengan replika sekunder baru ke grup ketersediaan. Untuk informasi selengkapnya, lihat Menggabungkan Replika Sekunder ke Grup Ketersediaan (SQL Server).

  4. Untuk setiap database dalam grup ketersediaan, buat database sekunder dengan memulihkan cadangan terbaru dari database utama, menggunakan RESTORE WITH NORECOVERY. Untuk informasi selengkapnya, lihat Contoh: Menyiapkan Grup Ketersediaan Menggunakan Autentikasi Windows (Transact-SQL), dimulai dengan langkah yang memulihkan cadangan database.

  5. Gabungkan setiap database sekunder baru ke grup ketersediaan. Untuk informasi selengkapnya, lihat Menggabungkan Replika Sekunder ke Grup Ketersediaan (SQL Server).

Contoh: Mengonfigurasi Grup Ketersediaan yang Menggunakan Autentikasi Windows

Contoh ini membuat sampel prosedur konfigurasi grup ketersediaan AlwaysOn yang menggunakan Transact-SQL untuk menyiapkan titik akhir pencerminan database yang menggunakan Autentikasi Windows dan untuk membuat dan mengonfigurasi grup ketersediaan dan database sekundernya.

Contoh ini berisi bagian berikut:

Prasyarat untuk Menggunakan Prosedur Konfigurasi Sampel

Prosedur sampel ini memiliki persyaratan berikut:

  • Instans server harus mendukung grup ketersediaan AlwaysOn. Untuk informasi selengkapnya, lihat Prasyarat, Pembatasan, dan Rekomendasi untuk Grup Ketersediaan AlwaysOn (SQL Server).

  • Dua database sampel, MyDb1 dan MyDb2, harus ada pada instans server yang akan menghosting replika utama. Contoh kode berikut membuat dan mengonfigurasi kedua database ini dan membuat cadangan lengkap masing-masing. Jalankan contoh kode ini pada instans server tempat Anda ingin membuat grup ketersediaan sampel. Instans server ini akan menghosting replika utama awal dari grup ketersediaan sampel.

    1. Contoh Transact-SQL berikut membuat database ini dan mengubahnya untuk menggunakan model pemulihan penuh:

      -- Create sample databases:  
      CREATE DATABASE MyDb1;  
      GO  
      ALTER DATABASE MyDb1 SET RECOVERY FULL;  
      GO  
      
      CREATE DATABASE MyDb2;  
      GO  
      ALTER DATABASE MyDb2 SET RECOVERY FULL;  
      GO  
      
    2. Contoh kode berikut membuat cadangan database lengkap MyDb1 dan MyDb2. Contoh kode ini menggunakan berbagi cadangan fiktif, \\FILESERVER\SQLbackups.

      -- Backup sample databases:  
      BACKUP DATABASE MyDb1   
      TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
          WITH FORMAT;  
      GO  
      
      BACKUP DATABASE MyDb2   
      TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
          WITH FORMAT;  
      GO  
      

[TopOfExample]

Contoh Prosedur Konfigurasi

Dalam konfigurasi sampel ini, replika ketersediaan akan dibuat pada dua instans server mandiri yang akun layanannya berjalan di bawah yang berbeda, tetapi tepercaya, domain (DOMAIN1 dan DOMAIN2).

Tabel berikut ini meringkas nilai yang digunakan dalam konfigurasi sampel ini.

Peran awal Sistem Host Instans SQL Server
Primer COMPUTER01 AgHostInstance
Sekunder COMPUTER02 Instans default.
  1. Buat titik akhir pencerminan database bernama dbm_endpoint pada instans server tempat Anda berencana untuk membuat grup ketersediaan (ini adalah instans bernama AgHostInstance pada COMPUTER01). Titik akhir ini menggunakan port 7022. Perhatikan bahwa instans server tempat Anda membuat grup ketersediaan akan menghosting replika utama.

    -- Create endpoint on server instance that hosts the primary replica:  
    CREATE ENDPOINT dbm_endpoint  
        STATE=STARTED   
        AS TCP (LISTENER_PORT=7022)   
        FOR DATABASE_MIRRORING (ROLE=ALL);  
    GO  
    
  2. Buat titik akhir dbm_endpoint pada instans server yang akan menghosting replika sekunder (ini adalah instans server default pada COMPUTER02). Titik akhir ini menggunakan port 5022.

    -- Create endpoint on server instance that hosts the secondary replica:   
    CREATE ENDPOINT dbm_endpoint  
        STATE=STARTED   
        AS TCP (LISTENER_PORT=5022)   
        FOR DATABASE_MIRRORING (ROLE=ALL);  
    GO  
    
  3. Catatan

    Jika akun layanan instans server yang menghosting replika ketersediaan Anda berjalan di bawah akun domain yang sama, langkah ini tidak perlu. Lewati dan langsung ke langkah berikutnya.

    Jika akun layanan instans server berjalan di bawah pengguna domain yang berbeda, pada setiap instans server, buat login untuk instans server lain dan berikan izin masuk ini untuk mengakses titik akhir pencerminan database lokal.

    Contoh kode berikut menunjukkan pernyataan Transact-SQL untuk membuat login dan memberikannya izin pada titik akhir. Akun domain instans server jarak jauh diwakili di sini sebagai domain_name\user_name.

    -- If necessary, create a login for the service account, domain_name\user_name  
    -- of the server instance that will host the other replica:  
    USE master;  
    GO  
    CREATE LOGIN [domain_name\user_name] FROM WINDOWS;  
    GO  
    -- And Grant this login connect permissions on the endpoint:  
    GRANT CONNECT ON ENDPOINT::dbm_endpoint   
       TO [domain_name\user_name];  
    GO  
    
  4. Pada instans server tempat database pengguna berada, buat grup ketersediaan.

    Contoh kode berikut membuat grup ketersediaan bernama MyAG pada instans server tempat database sampel, MyDb1 dan MyDb2, dibuat. Instans server lokal, AgHostInstance, pada COMPUTER01 ditentukan terlebih dahulu. Instans ini akan menghosting replika utama awal. Instans server jarak jauh, instans server default pada COMPUTER02, ditentukan untuk menghosting replika sekunder. Kedua replika ketersediaan dikonfigurasi untuk menggunakan mode penerapan asinkron dengan failover manual (untuk failover manual replika penerapan asinkron berarti failover paksa dengan kemungkinan kehilangan data).

    -- Create the availability group, MyAG:   
    CREATE AVAILABILITY GROUP MyAG   
       FOR   
          DATABASE MyDB1, MyDB2   
       REPLICA ON   
          'COMPUTER01\AgHostInstance' WITH   
             (  
             ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',   
             AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
             FAILOVER_MODE = MANUAL  
             ),  
          'COMPUTER02' WITH   
             (  
             ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:5022',  
             AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
             FAILOVER_MODE = MANUAL  
             );   
    GO  
    

    Untuk contoh kode Transact-SQL tambahan dalam membuat grup ketersediaan, lihat MEMBUAT GRUP KETERSEDIAAN (Transact-SQL).

  5. Pada instans server yang menghosting replika sekunder, gabungkan replika sekunder ke grup ketersediaan.

    Contoh kode berikut menggabungkan replika COMPUTER02 sekunder ke MyAG grup ketersediaan.

    -- On the server instance that hosts the secondary replica,   
    -- join the secondary replica to the availability group:  
    ALTER AVAILABILITY GROUP MyAG JOIN;  
    GO  
    
  6. Pada instans server yang menghosting replika sekunder, buat database sekunder.

    Contoh kode berikut membuat database sekunder MyDb1 dan MyDb2 dengan memulihkan cadangan database menggunakan RESTORE WITH NORECOVERY.

    -- On the server instance that hosts the secondary replica,   
    -- Restore database backups using the WITH NORECOVERY option:  
    RESTORE DATABASE MyDb1   
        FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
        WITH NORECOVERY;  
    GO  
    
    RESTORE DATABASE MyDb2   
        FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
        WITH NORECOVERY;  
    GO 
    
  7. Pada instans server yang menghosting replika utama, cadangkan log transaksi pada setiap database utama.

    Penting

    Saat Anda mengonfigurasi grup ketersediaan nyata, kami sarankan, sebelum mengambil cadangan log ini, Anda menangguhkan tugas pencadangan log untuk database utama Anda sampai Anda telah bergabung dengan database sekunder yang sesuai ke grup ketersediaan.

    Contoh kode berikut membuat cadangan log transaksi di MyDb1 dan di MyDb2.

    -- On the server instance that hosts the primary replica,   
    -- Backup the transaction log on each primary database:  
    BACKUP LOG MyDb1   
    TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
        WITH NOFORMAT;  
    GO  
    
    BACKUP LOG MyDb2   
    TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
        WITH NOFORMAT;  
    GO
    

    Tip

    Biasanya, cadangan log harus diambil pada setiap database utama dan kemudian dipulihkan pada database sekunder yang sesuai (menggunakan WITH NORECOVERY). Namun, cadangan log ini mungkin tidak perlu jika database baru saja dibuat dan belum ada cadangan log yang diambil atau model pemulihan baru saja diubah dari SIMPLE ke FULL.

  8. Pada instans server yang menghosting replika sekunder, terapkan cadangan log ke database sekunder.

    Contoh kode berikut menerapkan pencadangan ke database sekunder MyDb1 dan MyDb2 dengan memulihkan cadangan database menggunakan RESTORE WITH NORECOVERY.

    Penting

    Saat Anda menyiapkan database sekunder nyata, Anda perlu menerapkan setiap cadangan log yang diambil sejak cadangan database tempat Anda membuat database sekunder, dimulai dengan yang paling awal dan selalu menggunakan RESTORE WITH NORECOVERY. Tentu saja, jika Anda memulihkan cadangan database penuh dan diferensial, Anda hanya perlu menerapkan cadangan log yang diambil setelah pencadangan diferensial.

    -- Restore the transaction log on each secondary database,  
    -- using the WITH NORECOVERY option:  
    RESTORE LOG MyDb1   
        FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
        WITH FILE=1, NORECOVERY;  
    GO  
    RESTORE LOG MyDb2   
        FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
        WITH FILE=1, NORECOVERY;  
    GO  
    
  9. Pada instans server yang menghosting replika sekunder, gabungkan database sekunder baru ke grup ketersediaan.

    Contoh kode berikut, menggabungkan database sekunder MyDb1 lalu database sekunder MyDb2 ke grup ketersediaan MyAG .

    -- On the server instance that hosts the secondary replica,   
    -- join each secondary database to the availability group:  
    ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
    GO  
    
    ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
    GO  
    

Contoh Kode Lengkap untuk Prosedur Konfigurasi Sampel

Contoh berikut menggabungkan contoh kode dari semua langkah prosedur konfigurasi sampel. Tabel berikut ini meringkas nilai tempat penampung yang digunakan dalam contoh kode ini. Untuk informasi selengkapnya tentang langkah-langkah dalam contoh kode ini, lihat Prasyarat untuk Menggunakan Prosedur Konfigurasi Sampel dan Prosedur Konfigurasi Sampel, sebelumnya dalam topik ini.

Placeholder Deskripsi
\\FILESERVER\SQLbackups Berbagi cadangan fiktif.
\\FILESERVER\SQLbackups\MyDb1.bak File cadangan untuk MyDb1.
\\FILESERVER\SQLbackups\MyDb2.bak File cadangan untuk MyDb2.
7022 Nomor port yang ditetapkan ke setiap titik akhir pencerminan database.
COMPUTER01\AgHostInstance Instans server yang menghosting replika utama awal.
COMPUTER02 Instans server yang menghosting replika sekunder awal. Ini adalah instans server default di COMPUTER02.
dbm_endpoint Nama yang ditentukan untuk setiap titik akhir pencerminan database.
MyAG Nama grup ketersediaan sampel.
MyDb1 Nama database sampel pertama.
MyDb2 Nama database sampel kedua.
DOMAIN1\user1 Akun layanan instans server yang menghosting replika utama awal.
DOMAIN2\user2 Akun layanan instans server yang menghosting replika sekunder awal.
TCP:// COMPUTER01.Adventure-Works.com:7022 URL titik akhir instans AgHostInstance dari SQL Server di COMPUTER01.
TCP:// COMPUTER02.Adventure-Works.com:5022 URL titik akhir instans default SQL Server di COMPUTER02.

Catatan

Untuk contoh kode Transact-SQL tambahan dalam membuat grup ketersediaan, lihat MEMBUAT GRUP KETERSEDIAAN (Transact-SQL).

-- on the server instance that will host the primary replica,   
-- create sample databases:  
CREATE DATABASE MyDb1;  
GO  
ALTER DATABASE MyDb1 SET RECOVERY FULL;  
GO  
  
CREATE DATABASE MyDb2;  
GO  
ALTER DATABASE MyDb2 SET RECOVERY FULL;  
GO  
  
-- Backup sample databases:  
BACKUP DATABASE MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FORMAT;  
GO  
  
BACKUP DATABASE MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FORMAT;  
GO  
  
-- Create the endpoint on the server instance that will host the primary replica:  
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  
  
-- Create the endpoint on the server instance that will host the secondary replica:   
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the primary replica,   
-- create a login for the service account   
-- of the server instance that will host the secondary replica, DOMAIN2\user2,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
CREATE LOGIN [DOMAIN2\user2] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN2\user2];  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the secondary replica,  
-- create a login for the service account   
-- of the server instance that will host the primary replica, DOMAIN1\user1,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
  
CREATE LOGIN [DOMAIN1\user1] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN1\user1];  
GO  
  
-- On the server instance that will host the primary replica,   
-- create the availability group, MyAG:  
CREATE AVAILABILITY GROUP MyAG   
   FOR   
      DATABASE MyDB1, MyDB2   
   REPLICA ON   
      'COMPUTER01\AgHostInstance' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         ),  
      'COMPUTER02' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         );   
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join the secondary replica to the availability group:  
ALTER AVAILABILITY GROUP MyAG JOIN;  
GO  
  
-- Restore database backups onto this server instance, using RESTORE WITH NORECOVERY:  
RESTORE DATABASE MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NORECOVERY;  
GO  
  
RESTORE DATABASE MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NORECOVERY;  
GO  
  
-- Back up the transaction log on each primary database:  
BACKUP LOG MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NOFORMAT;  
GO  
  
BACKUP LOG MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NOFORMAT  
GO  
  
-- Restore the transaction log on each secondary database,  
-- using the WITH NORECOVERY option:  
RESTORE LOG MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
RESTORE LOG MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join each secondary database to the availability group:  
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
GO  
  
ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
GO  

Tugas Terkait

Untuk mengonfigurasi grup ketersediaan dan properti replika

Untuk menyelesaikan konfigurasi grup ketersediaan

Cara alternatif untuk membuat grup ketersediaan

Untuk mengaktifkan Grup Ketersediaan AlwaysOn

Untuk mengonfigurasi titik akhir pencerminan database

Untuk memecahkan masalah konfigurasi Grup Ketersediaan AlwaysOn

Konten Terkait

Lihat Juga

Titik Akhir Pencerminan Database (SQL Server)
Gambaran Umum Grup Ketersediaan AlwaysOn (SQL Server)
Listener Grup Ketersediaan, Konektivitas Klien, dan Kegagalan Aplikasi (SQL Server)
Prasyarat, Pembatasan, dan Rekomendasi untuk Grup Ketersediaan AlwaysOn (SQL Server)