Virtual Network architecture 4 - SQL Database Private Endpoit
Published: 2022-08-29 14:38:00

# Summary

This article is Part.4 of virtual network architecture series. I explain the details of the private endpoint with Azure SQL Database in [api-management-vnet](https://github.com/koheikawata/api-management-vnet). 

- [Virtual Network architecture 1 - Do I need virtual network?](https://dev.to/koheikawata/virtual-network-architecture-1-do-i-need-virtual-network-1nhk)
- [Virtual Network architecture 2 - Deployment pipelines](https://dev.to/koheikawata/virtual-network-architecture-2-deployment-pipelines-39p5)
- [Virtual Network architecture 3 - Key Vault Private Endpoint](https://dev.to/koheikawata/virtual-network-architecture-3-key-vault-private-endpoint-43nj)
- [Virtual Network architecture 4 - SQL Database Private Endpoit](https://dev.to/koheikawata/virtual-network-architecture-4-sql-database-private-endpoit-49o2)
- [Virtual Network architecture 5 - App Service Private Endpoint](https://dev.to/koheikawata/virtual-network-architecture-5-app-service-private-endpoint-5eao)
- [Virtual Network architecture 6 - Service Bus Private Endpoint](https://dev.to/koheikawata/virtual-network-architecture-6-service-bus-private-endpoint-oa)
- [Virtual Network architecture 7 - Self-hosted agent](https://dev.to/koheikawata/virtual-network-architecture-7-self-hosted-agent-32j4)

# TOC
- [Private Endpoint configuration](#configuration)
- [Access to SQL Database](#access)
- [SQL project update](#update)

# Private Endpoint configuration <a name="configuration"></a>

- **Private Endpoint**: Deploy the private endpoint and connect it to SQL Database and its subnet in [PrivateEndpoint.bicep](https://github.com/koheikawata/api-management-vnet/blob/main/src/bicep/vnet/modules/PrivateEndpoint.bicep).

```
SqlServerId:existingSql.id
VirtualNetwork2SubnetIdSql:existingVnet2.properties.subnets[2].id

resource PrivateEndpointSql 'Microsoft.Network/privateEndpoints@2021-03-01' = {
  properties: {
    privateLinkServiceConnections: [
      {
        properties: {
          privateLinkServiceId: SqlServerId
          groupIds: [
            'sqlServer'
          ]
        }
      }
    ]
    subnet: {
      id: VirtualNetwork2SubnetIdSql
      properties: {
        privateEndpointNetworkPolicies: 'Enabled'
      }
    }
  }
}
```

- **Private DNS**: Deploy Private DNS with the DNS name `privatelink.database.windows.net` in [PrivateDns2.bicep](https://github.com/koheikawata/api-management-vnet/blob/main/src/bicep/vnet/modules/PrivateDns2.bicep).

```
var pdns_name_sql = 'privatelink${environment().suffixes.sqlServerHostname}'

resource PrivateDnsSql 'Microsoft.Network/privateDnsZones@2020-06-01' = {
  name: pdns_name_sql
  location: 'global'
}
```
- **Virtual network link**: Link the deployed Private DNS to the virtual network where the SQL Server subnet exists in [PrivateDns2.bicep](https://github.com/koheikawata/api-management-vnet/blob/main/src/bicep/vnet/modules/PrivateDns2.bicep).

```
VirtualNetwork2Id:existingVnet2.id

resource VnetLinkSql 'Microsoft.Network/privateDnsZones/virtualNetworkLinks@2020-06-01' = {
  name: '${PrivateDnsSql.name}/${PrivateDnsSql.name}-link'
  location: 'global'
  properties: {
    registrationEnabled: false
    virtualNetwork: {
      id: VirtualNetwork2Id
    }
  }
}
```

- **Private DNS A record**: Create a DNS A record and set the IP address from the deployed private endpoint in [PrivateDns2.bicep](https://github.com/koheikawata/api-management-vnet/blob/main/src/bicep/vnet/modules/PrivateDns2.bicep).

```
output PrivateEndpointSqlIpAddress string = PrivateEndpointSql.properties.customDnsConfigs[0].ipAddresses[0]

resource PrivateDnsASql 'Microsoft.Network/privateDnsZones/A@2020-06-01' = {
  name: '${PrivateDnsSql.name}/${SqlServerName}'
  properties: {
    ttl: 3600
    aRecords: [
      {
        ipv4Address: PrivateEndpointSqlIpAddress
      }
    ]
  }
}
```

# Access to SQL Database <a name="access"></a>

App Service and Functions need to access the SQL Database to insert and extract the data records. Both can access through V-net integration and Private Endpoint. In [SqlDatabase2.bicep](https://github.com/koheikawata/api-management-vnet/blob/main/src/bicep/vnet/modules/SqlDatabase2.bicep), public network access is disabled so no one can access through the public IP. 

```
resource SqlServer 'Microsoft.Sql/servers@2021-02-01-preview' = {
  properties: {
    publicNetworkAccess: 'Disabled'
  }
}
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/szwktor3ci7dnwcjh32h.png)

# SQL project update <a name="access"></a>

The biggest problem is when you want to update SQL projects including data schema on the Azure SQL Database. In this sample template, you cannot update the SQL project because a Linux based self-hosted agent does not support build and deployment of SQL Database projects. On the other hand, a Windows based self-hosted agent in the docker container on Azure Container Instance cannot be deployed on the virtual network as I mentioned in the previous article [Virtual Network architecture 2 - Deployment pipelines](https://dev.to/koheikawata/virtual-network-architecture-2-deployment-pipelines-39p5#pipeline-vnet1). Possible workaround is to create a Windows based self-hosted agent on a Virtual Machine, to change IP filtering rules when in code deployment, or to wait for the Windows docker container supporting the virtual network deployment.