Actionable Data Analytics
Join Our Email List for Data News Sent to Your Inbox

Azure Synapse Pathway Introduction

Azure Synapse Pathway accelerates migration to Azure Synapse Analytics SQL Pools. It is built on top of smart logic already tested with SQL Server Migration Assistant. However, its main focus is analytical data store migrations. You will save hundreds of hours of human effort on the conversion of your scripts. 

This application is currently in public preview. Because it is at an early stage of development, it is not recommended for production workloads at the moment. 

What is Azure Synapse Pathway? 

To begin, Azure Synapse Pathway accelerates the migration of the scripts (tables, procedures, functions, views, etc) from other analytical store providers to Azure Synapse Analytics. 

Azure Synapse Pathway was originally announced during Microsoft Ignite. It simplifies the migration to Azure Synapse Analytics from other providers like IBM Netezza, Microsoft SQL Server, Snowflake, Amazon Redshift, Google BigQuery and Teradata. 

You simply select the scripts that need to be translated to Azure Synapse Analytics, and Azure Synapse Pathway outputs the converted scripts. 

You cannot expect a 100% successful translation for all your scripts. The idea is to save manual human effort needed to convert scripts.  

For example: if out of 2000 scripts, Azure Synapse Pathway converts 1600 scripts, you avoid manually translating 1600 scripts with a translation rate of 80%, which is amazing. 

Azure Synapse Pathway code translation

Picture extracted from MSDN documentation 

Why You Should Consider Azure Synapse Pathway 

Azure Synapse Pathway helps minimize human intervention and provides a scope of the effort required for analytical data store migrations to Azure Synapse Analytics. 

If you have ever been involved in a data warehouse migration project, you know that adopting any new database technology is time-consuming. This is especially true when it comes to enterprise data warehouse environments that are quite mature. They may have been serving their purpose for many years, but are starting to be legacy systems that don’t match current market needs. 

AS Pathway is built on top of existing SQL Server Migration Assistant logic, but its main focus is analytical data stores. 

Amount of person hours

Install Azure Synapse Analytics Pathway 

Next, to install Azure Synapse Pathway, I recommend using a dedicated server that has connectivity with a source system and the Azure ecosystem. This includes good bandwidth for big volumes of data. 

Pre-requisites: 

  • Before installing Azure Synapse Pathway, .NET Runtime is required. 

You can download Azure Synapse Pathway from this link

Download Azure Synapse pathway

Then, once you download Azure Synapse Pathway, you will get the following file. 

Follow the installation wizard. 

Follow the installation wizard

Next, accept the license agreement. 

Click next after the Privacy Statement. 

Finally, once Azure Synapse Pathway is installed, click Finish. 

You’ll find the Azure Synapse Pathway application on your server. 

find the Azure Synapse Pathway application on your server

Open the application and let’s get started! 

Azure Synapse Pathway Application 

To start, open Azure Synapse Pathway. You’ll see a few options. 

  1. Select Translation Type, for example: 
  • IBM Netezza 
  • Microsoft SQL Server 
  • Snowflake 
  • Amazon Redshift (coming soon) 
  • Google BigQuery (coming soon) 
  • Teradata (coming soon) 
  1. Choose the input directory for your script 
  1. Select the output directory for the translated scripts 
  1. Save the Azure Synapse Pathway assessment 
  1. Load an existing Azure Synapse Pathway assessment 
  1. Find the Azure Synapse Pathway documentation 
  1. Translate your scripts 

Example Azure Synapse Pathway with SQL Server 

For this example, we are going to use SQL Server.  

Pre-requirements: 

  • A folder with the scripts that you want to convert 
  • Another folder where you can store the translated scripts 

First, select your input and output script folders for the Azure Synapse Pathway assessment and click Translate. 

select your input and output script folders

Once the translation process finishes, you can see the results. 

  1. Conversion success (the graph does not work correctly at the moment. You can see I have many errors) 
  1. The amount of time saved by translating the scripts manually 
  1. A summary of the statement distribution (tables, stored procedures, views, etc) 
  1. The issues and warning found 
  1. A description of the warning or error for the selected issue 
  1. A link to the folder where the scripts were saved 

Click on any of the errors or warnings to get more information. 

If you click on View Results, you will find the folders with the translated scripts and a summary in a delimited file format. 

The results file provides a nice summary in case you want to import it into your Azure DevOps backlog items. 

And that’s it! A few clicks here and there and a lot of time saved on the migration of your analytical data store. 

Summary  

In summary, this is a first for accelerating migrations from analytical data stores that are managed by other providers to Azure Synapse Analytics. 

 I don’t usually talk about the limitations of public preview services because there are many updates and features coming up, but it would be great to see the following features: 

  • Direct import and deployment options for the scripts 
  • Integration with Azure Migration projects 
  • An option for hard translation that does not stop a script from being converted, even if some lines are not converted 

What’s Next?   

In upcoming blog posts, we’ll continue to explore some of the features within Azure Data Services.     

As always, please leave any comments or questions below.   

If you haven’t already, you can follow me on Twitter for blog updates, virtual presentations, and more!   

Check out these other posts

comment [ 1 ]
share
No tags 0
1 Response
  • Jai
    26 . 07 . 2021

    David, i follow many of yours posts and implemented as well. Appreciate your giving back to community, i have a request or your expert advise. Is there a better and easy way of converting Oracle Objects/scripts to azure synapse analytics compatible ?

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.