Database / Azure SQL Database Interview questions

Azure SQL Database is Microsoft's fully managed relational database service, built on the latest stable version of the SQL Server engine. It is a PaaS offering: Azure takes care of patching, backups, hardware and high availability, so you only deal with schema, queries and data.

You connect with the same tools you already know (SSMS, T-SQL, ADO.NET, JDBC), but there is no operating system or instance to administer. Compute and storage can be scaled up or down without rebuilding anything.

• Built-in high availability with an SLA of up to 99.99% (higher with zone redundancy)
• Automated backups with point-in-time restore
• Built-in security features such as TDE, auditing and threat detection
• Intelligent features such as automatic tuning and Query Store

Azure SQL Database itself offers two deployment options: a single database and an elastic pool. Both live on a logical server and use the same engine; they differ in how resources are allocated.

Outside these two, the wider Azure SQL family also includes SQL Managed Instance (near-complete instance compatibility) and SQL Server on Azure VMs (full IaaS control). People often mix these up, so be clear in an interview that they are separate products.

There are two: the DTU-based model and the vCore-based model. The first bundles resources into fixed packages, the second lets you pick them explicitly.

DTU is simpler for small, predictable workloads. vCore gives more transparency and more cost levers, which is why Microsoft steers new deployments towards it.

A Database Transaction Unit is a blended measure of CPU, memory, data I/O and log I/O. Microsoft calibrated it against a benchmark OLTP workload, so a database with twice the DTUs should have roughly twice the headroom for that kind of workload.

The DTU percentage you see in the portal is simply the highest of CPU, data I/O and log write utilisation. If any one of them hits 100%, you are effectively out of DTUs, even if the other two are idle.

The vCore model has three service tiers, each built on a different storage and availability design.

You also choose a hardware configuration, such as standard-series or premium-series, which affects CPU speed and memory per vCore.

A logical server is an administrative container for your databases, elastic pools and server-level settings. It gives you the endpoint yourserver.database.windows.net, but it is not a physical machine and not a SQL Server instance.

Settings managed at this level include the server admin login, the Microsoft Entra admin, firewall rules, auditing policy, failover groups and the TDE protector. Databases on the same server can use different service tiers and sizes.

A server belongs to one region and one subscription. Deleting the server deletes every database on it, so protect it with a resource lock in production.

An elastic pool is a set of databases that share a fixed budget of resources (eDTUs or vCores) for one price. It suits workloads where each database is busy only part of the time, and the busy periods don't line up.

You control fairness with two per-database settings: a minimum that guarantees resources and a maximum that stops one database from starving the others. Databases can be moved in and out of the pool online.

The classic fit is a SaaS app with a database per tenant. Sizing each tenant database for its own peak would be wasteful; the pool absorbs the spikes.

Serverless is a vCore compute tier that scales between a minimum and maximum vCore setting based on load, and bills compute per second for what is actually used. It is available in General Purpose and, more recently, Hyperscale.

After a configurable idle period (the auto-pause delay, minimum 15 minutes) the database pauses and you pay only for storage. The next connection triggers a resume, which takes a short while, so the first login may fail with a transient error and should be retried.

It fits dev/test and intermittently used apps. It is a poor fit for steady, heavy workloads, where provisioned compute is usually cheaper.

Azure SQL Database blocks all connections until a firewall rule allows them. Server-level rules are stored in the master database and apply to every database on the logical server. Database-level rules are stored inside that database and apply only to it.

Database-level rules can only be managed through T-SQL, which makes them portable: they travel with the database when it is copied or geo-replicated.

-- server-level (run in master)
EXEC sp_set_firewall_rule N'OfficeIP', '203.0.113.10', '203.0.113.20';
-- database-level (run in the user database)
EXEC sp_set_database_firewall_rule N'AppHost', '198.51.100.5', '198.51.100.5';

The special 0.0.0.0 rule ('Allow Azure services') opens the server to any Azure resource, including other tenants' - so use it sparingly.

You need the server's fully qualified name (<server>.database.windows.net), a firewall rule or private endpoint that lets your client in, valid credentials, and outbound access on TCP port 1433.

Any tool using a SQL Server driver works: SSMS, the VS Code mssql extension, sqlcmd, or application drivers. Connections are encrypted by default, and you should keep Encrypt=True in your connection string.

