Azure Synapse dedicated SQL pool vs. Azure SQL vs SQL
Hello, currently we operate on-premises PostgreSQL DB that we use as our data warehouse. I would like to set up a data warehouse in Azure, but the Azure portal is bit confusing, as there are multiple options: 1) in Azure Synapse, there is dedicated SQL pool (formerly labeled as Azure Data Warehouse) Then, outside Azure Synapse there are two additional options: 2) Azure SQL database ( /BrowseResource/resourceType/Microsoft.Sql%2Fazuresql ) and 3) "SQL database" (/BrowseResource/resourceType/Microsoft.Sql%2Fservers%2Fdatabases) https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/data-warehousing describes that Azure SQL (#2 above) uses symmetric multiprocessing (SMP) while "Azure Synapse Analytics" (#1) above uses massively parallel processing (MPP). My data needs are not so vast to utilize the MPP. Thus it seems I should be considering #2, i.e. outside the Synapse Analytics. Azure SQL (#2) above further branches into "Single Database", "Elastic pool" and "Single instance managed DB". I am guessing that for my scenario, "Azure SQL - Single Database" is the best option. If I choose this, will I be able to use this storage in Azure Synapse Analytics? Furthermore, to use Azure Data Factory (ADF) - if I set up "Azure SQL - Single Database", should I be aiming to use Data Factory within Azure Synapse Analytics, or outside it (i.e. use ADF in Azure portal)?
An automation and templating question with Azure Pipelines
Hello, we are working on a project where we have to automate resources across different environments. We are using Azure Pipelines What we have: An original environment where we would publish straight from the collaboration branch to workspace_publish. This env contains: - A Synapse workspace (Dedicated SQL Pool) - Azure Functions used in the Synapse pipelines through Linked Services and also one function that works as an API and should be callable always. - Blobstorage (StorageV2) - Key vault - Logic Apps (sending emails) - A environment deploy pipeline in DevOps. What we want: - An automated way of deploying the Synapse Workspace, Linked Services, Pipelines, Stored Procedures and tables from the dev environment to the others (qa, stage, prod) - Templating of connection strings, function URLs, linked services so that all connections work in each environment and any other interaction with resources outside Synapse. - Definition of the SQL Pool to run Stored Procedures - Database migration: If we create a table/column in dev how can we push it to prod without affecting the data? - Templating variables and resources in Azure Functions - Template permissions/access, specifically between Azure Functions and Database, Blob, How far we've gotten: Following this tutorial: https://www.youtube.com/watch?v=d7wsEh8Vr34 - We have tried to expose all the parameters that need templating using a template-parameters-definition.json file placed in the main branch of the repo (CICD-Test). - Then using override parameters in the Release pipeline in DevOps change the parameters to point at the correct resources for example: Changing the blobstorage connection string from AccountName=stgnkdwdatalakedev to AccountName=stgnkdwdatalakeqa How can we proceed with this? Since we do a lot of adhoc analysis in the DW and also with the live data coming from the event hub we require at least the dev and prod environments to be running.