Your First Azure VM: Same SQL Server, New Rules

Shannon Lowder

Shannon Lowder

— 3 min read

Spinning up your first Azure VM feels almost anticlimactic. You pick an image, choose a size, click through the wizard, wait ten minutes, and there's a SQL Server instance accessible from SSMS. Same engine, same T-SQL, same management tools. It's easy to conclude that you've just moved your on-prem server to the cloud and everything else stays the same.

That conclusion is wrong, and the places where it's wrong will bite you if you don't know about them. Here's what actually changes when SQL Server runs on an Azure VM.

Storage Is Different

On a physical server, your data files sit on drives directly attached to the machine. The I/O path is short and fast. On an Azure VM in 2012, your OS disk is backed by Azure Blob Storage — network-attached, not locally attached. This has consequences.

The OS disk had a specific IOPS limit. Running SQL Server's data files, log files, and TempDB all on the OS disk — which is what you'd get if you just installed SQL Server and accepted defaults — meant sharing that limited IOPS budget across everything. Under load, this caused I/O bottlenecks that looked like inexplicable performance problems until you understood the storage architecture.

The correct configuration:

  • Separate data disk(s) for data files — attach additional data disks (Premium Storage for SSD-backed, Standard for HDD) and place your .mdf files there
  • Separate data disk for log files — transaction logs are sequential write-heavy; they benefit from dedicated disk bandwidth
  • Separate disk for TempDB — TempDB is often the most I/O-intensive component; it needs its own disk, or use the instance's local SSD temp storage if your VM size includes it (data there is non-persistent, which is fine for TempDB)
-- After attaching and formatting separate data disks:
-- Move data files to D:\SQLData
-- Move log files to L:\SQLLog
-- Move TempDB to T:\SQLTemp

ALTER DATABASE YourDatabase
MODIFY FILE (NAME = YourDatabase_data, FILENAME = 'D:\SQLData\YourDatabase.mdf');

ALTER DATABASE YourDatabase
MODIFY FILE (NAME = YourDatabase_log, FILENAME = 'L:\SQLLog\YourDatabase_log.ldf');

Networking Has Rules You Don't Control

On a physical server, you control the firewall and the network configuration. On an Azure VM, network traffic is controlled by Network Security Groups (NSGs) at the Azure level, before packets ever reach the VM's Windows Firewall. You need to open the right ports in the NSG for SQL Server to be reachable — and you need to be deliberate about which source IP ranges you allow, because an open SQL Server port to the internet will be brute-forced within hours.

The minimal NSG configuration for SQL Server access:

  • TCP port 1433 — SQL Server default instance
  • Source: your specific IP ranges, not Any
  • TCP port 3389 (RDP) — for management, also restricted to known IPs

No exceptions to the "never open 1433 to Any" rule. Use a VPN or Azure Virtual Network for broad access patterns if you need connectivity from multiple variable IPs.

Patching Is Still Your Job

This is the biggest mindset difference from SQL Azure. On the managed service, Microsoft patches the OS and SQL Server engine. On an IaaS VM, you own the patching. Windows updates, SQL Server service packs, cumulative updates — all your responsibility, all of them requiring a maintenance window and a plan for what happens if a patch causes a regression.

Build a patching process into your runbook before you put production workloads on an Azure VM. At minimum: monthly Windows patches with a tested rollback plan, SQL Server cumulative updates on a quarterly schedule with a test environment pass first, and a monitoring check after each patching cycle to verify expected performance baselines.

Backup Needs Explicit Configuration

SQL Azure had point-in-time restore built in. SQL Server on an Azure VM has whatever backup configuration you set up. If you install SQL Server and do nothing about backups, there are no backups. Azure VM snapshots exist but they're not a SQL Server-aware backup — they're disk-level snapshots that can leave the database in an inconsistent state if taken while SQL Server is running.

The right answer: SQL Server Agent jobs for full, differential, and transaction log backups, writing BACKUP TO URL directly to Azure Blob Storage. This gives you SQL Server-consistent backups, off-VM storage, and point-in-time recovery. Set this up on day one, verify the backups are restorable, and test a restore before production go-live.

-- Backup to Azure Blob Storage
BACKUP DATABASE YourDatabase
TO URL = 'https://yourstorageaccount.blob.core.windows.net/sqlbackups/YourDatabase_full.bak'
WITH CREDENTIAL = 'AzureStorageCredential',
     COMPRESSION, STATS = 10;

Microsoft provided pre-built gallery images — Windows Server with SQL Server pre-installed and pre-configured — that handled some of the installation complexity. These images were pre-activated with SQL Server licensing included in the VM compute cost, which avoided the separate licensing procurement for clients who didn't have Software Assurance.

The gallery images were a reasonable starting point, but they came with default configurations that weren't tuned for production: SQL Server placed on the OS disk, MAXDOP set to the default (which on an 8-core VM meant SQL Server might use all 8 cores on a single query), max server memory unconfigured (meaning SQL Server could consume all available RAM, leaving nothing for the OS). Apply the production configuration checklist before putting workloads on a gallery-image VM. The image gives you the bits; you're still responsible for the configuration.

Same SQL Server. Different rules. Know the rules before you go to production. As always, I'm here to help.

Read more