Move data to Azure SQL Database for Azure Machine Learning

This article outlines the options for moving data either from flat files (CSV or TSV formats) or from data stored in SQL Server to an Azure SQL Database. These tasks for moving data to the cloud are part of the Team Data Science Process.

For a topic that outlines the options for migrating data from SQL Server into Azure SQL options, see Migrate to Azure SQL.

The following table summarizes the options for moving data to an Azure SQL Database.

SOURCE DESTINATION: Azure SQL
Flat file (CSV or TSV formatted) Bulk Insert SQL Query
On-premises SQL Server 1.Export to Flat File
2. SQL Server Migration Assistant (SSMA)
3. Database back up and restore
4. Azure Data Factory

Prerequisites

The procedures outlined here require that you have:

Data: The migration processes are demonstrated using the NYC Taxi dataset. The NYC Taxi dataset contains information on trip data and fares, which is either available through Azure Open Datasets or from the source TLC Trip Record Data. A sample and description of these files are provided in NYC Taxi Trips Dataset Description.

You can either adapt the procedures described here to a set of your own data or follow the steps as described by using the NYC Taxi dataset. To upload the NYC Taxi dataset into your SQL Server database, follow the procedure outlined in Bulk Import Data into SQL Server Database.

Moving data from a flat file source to an Azure SQL Database

Data in flat files (CSV or TSV formatted) can be moved to an Azure SQL Database using a Bulk Insert SQL Query.

Bulk Insert SQL Query

The steps for the procedure using the Bulk Insert SQL Query are similar to the directions for moving data from a flat file source to SQL Server on an Azure VM. For details, see Bulk Insert SQL Query.

Moving Data from SQL Server to an Azure SQL Database

If the source data is stored in SQL Server, there are various possibilities for moving the data to an Azure SQL Database:

  1. Export to Flat File
  2. SQL Server Migration Assistant (SSMA)
  3. Database back up and restore
  4. Azure Data Factory

The steps for the first three are similar to those sections in Move data to SQL Server on an Azure virtual machine that cover these same procedures. Links to the appropriate sections in that topic are provided in the following instructions.

Export to Flat File

The steps for this exporting to a flat file are similar to those directions covered in Export to Flat File.

SQL Server Migration Assistant (SSMA)

The steps for using the SQL Server Migration Assistant (SSMA) are similar to those directions covered in SQL Server Migration Assistant (SSMA).

Database back up and restore

The steps for using database backup and restore are similar to those directions listed in Database backup and restore.

Azure Data Factory

Learn how to move data to an Azure SQL Database with Azure Data Factory (ADF) in this topic, Move data from a SQL Server to SQL Azure with Azure Data Factory. This topic shows how to use ADF to move data from a SQL Server database to an Azure SQL Database via Azure Blob Storage.

Consider using ADF when data needs to be continually migrated with hybrid on-premises and cloud sources. ADF also helps when the data needs transformations, or needs new business logic during migration. ADF allows for the scheduling and monitoring of jobs using simple JSON scripts that manage the movement of data on a periodic basis. ADF also has other capabilities such as support for complex operations.