Server=tcp:myserver.database.windows.net,1433;
Initial Catalog=SalesDb;
Authentication=Active Directory Default;
Encrypt=True;Connection Timeout=30;

If the connection fails, check the firewall rule first, then outbound port 1433 on the client network, then the credentials.

Two families: SQL authentication (username and password stored in the server or the database) and Microsoft Entra ID authentication (formerly Azure AD). Classic on-premises Windows authentication isn't available.

For apps hosted in Azure, a managed identity is the cleanest choice because no password exists to leak or rotate.

TDE encrypts the database's data files, transaction log and backups at rest. Pages are encrypted as they are written to disk and decrypted as they are read into memory, so applications don't change at all.

A database encryption key (DEK) does the work, and the DEK is protected by the TDE protector: either a service-managed certificate (the default) or your own key in Azure Key Vault or Managed HSM (customer-managed key, or BYOK). TDE is on by default for new databases.

It does not protect data in transit or data in use, and it won't stop a privileged user from running a SELECT. Use TLS, Always Encrypted or masking for those.

The service takes backups for you with no setup and no way to switch them off. The usual cadence is a full backup weekly, differentials every 12 or 24 hours and transaction log backups roughly every 10 minutes, depending on activity.

Backups are kept in Azure storage with a redundancy you choose: locally redundant, zone-redundant, geo-redundant or geo-zone-redundant. The point-in-time restore window defaults to 7 days and can be set from 1 to 35 days. Hyperscale uses storage snapshots instead of the traditional chain.

For anything longer than 35 days, configure long-term retention.

PITR lets you recover a database to any second within its retention window. The service combines the latest full backup, the right differential and the log backups up to your chosen time.

The restore always creates a new database; it never overwrites the source. You pick a name, target server and service objective. If you want to replace the original, rename the old one and the restored one afterwards, or copy the needed rows across.

az sql db restore --resource-group rg1 --server myserver \
  --name SalesDb --dest-name SalesDb_restored \
  --time "2026-10-15T09:30:00Z"

Restore time depends on database size and how much log must be replayed.

LTR keeps full backups for up to 10 years, mainly for compliance and audit needs that go beyond the 35-day PITR limit. It copies weekly full backups into separate Azure blob storage.

You define a policy with weekly (W), monthly (M) and yearly (Y) retention, plus the week of the year to keep for the yearly copy. For example, W=4 weeks, M=12 months, Y=5 years.

LTR backups can only be restored to a new database; they don't support point-in-time recovery inside the retained backup.

DDM hides sensitive column values in query results for users who aren't allowed to see them. The stored data is unchanged; only the output is masked, so no application changes are needed.

Built-in masks include default(), email(), random() and partial(). Users with the UNMASK permission see the real values.

ALTER TABLE dbo.Customer
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
GRANT UNMASK TO SupportLead;

Masking is not encryption. A user who can run ad-hoc queries may still infer values through filters, so combine it with proper permissions.

RLS restricts which rows a user can read or modify, enforced inside the database rather than in application code. You write an inline table-valued function that returns 1 for rows a user may access, then bind it to a table with a security policy.

• FILTER predicate: silently hides rows from SELECT, UPDATE and DELETE
• BLOCK predicate: stops INSERT or UPDATE operations that would violate the rule

CREATE FUNCTION Security.fn_TenantFilter(@TenantId int)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS ok
WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS int);

CREATE SECURITY POLICY Security.TenantPolicy
ADD FILTER PREDICATE Security.fn_TenantFilter(TenantId) ON dbo.Orders;

It is popular in multi-tenant apps that share one table across tenants.

Query Store is a built-in flight recorder for query performance. It captures query text, execution plans, runtime statistics and wait statistics in time-sliced intervals, and keeps them in the user database itself.

It is enabled by default in Azure SQL Database. Its main uses are spotting plan regressions, finding top resource consumers and forcing a known good plan with sp_query_store_force_plan.

Query Performance Insight and automatic tuning both build on Query Store data, so don't turn it off casually. The views to know are sys.query_store_query, sys.query_store_plan and sys.query_store_runtime_stats.

Hyperscale is a vCore tier built for very large databases, currently up to 128 TB. Instead of one monolithic storage layer, it splits the engine into compute nodes, page servers and a separate log service.

