Synapse setup PowerShell

Azure Data engineering Part 3

Creating Synpase environment

Azure Synapse is a cloud-based integrated analytics service. Azure Synapse combines
1. Synapse Studio — (workspace for data exploration, preparation transformation, warehousing, big data, and Machine learning)
Synapse also integrates PowerBI, CosmosDB, and AzureML
2. ADF (Synapse Pipelines) — Data integration
3. Apache Spark — Big data processing
4. SQL pool also called serverless pool
5. SQL dedicated pool (Data warehousing)

1. Create Synapse workspace

Azure Synapse is an enterprise analytics service that accelerates time to insight across data warehouses and big data systems. It is a web-native offering that unifies end-to-end analytics.

## Setup Synapse workspace
$rg="rg-azuretraining-poc-001"
$SqlPassword='XXXXXXXX'
$SqlUser='XXXX'
$Location="$Location ="South Central US"
$Cred = New-Object -TypeName System.Management.Automation.PSCredential 
     ($SqlUser, (ConvertTo-SecureString 
                      $SqlPassword -AsPlainText -Force))
$SynapseWorkspaceName='synp-azuretraining-poc-ws001'
$StorageAccountName='stgdlraw01'
$FileShareName='stgdlraw01-fs-synapse-01'
$ManagedResourceGroupName='rg-azuretraining-poc-syn-mg001'
$WorkspaceParams =  @{
        Name = $SynapseWorkspaceName
        ResourceGroupName = $rg
        DefaultDataLakeStorageAccountName = $StorageAccountName
        DefaultDataLakeStorageFilesystem = $FileShareName
        SqlAdministratorLoginCredential = $Cred
        Location = $Region
        ManagedResourceGroupName=$ManagedResourceGroupName
}
New-AzSynapseWorkspace @WorkspaceParams

2. ADF Auto resolve

The AutoResolveIntegrationRuntime is automatically deployed with Auto Resolve and cannot be changed.

3. Create Synapse Spark Pool

$SynapseWorkspaceName='synp-azuretraining-poc-ws001'
$SparkPoolName='synpsparkpool01'
$sparkMinNodeCount =3
$sparkMaxNodeCount =10
$sparkNodeSize ="Small"
$SparkVersion=3.1
New-AzSynapseSparkPool `
 -WorkspaceName $SynapseWorkspaceName `
 -Name $SparkPoolName `
 -AutoScaleMinNodeCount $sparkMinNodeCount `
 -AutoScaleMaxNodeCount $sparkMaxNodeCount `
 -SparkVersion $SparkVersion `
 -NodeSize $sparkNodeSize `
 -EnableAutoPause  `
 -AutoPauseDelayInMinute 5

4. Synapse serverless pool

There is no infrastructure to setup or clusters to maintain. A default endpoint for this service is provided within every Azure Synapse workspace, so we can start querying data as soon as the workspace is created.

5. Create Synpase dedicated SQL Pool

Dedicated SQL pool (formerly SQL DW) represents a collection of analytic resources that are provisioned when using Synapse SQL. The size of a dedicated SQL pool (formerly SQL DW) is determined by Data Warehousing Units (DWU)

