Question 1

Can you explain the differences between Azure SQL Database and Azure SQL Managed Instance?

Accepted Answer

Azure SQL Database is a fully managed PaaS offering optimized for modern cloud native applications, providing a single database or an elastic pool with built in high availability and automatic patching. Azure SQL Managed Instance provides near 100% compatibility with the on premises SQL Server engine, supporting features like SQL Agent , cross database queries , and CLR integration that are unavailable in Azure SQL Database. Key differences: Networking : Managed Instance deploys into a VNet natively; SQL Database uses firewall rules or private endpoints Migration : Managed Instance is the recommended target for lift and shift migrations due to broader engine compatibility Pricing : SQL Database offers serverless and Hyperscale tiers; Managed Instance uses the vCore model only Features : Managed Instance supports Service Broker , linked servers , and Database Mail ; SQL Database does not

Question 2

How would you implement row-level security in Azure SQL Database to restrict data access for different users?

Accepted Answer

Row Level Security (RLS) restricts which rows a user can read or modify by attaching an inline security predicate function to the table through a security policy . The predicate function runs transparently on every query, so no application code changes are needed. Use FILTER predicates to silently exclude rows from SELECT results Use BLOCK predicates to prevent unauthorized INSERT , UPDATE , or DELETE Set the user context per session with sp set session context or map to DATABASE PRINCIPAL ID()

Question 3

Describe a scenario where you would use Elastic Pools in Azure SQL Database.

Accepted Answer

Elastic Pools are ideal when you manage multiple databases whose usage peaks at different, unpredictable times. For example, a SaaS platform serving 50 tenants where each tenant has its own database can share a single pool of eDTUs or vCores , so idle databases release resources for active ones. Cost savings : Instead of provisioning peak capacity per database, you provision once for the pool Resource governance : Set per database min and max resource limits to prevent a single tenant from starving others Simplified management : Add or remove databases from the pool without downtime

Question 4

How can you monitor and optimize query performance in Azure SQL Database?

Accepted Answer

Azure SQL Database provides several built in tools for monitoring and tuning. Query Performance Insight surfaces the top resource consuming queries, while Query Store captures historical execution plans and runtime statistics so you can detect plan regressions. Intelligent Performance : Azure can apply automatic tuning actions such as creating/dropping indexes and forcing good execution plans DMVs : Use sys.dm exec query stats , sys.dm exec requests , and sys.dm db resource stats for real time diagnostics Execution plans : Analyze actual execution plans to spot missing indexes, implicit conversions, or expensive scans

Question 5

In a situation where you need to migrate an on-premises SQL Server database to Azure SQL Database, what steps would you take?

Accepted Answer

The migration follows an assess migrate validate pattern. First, run the Data Migration Assistant (DMA) to identify compatibility issues and feature parity gaps between your on premises instance and Azure SQL Database. Then execute the migration using Azure Database Migration Service (DMS) for minimal downtime. Assess : DMA flags unsupported features (e.g., cross database queries, CLR) and suggests remediation Choose SKU : Use the Azure SQL Migration extension in Azure Data Studio to get a right sized SKU recommendation based on workload metrics Migrate : DMS performs an initial full backup restore followed by continuous log shipping until cutover Validate : Compare row counts, run application smoke tests, and verify performance using Query Store baselines Cutover : Stop writes to the source, let the final logs replicate, then redirect application connection strings

Question 6

How does Transparent Data Encryption (TDE) work in Azure SQL Database, and when would you use it?

Accepted Answer

Transparent Data Encryption (TDE) encrypts the database files, backups, and transaction log at rest using a symmetric Database Encryption Key (DEK) protected by a certificate or an asymmetric key. In Azure SQL Database, TDE is enabled by default and uses a service managed key, requiring zero application changes. Service managed key : Microsoft manages key rotation automatically Customer managed key (BYOK) : Store your own key in Azure Key Vault for full control over the encryption lifecycle Use case : Compliance with regulations like GDPR, HIPAA, or PCI DSS that mandate encryption at rest TDE does not encrypt data in memory or in transit pair it with Always Encrypted for column level protection and TLS for in transit encryption.

Question 7

Can you explain the concept of DTUs and how they relate to performance in Azure SQL Database?

Accepted Answer

A Database Transaction Unit (DTU) is a blended measure of CPU, memory, data I/O, and log I/O calibrated against a benchmark workload. Higher DTU tiers provide proportionally more resources. The alternative is the vCore model, which lets you independently select compute and storage. Model Best for Key trait DTU Simple, predictable workloads Bundled resources, easy to reason about vCore Granular control, hybrid benefit Choose CPU cores, memory, and storage separately Basic (5 DTUs): Dev/test and light workloads Standard (10 3000 DTUs): Most production OLTP applications Premium (125 4000 DTUs): High I/O, in memory OLTP Use sys.dm db resource stats to check whether your database is hitting DTU limits. If avg CPU , avg I/O , or avg log write consistently exceeds 80%, scale up to a higher DTU tier.

Question 8

How would you implement geo-replication in Azure SQL Database for disaster recovery?

Accepted Answer

Active geo replication asynchronously replicates transactions to up to four readable secondary databases in different Azure regions. If the primary region fails, you promote a secondary to primary with minimal data loss (RPO of ~5 seconds). Readable secondaries offload read only reporting and analytics queries Auto failover groups add a listener endpoint that automatically redirects connections after failover, so applications need no connection string changes Planned failover : Zero data loss, used during maintenance or region migration Forced failover : May lose recent transactions, used during an actual outage