• Backups are file snapshots, so they complete quickly regardless of database size
• Restores take minutes rather than hours for most sizes
• Storage grows automatically; you don't pre-allocate it
• High-availability replicas and named replicas offer read scale-out

The trade-off is that it's a one-way street in some cases: moving a database back out of Hyperscale is limited, so check current documentation before committing.

Active geo-replication continuously copies a database asynchronously to up to four readable secondary databases, in the same or different regions. You create it per database.

The secondaries are readable, so they can serve reporting or act as a disaster recovery target. Failover is manual: a planned failover waits for sync and loses nothing, while a forced failover can lose the most recent transactions because replication is asynchronous.

Because it works per database, each secondary has its own server name, and your app must update its connection string unless you use a failover group on top.

A failover group manages replication and failover for a set of databases between a primary and a secondary logical server, usually in different regions. It builds on geo-replication but adds two stable DNS names.

• Read-write listener: <group>.database.windows.net, always points to the current primary
• Read-only listener: <group>.secondary.database.windows.net, points to the secondary

Failover can be automatic, controlled by a policy and a grace period that decides how long to wait before accepting potential data loss. Since applications connect through the listener, the connection string doesn't change after a failover.

Always Encrypted protects sensitive columns by encrypting them on the client. The database engine only ever sees ciphertext, and the keys never reach the server, so even DBAs and cloud operators can't read the plaintext.

A client driver does the encrypting and decrypting if you set Column Encryption Setting=Enabled in the connection string. Keys are held in Azure Key Vault or a local certificate store.

Secure enclaves extend this to richer operations such as range queries and pattern matching.

Defender for SQL is a security add-on with two parts: Vulnerability Assessment and Advanced Threat Protection.

• Vulnerability Assessment scans for misconfigurations, excessive permissions and exposed sensitive data, and compares results to a baseline
• Advanced Threat Protection raises alerts on suspicious activity such as SQL injection patterns, brute-force logins and access from unusual locations

Alerts show up in Microsoft Defender for Cloud and can be emailed to administrators. You enable it at the server or subscription level, and it is billed per protected resource.

Azure SQL Database has no SQL Server Agent. Elastic jobs fill that gap by running T-SQL scripts on a schedule across one or many databases.

You create an elastic job agent, which needs its own job database, then define target groups (servers, elastic pools, individual databases, or shard maps), credentials, steps and schedules. Typical uses: index maintenance, schema rollouts across tenant databases, and data collection.

For jobs that aren't T-SQL, use Azure Automation, Logic Apps, Functions or Data Factory instead.

Because it's PaaS, a few instance-level features are unavailable. Most have a workable alternative.

If your application depends on several of these, SQL Managed Instance is usually the lower-friction target.

Azure SQL Database is scoped to a database; SQL Managed Instance is scoped to an instance. That single difference explains most of the rest.

Choose SQL Database for new cloud-native apps and elastic scale. Choose Managed Instance when lifting and shifting an app that depends on instance-level features.

This is the PaaS versus IaaS choice. With SQL Database, Microsoft runs everything below the database. With a SQL Server VM you get a full operating system and instance, and you run them yourself.

Pick the VM when you need OS-level tools, third-party agents, an old version or unsupported features. Otherwise PaaS usually wins on cost of ownership.

For most new workloads, vCore. It shows you exactly what you buy, lets you scale compute and storage separately, and opens up serverless, Hyperscale, Azure Hybrid Benefit and reserved capacity.

DTU still makes sense for small, steady workloads where a simple fixed package is easier to budget, and where nobody wants to think about cores and memory.

If you're moving from DTU to vCore, Microsoft's rule of thumb is that 100 Standard DTUs is roughly 1 General Purpose vCore, and 125 Premium DTUs is roughly 1 Business Critical vCore. Treat these as a starting point and validate against real metrics, since workload shape matters.

Choose serverless when usage is intermittent or unpredictable and the database sits idle for long stretches. You pay per second for the vCores used, plus storage, and nothing for compute while paused.

Good candidates: dev/test, internal tools used during office hours, new apps with unknown demand, and proof-of-concepts.

Avoid serverless when:

• load is steady and high, since provisioned is cheaper per vCore at constant use
• the app can't tolerate a resume delay after auto-pause (you can disable auto-pause, but then savings shrink)
• you need Business Critical features, which serverless doesn't offer

