Tag: azure

Private Link Service for Snowflake OpenFlow: Technical Overview

Understanding Private Link Service vs Private Endpoint

Key Distinction

  • Private Endpoint: Allows resources within your VNet to connect outbound to Azure services
  • Private Link Service: Allows external services to connect inbound to resources in your VNet

The Challenge with VNet-Injected MySQL Flexible Server

When MySQL Flexible Server is VNet-injected:

  • It exists entirely within your private VNet address space
  • Traditional private endpoints are not supported (as your engineering team noted)
  • External services like Snowflake OpenFlow cannot directly reach it

How Private Link Service Solves This

Architecture Flow

Snowflake OpenFlow → Private Endpoint (Snowflake's VNet) → Private Link Service (Your VNet) → MySQL Flexible Server

Step-by-Step Process

  1. Private Link Service Creation
  • You create a Private Link Service in your VNet
  • This service acts as a secure “front door” to your MySQL server
  • It gets a unique service identifier (alias)
  1. Load Balancer Integration
  • Private Link Service requires a Standard Load Balancer
  • Load balancer backend pool contains your MySQL Flexible Server
  • Traffic routing is handled transparently
  1. Connection Establishment
  • Snowflake creates a Private Endpoint in their VNet
  • This Private Endpoint connects to your Private Link Service
  • Connection request appears in your Azure portal for approval
  1. Traffic Flow
  • OpenFlow sends requests to their Private Endpoint
  • Traffic routes through the Private Link connection to your Private Link Service
  • Your Load Balancer forwards traffic to MySQL Flexible Server
  • Responses follow the reverse path

Traffic Direction Analysis

Inbound Connection Requirement

YES, the inbound connection shown in your diagram is necessary because:

  • OpenFlow Architecture: Snowflake OpenFlow runs in Snowflake’s infrastructure and must connect TO your database
  • CDC Requirements: Change Data Capture requires persistent connections from OpenFlow to monitor MySQL binlogs
  • Connection Initiation: The connection is always initiated from Snowflake’s side, making it inherently inbound to your infrastructure

Traffic Flow Breakdown

PhaseDirectionDescription
Connection SetupSnowflake → Your VNetOpenFlow establishes persistent connection
Binlog MonitoringSnowflake → MySQLContinuous monitoring for changes
Change NotificationMySQL → SnowflakeData changes sent back
Heartbeat/HealthBidirectionalConnection maintenance

Security Benefits

Network Isolation

  • No public IP addresses required on MySQL
  • Traffic never traverses the public internet
  • Connection uses Azure’s backbone network

Access Control

  • You control which services can connect via Private Link Service
  • Connection requests require your explicit approval
  • NSG rules can further restrict traffic

Monitoring

  • All connections are logged and auditable
  • Private Link Service provides connection metrics
  • Standard Azure monitoring applies

Implementation Requirements

Prerequisites

  • Standard Load Balancer (required for Private Link Service)
  • MySQL Flexible Server in VNet-injected mode
  • Appropriate NSG rules
  • Resource permissions for Private Link Service creation

Configuration Steps

  1. Create Standard Load Balancer with MySQL in backend pool
  2. Create Private Link Service linked to the Load Balancer
  3. Configure NSG rules to allow traffic from Private Link Service subnet
  4. Share Private Link Service alias with Snowflake team
  5. Approve connection request when it appears
  6. Configure OpenFlow connector with connection details

Why This Approach Works

The Private Link Service architecture elegantly solves the fundamental challenge:

  • Your Constraint: VNet-injected MySQL cannot have traditional private endpoints
  • Snowflake’s Need: OpenFlow requires inbound connectivity for CDC
  • The Solution: Private Link Service provides secure inbound connectivity without compromising your network isolation

This is Microsoft and Snowflake’s recommended pattern for exactly this scenario, allowing enterprise-grade security while enabling real-time data integration.

How Increasing Azure PostgreSQL IOPS Supercharged Our Bulk Insert Performance

Loading millions of records into a cloud database can be a frustratingly slow task—unless you identify where your bottlenecks are. In this post, I will share how we significantly improved our insertion speeds on Azure Database for PostgreSQL Flexible Server by adjusting a single, often-overlooked setting: provisioned IOPS.

The Challenge: Slow Inserts Despite Low CPU

We were running a large data migration from Databricks to Azure Database for PostgreSQL Flexible Server. Our setup:

  • Instance: Memory Optimized, E8ds_v4 (8 vCores, 64 GiB RAM, 256 GiB Premium SSD)
  • Insert Method: 8 parallel threads from Databricks, each batching 50,000 rows

Despite this robust configuration, our insert speeds were disappointing. Monitoring showed:

  • CPU usage: ~10%
  • Disk IOPS: 100% utilization

Clearly, our CPU wasn’t the problem—disk I/O was.

The Bottleneck: Disk IOPS Saturation

Azure Database for PostgreSQL Flexible Server ties write performance directly to your provisioned IOPS (Input/Output Operations Per Second). PostgreSQL is forced to queue up write operations when your workload hits this limit, causing inserts to slow down dramatically.

Key signs you’re IOPS-bound:

  • Disk IOPS metric at or near 100%
  • Low CPU and memory utilization
  • Inserts (and possibly other write operations) are much slower than expected

The Fix: Increase Provisioned IOPS

We increased our provisioned IOPS from 1,100 to 5,000 using the Azure Portal:

  1. Go to your PostgreSQL Flexible Server in Azure.
  2. Select Compute + storage.
  3. Adjust the IOPS slider (or enter a higher value if using Premium SSD v2).
  4. Save changes—no downtime required.

Result:
Insert speeds improved immediately and dramatically. Disk performance no longer throttled the database, and we could fully utilize our CPU and memory resources.

Lessons Learned & Best Practices

  • Monitor your bottlenecks: Always check disk IOPS, CPU, and memory during heavy data loads.
  • Scale IOPS with workload: Azure lets you increase IOPS on the fly. For bulk loads, temporarily raising IOPS can save hours or days of processing time.
  • Batch and parallelize wisely: Match your parallel threads to your vCPU count, but remember that IOPS is often the true limiter for bulk writes.
  • Optimize indexes and constraints: Fewer indexes mean fewer writes per insert. Drop non-essential indexes before bulk loads and recreate them afterward.

Conclusion:
If your PostgreSQL inserts are slow on Azure, check your disk IOPS. Increasing provisioned IOPS can unlock the performance your hardware is capable of—sometimes, it’s the simplest tweak that makes the biggest difference.

Query Azure MySQL Audit Logs

The common practice for user management in Azure AD is to create Azure AD Security Groups for users and assign permissions to groups rather than individual users. For example, the common practice for users who need access to Azure MySQL instance would to be add them to an Azure AD Group (say MySqlDb_Users) and then setup this Azure AD Group for access on Azure MySQL Instance.

Pre-requisite

  • Ensure that Audit Logs are enabled on all the production instances for Azure MySql as documented here — https://docs.microsoft.com/en-us/azure/mysql/howto-configure-audit-logs-portal
  • Make sure to enable Auditing on Server Parameters section (at least Connection audit log event type).
  • The logs can be saved to Storage Account, Event Hubs or Azure Log Analytics Workspace. We used Log Analytics Workspace for the runbook because it provides rich querying capabilities.

The following Log Analytics Query and the corresponding screenshot shows that individual user activity is tracked in the Audit Logs in spite of them using the Azure AD Group as username to connect Azure MySQL — external_user_s field logs the exact individual user, connection_id_d and thread_id_d carry over the Connection/ThreadId which can be used to tie the user across all log records.

AzureDiagnostics
| where Category == "MySqlAuditLogs"
| order by TimeGenerated desc
| project TimeGenerated, event_class_s, event_subclass_s, user_s, external_user_s, connection_id_d, thread_id_d, event_time_t, sql_text_s, db_s, table_name_s, error_code_d

Upload files and folder into Azure Blob Storage using Powershell

Recently, I have had to automate a process to generate csv files and upload them to a certain folder in Azure blob storage. Here is the powershell code that does just that. After some research online, I have put together couple of solutions into one that worked well for me.

In order for this to work, you will need to install Azure powershell module on your machine. This solution assumes you are authenticating using account name and storage account key

#Upload csv files to Azure Blob Storage
$ErrorActionPreference = "Stop"

$acct = "storage-account-name" #Storage Account Name
$key = "storage-account-key" #Storage Account Key
$ContainerName = "container-name" #Container Name
$containerdirectory = "subfolder path within the container"
$localfilepath = "local-file-directory"

#create a context for communicating with azure storage
$ctx = New-AzStorageContext -StorageAccountName $acct -StorageAccountKey $key -Protocol Https
$container = Get-AzStorageContainer -Name $ContainerName -Context $ctx

$container.CloudBlobContainer.Uri.AbsoluteUri

if ($container) {
#use Set-AzStorageBlobContent to upload file
    $filesToUpload = Get-Childitem -Path $localfilepath -Filter "*.csv" 

        ForEach ($x in $filesToUpload) { 
            $targetPath = $containerdirectory+($x.fullname.Substring($localfilepath.Length)).Replace("\", "/")

            Write-Verbose "Uploading $("\" + $x.fullname.Substring($localfilepath.Length)) to $($container.CloudBlobContainer.Uri.AbsoluteUri + "/" + $targetPath)"
            Set-AzStorageBlobContent -File $x.fullname -Container $container.Name -Blob $targetPath -Context $ctx -Force:$Force | Out-Null
            }
        }