$SynapseWorkspaceName='synp-azuretraining-poc-ws001' 
$SQLPoolName='synpSQLpool01' 
$PerformanceLevel='DW100c'
New-AzSynapseSqlPool  `
-WorkspaceName $SynapseWorkspaceName  `
-Name $SQLPoolName `
-PerformanceLevel $PerformanceLevel

Securing Synpase resources

In Synapse managed workspace virtual network, data integration and spark resources are deployed. A Managed workspace Virtual Network also provides user-level isolation for spark activities because each Spark cluster is in its own subnet.

Dedicated SQL pool and serverless SQL pool are multi-tenant capabilities and therefore reside outside of the managed workspace virtual network. Intra-workspace communication to dedicated SQL pool and serverless SQL pool use azure private links. These private links are automatically created as part of a workspace with a managed workspace virtual network associated to it.

When we use managed private endpoints, traffic between your Azure Synapse workspace and other Azure resources traverse entirely over the Microsoft backbone network. Managed private endpoints protect against data exfiltration.

We will need to create a private endpoint for the following:
-Dedicated SQL endpoint (SQL) subresource
-Serverless SQL endpoint (SqlOnDemand ) subresource
-Development endpoint (DEV) subresource. DEV endpoint is used to run Spark Pool, ADF runtime
Studio workspace web URL will need a Private Link Hub to create the secured connection

The workspace studio UI is a single-page application (SPA) and is created as part of the Synapse workspace deployment
- We can create a private endpoint to connect the private link hub of the Synapse studio workspace.
- Users can connect to the Studio UI using this private endpoint
- Executions like notebooks or SQL scripts made from the Studio web interface will submit commands via the DEV private endpoint and will run on the appropriate pool

Get Raj Rathi’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

.Private endpoint to SQL

$rg = 'rg-azuretraining-poc-001'
$SynapseWorkspaceName='synp-azuretraining-poc-ws001'
$VNetName='vnet-azuretraining-southcenteral-001'
$subnetName = 'snet-azuretraining-southcenteral-001'
$privateEndpointCnName = 'privenc-conn-02'
$privateEndpointName = 'privend-sql-synp-01'
$Location ="South Central US"
$DNSnetworklink ="dns-networklink-02"
$ZNGrpName="dns-sngrp-02"
$DNSZone="privatelink.sql.azuresynapse.net"
### Private endpoint(s) are not permitted in conjunction with service endpoints in the same subnet!
$Synapseworkspace = Get-AzSynapseWorkspace `
  -ResourceGroupName $rg `
  -Name $SynapseWorkspaceName
## Create private endpoint connection. ##
$parameters1 = @{   
    Name = $privateEndpointCnName   
    PrivateLinkServiceId = $Synapseworkspace.ID  
    GroupID = 'sql'  
                 }
$privateEndpointConnection  `
                = New-AzPrivateLinkServiceConnection @parameters1
    
# Get virtual network
$vnet = Get-AzVirtualNetwork   `
            -ResourceGroupName $rg   `
            -Name $VNetName
$subnet = Get-AzVirtualNetworkSubnetConfig   `
              -Name $subnetName   `
              -VirtualNetwork $vnet
# Disable private end point policy      
$subnet.PrivateEndpointNetworkPolicies="Disabled"
($vnet | Select -ExpandProperty subnets | Where-Object {$_.Name -eq 'snet-azuretraining-southcenteral-001'}).PrivateEndpointNetworkPolicies = "Disabled"
$vnet | Set-AzVirtualNetwork 
    
#  Create private endpoint
$parameters2 = @{ 
    ResourceGroupName = $rg   
    Name = $privateEndpointName   
    Location = $Location   
    Subnet = $subnet   
    PrivateLinkServiceConnection = $privateEndpointConnection  
}
New-AzPrivateEndpoint @parameters2
#  Create private DNS zone 
$parameters1 = @{  
    ResourceGroupName = $rg  
    Name = $DNSZone 
                }
$zone = New-AzPrivateDnsZone @parameters1
#  Create DNS  network link.  
$parameters2 = @{  
    ResourceGroupName = $rg  
    ZoneName = $DNSZone  
    Name = $DNSnetworklink   
    VirtualNetworkId = $vnet.Id
}
$link = New-AzPrivateDnsVirtualNetworkLink @parameters2
# Create DNS configuration  
$parameters3 = @{   
    Name = $DNSZone  
    PrivateDnsZoneId = $zone.ResourceId   
}
$config = New-AzPrivateDnsZoneConfig @parameters3
#  Create DNS zone group 
$parameters4 = @{   
    ResourceGroupName = $rg   
    PrivateEndpointName = $privateEndpointName   
    Name = $ZNGrpName   
    PrivateDnsZoneConfig = $config   
}
New-AzPrivateDnsZoneGroup @parameters4