Tune the minimum and maximum vCores and the auto-pause delay to balance savings against warm-up cost, as the buffer pool shrinks when the database is idle.

Pick a pool when you have many databases whose average usage is far below the sum of their peaks, and whose peaks happen at different times. The pool is sized for the combined load, not for every database's worst case.

A typical case is a SaaS product with hundreds of small tenant databases. Individually provisioned, most would sit idle; pooled, a few busy tenants borrow what quiet ones don't use.

Stay with single databases when one database has a heavy steady load, when you need to scale a database independently, or when you only have a couple of databases. A pool is a poor fit if everything peaks at once, because the shared limit becomes the bottleneck.

General Purpose separates compute from storage. The SQL engine runs as a stateless process on a compute node managed by Service Fabric, while data and log files sit on remote Azure Premium Storage, which keeps its own redundant copies.

1. The active node fails or is taken down for maintenance
2. Service Fabric starts a new node with spare capacity
3. The new node attaches the same data and log files
4. The engine runs recovery and begins accepting connections

This takes a short time, typically seconds to tens of seconds, and the memory cache starts cold, so performance dips briefly. Applications need retry logic to ride it out. With zone redundancy enabled, storage becomes zone-redundant and replacement nodes can come up in another zone.

Business Critical uses an Always On availability group-style design: one primary and typically three secondary replicas, each with its own local SSD copy of the data. Commits are synchronous to a quorum of replicas, so a committed transaction survives a node failure.

flowchart LR
  C[Client] --> GW[Gateway]
  GW --> P[(Primary replica)]
  P -- sync commit --> S1[(Secondary 1 - readable)]
  P -- sync commit --> S2[(Secondary 2)]
  P -- sync commit --> S3[(Secondary 3)]

If the primary fails, one of the secondaries is promoted and failover completes in seconds, faster than General Purpose because no storage reattach or cold start from remote disk is needed. One secondary is exposed as a free read-only replica. With zone redundancy the replicas are spread across availability zones.

Hyperscale decouples the SQL engine into four layers so that size and speed no longer depend on a single node's disk.

• Compute nodes: run the SQL engine; one primary plus optional HA and named replicas, each with a local SSD cache (RBPEX)
• Log service: receives log records from the primary, makes them durable and feeds replicas and page servers
• Page servers: each owns a slice of the database, applies log records and serves page requests
• Azure storage: durable files plus snapshots used for backups

flowchart LR
  A["Primary compute"] -- log records --> L["Log service"]
  L --> P1["Page server 1"]
  L --> P2["Page server 2"]
  L --> R["Secondary replicas"]
  A -- page read on cache miss --> P1
  P1 --> S[(Azure storage and snapshots)]
  P2 --> S

A write only needs the log to be durable in the log service, so commits stay fast. A read that misses the buffer pool and local cache goes to the right page server. Because backups are storage snapshots and restores attach snapshots, both are close to size-independent.

Failover groups are a management layer on top of geo-replication. Geo-replication replicates individual databases; failover groups coordinate replication and failover for a whole set of databases and give you stable endpoints.

Use geo-replication when you need several readable copies in different regions. Use failover groups when you need simple, app-friendly disaster recovery.

Zone redundancy spreads the replicas of a database across availability zones in one region, so the loss of an entire datacenter zone doesn't take the database down.

In General Purpose, storage becomes zone-redundant (ZRS) and spare compute can start in another zone. In Business Critical and Hyperscale, replicas are placed in different zones. The SLA increases to 99.995% and applications need no code change.

It is different from geo-replication: everything stays in the same region, so it won't help in a regional outage. It also doesn't protect against logical errors such as a dropped table, which is what PITR is for. It must be supported in the region and may cost extra on some tiers.

Both give you disaster recovery in another region, but at very different recovery points and costs.

Geo-restore is the cheap safety net for non-critical databases. Geo-replication or failover groups are for databases where downtime and data loss must be minimal.

Every client first reaches a regional gateway on port 1433, which authenticates the request and finds the node hosting the database. What happens next depends on the connection policy.

• Redirect: the gateway tells the client the node's address, and the client reconnects directly using ports 11000-11999. Lower latency.
• Proxy: all traffic flows through the gateway on 1433. Simpler firewall rules, slightly higher latency.
• Default: Redirect for connections from inside Azure, Proxy for connections from outside. Private endpoints always use Proxy.

