SQL Server error 47106

Problem

I was trying to configure Read-Scale Always On Availability Group in our test server using the steps from https://www.mssqltips.com/sqlservertip/6905/sql-server-read-scale-always-on-availability-groups/ but I encountered an error "SQL Server error 47106".

Below is the error that I got:

TITLE: Microsoft SQL Server Management Studio

------------------------------
Failed to join the instance 'TESTSQL17A' to the availability group 'TESTSQL17RS'. (Microsoft.SqlServer.Management.HadrModel)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot join availability group 'TESTSQL17RS'. Download configuration timeout. Please check primary configuration, network connectivity and firewall setup, then retry the operation.
Failed to join local availability replica to availability group 'TESTSQL17RS'.  The operation encountered SQL Server error 47106 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 47106)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-47106-database-engine-error
------------------------------
BUTTONS:
OK
------------------------------

Solution

I scoured the internet for any related issues and saw a post from  https://www.voltol.com/2020/02/sql-error-47106-when-trying-to-add.html

Reading through the article above helped me in the right direction. The port used by read-scale is being blocked by a firewall.

Below is the link of required firewall rules to access SQL Server: 

Configure the Windows Firewall to allow SQL Server access - SQL Server | Microsoft Learn

During the course of the investigation, I performed the following tasks:

1. Checked the port being used for Read-Scale Availability Group. Availability group uses port 5022 or 7022 for database mirroring.

SQL

SELECT name, protocol_desc, port, state_desc
FROM sys.tcp_endpoints
WHERE type_desc = 'DATABASE_MIRRORING'

2. Using PowerShell command Test-NetConnection, I tested the port being used by SQL Server for database mirroring on both servers. I included port 1433 since it is the default TCP port of SQL Server instance.

PowerShell

clear
Test-NetConnection -ComputerName TESTSQL17A -Port 1433
Test-NetConnection -ComputerName TESTSQL17A -Port 5022

Test-NetConnection -ComputerName TESTSQL17B -Port 1433
Test-NetConnection -ComputerName TESTSQL17B -Port 5022

The test from TESTSQL17B to TESTSQL17A failed on port 5022, but the connection using port 1433 was successful.

3. To ensure that the ports are open, I manually added the ports 1433 and 5022 in the firewall rules of both servers.

PowerShell

clear
New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "SQLServer Database Mirroring" -Direction Inbound -LocalPort 5022 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Outbound -LocalPort 1433 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "SQLServer Database Mirroring" -Direction Outbound -LocalPort 5022 -Protocol TCP -Action Allow

The test to connect using port 5022 still failed even though both outbound and inbound firewall rules were already been configured.

4. Contacted systems administrators to check the ports and firewall rules. The server admin confirmed that ports and firewall rule settings are correct. 

I recall getting an email regarding "Zero Trust Network Access" software being tested by our security team.

5. Contacted our security team and asked someone to check the servers that I am using for my testing.

The security admin confirmed that one of the server is included in their "Zero Trust Network Access" testing. They opened/verified that port 5022 are open on both servers.

The Test-NetConnection testing on port 5022 was successful after the change was implemented.

6. Read-scale Always On configuration was successful after the changes were made.

Some solutions might require working with other teams in your company. Awareness of events happening around you is also important.

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