Synapse comes with the multiple flavour including ETL, Storage & Analytics. Current solution will help you to prepare synapse analytics to transfer data from SQL Server to Synapse dedicated SQL pool with the Azure Data factory pipeline. Infrastructure architects/developers will have better understanding of configuring Synapse to connect SQL Server with the Azure KeyVault & will help Data Engineers to use Azure Data factory pipeline with Copy Table component to move Sales data from the Adventure Works Sales data to the Synapse Dedicated pool.
Template should help you to kick start working with the Synapse environment with the preloaded sales data. ARM template will deploy following components in the environment.
- Microsoft SQL Server with Sales Adventureworks Database
- Azure Key Vault with Connection Strings for the SQL Server
- Storage Account
- Synapse Analytics workspace
- Dedicated SQLServer Pool
- Create Table Script
- Select Table Script
- Azure Data Factory pipeline to move data from SQL Server to Synapse Storage
- Linked Service with its connection configurations
- Security Firewall
- Synapse Workspace will have its access only from the client ip given at the time of deployment
- Add more ip whitelisting in network firewall to allow other to use the synapse workspace
- Infrastructure Engineer
- Data Engineer
- Cloud Solution Architect
- Data Architect
The Deployment.json Azure Resource Manager template will help you automatically deploy the diagram below architecture
Deployment.json can be modified to match your current infrastructure needs.
To use this solution, you will need access to an Azure subscription. Also have to look for correct public ip. You can find your public ipv4 ip from the website https://whatsmyip.com/
- Visit https://portal.azure.com
Using the search bar on top type Templates
- Create a new template
- Give a name and a description to the template
- Add for modified Deployment.json and save it
- Select the newly added template and click deploy
- Fill out the blanks with your details and click purchase
- Allow 30 minutes for the deployment to complete
- Once deployment is completed please proceed with the Post deployment tasks
Post deployment its required to configure correct Azure KeyVault which has required credentials to connect to the Microsoft SQL Server having sample Sales data. ARM template will comission, ARM template will deploy required infrastructure with Synapse. Also, it will deploy following items in synapse to kickstart with the demo
- Under Data 1 Database
- dedicatedsqlpool(SQL)
- Under Develop 2 Scripts,
- 01 Customer_Table
- 02 Select_Customer_Data
- Under Integrate 1 Pipeline
- Copy Customer Data Demo
Validate deployed services which will have similar to below screenshot except the suffix "pagi6to7auim2". This suffix is unique for each resource group name.
- https://docs.microsoft.com/en-us/azure/synapse-analytics/overview-what-is
- https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-what-is
- https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms
- https://docs.microsoft.com/en-us/azure/devops/pipelines/release/azure-key-vault?view=azure-devops&tabs=yaml
- https://docs.microsoft.com/en-us/azure/data-factory/how-to-use-azure-key-vault-secrets-pipeline-activities
- https://docs.microsoft.com/en-us/azure/cloud-adoption-framework/ready/landing-zone/
You are responsible for the performance, the necessary testing, and if needed any regulatory clearance for any of the models produced by this toolbox. Please refer LICENSE & Contribute for more details