sequenceDiagram
  participant C as Client
  participant G as Gateway
  participant N as Database node
  C->>G: Login on port 1433
  G-->>C: Redirect to node address
  C->>N: Direct connection on 11000-11999
  N-->>C: Session established

Failovers, scaling operations, maintenance and throttling can briefly drop connections, so the application must expect and retry certain errors. Typical transient error numbers include 40197, 40501, 40613, 49918, 49919 and 49920.

1. Detect transient errors, not all errors; don't retry bad passwords or syntax errors
2. Retry with exponential backoff and some jitter, with a sensible maximum
3. Use short-lived connections from a pool, and open them late and close them early
4. Make operations idempotent, so a retried command doesn't duplicate data

services.AddDbContext<AppDb>(o =>
  o.UseSqlServer(cs, sql => sql.EnableRetryOnFailure(
      maxRetryCount: 5,
      maxRetryDelay: TimeSpan.FromSeconds(30),
      errorNumbersToAdd: null)));

Most current drivers (Microsoft.Data.SqlClient, JDBC) also offer built-in connection resiliency settings.

Automatic tuning watches workload data in Query Store and applies fixes on its own, then checks whether they really helped. There are three options:

• FORCE_LAST_GOOD_PLAN: when a query's plan regresses, force the last plan that performed well. This is typically the one enabled by default.
• CREATE_INDEX: create indexes the workload would benefit from
• DROP_INDEX: drop unused or duplicate indexes

After each action the service compares performance. If things got worse, it reverts the change automatically. Recommendations and their state are visible in sys.dm_db_tuning_recommendations and the portal.

ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON, DROP_INDEX = OFF);

Work from the outside in, so you don't tune a query when the real problem is an exhausted resource.

1. Check portal metrics or sys.dm_db_resource_stats: is CPU, data I/O, log I/O or the worker limit saturated?
2. Open Query Store and sort by duration, CPU or reads to see whether this query regressed and when
3. Look at waits (sys.dm_exec_requests, Query Store wait stats) and blocking chains via blocking_session_id
4. Capture the actual execution plan: look for scans, spills, bad estimates and missing indexes
5. Check statistics freshness and parameter sniffing; force a good plan or add OPTION (RECOMPILE) if needed
6. Scale up only if the workload is genuinely resource-bound

SELECT TOP 10 qt.query_sql_text, rs.avg_duration, rs.count_executions
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
ORDER BY rs.avg_duration DESC;

Use several layers, depending on how far back you need to look.

• Azure Monitor metrics in the portal: CPU, DTU, data I/O, log I/O, workers, sessions, storage. Set alerts on them.
• sys.dm_db_resource_stats: about one hour of data in 15-second samples, queried in the user database
• sys.resource_stats: roughly 14 days in 5-minute samples, queried in the master database
• Diagnostic settings: stream metrics and logs to Log Analytics, Event Hubs or storage for long-term analysis
• Query Performance Insight and Intelligent Insights: query-level and automated root-cause hints

SELECT TOP 20 end_time, avg_cpu_percent, avg_data_io_percent,
       avg_log_write_percent, max_worker_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

It depends on which limit you hit. Some limits slow you down, others produce errors.

The right response is to confirm which resource is saturated, tune the offending queries first, and then scale up the service objective. Alerts on percentage metrics at around 80% give you time to react before users notice.

A sound migration has an assessment step, a data move and a cutover plan.

flowchart LR
  A["Assess compatibility"] --> B["Choose target and tier"]
  B --> C{Downtime allowed?}
  C -- Yes --> D["Offline: BACPAC or DMS offline"]
  C -- No --> E["Online: transactional replication"]
  D --> F["Validate data and performance"]
  E --> F
  F --> G["Cut over and update connection strings"]

• Assess with Azure Migrate or a compatibility tool to find unsupported features
• Offline: export a BACPAC and import it with SqlPackage, or use Azure Database Migration Service
• Online: use transactional replication to keep Azure in sync until cutover
• After: recreate users and permissions, update statistics, set the compatibility level and test

sqlpackage /Action:Import /SourceFile:SalesDb.bacpac \
  /TargetServerName:myserver.database.windows.net \
  /TargetDatabaseName:SalesDb /TargetUser:sqladmin /TargetPassword:***

