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:
There are several requirements:
- 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
- CONNECTION_OPTIONS argument for External Data Source is only available on SQL Server 2019.
- Declare the Application_Intent=ReadOnly on CONNECTION_OPTIONS
- Run a query on external table and execute it multiple times. This will give you time to check where the session is being executed.
- 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
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
