Migrate Microsoft Azure Synapse Analytics to Amazon Redshift utilizing AWS SCT

Amazon Redshift is a quick, totally managed, petabyte-scale knowledge warehouse that gives the pliability to make use of provisioned or serverless compute in your analytical workloads. With Amazon Redshift Serverless and Question Editor v2, you may load and question giant datasets in only a few clicks and pay just for what you utilize. The decoupled compute and storage structure of Amazon Redshift allows you to construct extremely scalable, resilient, and cost-effective workloads. Many purchasers migrate their knowledge warehousing workloads to Amazon Redshift and profit from the wealthy capabilities it presents, equivalent to the next:

• Amazon Redshift seamlessly integrates with broader knowledge, analytics, and AI or machine studying (ML) providers on AWS, enabling you to decide on the precise instrument for the precise job. Trendy analytics is far wider than SQL-based knowledge warehousing. With Amazon Redshift, you may construct lake home architectures and carry out any type of analytics, equivalent to interactive analytics, operational analytics, massive knowledge processing, visible knowledge preparation, predictive analytics, machine studying, and extra.
• You don’t want to fret about workloads equivalent to ETL (extract, remodel, and cargo), dashboards, ad-hoc queries, and so forth interfering with one another. You possibly can isolate workloads utilizing knowledge sharing, whereas utilizing the identical underlying datasets.
• When customers run many queries at peak instances, compute seamlessly scales inside seconds to offer constant efficiency at excessive concurrency. You get 1 hour of free concurrency scaling capability for twenty-four hours of utilization. This free credit score meets the concurrency demand of 97% of the Amazon Redshift buyer base.
• Amazon Redshift is simple to make use of with self-tuning and self-optimizing capabilities. You may get sooner insights with out spending worthwhile time managing your knowledge warehouse.
• Fault tolerance is inbuilt. All knowledge written to Amazon Redshift is routinely and repeatedly replicated to Amazon Easy Storage Service (Amazon S3). Any {hardware} failures are routinely changed.
• Amazon Redshift is straightforward to work together with. You possibly can entry knowledge with conventional, cloud-native, containerized, serverless net providers or event-driven purposes. It’s also possible to use your favourite enterprise intelligence (BI) and SQL instruments to entry, analyze, and visualize knowledge in Amazon Redshift.
• Amazon Redshift ML makes it simple for knowledge scientists to create, practice, and deploy ML fashions utilizing acquainted SQL. It’s also possible to run predictions utilizing SQL.
• Amazon Redshift offers complete knowledge safety at no additional value. You possibly can arrange end-to-end knowledge encryption, configure firewall guidelines, outline granular row-level and column-level safety controls on delicate knowledge, and extra.

On this submit, we present easy methods to migrate an information warehouse from Microsoft Azure Synapse to Redshift Serverless utilizing AWS Schema Conversion Instrument (AWS SCT) and AWS SCT knowledge extraction brokers. AWS SCT makes heterogeneous database migrations predictable by routinely changing the supply database code and storage objects to a format suitable with the goal database. Any objects that may’t be routinely transformed are clearly marked in order that they are often manually transformed to finish the migration. AWS SCT may also scan your utility code for embedded SQL statements and convert them.

Resolution overview

AWS SCT makes use of a service account to hook up with your Azure Synapse Analytics. First, we create a Redshift database into which Azure Synapse knowledge will probably be migrated. Subsequent, we create an S3 bucket. Then, we use AWS SCT to transform Azure Synapse schemas and apply them to Amazon Redshift. Lastly, emigrate knowledge, we use AWS SCT knowledge extraction brokers, which extract knowledge from Azure Synapse, add it into an S3 bucket, and duplicate it to Amazon Redshift.

The next diagram illustrates our answer structure.

This walkthrough covers the next steps:

1. Create a Redshift Serverless knowledge warehouse.
2. Create the S3 bucket and folder.
3. Convert and apply the Azure Synapse schema to Amazon Redshift utilizing AWS SCT:
   1. Hook up with the Azure Synapse supply.
   2. Hook up with the Amazon Redshift goal.
   3. Convert the Azure Synapse schema to a Redshift database.
   4. Analyze the evaluation report and tackle the motion gadgets.
   5. Apply the transformed schema to the goal Redshift database.
4. Migrate knowledge from Azure Synapse to Amazon Redshift utilizing AWS SCT knowledge extraction brokers:
   1. Generate belief and key shops (this step is optionally available).
   2. Set up and configure the information extraction agent.
   3. Begin the information extraction agent.
   4. Register the information extraction agent.
   5. Add digital partitions for big tables (this step is optionally available).
   6. Create a neighborhood knowledge migration activity.
   7. Begin the native knowledge migration activity.