Private endpoint for SQL on-demand (We will use the same DNS setup created above and add only DNS zone for SQL on demand)

$DNSZone="privatelink.sql.azuresynapse.net"
$rg = 'rg-azuretraining-poc-001'
$DNSnetworklink ="dns-networklink-02"
$privateEndpointName = 'privend-sql-synp-02'
$ZNGrpName="dns-sngrp-02"
$SynapseWorkspaceName='synp-azuretraining-poc-ws001'
$privateEndpointCnName = 'privenc-conn-03'
$VNetName='vnet-azuretraining-southcenteral-001'
$subnetName = 'snet-azuretraining-southcenteral-001'
$Location ="South Central US"
 
 
$Synapseworkspace = Get-AzSynapseWorkspace `
  -ResourceGroupName $rg `
  -Name $SynapseWorkspaceName
## Create private endpoint connection.  
$ParmPvtendpoint01 = @{   
    Name = $privateEndpointCnName   
    PrivateLinkServiceId = $Synapseworkspace.ID  
    GroupID = 'SqlOnDemand'  
                 }
$privateEndpointConnection  `
                = New-AzPrivateLinkServiceConnection @ParmPvtendpoint01
    
# Get virtual network
$vnet = Get-AzVirtualNetwork   `
            -ResourceGroupName $rg   `
            -Name $VNetName
$subnet = Get-AzVirtualNetworkSubnetConfig   `
              -Name $subnetName   `
              -VirtualNetwork $vnet
    
#  Create private endpoint
$ParmPvtendpoint02 = @{ 
    ResourceGroupName = $rg   
    Name = $privateEndpointName   
    Location = $Location   
    Subnet = $subnet   
    PrivateLinkServiceConnection = $privateEndpointConnection  
}
New-AzPrivateEndpoint @ParmPvtendpoint02
#  Create DNS zone group For exisiting DNS Zone
$Zoneconfig = Get-AzPrivateDnsZone   `
 -ResourceGroupName $rg    `
 -Name $DNSZone
 
$privateDnsZoneConfigs= @{                                      
         name= $Zoneconfig.Name
        privateDnsZoneId= $Zoneconfig.ResourceID
}
$ParmDNSzg = @{   
    ResourceGroupName = $rg   
    PrivateEndpointName = $privateEndpointName   
    Name = $ZNGrpName   
    PrivateDnsZoneConfig = $privateDnsZoneConfigs   
}
New-AzPrivateDnsZoneGroup @ParmDNSzg

Adding private end point to dev tools in Azure synapse : Here we are splitting DNS setup and private end point into two different scripts

Create DNS for dev and link to VNet

# DNS for privatelink.dev.azuresynapse.net
$DNSZone="privatelink.dev.azuresynapse.net"
$rg = 'rg-azuretraining-poc-001'
$DNSnetworklink ="dns-networklink-03" 
$VNetName='vnet-azuretraining-southcenteral-001'
$subnetName = 'snet-azuretraining-southcenteral-001'
# Get virtual network
$vnet = Get-AzVirtualNetwork   `
            -ResourceGroupName $rg   `
            -Name $VNetName
$subnet = Get-AzVirtualNetworkSubnetConfig   `
              -Name $subnetName   `
              -VirtualNetwork $vnet
     
#  Create private DNS zone 
$ParmDNS01 = @{  
    ResourceGroupName = $rg  
    Name = $DNSZone 
                }
$zone = New-AzPrivateDnsZone @ParmDNS01
$zone = Get-AzPrivateDnsZone @ParmDNS01
#  Create DNS  network link.  
$ParmDNS02 = @{  
    ResourceGroupName = $rg  
    ZoneName = $DNSZone  
    Name = $DNSnetworklink   
    VirtualNetworkId = $vnet.Id
}
$link = New-AzPrivateDnsVirtualNetworkLink @ParmDNS02
# Create DNS configuration  
$ParmDNS03 = @{   
    Name = $DNSZone  
    PrivateDnsZoneId = $zone.ResourceId   
}
$config = New-AzPrivateDnsZoneConfig @ParmDNS03

Add private endpoint to Dev in using above DNS

$DNSZone="privatelink.dev.azuresynapse.net"
$rg = 'rg-azuretraining-poc-001' 
$privateEndpointName = 'privend-dev-synp-02'
$ZNGrpName="dns-sngrp-03"
$SynapseWorkspaceName='synp-azuretraining-poc-ws001' 
$VNetName='vnet-azuretraining-southcenteral-001'
$subnetName = 'snet-azuretraining-southcenteral-001'
$Location ="South Central US"
$privateEndpointCnName = 'privenc-conn-04'
 
 
$Synapseworkspace = Get-AzSynapseWorkspace `
  -ResourceGroupName $rg `
  -Name $SynapseWorkspaceName
## Create private endpoint connection.  
$ParmPvtendpoint01 = @{   
    Name = $privateEndpointCnName   
    PrivateLinkServiceId = $Synapseworkspace.ID  
    GroupID = 'dev'  
                 }
$privateEndpointConnection  `
                = New-AzPrivateLinkServiceConnection @ParmPvtendpoint01
    
