Azure SQL Database

1. What is Azure SQL Database?

• Supports most SQL Server features and T-SQL syntax.

• Eliminates need for manual patching, backups, monitoring, and infrastructure maintenance.

• Offers built-in intelligence to optimize performance and security.

2. Core Architecture and Deployment Models

• Single Database: Isolated, managed database with dedicated compute and storage resources.

• Elastic Pool: A collection of databases sharing resources dynamically to optimize cost and performance.

• Managed Instance: Provides near 100% compatibility with on-prem SQL Server with instance-level features and network isolation.

3. Key Features and Capabilities

• Scalability: Elastic compute and storage scaling with minimal downtime.

• High Availability: Built-in geo-replication, zone redundancy, and fault tolerance with automatic failover.

• Security: Advanced threat protection, encryption at rest and in transit, firewall, and VNet integration.

• Performance Optimization: Automatic tuning, intelligent query processing, in-memory technologies.

• Backup and Restore: Automated full, differential, and transaction log backups with point-in-time restore.

• Compatibility: Supports T-SQL, SQL Server tools, and cross-platform drivers.

4. Pricing Models and Service Tiers

5. Development and Tooling

• Supports all standard SQL Server development tools: SQL Server Management Studio (SSMS), Azure Data Studio, Visual Studio.

• Compatible with .NET, Java, Python, PHP, Node.js via standard SQL drivers (ODBC, JDBC, ADO.NET).

• Integration with Azure DevOps CI/CD pipelines for automated deployments (using DACPACs, ARM templates, or Terraform).

• Supports dynamic scaling via REST APIs, Portal, CLI, or PowerShell.

Sample: Connecting and Querying using C# (ADO.NET)

using System;
using System.Data.SqlClient;

string connectionString = "Server=tcp:<your_server>.database.windows.net,1433;Initial Catalog=<your_db>;Persist Security Info=False;User ID=<user>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand("SELECT TOP 10 * FROM dbo.MyTable", conn);
    SqlDataReader reader = cmd.ExecuteReader();
    while(reader.Read())
    {
        Console.WriteLine(reader[0].ToString());
    }
}

6. Configuration & Management

• Manage server-level firewall to whitelist client IPs.

• Configure Active Directory authentication for Azure AD users/groups.

• Scale compute tier or storage size on demand through Portal/CLI/ARM.

• Configure Advanced Data Security, Auditing, and Threat Detection for compliance.

• Manage backups, long-term retention policies, and geo-replication settings.

7. Security and Identity Management (IAM)

8. High Availability, Geo-Replication, and Disaster Recovery

• Zone-redundant configurations spread replicas across availability zones.

• Active Geo-Replication: Create up to 4 readable secondary replicas globally for read scaling and disaster recovery.

• Auto-failover Groups: Coordinated failover of groups of databases across regions for high availability.

• Automated backups enable point-in-time restore across retention period.

9. Monitoring & Performance

• Integrates with Azure Monitor for metrics and alerts.

• Provides Query Performance Insight, Intelligent Insights for workload optimization.

• Diagnostic logs for auditing, query store for query analysis.

• Support for Dynamic Management Views (DMVs) for performance troubleshooting.

10. Advanced Features and Integration

• Intelligent Query Processing and Automatic Tuning: Adaptive joins, memory grants, and performance tuning.

• Hyperscale Service Tier: Decouples compute and storage for rapid scale, supports large databases.

• In-memory OLTP and Columnstore indexes for performance gains.

• Integration with Azure Data Factory, Power BI, Azure Synapse Analytics, and Machine Learning.

• Supports Temporal Tables for auditing data changes.

11. Sample ARM Template Snippet for Azure SQL Database Deployment

{
  "type": "Microsoft.Sql/servers/databases",
  "apiVersion": "2021-05-01-preview",
  "name": "[concat(parameters('sqlServerName'), '/', parameters('databaseName'))]",
  "location": "[resourceGroup().location]",
  "properties": {
    "collation": "SQL_Latin1_General_CP1_CI_AS",
    "maxSizeBytes": "1073741824",
    "sampleName": "AdventureWorksLT"
  },
  "sku": {
    "name": "S0",
    "tier": "Standard",
    "capacity": 10
  }
}

12. Summary Table: Azure SQL Database Features

FAQ

• Single Database: Individual, isolated fully managed databases with dedicated compute and storage.

• Elastic Pool: Multiple databases sharing a pool of resources to optimize cost and performance for variable usage scenarios.

• Managed Instance: Near 100% compatibility with on-prem SQL Server, supports instance-level features, suitable for lift-and-shift migrations.

• Hyperscale: Scalable storage and compute for very large databases (up to 100+ TB).

• Serverless: Automatically scales compute and can pause during inactive periods to save costs.

• DTU (Database Transaction Unit): A blended measure of CPU, memory, reads, and writes that provides simple performance benchmarking.

• vCore: Offers more transparent and flexible resource scaling by specifying virtual CPU cores and memory directly, allowing custom resource allocation and licensing models.

Intermediate Interview Questions and Answers

• Authentication: Supports SQL authentication and Azure Active Directory (Azure AD) integrated authentication.

• Authorization: Role-Based Access Control (RBAC) via Azure AD.

• Encryption: Transparent Data Encryption (TDE) for data at rest, Always Encrypted for sensitive data, TLS for data in transit.

• Advanced Threat Protection: Detects anomalous activities and vulnerabilities.

• Firewall and Virtual Network integration: Controls access by IP and network isolation.

• Data Masking & Auditing: To protect sensitive data and monitor access.

Advanced and Tricky Interview Questions and Answers

• Compatibility issues with unsupported features or deprecated SQL Server components.

• Differences in administrative access and custom configurations.

• Network connectivity and latency.

• Data migration volume and downtime concerns.

• Adjusting security models to cloud environment.

Scenario-Based Interview Questions and Answers