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.

Catatan: Cloud SQL untuk SQL Server tidak mendukung pengiriman log, pencerminan database, atau fitur ini. Microsoft SQL Server tidak mendukung replikasi dari edisi Developer, Web, atau Express.

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

• SQL Server
• Microsoft PowerShell
• Compute Engine
• Cloud SQL untuk SQL Server

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:

• Compute Engine
• Cloud SQL
• Cloud Storage
• SQL Server (premium dengan Compute Engine)

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. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

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

   Catatan: Jika tidak berencana untuk menyimpan resource yang Anda buat dalam prosedur ini, buatlah project, bukan memilih project yang ada. Setelah menyelesaikan langkah ini, Anda dapat menghapus project, yang menghapus semua resource yang terkait dengan project ini.

   Buka pemilih project

3. Make sure that billing is enabled for your Google Cloud project.

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.

   Catatan: Jika tidak berencana untuk menyimpan resource yang Anda buat dalam prosedur ini, buatlah project, bukan memilih project yang ada. Setelah menyelesaikan langkah ini, Anda dapat menghapus project, yang menghapus semua resource yang terkait dengan project ini.

   Buka pemilih project

6. Make sure that billing is enabled for your Google Cloud project.

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.

   

   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

Perhatian: Menghapus project memiliki efek berikut:
• Semua hal dalam project akan dihapus. Jika menggunakan project yang sudah ada untuk tugas dalam dokumen ini, saat Anda menghapusnya, pekerjaan lain yang telah Anda lakukan dalam project tersebut juga akan terhapus.
• Project ID kustom hilang. Saat membuat project ini, Anda mungkin telah membuat project ID kustom yang ingin digunakan di masa mendatang. Untuk mempertahankan URL yang menggunakan project ID, seperti URL appspot.com, hapus resource yang dipilih di dalam project, bukan menghapus seluruh 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

• Pelajari cara memigrasikan data dari SQL Server 2017 ke Cloud SQL untuk SQL Server menggunakan file cadangan.
• Pelajari cara memigrasikan data dari SQL Server 2008 ke Cloud SQL untuk SQL Server menggunakan file cadangan.
• Pelajari arsitektur referensi, diagram, dan praktik terbaik tentang Google Cloud. Lihat Cloud Architecture Center kami.