5. View knowledge in Amazon Redshift.

Stipulations

Earlier than beginning this walkthrough, you should have the next conditions:

• A workstation with AWS SCT, Amazon Corretto 11, and Redshift drivers.
• A database consumer account that AWS SCT can use to hook up with your supply Azure Synapse Analytics database.
  • Grant VIEW DEFINITION and VIEW DATABASE STATE to every schema you are attempting to transform to the database consumer used for migration.

Create a Redshift Serverless knowledge warehouse

On this step, we create a Redshift Serverless knowledge warehouse with a workgroup and namespace. A workgroup is a group of compute sources and a namespace is a group of database objects and customers. To isolate workloads and handle totally different sources in Redshift Serverless, you may create namespaces and workgroups and handle storage and compute sources individually.

Observe these steps to create a Redshift Serverless knowledge warehouse with a workgroup and namespace:

1. On the Amazon Redshift console, select the AWS Area that you simply need to use.
2. Within the navigation pane, select Redshift Serverless.
3. Select Create workgroup.

4. For Workgroup identify, enter a reputation that describes the compute sources.

5. Confirm that the VPC is identical because the VPC because the EC2 occasion with AWS SCT.
6. Select Subsequent.
7. For Namespace, enter a reputation that describes your dataset.
8. Within the Database identify and password part, choose Customise admin consumer credentials.
9. For Admin consumer identify, enter a consumer identify of your selection (for instance, awsuser).
10. For Admin consumer password, enter a password of your selection (for instance, MyRedShiftPW2022).

11. Select Subsequent.

Observe that knowledge within the Redshift Serverless namespace is encrypted by default.

12. Within the Assessment and Create part, select Create.

Now you create an AWS Identification and Entry Administration (IAM) function and set it because the default in your namespace. Observe that there can solely be one default IAM function.

13. On the Redshift Serverless Dashboard, within the Namespaces / Workgroups part, select the namespace you simply created.
14. On the Safety and encryption tab, within the Permissions part, select Handle IAM roles.
15. Select Handle IAM roles and select Create IAM function.
16. Within the Specify an Amazon S3 bucket for the IAM function to entry part, select one of many following strategies:
    1. Select No extra Amazon S3 bucket to permit the created IAM function to entry solely the S3 buckets with names containing the phrase redshift.
    2. Select Any Amazon S3 bucket to permit the created IAM function to entry all S3 buckets.
    3. Select Particular Amazon S3 buckets to specify a number of S3 buckets for the created IAM function to entry. Then select a number of S3 buckets from the desk.
17. Select Create IAM function as default.
18. Seize the endpoint for the Redshift Serverless workgroup you simply created.
19. On the Redshift Serverless Dashboard, within the Namespaces / Workgroups part, select the workgroup you simply created.
20. Within the Normal data part, copy the endpoint.

Create the S3 bucket and folder

Throughout the knowledge migration course of, AWS SCT makes use of Amazon S3 as a staging space for the extracted knowledge. Observe these steps to create an S3 bucket:

1. On the Amazon S3 console, select Buckets within the navigation pane.
2. Select Create bucket.
3. For Bucket identify, enter a singular DNS-compliant identify in your bucket (for instance, uniquename-as-rs).

For extra details about bucket names, discuss with Bucket naming guidelines.

4. For AWS Area, select the Area during which you created the Redshift Serverless workgroup.
5. Select Create bucket.

6. Select Buckets within the navigation pane and navigate to the S3 bucket you simply created (uniquename-as-rs).
7. Select Create folder.
8. For Folder identify, enter incoming.
9. Select Create folder.

Convert and apply the Azure Synapse schema to Amazon Redshift utilizing AWS SCT

To transform the Azure Synapse schema to Amazon Redshift format, we use AWS SCT. Begin by logging in to the EC2 occasion that you simply created beforehand and launch AWS SCT.

Hook up with the Azure Synapse supply

Full the next steps to hook up with the Azure Synapse supply:

1. On the File menu, select Create New Undertaking.
2. Select a location to retailer your venture recordsdata and knowledge.
3. Present a significant however memorable identify in your venture (for instance, Azure Synapse to Amazon Redshift).
4. To connect with the Azure Synapse supply knowledge warehouse, select Add supply.
5. Select Azure Synapse and select Subsequent.
6. For Connection identify, enter a reputation (for instance, olap-azure-synapse).

