Using External Data Sources with Azure SQL or SQL Server

Recently, I made the unfortunate (and admittedly foolish) mistake of overwriting a column's value across an entire table. I had intended to update just a single row, but it was late at night, and my exhausted brain overlooked the fact that a WHERE clause was missing from my SQL statement.

Fortunately, this was only a testing database, so it was more of an inconvenience than a travesty.

Still, it was a problem that needed fixing.

It's easy enough to perform a point-in-time restore on the Azure SQL Hyperscale instance and retrieve the uncorrupted data; but I didn't want to restore a copy of the entire database, I only needed the original values for that particualr column.

This is a perfect application for SQL Server's External Data Sources, also available in Azure SQL.

What does an external data source provide?

External Data Sources are a powerful feature in Microsoft SQL Server that allow you to work with data stored in other mediums, such as flat files, APIs, other relational databases, unstructured databases, document stores, and so on.

Using SQL, you can query directly against an external data source to select, update, insert, or delete data from either the local database or the remote data store, including performing joins and filtering tables from both data stores.

In this article, I'll show you how I joined a local table to a remote table in order to perform an update on the local table.

Using an external table for a remote SQL Server database

Connecting to a remote server requires authentication, even if it's on the same SQL Server instance. To authenticate, we must create a Scoped Database Credential that stores the username and password for the remote server.

CREATE DATABASE SCOPED CREDENTIAL [RemoteServerCredentials]
WITH IDENTITY = '<remote database username>',
SECRET = '<remote database password>';

Then, we can create an external data source pointing to the remote database using the scoped credentials object, specifying that we are connecting to an SQL Server database in the Type.

CREATE EXTERNAL DATA SOURCE [RemoteDataSource]
WITH
(
    TYPE = RDBMS,
    LOCATION = '<remote hostname>',
    DATABASE_NAME = '<remote database>',
    CREDENTIAL = [RemoteServerCredentials]
);

Finally, we need to create an External Table in our database that mirrors the definition of the remote table. Once created, we can query this table as if it were a local table in our database.

CREATE EXTERNAL TABLE [dbo].[RemoteTable] (
    [Id] INT NOT NULL,
    [SerialNumber] VARCHAR(12) NOT NULL,
    [Password] VARCHAR(128) NOT NULL
)
WITH
(
    DATA_SOURCE = [RemoteDataSource],
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = N'<remote table>'
);

Updating rows by selecting from an external table

In my case, I needed to update values in my local table by joining it with and selecting data from a remote table. This is easy to do, thanks to the external table representing the data from the remote source.

Here, we are updating the LocalTable by joining on its ID with the remote table and replacing the local Password column with the value from the remote column.

UPDATE [LocalTable]
SET
    [Password] = [RT].[Password]
FROM [LocalTable] AS [LT]
INNER JOIN [RemoteTable] AS [RT]
    ON [LT].[Id] = [RT].[Id];

Cleanup the database objects

I often use external data sources for one-off data analysis projects or maintenance operations. In these cases, I would suggest deleting these objects for good housekeeping.

DROP EXTERNAL TABLE [dbo].[RemoteTable]
DROP EXTERNAL DATA SOURCE [RemoteDataSource];
DROP DATABASE SCOPED CREDENTIAL [RemoteServerCredentials];