In this blog post, we will explore the Data Warehouse Migration Utility, a powerful tool designed to migrate data from SQL Server on premises and Azure SQL to Azure SQL Data Warehouse (ASDW). Whether you are looking to move your data to the cloud or simply transfer data between different SQL Server instances, this utility can simplify the process for you.
Requirements
Before we get started, make sure you have the following:
- SQL Server Management Studio (SSMS)
- SQL Server on premises installed
- An Azure Account
- Data Warehouse Migration Utility
- A database with tables (for this article, we will use the AdventureWorks database, but any database with tables and data will work)
Getting Started
Let’s walk through the process of migrating a table from SQL Server on premises to ASDW using the Data Warehouse Migration Utility:
- Open SQL Server Management Studio (SSMS) on your desktop.
- Launch the Data Warehouse Migration Utility (if you haven’t installed it, refer to the requirements section for instructions).
- Select the source and destination types. In this example, we are copying data from SQL Server on premises to ASDW.
- Enter the SQL Server on premises name and authentication information, then press Connect.
- Select the database with the table(s) you want to export to ASDW (in this example, we will use the AdventureWorks database).
- Select the specific table(s) you want to migrate (in this example, we will migrate the Production.ScrapReason table).
- Choose the option to migrate the schema. This will automatically generate the T-SQL code to create the schema and table in ASDW.
- Click the Run Script icon to execute the generated T-SQL code.
- Enter your ASDW connection information when prompted.
- If there are any errors, you can refer to the Error Log for troubleshooting. Common issues include firewall rules not being enabled in Azure or objects already being created.
- Once the migration is successful, you will be able to see the newly created schema and table in SSMS when you connect to ASDW.
- To migrate the data, click the Migrate Data icon.
- Choose the option to Run Migration.
- Review the path of the packages and click Next.
- Specify your ASDW credentials and click Generate.
- Locate the generated batch files. One is for exporting data and the other is for importing data.
- Execute the batch file for importing data to ASDW.
- Verify that the data has been successfully migrated by querying the table in ASDW.
Conclusion
In this article, we have explored the Data Warehouse Migration Utility and learned how it can simplify the process of migrating data from SQL Server on premises to Azure SQL Data Warehouse. By generating the schema and tables automatically, and invoking bcp commands to import the data, this utility streamlines the migration process. Whether you are moving your data to the cloud or transferring data between SQL Server instances, the Data Warehouse Migration Utility is a valuable tool to have in your arsenal.
References: BCP Utility, Data Warehouse Migration Utility