Spostamento dei dati in SQL Server in una macchina virtuale di Azure

Questo argomento descrive le opzioni per lo spostamento dei dati da file flat con estensione csv o tsv o da SQL Server locale a SQL Server in una macchina virtuale di Azure. Queste attività per lo spostamento dei dati nel cloud fanno parte del Processo di analisi scientifica dei dati per i team.

Per un argomento che descrive le opzioni per lo spostamento dei dati a un database SQL Azure per Machine Learning, vedere Spostare i dati a un Database di SQL Azure per Azure Machine Learning.

Il menu seguente si collega ad argomenti che descrivono come inserire dati in altri ambienti di destinazione in cui i dati possono essere archiviati ed elaborati durante il Processo di analisi scientifica dei dati per i team (TDSP).

Nella tabella seguente vengono riepilogate le opzioni per lo spostamento dei dati in SQL Server in una macchina virtuale Azure.

SOURCE DESTINAZIONE: SQL Server in VM di Azure
File Flat 1. Utilità copia di massa della riga di comando (BCP)
2. Inserimento di massa query SQL
3. Utilità grafiche integrate in SQL Server
Server SQL locale 1. Distribuzione di un database di SQL Server a una macchina virtuale di Microsoft Azure
2. Esportazione in un file flat
3. Migrazione guidata database SQL
4. Backup e ripristino database

Tenere presente che il presente documento presuppone che i comandi SQL vengano eseguiti da SQL Server Management Studio o Visual Studio Database Explorer.

Suggerimento

In alternativa, è possibile usare Data factory di Azure per creare e pianificare una pipeline che sposta i dati a una macchina virtuale di SQL Server in Azure. Per altre informazioni, vedere Copia di dati con Data factory di Azure (Attività di copia).

Prerequisiti

Il tutorial presuppone:

Spostamento di dati da un'origine di file flat a SQL Server su una VM di Azure

Se i dati si trovano in un file flat (organizzati in un formato righe/colonne), possono essere spostati a una macchina virtuale di SQL Server attraverso i seguenti metodi:

  1. Utilità copia di massa della riga di comando (BCP)
  2. Inserimento di massa query SQL
  3. Utilità grafiche integrate in SQL Server (importazione/esportazione, SSIS)

Utilità copia di massa della riga di comando (BCP)

BCP è un'utilità della riga di comando installata con SQL Server e rappresenta uno dei metodi più rapidi per spostare i dati. Funziona in tutte e tre le varianti di SQL Server (SQL Server locale, SQL Azure e macchine virtuali SQL Server in Azure).

Nota

Dove devono trovarsi i dati per eseguire la copia BCP?
Anche se non è obbligatorio che i file contenenti i dati di origine si trovino nello stesso computer del server SQL di destinazione, questo garantisce trasferimenti più rapidi, a causa della differenza tra velocità di rete e velocità di I/O dei dischi locali. È possibile spostare i file flat contenenti i dati nel computer dove è installato SQL Server usando diversi strumenti per la copia dei file quali AZCopy, Esplora archivi di Azure o la funzione di copia/incolla di Windows tramite Remote Desktop Protocol (RDP).

  1. Assicurarsi che il database e le tabelle vengano create nel database di SQL Server di destinazione. Ecco un esempio di come procedere utilizzando i comandi Create Database e Create Table:

     CREATE DATABASE <database_name>
    
     CREATE TABLE <tablename>
     (
         <columnname1> <datatype> <constraint>,
         <columnname2> <datatype> <constraint>,
         <columnname3> <datatype> <constraint>
     )
    
  2. Generare il file di formato che descrive lo schema per la tabella eseguendo il comando seguente dalla riga di comando del computer in cui è installato bcp.

    bcp dbname..tablename format nul -c -x -f exportformatfilename.xml -S servername\sqlinstance -T -t \t -r \n

  3. Inserire i dati nel database utilizzando il comando bcp come indicato di seguito. Dovrebbe funzionare dalla riga di comando, presupponendo che SQL Server sia installato nello stesso computer:

    bcp dbname..tablename in datafilename.tsv -f exportformatfilename.xml -S servername\sqlinstancename -U username -P password -b block_size_to_move_in_single_attemp -t \t -r \n

Ottimizzazione inserimenti BCP Per ottimizzare gli inserimenti, fare riferimento al seguente articolo "Linee guida per ottimizzare l'importazione di massa" .

Parallelizzazione delle operazioni di inserimento per uno spostamento dei dati più veloce

Se i dati che si stanno spostando sono grandi, è possibile velocizzare l'operazione eseguendo contemporaneamente più comandi BCP in uno script di PowerShell.

Nota

Inserimento di Big Data Per ottimizzare il caricamento dei dati per set di dati grandi e molto grandi, partizionare le tabelle dei database logici e fisici mediante più filegroup e tabelle di partizione. Per ulteriori informazioni sulla creazione e sul caricamento dei dati in tabelle di partizione, vedere Caricamento parallelo di tabelle di partizione SQL.

Lo script PowerShell di esempio illustra gli inserimenti mediante bcp:

$NO_OF_PARALLEL_JOBS=2

 Set-ExecutionPolicy RemoteSigned #set execution policy for the script to execute
 # Define what each job does
   $ScriptBlock = {
       param($partitionnumber)

       #Explictly using SQL username password
       bcp database..tablename in datafile_path.csv -F 2 -f format_file_path.xml -U username@servername -S tcp:servername -P password -b block_size_to_move_in_single_attempt -t "," -r \n -o path_to_outputfile.$partitionnumber.txt

        #Trusted connection w.o username password (if you are using windows auth and are signed in with that credentials)
        #bcp database..tablename in datafile_path.csv -o path_to_outputfile.$partitionnumber.txt -h "TABLOCK" -F 2 -f format_file_path.xml  -T -b block_size_to_move_in_single_attempt -t "," -r \n
  }


