How SnapLogic enables faster digital business transformation with Azure cloud migration

4 min read

Digital transformation is a key initiative many organizations are undertaking to deliver value to their customers rapidly. This type of initiative requires fundamental organizational changes, including operational changes, culture and leadership change, innovation through the adoption of new business models, and the improvement in the experience for their overall ecosystem partners and customers.

A recent IDC report shows that “By 2018, 70% of siloed digital transformation (DX) initiatives will ultimately fail because of insufficient collaboration, integration, sourcing, or project management.” Hence, it is essential for organizations to have the appropriate set of digital tools, expertise, mindset, and integration mechanisms to achieve digital transformation.

Organizations must consider folding a cloud strategy into their digital transformation efforts that will allow them to migrate data from on-premises environments to the cloud. By migrating data onto the cloud, organizations can improve their operational agility and quick enablement.

Microsoft’s cloud-first strategy

Microsoft also has fully embraced the cloud first strategy model with SQL Server’s newest capabilities being first released to Azure SQL Database in the cloud, and later to an on-premises SQL Server Database.

In the SnapLogic Enterprise Integration Cloud Summer 2017 Release (4.10), we launched the new Azure SQL DB Snap Pack that provides abstractions to users and enables them to quickly move data from an on-premises environment to the Azure cloud.

Azure SQL DB is a relational database-as-a service using the SQL server engine underneath. It provides multi-tenancy and can scale out based on the application needs with no downtime. SnapLogic offers the abstraction layer component, called Snaps, allowing users to perform various operations on Azure SQL DB without any coding. The following Azure SQL Snaps are provided in the Summer 2017 release:

  • Azure SQL Bulk Load: The Bulk Load Snap enables users to quickly move on-premises data stored in databases like MySQL, SQL Server, or other file systems to Azure SQL DB in the cloud. It uses the BulkCopy API to stream data quickly to Azure SQL DB. This API was introduced in SQL Server JDBC v4.2 and it does not rely on BCP command line utilities. This eliminates the need to generate temporary files during the process as the data is handled in memory. It is fast!

This Snap also has the flexibility to be used in cloud or on-premises environments, regardless of the execution location.

  • Azure SQL Bulk Extract: Bulk Extract Snap allows users to move large amounts of data stored in Azure SQL DB to other downstream systems. These downstream systems can be Azure Blob, Azure Data Lake Store, Azure Data Warehouse, Redshift, or others. This Snap uses the BCP command line utility to extract data, and storing it temporarily in the local system before moving it to the designated system.
  • Azure SQL Execute: This Snap executes various SQL statements (select, insert, delete) and can be used in a pipeline to perform respective database operations.
  • Azure SQL Stored Procedure: This Snap invokes a stored procedure in the Azure SQL DB.
  • Azure SQL Table List: This Snap connects to Azure SQL DB, reads its metadata, and outputs a list of tables in a database.
  • Azure SQL Update: This Snap updates database columns associated with a table based on a given condition.

The Azure SQL Snap Pack supports two types of authentications:

  • SQL Authentication (Username and password)
  • ActiveDirectoryPassword (standard AD integration)

The following are some of the use cases where users can gain value from the Azure SQL Snap Pack:

  • On-premises database migrations (SQL Server or MySQL or Oracle) to Azure SQL DB in the cloud.
  • Data movement in Azure SQL DB to either Azure Data Lake or Redshift or other CDWs for analytics.
  • Strategically invested in Microsoft Azure cloud space or Microsoft in general.

Azure SQL sample pipelines

Below is a sample pipeline with details. The goal is to move data stored in on-premises environments such as files and SQL Server to Azure SQL DB in the cloud. Users can select an already existing schema name and table name in the Snap settings or create a new table by enabling the selection. The batch size can be tuned based on the data size and how fast users want to load data.

In the second pipeline, the data is extracted from Azure SQL DB and moved to Azure Data Lake store, so users can run analytics on top of it. More info on the Azure Data Lake can be read in my previous blog post.

Cloud strategy becomes imperative in order for organizations to move towards digital transformation, so they can achieve business agility and provide quick workforce enablement. This includes moving data from old systems stored in on-premises systems to the cloud. SnapLogic – an enterprise integration cloud platform – enables customers with the right set of Snaps like Azure SQL DB and others (over 400+ Snaps) to easily move data to the cloud and to meet digital transformation goals.

Interested in more? Watch the Azure SQL Demo here.

For a complete list of features and functionality in our most recent release, please see the Summer release blog post.

Former Senior Product Manager at SnapLogic

We're hiring!

Discover your next great career opportunity.