AWS SCT shows this identify within the object tree in left pane.

7. For Server identify, enter your Azure Synapse server identify.
8. For SQL pool, enter your Azure Synapse pool identify.
9. Enter a consumer identify and password.
10. Select Take a look at connection to confirm that AWS SCT can connect with your supply Azure Synapse venture.
11. When the connection is efficiently validated, select Okay and Join.

Hook up with the Amazon Redshift goal

Observe these steps to hook up with Amazon Redshift:

1. In AWS SCT, select Add goal.
2. Select Amazon Redshift, then select Subsequent.
3. For Connection identify, enter a reputation to explain the Amazon Redshift connection.

AWS SCT shows this identify within the object tree in the precise pane.

4. For Server identify, enter the Redshift Serverless workgroup endpoint you captured earlier.
5. For Server port, enter 5439.
6. For Database, enter dev.
7. For Person identify, enter the consumer identify you selected when creating the Redshift Serverless workgroup.
8. For Password, enter the password you selected when creating the Redshift Serverless workgroup.
9. Deselect Use AWS Glue.
10. Select Take a look at connection to confirm that AWS SCT can connect with your goal Redshift workgroup.
11. When the check is profitable, select OK.
12. Select Join to hook up with the Amazon Redshift goal.

Alternatively, you need to use connection values which can be saved in AWS Secrets and techniques Supervisor.

Convert the Azure Synapse schema to a Redshift knowledge warehouse

After you create the supply and goal connections, you will notice the supply Azure Synapse object tree within the left pane and the goal Amazon Redshift object tree in the precise pane. We then create mapping guidelines to explain the supply goal pair for the Azure Synapse to Amazon Redshift migration.

Observe these steps to transform the Azure Synapse dataset to Amazon Redshift format:

1. Within the left pane, select (right-click) the schema you need to convert.
2. Select Convert schema.
3. Within the dialog field, select Sure.

When the conversion is full, you will notice a brand new schema created within the Amazon Redshift pane (proper pane) with the identical identify as your Azure Synapse schema.

The pattern schema we used has three tables; you may see these objects in Amazon Redshift format in the precise pane. AWS SCT converts all of the Azure Synapse code and knowledge objects to Amazon Redshift format. It’s also possible to use AWS SCT to transform exterior SQL scripts, utility code, or extra recordsdata with embedded SQL.

Analyze the evaluation report and tackle the motion gadgets

AWS SCT creates an evaluation report back to assess the migration complexity. AWS SCT can convert the vast majority of code and database objects, however some objects could require guide conversion. AWS SCT highlights these objects in blue within the conversion statistics diagram and creates motion gadgets with a complexity connected to them.

To view the evaluation report, change from Primary view to Evaluation Report view as proven within the following screenshot.

The Abstract tab reveals objects that have been transformed routinely and objects that weren’t transformed routinely. Inexperienced represents routinely transformed objects or objects with easy motion gadgets. Blue represents medium and sophisticated motion gadgets that require guide intervention.

The Motion gadgets tab reveals the really useful actions for every conversion challenge. For those who select an motion merchandise from the listing, AWS SCT highlights the thing that the motion merchandise applies to.

The report additionally accommodates suggestions for easy methods to manually convert the schema merchandise. For instance, after the evaluation runs, detailed studies for the database and schema present you the trouble required to design and implement the suggestions for changing motion gadgets. For extra details about deciding easy methods to deal with guide conversions, see Dealing with guide conversions in AWS SCT. AWS SCT completes some actions routinely whereas changing the schema to Amazon Redshift; objects with such actions are marked with a crimson warning signal.

You possibly can consider and examine the person object DDL by choosing it in the precise pane, and it’s also possible to edit it as wanted. Within the following instance, AWS SCT modifies the ID column knowledge sort from decimal(3,0) in Azure Synapse to the smallint knowledge sort in Amazon Redshift.

Apply the transformed schema to the goal Redshift knowledge warehouse

To use the transformed schema to Amazon Redshift, choose the transformed schema in the precise pane, right-click, and select Apply to database.

Migrate knowledge from Azure Synapse to Amazon Redshift utilizing AWS SCT knowledge extraction brokers

AWS SCT extraction brokers extract knowledge out of your supply database and migrate it to the AWS Cloud. On this part, we configure AWS SCT extraction brokers to extract knowledge from Azure Synapse and migrate to Amazon Redshift. For this submit, we set up the AWS SCT extraction agent on the identical Home windows occasion that has AWS SCT put in. For higher efficiency, we suggest that you simply use a separate Linux occasion to put in extraction brokers if attainable. For very giant datasets, AWS SCT helps the usage of a number of knowledge extraction brokers operating on a number of cases to maximise throughput and enhance the velocity of knowledge migration.

