Go back

Inter-database communication in SQL Server

Chip Hartney (The Datamology Company)

First written: June 2, 2023

Last updated: N/A

Contents

Abstract 1

Overview.. 1

Cross-database query. 1

Elastic query. 1

Linked server 1

Abstract

In this article, I describe the technologies that can be used in SQL Server to communicate (query) across databases.

Overview

Databases are defined and implemented differently across RDBMS vendors.

SQL Server, in its modern configuration (especially a SQL Managed Instance in Azure), implements databases in a manner fairly consistent with the SQL standard such that data values are stored in a hierarchical set of named objects:

Where:

· A server manages an Instance.

· An instance contains any number of Databases.

· A database contains any number of Schemas.

· A schema contains any number of Tables.

· A table contains any number of Columns.

Within a SQL Server Managed Instance, any given object can be referenced with a multi-part name following that hierarchy:

· A database is identified by its name as in “database1”.

· A schema is identified, within a named database, by its name as in “schema1.database1”.

· A table is identified, within a named schema, by its name as in “schema1.database1.table1”.

· A column is identified, within a named table, by its name as in “schema1.database1.table1.column1”.

Note: There are shortcuts by which one can omit the higher levels of the name based on the context in which the SQL is running.

Cross-database query

Azure SQL Server Managed Instance supports the direct reference to any named object in the instance in any SQL statement. Therefore:

· A query can join data from different databases.

· A view in one database can expose data from a different database.

· Etc

This allows one to use databases as part of their architecture to organize assets in a meaningful way without impacting maintenance or performance.

Other SQL Server products (such as Azure SQL Database) are not as flexible. Databases in those products are independent of each other and cross-database communication must be enabled through additional techniques and tools.

Elastic query

Elastic query (in preview in Azure SQL Database as of February 2023) is a feature that enables you to run T-SQL query that spans databases. It requires configuration of each database that needs access to data in another database. The feature is described here:

Elastic query overview - Azure SQL Database | Microsoft Learn

The basic approach is to define a data source (with pertinent key and credentials) for the remote database and then to define an external table in the local database that maps to the desired table in the remote database. It can then be queried normally as if it is local.

Linked server

A linked server is another technique that enables cross-database communication. It enables the database engine to read data from a remote data source (including other database servers) and execute commands against the remote server. The feature is described here:

Linked Servers (Database Engine) - SQL Server | Microsoft Learn