# Get virtual network
$vnet = Get-AzVirtualNetwork   `
            -ResourceGroupName $rg   `
            -Name $VNetName
$subnet = Get-AzVirtualNetworkSubnetConfig   `
              -Name $subnetName   `
              -VirtualNetwork $vnet
    
#  Create private endpoint
$ParmPvtendpoint02 = @{ 
    ResourceGroupName = $rg   
    Name = $privateEndpointName   
    Location = $Location   
    Subnet = $subnet   
    PrivateLinkServiceConnection = $privateEndpointConnection  
}
New-AzPrivateEndpoint @ParmPvtendpoint02
#  Create DNS zone group For exisiting DNS Zone
$Zoneconfig = Get-AzPrivateDnsZone   `
 -ResourceGroupName $rg    `
 -Name $DNSZone
 
$privateDnsZoneConfigs= @{                                      
         name= $Zoneconfig.Name
        privateDnsZoneId= $Zoneconfig.ResourceID
}
$ParmDNSzg = @{   
    ResourceGroupName = $rg   
    PrivateEndpointName = $privateEndpointName   
    Name = $ZNGrpName   
    PrivateDnsZoneConfig = $privateDnsZoneConfigs   
}
New-AzPrivateDnsZoneGroup @ParmDNSzg

Private link hubs are intended for securely loading the static content Synapse Studio over private links. Create a private link hub using API. There is no Powershell or CLI script to invoke this as of this writing

$Url = "https://management.azure.com/subscriptions/"
$subID=(Get-AzContext).Subscription.id
$rgurl="/resourceGroups/"
$rg="rg-azuretraining-poc-001"
$provd="/providers/Microsoft.Synapse/privateLinkHubs/"
$prvtLinkName="pvtlnkhubazuretrainingpoc01"
$apiversion="?api-version=2021-06-01"
$URI = $Url+$subID+$rgurl+$rg+$provd+$prvtLinkName+$apiversion
$Cred = Get-Credential
$Body = @{
    location: "South Central US"
}
Invoke-RestMethod -Method 'Post' -Uri $URI -Credential $Cred -Body $Body -OutFile output.csv

Now create DNS entries for the private link hub