Generate belief and key shops (optionally available)

You should use Safe Socket Layer (SSL) encrypted communication with AWS SCT knowledge extractors. Once you use SSL, all knowledge handed between the purposes stays non-public and integral. To make use of SSL communication, you’ll want to generate belief and key shops utilizing AWS SCT. You possibly can skip this step for those who don’t need to use SSL. We suggest utilizing SSL for manufacturing workloads.

Observe these steps to generate belief and key shops:

1. In AWS SCT, select Settings, International settings, and Safety.
2. Select Generate belief and key retailer.

3. Enter a reputation and password for the belief and key shops.
4. Enter a location to retailer them.
5. Select Generate, then select OK.

Set up and configure the information extraction agent

Within the set up package deal for AWS SCT, you’ll find a subfolder referred to as brokers (aws-schema-conversion-tool-1.0.newest.zipagents). Find and set up the executable file with a reputation like aws-schema-conversion-tool-extractor-xxxxxxxx.msi.

Within the set up course of, observe these steps to configure AWS SCT Information Extractor:

1. For Service port, enter the port quantity the agent listens on. It’s 8192 by default.
2. For Working folder, enter the trail the place the AWS SCT knowledge extraction agent will retailer the extracted knowledge.

The working folder will be on a unique laptop from the agent, and a single working folder will be shared by a number of brokers on totally different computer systems.

3. For Enter Redshift JDBC driver file or recordsdata, enter the placement the place you downloaded the Redshift JDBC drivers.
4. For Add the Amazon Redshift driver, enter YES.
5. For Allow SSL communication, enter sure. Enter No right here for those who don’t need to use SSL.
6. Select Subsequent.

7. For Belief retailer path, enter the storage location you specified when creating the belief and key retailer.
8. For Belief retailer password, enter the password for the belief retailer.
9. For Allow consumer SSL authentication, enter sure.
10. For Key retailer path, enter the storage location you specified when creating the belief and key retailer.
11. For Key retailer password, enter the password for the important thing retailer.
12. Select Subsequent.

Begin the information extraction agent

Use the next process to begin extraction brokers. Repeat this process on every laptop that has an extraction agent put in.

Extraction brokers act as listeners. Once you begin an agent with this process, the agent begins listening for directions. You ship the brokers directions to extract knowledge out of your knowledge warehouse in a later part.

To begin the extraction agent, navigate to the AWS SCT Information Extractor Agent listing. For instance, in Microsoft Home windows, use C:Program FilesAWS SCT Information Extractor AgentStartAgent.bat.

On the pc that has the extraction agent put in, from a command immediate or terminal window, run the command listed in your working system. To cease an agent, run the identical command however substitute begin with cease. To restart an agent, run the identical RestartAgent.bat file.

Observe that it’s best to have administrator entry to run these instructions.

Register the information extraction agent

Observe these steps to register the information extraction agent:

1. In AWS SCT, change the view to Information Migration view select Register.
2. Choose Redshift knowledge agent, then select OK.

3. For Description, enter a reputation to determine the agent.
4. For Host identify, for those who put in the extraction agent on the identical workstation as AWS SCT, enter 0.0.0.0 to point native host. In any other case, enter the host identify of the machine on which the AWS SCT extraction agent is put in. It’s endorsed to put in extraction brokers on Linux for higher efficiency.
5. For Port, enter the quantity you used for the listening port (default 8192) when putting in the AWS SCT extraction agent.
6. Choose Use SSL to encrypt AWS SCT connection to Information Extraction Agent.

7. For those who’re utilizing SSL, navigate to the SSL tab.
8. For Belief retailer, select the belief retailer you created earlier.
9. For Key retailer, select the important thing retailer you created earlier.
10. Select Take a look at connection.
11. After the connection is validated efficiently, select OK and Register.

Create a neighborhood knowledge migration activity

Emigrate knowledge from Azure Synapse Analytics to Amazon Redshift, you create, run, and monitor the native migration activity from AWS SCT. This step makes use of the information extraction agent emigrate knowledge by making a activity.

Observe these steps to create a neighborhood knowledge migration activity:

1. In AWS SCT, below the schema identify within the left pane, select (right-click) the desk you need to migrate (for this submit, we use the desk tbl_currency).
2. Select Create Native activity.

