Create External Data Source to Reference a Readable Secondary Replica of Always On Availability Group

Problem

We need to create an external table that uses and external data source. The external data source needs to query the (read_only) readable secondary node of an availability group.

Pretty simple, just follow the Microsoft document:

CREATE EXTERNAL DATA SOURCE (Transact-SQL) - SQL Server | Microsoft Docs

There are several requirements:

1. The external data source needs to be configured as AlwayOn Availability Group with Read Only Routing List configured.  Configure read-only routing for an availability group - SQL Server Always On | Microsoft Docs
2. CONNECTION_OPTIONS argument for External Data Source is only available on SQL Server 2019.
3. Declare the Application_Intent=ReadOnly on CONNECTION_OPTIONS
4. Run a query on external table and execute it multiple times. This will give you time to check where the session is being executed. 
5. Verify the connection on both primary and secondary server. The session should be running in the secondary server.

Solution

First Try:

1. Configured/created the external data source

CREATE EXTERNAL DATA SOURCE [XDSName]

WITH (LOCATION=n'sqlserver://AGListenerName',

CONNECTION_OPTIONS='ApplicationIntent=ReadOnly',

CREDENTIAL=[dbuser])

GO

2. Configured/created external data table

CREATE EXTERNAL TABLE [XTable] ([column1] INT not null...)
WITH (LOCATION=N'[dbname].[schemaname].[tablename]',

DATA_SOURCE=[XDSName])

GO

3. Run a query against the external data table and repeated it 1000 times

SELECT * FROM [XTable]

GO 1000

4. Verify both the primary and secondary server. The query should run in secondary server.

 

With my first test, the query ran in the primary server. 

After doing some investigation, I found out that the "default database" of the credential that I used is pointed to the "master" database.

I modified "default database" of the credential I used to point to database name that is being replicated by the Availability Group. I tested again by running a query on the external data table multiple time, this time the session is running on the secondary node.

But we are not allowed to change the "default database" of the user.

The "default database" was changed back to "master"

Second Try:

1. Configured/created the external data source, but this time the database needs to be declared.

CREATE EXTERNAL DATA SOURCE [XDSName]

WITH (LOCATION=n'sqlserver://AGListenerName',

CONNECTION_OPTIONS='ApplicationIntent=ReadOnly; Database=dbname',

CREDENTIAL=[dbuser])

GO

2. Configured/created external data table

3. Run a query against the external data table and repeated it 1000 times

4. Verify both the primary and secondary server. The query should run in secondary server.

The initial test failed because I used a wrong keyword "DatabaseName". 

A co-worker of mine gave me a sample of his external data source that he has configured and already working against a secondary server.

After comparing the 2 definition, I found out that the keyword should be "Database" and not "DatabaseName"

I tested again and it worked because the "database" keyword is defined in the CONNECTION_OPTIONS which override the "default database" of the credential.

Allowed connection string keywords that can be used on CONNECTION_OPTIONS

Using connection string keywords - SQL Server Native Client | Microsoft Docs

Since there is no example on Microsoft's document, I submitted a pull request and added an example G. Create external data source to reference a readable secondary replica of Always On Availability Group