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

---

Ce tutoriel explique comment migrer des données de Microsoft SQL Server 2017 Enterprise s'exécutant sur Compute Engine vers Cloud SQL pour SQL Server 2017 Enterprise. Ce tutoriel est utile si vous êtes administrateur système, développeur, ingénieur, administrateur de base de données ou ingénieur DevOps. Ce tutoriel vous explique comment configurer le distributeur SQL Server, utiliser l'agent SQL Server pour la réplication d'instantanés dans Cloud SQL pour SQL Server 2017, et vérifier que les données ont bien été importées.

Dans ce tutoriel, vous utiliserez la réplication d'instantanés pour que la source et la cible soient synchronisées. La réplication d'instantanés envoie aux abonnés une copie complète de chaque article, c'est-à-dire un objet de base de données publié. Cloud SQL est également compatible avec la réplication transactionnelle, qui n'envoie que les données incrémentielles. Une des limites de la réplication transactionnelle est que les tables doivent avoir des clés primaires. Pour en savoir plus sur les types de réplication SQL Server, consultez la documentation SQL Server.

Pour plus de simplicité, le tutoriel utilise la solution SQL Server source pour héberger un distributeur. Dans un scénario de production, si les données sont répliquées en dehors de Google Cloud, vous préférerez peut-être héberger la distribution côté Google Cloud sur une instance Compute Engine.

Remarque : Cloud SQL pour SQL Server n'est pas compatible avec l'envoi de journaux, la mise en miroir de bases de données ni ces fonctionnalités. Microsoft SQL Server n'est pas compatible avec la réplication à partir des éditions Developer, Web et Express.

Pour ce tutoriel, nous partons du principe que vous connaissez bien les éléments suivants :

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

Objectifs

• Créer une instance de machine virtuelle (VM) SQL Server sur Compute Engine pour héberger l'exemple de base de données
• Remplir un exemple de base de données.
• Créer une instance Cloud SQL pour SQL Server
• Créer un distributeur.
• Configurer la publication et l'abonnement.
• Lancer la réplication depuis SQL Server vers Cloud SQL.
• Valider les données importées

Coûts

Dans ce document, vous utilisez les composants facturables suivants de Google Cloud :

• Compute Engine
• Cloud SQL
• Cloud Storage
• SQL Server (Premium avec Compute Engine)

Obtenez une estimation des coûts en fonction de votre utilisation prévue à l'aide du simulateur de coût. Les nouveaux utilisateurs de Google Cloud peuvent bénéficier d'un essai gratuit.

Avant de commencer

1. Connectez-vous à votre compte Google Cloud. Si vous débutez sur Google Cloud, créez un compte pour évaluer les performances de nos produits en conditions réelles. Les nouveaux clients bénéficient également de 300 $ de crédits gratuits pour exécuter, tester et déployer des charges de travail.

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

   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

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

4. Enable the Cloud SQL Admin and Compute Engine API APIs.

   Enable the APIs

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

   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

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

7. Enable the Cloud SQL Admin and Compute Engine API APIs.

   Enable the APIs

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

9. Installez le client RDP (Remote Desktop Protocol) de votre choix. Pour plus d'informations, voir les clients Bureau à distance de Microsoft. Si un client RDP est déjà installé, vous pouvez ignorer cette tâche.

Une fois que vous avez terminé les tâches décrites dans ce document, vous pouvez éviter de continuer à payer des frais en supprimant les ressources que vous avez créées. Pour en savoir plus, consultez la section Effectuer un nettoyage.

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

Nettoyer

Une fois le tutoriel terminé, vous pouvez procéder au nettoyage des ressources que vous avez créées afin qu'elles ne soient plus comptabilisées dans votre quota et qu'elles ne vous soient plus facturées. Dans les sections suivantes, nous allons voir comment supprimer ou désactiver ces ressources.

Supprimer le projet

Attention : La suppression d'un projet entraîne les effets décrits ci-dessous :
• Tout le contenu du projet est supprimé. Si vous avez utilisé un projet existant pour les tâches décrites dans ce document et que vous le supprimez, vous supprimerez également tout autre travail effectué dans le projet.
• Les ID de projets personnalisés sont perdus. Lorsque vous avez créé ce projet, vous avez peut-être créé un ID de projet personnalisé que vous souhaitez utiliser à l'avenir. Pour conserver les URL qui utilisent l'ID de projet, telle qu'une URL appspot.com, supprimez les ressources sélectionnées dans le projet au lieu de supprimer l'ensemble du projet.
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.

Étape suivante

• Découvrez comment migrer des données de SQL Server 2017 vers Cloud SQL pour SQL Server à l'aide de fichiers de sauvegarde.
• Découvrez comment migrer des données de SQL Server 2008 vers Cloud SQL pour SQL Server à l'aide de fichiers de sauvegarde.
• Découvrez des architectures de référence, des schémas et des bonnes pratiques concernant Google Cloud. Consultez notre Centre d'architecture cloud.