# DNS for privatelink.azuresynapse.net
$DNSZone="privatelink.azuresynapse.net"
$rg = 'rg-azuretraining-poc-001'
$DNSnetworklink ="dns-networklink-04" 
$VNetName='vnet-azuretraining-southcenteral-001'
$subnetName = 'snet-azuretraining-southcenteral-001'
# Get virtual network
$vnet = Get-AzVirtualNetwork   `
            -ResourceGroupName $rg   `
            -Name $VNetName
$subnet = Get-AzVirtualNetworkSubnetConfig   `
              -Name $subnetName   `
              -VirtualNetwork $vnet
     
#  Create private DNS zone 
$ParmDNS01 = @{  
    ResourceGroupName = $rg  
    Name = $DNSZone 
                }
$zone = New-AzPrivateDnsZone @ParmDNS01
$zone = Get-AzPrivateDnsZone @ParmDNS01
#  Create DNS  network link.  
$ParmDNS02 = @{  
    ResourceGroupName = $rg  
    ZoneName = $DNSZone  
    Name = $DNSnetworklink   
    VirtualNetworkId = $vnet.Id
}
$link = New-AzPrivateDnsVirtualNetworkLink @ParmDNS02
# Create DNS configuration  
$ParmDNS03 = @{   
    Name = $DNSZone  
    PrivateDnsZoneId = $zone.ResourceId   
}
$config = New-AzPrivateDnsZoneConfig @ParmDNS03

Add private endpoint from VNet to private link hub over web using above DNS for Synapse studio workbench

$DNSZone="privatelink.azuresynapse.net"
$rg = 'rg-azuretraining-poc-001' 
$privateEndpointName = 'privend-web-synp-studio-01'
$ZNGrpName="dns-sngrp-04"
$SynapseWorkspaceName='synp-azuretraining-poc-ws001' 
$VNetName='vnet-azuretraining-southcenteral-001'
$subnetName = 'snet-azuretraining-southcenteral-001'
$Location ="South Central US"
$privateEndpointCnName = 'privenc-conn-05'
$Url = "/subscriptions/"
$subID=(Get-AzContext).Subscription.id
$rgurl="/resourceGroups/"
$rg="rg-azuretraining-poc-001"
$provd="/providers/Microsoft.Synapse/privateLinkHubs/"
$prvtLinkName="pvtlnkhubazuretrainingpoc01" 
$privateLinkResource = $Url+$subID+$rgurl+$rg+$provd+prvtLinkName
 
$ParmPvtendpoint01 = @{   
    Name = $privateEndpointCnName   
    PrivateLinkServiceId = $privateLinkResource
 GroupID ="Web"}
 
$privateEndpointConnection  `
            = New-AzPrivateLinkServiceConnection @ParmPvtendpoint01
# Get virtual network
$vnet = Get-AzVirtualNetwork   `
            -ResourceGroupName $rg   `
            -Name $VNetName
$subnet = Get-AzVirtualNetworkSubnetConfig   `
              -Name $subnetName   `
              -VirtualNetwork $vnet
    
#  Create private endpoint
$ParmPvtendpoint02 = @{ 
    ResourceGroupName = $rg   
    Name = $privateEndpointName   
    Location = $Location   
    Subnet = $subnet   
    PrivateLinkServiceConnection = $privateEndpointConnection  
}
New-AzPrivateEndpoint @ParmPvtendpoint02
#  Create DNS zone group For exisiting DNS Zone
$Zoneconfig = Get-AzPrivateDnsZone   `
 -ResourceGroupName $rg    `
 -Name $DNSZone
 
$privateDnsZoneConfigs= @{                                      
         name= $Zoneconfig.Name
        privateDnsZoneId= $Zoneconfig.ResourceID
}
$ParmDNSzg = @{   
    ResourceGroupName = $rg   
    PrivateEndpointName = $privateEndpointName   
    Name = $ZNGrpName   
    PrivateDnsZoneConfig = $privateDnsZoneConfigs   
}
New-AzPrivateDnsZoneGroup @ParmDNSzg

We have built a Synapse workspace with private endpoint communication to all its resources.