Memigrasikan data dari SQL Server 2017 ke Cloud SQL untuk SQL Server menggunakan replikasi snapshot


Tutorial ini menunjukkan cara memigrasikan data dari Microsoft SQL Server 2017 Enterprise yang berjalan di Compute Engine ke Cloud SQL untuk SQL Server 2017 Enterprise. Tutorial ini berguna jika Anda merupakan admin sistem, developer, engineer, admin database, atau engineer DevOps. Tutorial ini menunjukkan cara menyiapkan Distributor SQL Server, menggunakan Agen SQL Server untuk replikasi snapshot ke Cloud SQL untuk SQL Server 2017, dan memvalidasi bahwa data berhasil diimpor.

Dalam tutorial ini, Anda menggunakan replikasi snapshot untuk menjaga sumber dan target tetap sinkron. Replikasi snapshot mengirimkan salinan lengkap setiap artikel kepada pelanggan—yaitu, objek database yang dipublikasikan. Cloud SQL juga mendukung replikasi transaksional, yang hanya mengirim data inkremental. Batasan replikasi transaksional adalah bahwa tabel harus memiliki kunci utama. Untuk mengetahui informasi selengkapnya tentang jenis replikasi SQL Server, lihat dokumentasi SQL Server.

Agar lebih mudah, tutorial ini menggunakan SQL Server sumber untuk menghosting Distributor. Dalam skenario produksi, jika data direplikasi dari luar Google Cloud, Anda mungkin lebih memilih untuk menghosting distribusi di sisi Google Cloud pada instance Compute Engine.

Tutorial ini mengasumsikan bahwa Anda sudah memahami hal-hal berikut:

Tujuan

  • Membuat instance virtual machine (VM) SQL Server di Compute Engine untuk menghosting database sampel.
  • Mengisi contoh database.
  • Membuat instance Cloud SQL untuk SQL Server.
  • Membuat Distributor.
  • Menyiapkan publikasi dan langganan.
  • Memulai replikasi dari SQL Server ke Cloud SQL.
  • Memvalidasi data yang diimpor.

Biaya

Dalam dokumen ini, Anda menggunakan komponen Google Cloud yang dapat ditagih berikut:

Untuk membuat perkiraan biaya berdasarkan proyeksi penggunaan Anda, gunakan kalkulator harga. Pengguna baru Google Cloud mungkin memenuhi syarat untuk mendapatkan uji coba gratis.

Sebelum memulai

  1. Login ke akun Google Cloud Anda. Jika Anda baru menggunakan Google Cloud, buat akun untuk mengevaluasi performa produk kami dalam skenario dunia nyata. Pelanggan baru juga mendapatkan kredit gratis senilai $300 untuk menjalankan, menguji, dan men-deploy workload.
  2. Di konsol Google Cloud, pada halaman pemilih project, pilih atau buat project Google Cloud.

    Buka pemilih project

  3. Pastikan penagihan telah diaktifkan untuk project Google Cloud Anda.

  4. Aktifkan API Cloud SQL Admin and Compute Engine API.

    Mengaktifkan API

  5. Di konsol Google Cloud, pada halaman pemilih project, pilih atau buat project Google Cloud.

    Buka pemilih project

  6. Pastikan penagihan telah diaktifkan untuk project Google Cloud Anda.

  7. Aktifkan API Cloud SQL Admin and Compute Engine API.

    Mengaktifkan API

  8. Di konsol Google Cloud, aktifkan Cloud Shell.

    Aktifkan Cloud Shell

    Di bagian bawah Google Cloud Console, Cloud Shell sesi akan terbuka dan menampilkan perintah command line. Cloud Shell adalah lingkungan shell dengan Google Cloud CLI yang sudah terinstal, dan dengan nilai yang sudah ditetapkan untuk project Anda saat ini. Diperlukan waktu beberapa detik untuk melakukan inisialisasi sesi.

  9. Instal klien Remote Desktop Protocol (RDP) pilihan Anda. Untuk informasi selengkapnya, lihat Klien Microsoft Desktop Jarak Jauh. Jika klien RDP sudah terinstal, Anda dapat melewati tugas ini.

