Building Database Administration Standards for Azure

Shannon Lowder

Shannon Lowder

— 3 min read

Every client environment I walked into in 2011 had a version of the same problem: a SQL Server instance that had grown organically over years, with naming conventions that reflected whoever set it up in 2004, backup policies that made sense when the server had 20 GB of storage, and security configurations that had accumulated like sediment without anyone designing them. Moving to Azure was an opportunity to reset that — but only if you wrote down what "better" actually looked like before you started building.

This is the set of standards I developed for Azure SQL deployments in 2011. Some of these seem obvious in retrospect. At the time, they represented decisions that teams were making inconsistently and then fighting about later.

Naming Conventions

Azure resources are globally scoped in ways on-prem resources aren't. Your SQL Azure server name becomes part of the connection string: yourservername.database.windows.net. That name has to be globally unique across all Azure customers. This forces a naming decision you'd normally defer.

The pattern I standardized on:

-- Server naming: {client-abbreviation}-{environment}-sql
-- Examples:
acme-prod-sql.database.windows.net
acme-dev-sql.database.windows.net
acme-staging-sql.database.windows.net

-- Database naming: {application}-{purpose}
-- Examples:
crm-transactional
crm-reporting
billing-archive

Client abbreviation in the server name mattered for multi-tenant consulting work — I was managing multiple client environments from the same Azure subscription at points, and being able to identify the client from the resource name without opening the portal saved time on every support call.

Environment Separation

Dev, staging, and production get separate SQL Azure servers. Not separate databases on the same server — separate servers. The reasons are:

  • Firewall rules are at the server level in SQL Azure. A single rule change that needs to apply to prod shouldn't require you to verify it won't affect dev first.
  • Resource contention on a shared server is invisible and unpredictable. Prod workloads and dev queries running on the same tier affect each other in ways you can't easily isolate.
  • Billing and cost visibility. Separate servers give you separate cost lines you can attribute to each environment.

Firewall Rules

SQL Azure's server-level firewall was the first layer of network security. The standard I applied:

  • No rule for 0.0.0.0 to 255.255.255.255 (the "allow all Azure services" shortcut that some quickstart guides recommended). Too broad.
  • Named rules, one per source, with the source in the rule name: ClientOffice-Chicago, ConsultantHomeVPN, AppServiceOutbound-EastUS
  • Rules reviewed quarterly and any rule older than 90 days with no corresponding documented justification gets removed.

Backup and Retention Policy

SQL Azure's built-in point-in-time restore covered 7 days. That covered most operational recovery scenarios — "I accidentally deleted this record from production three days ago." It did not cover compliance scenarios requiring 30- or 90-day retention, or disaster scenarios where you needed an offline copy outside of Azure.

The standard I built:

  • Automated weekly BACPAC export to Azure Blob Storage using an Azure Worker Role scheduled task
  • Blob Storage lifecycle policy: keep weekly backups for 90 days, monthly backups (the first Sunday of each month) for 1 year
  • Restore test quarterly — actually restore a BACPAC to a separate dev server and verify the data

Access Tiers

Four service accounts per environment, no exceptions:

  • AppUser — db_datareader, db_datawriter only. This is what the application connects as.
  • ReportUser — db_datareader only. Reporting tools connect as this account.
  • MigrationsUser — db_ddladmin in addition to reader/writer. Used only during deployments for schema changes. Password rotated after each deployment window.
  • AdminUser — db_owner. Used for break-glass scenarios only. Credentials stored in a secure vault, not in any application config.

The discipline of separating the application account from the migrations account was the single most useful thing this standard enforced. It made "the app account should never be able to drop a table" a system constraint rather than a policy that depended on everyone remembering.

Why Write This Down

Standards documents exist to make the second deployment easier than the first. When I onboarded a new client in 2012, I had a checklist. When a junior team member set up a dev environment, they had a checklist. The decisions had already been made, the tradeoffs documented, the reasoning preserved. That's the whole point.

If you're setting up Azure SQL environments and making it up as you go, you're accumulating technical debt before you've written a line of application code. Write the standards first. You can always revise them. You can't easily fix a production environment built without them.

I'm happy to share the full template I developed for this if it's useful. As always, I'm here to help.

Read more