Why do Azure SQL databases on the same logical server act like strangers in a crowded room?
You’d think slapping two databases on one server—sql-server-dev-001, say—lets you fire off a quick three-part query, just like on-prem SQL Server. SELECT * FROM [sqldb-dev-001].SalesLT.vGetAllCategories. Boom, data flows. But nope. Azure slams the door with this gem:
Reference to database and/or server name in ‘sqldb-dev-001.SalesLT.vGetAllCategories’ is not supported in this version of SQL Server.
It’s a head-scratcher for anyone migrating from on-prem. Microsoft built Azure SQL for the cloud’s multi-tenant world—think isolation, security, scalability. Three-part names? Too risky, they say. Fair enough. But devs pay the price.
Enter Elastic Queries. Not some stretchy UI gimmick. A real-time bridge. No data copying, no syncing. Query the target db live, every time. Overhead? Yeah, network hops add latency. But it works.
Here’s the thing. This setup screams workaround. On-prem SQL Server did cross-db natively for decades. Azure? Forces this rigmarole. My take: Microsoft’s hedging bets, pushing you toward pricier tiers like Hyperscale or Synapse for ‘true’ federation. Remember Oracle’s database links in the ’90s? Clunky then, relic now. Elastic Queries feel like that—necessary evil until Azure catches up.
Why Azure SQL Blocks Your On-Prem Habits
Azure’s logical servers pool resources. Multiple databases, one endpoint. Sounds efficient. But security isolation trumps convenience. Three-part queries could leak data across tenants. So, blocked.
Elastic Queries sidestep this. Create external tables in your source db (sqldb-prod-001). They proxy to the target (sqldb-dev-001). SELECT * FROM [SalesLT].[vGetAllCategories]; Clean, no three-parters.
But setup? Not for the faint-hearted. Passwords in Key Vault. Scoped credentials. Master keys. It’s a ceremony.
First, bash out a password: openssl rand -base64 40. Plop it in Key Vault as sqldb-dev-001-crossdb-password. Smart—rotate later from one spot.
Target db: CREATE USER [sqldb-dev-001-identity] WITH PASSWORD = ‘‘; ALTER ROLE db_datareader ADD MEMBER [sqldb-dev-001-identity];
Source db needs a master key. Check sys.symmetric_keys for ##MS_DatabaseMasterKey##. None? Generate another openssl password, store in Vault, then CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘…’;
Scoped credential: CREATE DATABASE SCOPED CREDENTIAL [sqldb-dev-001-credential] WITH IDENTITY = ‘sqldb-dev-001-identity’, SECRET = ‘‘;
External data source: CREATE EXTERNAL DATA SOURCE [sqldb-dev-001] WITH (TYPE = RDBMS, LOCATION = ‘sql-server-001.database.windows.net’, DATABASE_NAME = ‘sqldb-dev-001’, CREDENTIAL = [sqldb-dev-001-credential]);
Finally, the external table. Match columns exactly:
CREATE EXTERNAL TABLE [SalesLT].[vGetAllCategories] ( [ParentProductCategoryName] NVARCHAR(50), [ProductCategoryName] NVARCHAR(50), [ProductCategoryID] INT ) WITH ( DATA_SOURCE = [sqldb-dev-001], SCHEMA_NAME = ‘SalesLT’, OBJECT_NAME = ‘vGetAllCategories’ );
Query away. Live data, every pop.
Tear down reverse: drop table, source, credential. Then user in target.
How Do You Enable Cross-Database Queries in Azure SQL?
That’s the search query devs hammer Google with. Answer: Elastic Queries, as above. But don’t sleep on permissions. Contained user gets db_datareader for reads. db_datawriter for writes. Passwords? Vault only—never code.
Performance? It’s networked. Fine for occasional joins. High-frequency? Latency bites. Test your workload. Azure docs gloss this; real-world, it’s why some bolt for Snowflake—native cross-db, no fuss.
And rotation. Update user password, credential secret. Vault shines here.
Look, this works. But is it hype? Microsoft pitches Elastic as innovative. Nah. It’s table stakes, delayed. My prediction: Native cross-db lands in a 2025 tier, chasing AWS Aurora’s cross-region tricks. Until then, Elastic’s your hammer.
Pro tip: One external table per object. No ad-hoc queries. Plan ahead.
Unique wrinkle—Azure’s multi-tenant paranoia mirrors early cloud days. Recall 2010 EC2 breaches? Isolation lessons stuck. Smart. But devs deserve better parity.
What’s the Performance Hit with Azure SQL Elastic Queries?
Network round-trips. 10-50ms extra per query, ballpark. Joins amplify it. Benchmark: Local query, sub-ms. Elastic? 20ms baseline, spikes under load.
Not disastrous for dashboards. Killer for OLTP. Microsoft won’t say; they push serverless for that.
Scales with DTUs/vCores. Beefier server, tighter latency. Still, no free lunch.
Bottom line. Elastic Queries unblock you. Solid for dev-to-prod peeks, analytics federation. But if cross-db’s core, rethink architecture. Single db? Or PolyBase to warehouses.
This setup’s market dynamic: Locks you deeper into Azure. Key Vault integration? Slick. But proprietary. Open source Postgres on AKS does cross-schema natively. Tradeoff.
🧬 Related Insights
- Read more: Ditch the $200 Chatbot: Build Your Own AI Fleet for $12 a Month
- Read more: Database Observability: Why Three Dashboards Are Bankrupting Your On-Call Rotation
Frequently Asked Questions
How do I enable cross-database queries in Azure SQL Database? Set up Elastic Queries: passwords in Key Vault, scoped credentials, external data source, then external tables per object.
What’s the performance overhead of Elastic Queries in Azure SQL? Network latency adds 10-50ms per query; avoid high-frequency joins.
Can I drop Elastic Query setup easily in Azure SQL? Yes—drop external table, data source, credential (source db), then user (target db). Reverse order.