You change the service objective in the portal, CLI, PowerShell or with T-SQL. It is an online operation: the service builds the database at the new size in the background, then switches over.

ALTER DATABASE SalesDb
MODIFY (EDITION = 'GeneralPurpose', SERVICE_OBJECTIVE = 'GP_Gen5_8');

The cutover causes a short drop of connections (usually seconds) and in-flight transactions are rolled back, so applications need retry logic. For most tiers the total time grows with database size because data must be copied; Hyperscale scales compute almost independently of data size.

You can monitor progress with sys.dm_operation_status. Schedule scale operations outside peak hours when you can.

If the logical server still exists and the backups are within the retention period, open the server in the portal, go to Deleted databases, pick the database and restore it to its deletion time or an earlier point. It comes back as a new database.

az sql db restore --resource-group rg1 --server myserver \
  --name SalesDb --dest-name SalesDb_recovered \
  --deleted-time "2026-10-15T14:00:00Z"

If the server itself was deleted, the restore options disappear with it, so protect both with CanNotDelete resource locks. For long-term needs, keep LTR policies configured, and consider longer PITR retention for critical databases.

For most production cases, private endpoints. They give the server a private IP address inside your VNet through Azure Private Link, and let you turn off public network access entirely.

Service endpoints are fine for simple cases entirely inside Azure when cost matters. Private endpoints win when you need on-premises access, strict exfiltration control or compliance requirements.

Start by setting a Microsoft Entra admin on the logical server (prefer a group, not a single person). Then connect as that admin and create contained database users mapped to Entra identities.

-- user or group
CREATE USER [dba-team@contoso.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [dba-team@contoso.com];

-- managed identity of an app
CREATE USER [my-app-service] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [my-app-service];

Applications then connect using Authentication=Active Directory Default or Active Directory Managed Identity in the connection string. Interactive users get MFA and conditional access automatically.

Once everything works, consider enabling Entra-only authentication to disable SQL logins altogether.

Start with measurement, then apply the levers that fit your workload.

• Right-size: look at 2-4 weeks of CPU and I/O metrics and drop oversized objectives
• Serverless for intermittent databases; elastic pools for many small, uncorrelated ones
• Reserved capacity (1 or 3 years) for steady provisioned compute, and Azure Hybrid Benefit if you own SQL Server licences
• Dev/test pricing for non-production subscriptions, and scale down or pause outside working hours
• Use locally redundant backup storage for non-production, and trim PITR and LTR retention to what you truly need
• Remove idle geo-secondaries and unused databases

Avoid cutting resources blindly; check that Query Store shows no regressions and set alerts so you notice if a smaller size starts to throttle.

Three-part names such as OtherDb.dbo.Orders aren't supported between separate Azure SQL databases. The usual answer is elastic query, which exposes a remote table as an external table.

CREATE DATABASE SCOPED CREDENTIAL RemoteCred
WITH IDENTITY = &#x27;remote_user&#x27;, SECRET = &#x27;<password>&#x27;;

CREATE EXTERNAL DATA SOURCE RemoteDb WITH (
  TYPE = RDBMS, LOCATION = &#x27;myserver.database.windows.net&#x27;,
  DATABASE_NAME = &#x27;OrdersDb&#x27;, CREDENTIAL = RemoteCred);

CREATE EXTERNAL TABLE dbo.Orders (OrderId int, CustomerId int, Total money)
WITH (DATA_SOURCE = RemoteDb);

Elastic query works well for reference lookups and small joins. For heavy cross-database workloads, push the data movement to Azure Data Factory, merge the data into one database, or pick SQL Managed Instance, which supports cross-database queries natively.

Business Critical (and Premium DTU) databases include a built-in read-only replica at no extra cost. Hyperscale offers the same through HA replicas, and named replicas for larger needs.

To use it, add ApplicationIntent=ReadOnly to the connection string. The gateway then routes that session to a readable secondary, so reporting and analytics queries don't compete with OLTP traffic on the primary.

Server=tcp:myserver.database.windows.net,1433;Database=SalesDb;
ApplicationIntent=ReadOnly;Authentication=Active Directory Default;

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');  -- READ_ONLY on the replica

Remember that the replica can lag slightly behind the primary, and General Purpose has no built-in readable secondary, so you'd use a geo-replica instead.