Migrer des données depuis SQL Server 2017 vers Cloud SQL pour SQL Server à l'aide de la réplication des instantanés

Créer une VM SQL Server

La première étape consiste à créer une instance SQL Server 2017 sur une VM Windows 2016 sur Compute Engine et à s'y connecter à l'aide du protocole RDP.

1. Dans Cloud Shell, créez une instance SQL Server 2017 Standard sur Windows Server 2016. La base de données source doit être une base de données SQL Server Standard ou Enterprise, car les niveaux inférieurs n'ont pas la fonctionnalité d'éditeur de base de données.

   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
   

   Pour ce tutoriel, vous allez créer l'instance dans la zone us-central1-f avec une taille de disque de démarrage de 100 Go. Pour en savoir plus, consultez la section Emplacements Cloud.

2. Générez un mot de passe Windows :

   gcloud compute reset-windows-password sqlserver --zone=us-central1-f
   

3. Dans la console Google Cloud, accédez à la page Instances de VM.

   Accéder à la page "Instances de VM"

4. Dans la section Compute Engine de la console Google Cloud, cliquez sur le menu déroulant RDP et sélectionnez l'option Télécharger le fichier RDP. pour télécharger le fichier RDP correspondant à votre instance.

   Utilisez ce fichier pour vous connecter à l'instance à l'aide d'un client RDP. Pour plus d'informations, voir les clients Bureau à distance de Microsoft.

5. Dans les champs username (nom d'utilisateur) et password (mot de passe), saisissez le nom d'utilisateur et le mot de passe que vous avez créés pour l'instance de VM SQL Server. Laissez le champ Domain (Domaine) vide, puis cliquez sur OK pour vous connecter à la VM SQL Server.

   

   Lorsque vous y êtes invité, acceptez le certificat.

6. Si vous acceptez les conditions, cliquez sur Continuer.

7. Dans l'instance, réduisez toutes les fenêtres, cliquez sur Démarrer dans la barre des tâches Windows, saisissez PowerShell, puis effectuez un clic droit sur l'application Windows PowerShell et sélectionnez Exécuter en tant qu'administrateur.

8. Lorsque l'invite PowerShell s'affiche, créez une structure de répertoires pour le stockage de la base de données :

   mkdir c:\sql-server-data\adventureworks
   

Créer et remplir un exemple de base de données

Vous allez maintenant télécharger le fichier de sauvegarde de la base de données AdventureWorks de Microsoft et le restaurer dans votre instance SQL Server. Cette base de données simule la base de données de production dont vous souhaitez effectuer la migration.

1. À partir de la ligne de commande PowerShell, téléchargez le fichier de sauvegarde AdventureWorksLT2017.bak vers le lecteur 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. Restaurez la base de données :

   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. Validez la base de données nouvellement restaurée en interrogeant le nombre de lignes de la table Customer. La sortie comprend 847 lignes.

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

Préparer l'instance Cloud SQL

1. Dans Cloud Shell, créez l'instance Cloud SQL pour 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
   

   L'utilisateur racine est sqlserver avec le mot de passe sqlserver12@. Notez également l'adresse IP de SQL Server.

2. Stockez l'adresse IP de la VM SQL Server source dans une variable d'environnement :

   sql_source_ip=$(gcloud compute instances describe sqlserver \
       --zone=us-central1-f | grep natIP | awk '{print $2}')
   echo $sql_source_ip
   

3. Ajoutez l'adresse IP de la VM SQL Server à la liste d'autorisations (liste blanche) à l'aide de l'instance Cloud SQL :

   gcloud sql instances patch target-sqlserver --authorized-networks=$sql_source_ip
   

4. Créez une base de données de destination :

   gcloud sql databases create AdventureWorksTarget --instance=target-sqlserver
   

5. Dans la ligne de commande PowerShell que vous avez lancée en tant qu'administrateur, vérifiez la connectivité à Cloud SQL pour 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'"
   

   La commande affiche 'test'.

Lancer la réplication de la VM vers Cloud SQL

À l'aide de la réplication des instantanés SQL Server, vous allez créer une tâche pour répliquer les instantanés de la base de données dans Cloud SQL.

Configurer le distributeur

Vous allez maintenant configurer la VM SQL Server en tant que distributeur SQL Server. En pratique, un distributeur peut s'exécuter sur une machine distincte, mais, dans ce tutoriel, vous l'exécuterez sur la même VM.

1. Dans la ligne de commande PowerShell que vous avez lancée en tant qu'administrateur, mettez à jour le nom de l'instance locale SQL Server pour qu'il corresponde au nom d'hôte de la 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
   

   Si vous recevez l'erreur Cannot open MSSQLServer service on computer '.'., vous n'avez probablement pas exécuté la ligne de commande PowerShell en tant qu'administrateur.

2. Vérifiez que le nom de l'instance est maintenant "sqlserver" :

   osql -E -Q "select @@servername;"
   

3. Spécifiez la base de données de distribution :

   $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. Configurez la base de données de distribution :

   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. Enregistrez le nom de la VM SQL Server auprès du distributeur en tant qu'éditeur :

   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'"
   

Configurer la publication

Maintenant que la distribution est configurée, vous allez configurer les tables qui sont publiées.

1. Depuis la ligne de commande PowerShell, activez et démarrez l'agent SQL Server :

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

2. Créez une publication pour la base de données 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. Configurez la publication pour envoyer un instantané de la source à Cloud SQL une fois par heure :

   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. Ajouter des articles (tables) à la publication. Bien que la base de données AdventureWorksLT2017 contienne de nombreuses tables, par souci de simplicité, vous en répliquez trois : Address, Customer et 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. Créez un instantané des articles manuellement. Si vous ne le faites pas, un délai d'une heure peut être nécessaire avant que le programmeur ne crée l'instantané initial.

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

Configurer un abonnement

Vous allez maintenant créer un abonnement qui envoie les données publiées à Cloud SQL.

1. À partir de la ligne de commande PowerShell, créez un abonnement pour envoyer l'instantané de publication à 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. Vérifiez que les données sont disponibles dans l'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]"
   

   La commande affiche 417, 450, 847