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 bagi sysadmin, 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 akan menggunakan replikasi snapshot untuk menjaga agar sumber dan target tetap sinkron. Replikasi snapshot mengirimkan salinan lengkap setiap artikel—yaitu objek database yang ditayangkan—kepada pelanggan. Cloud SQL juga mendukung replikasi transaksional, yang hanya mengirim data inkremental. Keterbatasan replikasi transaksional adalah tabel harus memiliki kunci utama. Untuk mengetahui informasi selengkapnya tentang jenis-jenis replikasi SQL Server, lihat dokumentasi SQL Server.

Untuk memudahkan, tutorial ini menggunakan SQL Server sumber untuk menghosting Distributor. Dalam skenario produksi, jika data direplikasi dari luar Google Cloud, sebaiknya Anda menghosting distribusi di sisi Google Cloud pada instance Compute Engine.

Catatan: Cloud SQL untuk SQL Server tidak mendukung pengiriman log, duplikasi 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 contoh.
• Mengisi database contoh.
• Membuat instance Cloud SQL untuk SQL Server.
• Membuat Distributor.
• Menyiapkan publikasi dan langganan.
• Memulai replikasi dari SQL Server ke Cloud SQL.
• Memvalidasi data yang telah diimpor.

Biaya

Dalam dokumen ini, Anda akan menggunakan komponen Google Cloudyang dapat ditagih berikut:

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

Untuk membuat perkiraan biaya berdasarkan proyeksi penggunaan Anda, gunakan kalkulator harga.

Pengguna Google Cloud baru mungkin memenuhi syarat untuk mendapatkan uji coba gratis.

Sebelum memulai

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.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL Admin and Compute Engine API APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

Enable the APIs

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL Admin and Compute Engine API APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

Enable the APIs

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. Instal klien Remote Desktop Protocol (RDP) pilihan Anda. Untuk mengetahui informasi selengkapnya, lihat Klien Desktop Jarak Jauh Microsoft. 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, baca bagian Pembersihan.

Membuat VM SQL Server

Langkah pertama adalah membuat instance SQL Server 2017 di VM Windows 2016 pada Compute Engine dan membuat koneksi ke sana melalui 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 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 VM instances.

   Buka Instance VM

4. Di bagian Compute Engine pada Konsol Google Cloud , klik menu dropdown RDP, lalu pilih opsi Download the RDP file untuk mendownload file RDP instance Anda.

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

5. Di kolom username dan password, 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 Continue.

7. Dalam instance, minimalkan semua jendela, klik Start di taskbar Windows, ketik PowerShell, lalu klik kanan aplikasi Windows PowerShell dan pilih Run as administrator.

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

   mkdir c:\sql-server-data\adventureworks
   

Membuat dan mengisi contoh database

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 mengkueri 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 di 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 akan 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, ubah nama instance lokal SQL Server agar sama 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 error Cannot open MSSQLServer service on computer '.'. ditampilkan, Anda mungkin tidak menjalankan command line PowerShell sebagai administrator.

2. Pastikan 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. Konfigurasi 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 yang ditayangkan.

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

   Set-Service -Name SQLServerAgent -StartupType Automatic
   Start-Service -Name SQLServerAgent
   

2. Buat penayangan 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. Konfigurasi penayangan agar mengirim snapshot sumber ke Cloud SQL satu jam sekali:

   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 penayangan. Meskipun database AdventureWorksLT2017 berisi banyak tabel, untuk memudahkan dalam tutorial ini, Anda akan 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 Anda tidak melakukannya, akan perlu waktu sekitar satu jam hingga scheduler membuat snapshot awal.

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

Menyiapkan langganan

Sekarang Anda akan membuat langganan yang mengirimkan data yang ditayangkan ke Cloud SQL.

1. Dari command line PowerShell, buat langganan untuk mengirim snapshot penayangan 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. Pastikan bahwa 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 tidak lagi menggunakan kuota dan menimbulkan tagihan. Bagian berikut menjelaskan cara menghapus atau menonaktifkan resource ini.

Menghapus project

Perhatian: Penghapusan project memiliki efek berikut:
• Semua isi project akan dihapus. Jika Anda menggunakan project Anda sendiri untuk mengerjakan tugas dalam dokumen ini, saat Anda menghapusnya, pekerjaan lain dalam project tersebut juga akan dihapus.
• Project ID kustom akan hilang. Saat membuat project ini, Anda mungkin juga membuat project ID kustom yang masih ingin Anda gunakan pada masa mendatang. Agar tidak kehilangan URL yang menggunakan project ID tersebut, seperti URL appspot.com, hapus resource yang dipilih di dalam project, bukan menghapus seluruh project.
1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

Langkah berikutnya

• 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.