3. Select from the next migration modes:
   1. Extract the supply knowledge and retailer it on a neighborhood PC or digital machine the place the agent runs.
   2. Extract the information and add it to an S3 bucket.
   3. Extract the information, add it to Amazon S3, and duplicate it into Amazon Redshift. (We select this feature for this submit.)

4. On the Superior tab, present the extraction and duplicate settings.

5. On the Supply server tab, ensure you are utilizing the present connection properties.

6. On the Amazon S3 settings tab, for Amazon S3 bucket folder, present the bucket and folder names of the S3 bucket you created earlier.

The AWS SCT knowledge extraction agent uploads the information in these S3 buckets and folders earlier than copying it to Amazon Redshift.

7. Select Take a look at Process.

8. When the duty is efficiently validated, select OK, then select Create.

Begin the native knowledge migration activity

To begin the duty, select Begin or Restart on the Duties tab.

First, the information extraction agent extracts knowledge from Azure Synapse. Then the agent uploads knowledge to Amazon S3 and launches a replica command to maneuver the information to Amazon Redshift.

At this level, AWS SCT has efficiently migrated knowledge from the supply Azure Synapse desk to the Redshift desk.

View knowledge in Amazon Redshift

After the information migration activity is full, you may connect with Amazon Redshift and validate the information. Full the next steps:

1. On the Amazon Redshift console, navigate to the Question Editor v2.
2. Open the Redshift Serverless workgroup you created.
3. Select Question knowledge.

4. For Database, enter a reputation in your database.
5. For Authentication, choose Federated consumer
6. Select Create connection.

7. Open a brand new editor by selecting the plus signal.
8. Within the editor, write a question to pick out from the schema identify and desk or view identify you need to confirm.

You possibly can discover the information, run ad-hoc queries, and make visualizations, charts, and views.

The next screenshot is the view of the supply Azure Synapse dataset we used on this submit.

Clear up

Observe the steps on this part to wash up any AWS sources you created as a part of this submit.

Cease the EC2 occasion

Observe these steps to cease the EC2 occasion:

1. On the Amazon EC2 console, within the navigation pane, select Situations.
2. Choose the occasion you created.
3. Select Occasion state, then select Terminate occasion.
4. Select Terminate when prompted for affirmation.

Delete the Redshift Serverless workgroup and namespace

Observe these steps to delete the Redshift Serverless workgroup and namespace:

1. On the Redshift Serverless Dashboard, within the Namespaces / Workgroups part, select the workspace you created
2. On the Actions menu, select Delete workgroup.
3. Choose Delete the related namespace.
4. Deselect Create closing snapshot.
5. Enter delete within the affirmation textual content field and select Delete.

Delete the S3 bucket

Observe these steps to delete the S3 bucket:

1. On the Amazon S3 console, select Buckets within the navigation pane.
2. Select the bucket you created.
3. Select Delete.
4. To substantiate deletion, enter the identify of the bucket.
5. Select Delete bucket.

Conclusion

Migrating an information warehouse is usually a difficult, advanced, and but rewarding venture. AWS SCT reduces the complexity of knowledge warehouse migrations. This submit mentioned how an information migration activity extracts, downloads, and migrates knowledge from Azure Synapse to Amazon Redshift. The answer we introduced performs a one-time migration of database objects and knowledge. Information adjustments made in Azure Synapse when the migration is in progress received’t be mirrored in Amazon Redshift. When knowledge migration is in progress, put your ETL jobs to Azure Synapse on maintain or rerun the ETL jobs by pointing to Amazon Redshift after the migration. Think about using the greatest practices for AWS SCT.

To get began, obtain and set up AWS SCT, register to the AWS Administration Console, try Redshift Serverless, and begin migrating!

In regards to the Authors

Ahmed Shehata is a Senior Analytics Specialist Options Architect at AWS based mostly on Toronto. He has greater than twenty years of expertise serving to prospects modernize their knowledge platforms. Ahmed is keen about serving to prospects construct environment friendly, performant, and scalable analytic options.

Jagadish Kumar is a Senior Analytics Specialist Options Architect at AWS centered on Amazon Redshift. He’s deeply keen about Information Structure and helps prospects construct analytics options at scale on AWS.

Anusha Challa is a Senior Analytics Specialist Resolution Architect at AWS centered on Amazon Redshift. She has helped many purchasers construct large-scale knowledge warehouse options within the cloud and on premises. Anusha is keen about knowledge analytics and knowledge science and enabling prospects obtain success with their large-scale knowledge initiatives.