Azure SQL & Best Practices
Understand what Azure SQL Database offers compared to on-premises SQL Server, and wrap up the course with general T-SQL and database design best practices.
Introduction
You have now covered T-SQL fundamentals, joins, aggregation, CTEs, views, indexes, procedures, functions, triggers, transactions, error handling, window functions, dynamic SQL, security, backups, performance tuning, and connecting from .NET. This final lesson looks at Azure SQL Database - Microsoft's fully managed cloud version of SQL Server - and closes the course with a consolidated set of best practices worth carrying into every real project.
- How Azure SQL Database differs from on-premises SQL Server.
- The main Azure SQL deployment options.
- DTU-based vs vCore-based purchasing models.
- What elastic pools are for.
- A consolidated set of schema, query, and operational best practices.
On-Premises vs Azure SQL
On-premises SQL Server runs on hardware (physical or virtual) that you or your organization fully manage - patching, backups, high availability, and capacity planning are all your responsibility. Azure SQL Database is a Platform-as-a-Service (PaaS) offering: Microsoft manages the underlying OS, patching, and infrastructure, and provides built-in automated backups and high availability, letting teams focus on schema and application logic instead of server maintenance.
| Aspect | On-Premises SQL Server | Azure SQL Database |
|---|---|---|
| Patching / OS maintenance | Your responsibility | Handled automatically by Azure |
| Backups | You configure and manage | Automated, built in |
| Scaling | Requires new hardware or manual reconfiguration | Scale up/down via a slider or API call |
| High availability | You design and implement (e.g. Always On) | Built in, configurable by service tier |
| Full server-level control | Yes (e.g. SQL Agent, cross-database queries) | Limited - it is a managed database, not a full server |
Azure SQL Deployment Options
Azure offers more than one way to run SQL Server workloads in the cloud, trading off management overhead against compatibility with on-prem features.
| Option | Description |
|---|---|
| Azure SQL Database | Fully managed PaaS database - highest automation, some feature limitations vs full SQL Server |
| Azure SQL Managed Instance | Near-complete SQL Server surface area (cross-database queries, SQL Agent) as a managed service |
| SQL Server on Azure VMs | Full SQL Server installed on an Azure virtual machine - most control, most management responsibility (IaaS) |
DTU vs vCore
Azure SQL Database offers two purchasing models. The DTU (Database Transaction Unit) model bundles compute, memory, and I/O into a single blended measure and simple tiers (Basic, Standard, Premium) - simpler to reason about for smaller workloads. The vCore model lets you choose compute and storage independently, mirrors on-premises core-based licensing more closely, and supports Azure Hybrid Benefit for organizations with existing SQL Server licenses.
| Model | Good For |
|---|---|
| DTU-based | Simpler workloads, easier initial sizing |
| vCore-based | Predictable, granular scaling; reusing existing SQL Server licenses |
Elastic Pools
An elastic pool lets multiple Azure SQL databases share a pool of compute and storage resources instead of each being provisioned separately. This fits multi-tenant applications well, where many databases (one per customer, for example) each have unpredictable, bursty usage - the pool absorbs those spikes collectively instead of over-provisioning every single database for its individual worst case.
Schema Design Best Practices
- Normalize to reduce redundancy, but denormalize deliberately where read performance genuinely requires it.
- Choose appropriate, narrow data types - do not default everything to NVARCHAR(MAX) or BIGINT.
- Enforce data integrity with PRIMARY KEY, FOREIGN KEY, and CHECK constraints rather than relying only on application code.
- Give every table a clear, consistent naming convention and a suitable clustered index (usually the primary key).
Query Writing Best Practices
- Select only the columns you need instead of SELECT *.
- Filter and join on indexed columns, and avoid wrapping indexed columns in functions in a WHERE clause.
- Always parameterize dynamic SQL and application-level queries, never concatenate untrusted input.
- Wrap related writes in an explicit transaction with TRY...CATCH for safe rollback on error.
- Use window functions instead of correlated subqueries for running totals and rankings where possible.
Operational Best Practices
- Follow the principle of least privilege for every login, user, and application.
- Take regular backups, and actually test restoring them.
- Monitor and periodically review index usage, dropping unused indexes and adding missing ones based on real evidence.
- Use execution plans and SET STATISTICS IO to make tuning decisions objectively rather than by guesswork.
- Keep statistics up to date so the query optimizer's estimates stay accurate as data grows and changes.
Common Mistakes
- Treating SELECT * and unindexed WHERE clauses as harmless in production-scale tables.
- Skipping transactions and error handling on multi-step writes, risking half-applied changes.
- Granting broad permissions (db_owner, sysadmin) to application accounts out of convenience.
- Building dynamic SQL or application queries by string concatenation instead of parameters.
- Choosing a cloud tier or on-prem sizing without measuring actual workload needs first.
Frequently Asked Questions
For most new projects, Azure SQL Database's managed backups, patching, and scaling reduce operational burden significantly; on-premises or IaaS (VMs) still make sense when specific SQL Server features, full server-level control, or existing infrastructure investment are required.
Often yes, though Azure SQL Database has some feature differences from full SQL Server (for example, limited cross-database queries), so Microsoft's Data Migration Assistant is commonly used first to check compatibility; Azure SQL Managed Instance offers closer compatibility if those gaps matter.
It lets organizations with existing SQL Server licenses covered by Software Assurance apply those licenses toward Azure SQL vCore pricing, reducing cost compared to paying for compute and a new license together.
The vast majority of T-SQL - everything covered in this course - works identically. Azure SQL Database omits a small number of server-level features (like cross-database queries and SQL Server Agent jobs in their on-prem form), which Managed Instance or VMs support if needed.
Key Takeaways
- Azure SQL Database is a fully managed PaaS offering; Managed Instance and Azure VMs trade some automation for more on-prem-like control.
- DTU pricing bundles resources simply; vCore pricing separates compute and storage and supports license reuse.
- Elastic pools share resources efficiently across many databases with variable usage.
- Solid schema design, safe query writing, and disciplined operations matter regardless of where SQL Server runs.
- The core T-SQL skills from this course - joins, transactions, indexing, security, tuning - transfer directly to Azure SQL.
Summary
Whether you deploy SQL Server on your own hardware, on an Azure virtual machine, or as a fully managed Azure SQL Database, the fundamentals you have built through this course - solid T-SQL, thoughtful indexing, safe transactions, least-privilege security, and disciplined performance tuning - are what actually make a database reliable and fast. That is the real skill, and it travels with you across every environment.