Setelah menyelesaikan tugas yang dijelaskan dalam dokumen ini, Anda dapat menghindari penagihan berkelanjutan dengan menghapus resource yang Anda buat. Untuk mengetahui informasi selengkapnya, lihat Pembersihan.

Membuat SQL Server VM

Langkah pertama adalah membuat instance SQL Server 2017 pada VM Windows 2016 di Compute Engine dan menghubungkannya menggunakan RDP.

  1. Di Cloud Shell, buat instance SQL Server 2017 Standard di Windows Server 2016. Database sumber harus berupa database SQL Server Standard atau Enterprise karena tingkat yang lebih rendah tidak memiliki kemampuan penayang database.

    gcloud compute instances create sqlserver --machine-type=n1-standard-4 \
        --boot-disk-size=100GB \
        --image-project=windows-sql-cloud \
        --image-family=sql-std-2017-win-2016 \
        --zone=us-central1-f  \
        --scopes=https://www.googleapis.com/auth/cloud-platform
    

    Untuk tutorial ini, Anda membuat instance di zona us-central1-f dengan ukuran boot disk sebesar 100 GB. Untuk mengetahui informasi selengkapnya, lihat Lokasi Cloud.

  2. Buat sandi Windows:

    gcloud compute reset-windows-password sqlserver --zone=us-central1-f
    
  3. Di konsol Google Cloud, buka halaman instance VM.

    Buka Instance VM

  4. Di bagian Compute Engine konsol Google Cloud, klik menu dropdown RDP dan pilih opsi Download file RDP untuk mendownload file RDP untuk instance Anda.

    Gunakan file ini untuk terhubung ke instance menggunakan klien RDP. Untuk informasi selengkapnya, lihat Klien Microsoft Desktop Jarak Jauh.

  5. Di kolom nama pengguna dan sandi, masukkan nama pengguna dan sandi yang Anda buat untuk instance VM SQL Server. Biarkan kolom Domain kosong, lalu klik OK untuk terhubung ke VM SQL Server.

    Screenshot dialog tempat Anda memasukkan nama pengguna dan sandi.

    Saat diminta, terima sertifikat.

  6. Jika Anda menyetujui persyaratan, klik Lanjutkan.

  7. Dalam instance, minimalkan semua jendela, klik Mulai pada taskbar Windows, ketik PowerShell, lalu klik kanan aplikasi Windows PowerShell dan pilih Jalankan sebagai administrator.

  8. Pada perintah PowerShell, buat struktur direktori untuk penyimpanan database:

    mkdir c:\sql-server-data\adventureworks
    

Membuat dan mengisi database sampel

Sekarang Anda mendownload file cadangan database AdventureWorks Microsoft dan memulihkannya ke dalam instance SQL Server. Database ini menyimulasikan database produksi yang ingin Anda migrasikan.

  1. Dari command line PowerShell, download file cadangan AdventureWorksLT2017.bak ke drive C:

    bitsadmin /transfer sampledb /dynamic /download /priority FOREGROUND "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksLT2017.bak " C:\sql-server-data\AdventureWorksLT2017.bak
    
  2. Pulihkan database:

    osql -E -Q "USE [master]
    RESTORE DATABASE [AdventureWorksLT2017]
    FROM  DISK = N'C:\sql-server-data\AdventureWorksLT2017.bak' WITH  FILE = 1,
    MOVE N'AdventureWorksLT2012_Data' TO N'C:\sql-server-data\adventureworks\AdventureWorksLT2012.mdf',
    MOVE N'AdventureWorksLT2012_Log' TO N'C:\sql-server-data\adventureworks\AdventureWorksLT2012_log.ldf',
    NOUNLOAD,  STATS = 5"
    
  3. Validasi database yang baru dipulihkan dengan membuat kueri jumlah baris dalam tabel Customer. Outputnya adalah 847 baris.

    osql -E -Q "select count(*) from AdventureWorksLT2017.SalesLT.Customer"
    

