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. Dans Google Cloud Console, sur la page de sélection du projet, sélectionnez ou créez un projet Google Cloud.

   Remarque : Si vous ne comptez pas conserver les ressources créées dans cette procédure, créez un projet au lieu de sélectionner un projet existant. Après avoir suivi ces étapes, vous pouvez supprimer le projet. Cela entraîne la suppression de toutes les ressources qui lui sont associées.

   Accéder au sélecteur de projet

3. Vérifiez que la facturation est activée pour votre projet Google Cloud.

4. Activer les API Cloud SQL Admin and Compute Engine API.

   Activer les API

5. Dans Google Cloud Console, sur la page de sélection du projet, sélectionnez ou créez un projet Google Cloud.

   Remarque : Si vous ne comptez pas conserver les ressources créées dans cette procédure, créez un projet au lieu de sélectionner un projet existant. Après avoir suivi ces étapes, vous pouvez supprimer le projet. Cela entraîne la suppression de toutes les ressources qui lui sont associées.

   Accéder au sélecteur de projet

6. Vérifiez que la facturation est activée pour votre projet Google Cloud.

7. Activer les API Cloud SQL Admin and Compute Engine API.

   Activer les API

8. Dans la console Google Cloud, activez Cloud Shell.

   Activer Cloud Shell

   En bas de la fenêtre de la console Google Cloud, une session Cloud Shell démarre et affiche une invite de ligne de commande. Cloud Shell est un environnement shell dans lequel Google Cloud CLI est déjà installé, et dans lequel des valeurs sont déjà définies pour votre projet actuel. L'initialisation de la session peut prendre quelques secondes.

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 aura les effets suivants :
• Tout le contenu du projet est supprimé. Si vous avez utilisé un projet existant pour les tâches de ce document, lorsque vous le supprimez, vous supprimez é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. Dans la console Google Cloud, accédez à la page Gérer les ressources.

   Accéder à la page Gérer les ressources

2. Dans la liste des projets, sélectionnez le projet que vous souhaitez supprimer, puis cliquez sur Supprimer.
3. Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.

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