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:
- An Azure subscription. If you do not have a subscription, you can sign up for a free trial.
- An Azure storage account. You use an Azure storage account for storing the data in this tutorial. If you don't have an Azure storage account, see the Create a storage account article. After you have created the storage account, you need to obtain the account key used to access the storage. See Manage storage account access keys.
- Access to an Azure SQL Database. If you must set up an Azure SQL Database, Getting Started with Microsoft Azure SQL Database provides information on how to provision a new instance of an Azure SQL Database.
- Installed and configured Azure PowerShell locally. For instructions, see How to install and configure Azure PowerShell.
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:
- Export to Flat File
- SQL Server Migration Assistant (SSMA)
- Database back up and restore
- 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.