Menyiapkan instance Cloud SQL

  1. Di Cloud Shell, buat instance Cloud SQL untuk SQL Server 2017 Enterprise:

    gcloud sql instances create target-sqlserver  \
        --database-version=SQLSERVER_2017_STANDARD \
        --cpu=4 \
        --memory=15 \
        --storage-size=100 \
        --root-password=sqlserver12@ \
        --zone=us-central1-f
    

    Pengguna root adalah sqlserver dengan sandi sqlserver12@. Catat juga alamat IP SQL Server.

  2. Simpan alamat IP VM SQL Server sumber dalam variabel lingkungan:

    sql_source_ip=$(gcloud compute instances describe sqlserver \
        --zone=us-central1-f | grep natIP | awk '{print $2}')
    echo $sql_source_ip
    
  3. Tambahkan alamat IP VM SQL Server dengan instance Cloud SQL ke daftar yang diizinkan (daftar yang disetujui):

    gcloud sql instances patch target-sqlserver --authorized-networks=$sql_source_ip
    
  4. Buat database tujuan:

    gcloud sql databases create AdventureWorksTarget --instance=target-sqlserver
    
  5. Di command line PowerShell yang Anda luncurkan sebagai administrator, periksa konektivitas ke Cloud SQL untuk SQL Server:

    $cloud_sql_server_ip=gcloud sql instances describe target-sqlserver --format='value(ipAddresses.ipAddress)'
    osql -S $cloud_sql_server_ip -U sqlserver -P sqlserver12@ -Q "select 'test'"
    

    Perintah tersebut mencetak 'test'.

Memulai replikasi dari VM ke Cloud SQL

Dengan menggunakan replikasi snapshot SQL Server, Anda membuat tugas untuk mereplikasi snapshot database ke dalam Cloud SQL.

Menyiapkan Distributor

Sekarang Anda mengonfigurasi VM SQL Server untuk menjadi Distributor SQL Server. Dalam praktiknya, Distributor dapat berjalan di mesin terpisah, tetapi dalam tutorial ini, Anda menjalankannya di VM yang sama.

  1. Dari command line PowerShell yang Anda luncurkan sebagai administrator, update nama instance lokal SQL Server agar cocok dengan nama host VM:

    $servername=hostname
    osql -E -Q "sp_dropserver 'INST-INSTALL-SQ';"
    osql -E -Q "sp_addserver '$servername', local;"
    Restart-Service -F MSSQLServer
    mkdir c:\sql-server-data\repldata
    

    Jika Anda menerima error Cannot open MSSQLServer service on computer '.'., Anda mungkin tidak menjalankan command line PowerShell sebagai administrator.

  2. Verifikasi bahwa nama instance sekarang menjadi "sqlserver":

    osql -E -Q "select @@servername;"
    
  3. Tentukan database distribusi:

    $servername=hostname
    osql -E -Q "use master; exec sp_adddistributor @distributor = N'$servername', @password = N'';"
    osql -E -Q "exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1"
    
  4. Konfigurasikan database distribusi:

    osql -E -Q "
    use [distribution]
    if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
        create table UIProperties(id int)
    if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
        exec sp_updateextendedproperty N'SnapshotFolder', N'c:\sql-server-data\repldata', 'user', dbo, 'table', 'UIProperties'
    else
        exec sp_addextendedproperty N'SnapshotFolder', N'c:\sql-server-data\repldata', 'user', dbo, 'table', 'UIProperties'
    "
    
  5. Daftarkan nama VM SQL Server ke Distributor sebagai penayang:

    osql -E -Q "exec sp_adddistpublisher @publisher = N'$servername',
    @distribution_db = N'distribution', @security_mode = 1, @working_directory
    = N'c:\sql-server-data\repldata', @trusted = N'false', @thirdparty_flag =
    0, @publisher_type = N'MSSQLSERVER'"
    

Menyiapkan penayangan