# Background processing of all partitions
for ($i=1; $i -le $NO_OF_PARALLEL_JOBS; $i++)
{
  Write-Debug "Submit loading partition # $i"
  Start-Job $ScriptBlock -Arg $i      
}


# Wait for it all to complete
While (Get-Job -State "Running")
{
  Start-Sleep 10
  Get-Job
}

# Getting the information back from the jobs
Get-Job | Receive-Job
Set-ExecutionPolicy Restricted #reset the execution policy

Inserimento di massa query SQL

L'inserimento di massa di query SQL può essere usato per importare dati nel database da file basati su righe/colonne (i tipi supportati sono indicati nell'argomento Preparazione dei dati per l'importazione o l'esportazione bulk (SQL Server)).

Ecco alcuni comandi di esempio per l'inserimento di massa:

  1. Analizzare i dati e impostare le opzioni personalizzate prima dell'importazione per assicurarsi che il database SQL Server presupponga lo stesso formato per tutti i campi speciali, ad esempio le date. Ecco un esempio di come impostare il formato della data come anno-mese-giorno (se i dati contengono la data in formato anno-mese-giorno):

     SET DATEFORMAT ymd;    
    
  2. portare i dati utilizzando le istruzioni per eseguire importazioni di massa

     BULK INSERT <tablename>
     FROM    
     '<datafilename>'
     WITH
     (
     FirstRow=2,
     FIELDTERMINATOR =',', --this should be column separator in your data
     ROWTERMINATOR ='\n'   --this should be the row separator in your data
     )
    

Utilità integrate in SQL Server

È possibile utilizzare SQL Server Integrations Services (SSIS) per importare i dati nelle macchine virtuali SQL Server in Azure da un file flat. SSIS è disponibile in due ambienti studio. Per ulteriori informazioni, vedere Integration Services (SSIS) e ambienti Studio:

Spostamento dei dati da SQL Server locale a SQL Server in una VM di Azure

È inoltre possibile utilizzare le strategie di migrazione seguenti:

  1. Distribuzione di un database di SQL Server a una macchina virtuale di Microsoft Azure
  2. Esportazione in un file flat
  3. Migrazione guidata database SQL
  4. Backup e ripristino database

Tali procedure vengono descritte qui di seguito:

Distribuzione di un database di SQL Server a una macchina virtuale di Microsoft Azure

La Distribuzione di un Database SQL Server in una macchina virtuale di Microsoft Azure è un modo semplice e consigliato per spostare dati da un'istanza di SQL Server locale a un SQL Server in una macchina virtuale di Azure. Per passaggi dettagliati, nonché per una descrizione delle altre alternative, vedere Migrazione di un database a SQL Server su una macchina virtuale di Azure.

Esportazione in un file flat

È possibile usare diversi metodi per l'esportazione di massa dei dati dal Server locale SQL come descritto nell'argomento Importazione ed esportazione dei dati in massa (SQL Server) . In questo documento si parla di Bulk Copy Program (BCP) come esempio. Una volta che i dati sono esportati in un file flat, possono essere importati in un altro server SQL mediante l'importazione di massa.

  1. Esportare i dati dal Server locale SQL in un file mediante l'utilità bcp come indicato di seguito

    bcp dbname..tablename out datafile.tsv -S servername\sqlinstancename -T -t \t -t \n -c

  2. Creare il database e la tabella nella macchina virtuale di SQL Server in Azure tramite create database e create table per lo schema della tabella esportato nel passaggio 1.
  3. Creare un file di formato per la descrizione dello schema della tabella dei dati da importare/esportare. I dettagli del file di formato sono descritti in Creazione di un file di formato (SQL Server).

    Creazione di file di formato quando si esegue BCP dal computer SQL Server

     bcp dbname..tablename format nul -c -x -f exportformatfilename.xml -S servername\sqlinstance -T -t \t -r \n
    

    Creazione di file di formato quando si esegue BCP in remoto rispetto a SQL Server

     bcp dbname..tablename format nul -c -x -f  exportformatfilename.xml  -U username@servername.database.windows.net -S tcp:servername -P password  --t \t -r \n
    
  4. Utilizzare uno dei metodi descritti nella sezione Spostamento dei dati dall'origine file per spostare i dati dai file flat in SQL Server.

Migrazione guidata database SQL

Migrazione guidata database SQL Server fornisce un modo semplice per spostare i dati tra due istanze del server SQL. Consente all'utente di mappare lo schema dei dati tra origini e tabelle di destinazione, scegliere i tipi di colonna e varie altre funzionalità. Utilizza la copia di massa (BCP) dietro le quinte. Di seguito è riportata una schermata della schermata iniziale della procedura guidata di migrazione del database SQL.

Migrazione guidata in SQL Server

Backup e ripristino database

SQL Server supporta:

  1. La funzionalità di backup e ripristino del database (sia in un file locale o in un'esportazione bacpac in un BLOB) e applicazioni livello dati (tramite bacpac).
  2. Possibilità di creare direttamente le macchine virtuali SQL Server in Azure con un database copiato o di copiare in un database esistente di SQL Azure. Per ulteriori informazioni, vedere Utilizzo della procedura guidata di copia del database.

Di seguito è riportata una schermata delle opzioni di backup e ripristino del database da SQL Server Management Studio.

Strumento di importazione di SQL Server

Risorse

Migrazione di un database a SQL Server su una macchina virtuale di Azure

Panoramica di SQL Server in macchine virtuali di Azure