Setelah distribusi disiapkan, Anda akan mengonfigurasi tabel mana yang dipublikasikan.

  1. Dari command line PowerShell, aktifkan dan mulai Agen SQL Server:

    Set-Service -Name SQLServerAgent -StartupType Automatic
    Start-Service -Name SQLServerAgent
    
  2. Buat publikasi untuk database AdventureWorksLT2017:

    $servername=hostname
    osql -E -Q "
    use [AdventureWorksLT2017]
    exec sp_replicationdboption @dbname = N'AdventureWorksLT2017', @optname = N'publish', @value = N'true'"
    
    osql -E -Q "use [AdventureWorksLT2017]
    exec sp_addpublication @publication = N'advn-pub3', @description = N'Snapshot publication of database ''AdventureWorksLT2017'' from Publisher ''$servername''.', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'snapshot', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1"
    
  3. Konfigurasikan publikasi untuk mengirim snapshot sumber ke Cloud SQL satu kali setiap jam:

    osql -E -Q "
    use [AdventureWorksLT2017]
    exec sp_addpublication_snapshot @publication = N'advn-pub3', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1"
    
  4. Tambahkan artikel (tabel) ke publikasi. Meskipun database AdventureWorksLT2017 berisi banyak tabel, untuk memudahkan tutorial ini, Anda mereplikasi tiga tabel: Address, Customer, dan CustomerAddress.

    osql -E -Q "
    use [AdventureWorksLT2017]
    exec sp_addarticle @publication = N'advn-pub3', @article = N'Address', @source_owner = N'SalesLT', @source_object = N'Address', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'Address', @destination_owner = N'SalesLT', @vertical_partition = N'false'
    "
    
    osql -E -Q "
    use [AdventureWorksLT2017]
    exec sp_addarticle @publication = N'advn-pub3', @article = N'Customer', @source_owner = N'SalesLT', @source_object = N'Customer', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'Customer', @destination_owner = N'SalesLT', @vertical_partition = N'false'
    "
    
    osql -E -Q "
    use [AdventureWorksLT2017]
    exec sp_addarticle @publication = N'advn-pub3', @article = N'CustomerAddress', @source_owner = N'SalesLT', @source_object = N'CustomerAddress', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'CustomerAddress', @destination_owner = N'SalesLT', @vertical_partition = N'false'
    "
    
  5. Buat snapshot artikel secara manual. Jika tidak, diperlukan waktu hingga satu jam sebelum scheduler membuat snapshot awal.

    osql -E -Q "use [AdventureWorksLT2017]
    exec sp_startpublication_snapshot @publication = N'advn-pub3'"
    

Menyiapkan langganan

Sekarang Anda membuat langganan yang mengirimkan data yang dipublikasikan ke Cloud SQL.

  1. Dari command line PowerShell, buat langganan untuk mengirim snapshot publikasi ke Cloud SQL:

    $cloud_sql_server_ip=gcloud sql instances describe target-sqlserver --format='value(ipAddresses.ipAddress)'
    $cloud_sql_user="sqlserver"
    $cloud_sql_password="sqlserver12@"
    $target_db_name="AdventureWorksTarget"
    osql -E -Q " use [AdventureWorksLT2017] exec sp_addsubscription @publication = N'advn-pub3', @subscriber = N'$cloud_sql_server_ip', @destination_db = N'$target_db_name', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
    
    exec sp_addpushsubscription_agent @publication = N'advn-pub3', @subscriber = N'$cloud_sql_server_ip', @subscriber_db = N'$target_db_name', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'$cloud_sql_user', @subscriber_password = N'$cloud_sql_password', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20200408, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor' "
    
  1. Periksa apakah data tersedia di instance Cloud SQL:

    osql -S $cloud_sql_server_ip -U $cloud_sql_user -P $cloud_sql_password -Q "select count(*) from [AdventureWorksTarget].[SalesLT].[CustomerAddress] UNION
    select count(*) from [AdventureWorksTarget].[SalesLT].[Customer]
    UNION
    Select count(*) from [AdventureWorksTarget].[SalesLT].[Address]"
    

    Perintah tersebut mencetak 417, 450, 847

Pembersihan

Setelah menyelesaikan tutorial, Anda dapat membersihkan resource yang dibuat agar resource tersebut berhenti menggunakan kuota dan dikenai biaya. Bagian berikut menjelaskan cara menghapus atau menonaktifkan resource ini.

Menghapus project

  1. Di konsol Google Cloud, buka halaman Manage resource.

    Buka Manage resource

  2. Pada daftar project, pilih project yang ingin Anda hapus, lalu klik Delete.
  3. Pada dialog, ketik project ID, lalu klik Shut down untuk menghapus project.

Langkah selanjutnya