AlwaysOn availability group - Setting Up Read-Intent Only Secondary Replica

Setting Up Read-Intent Only on Secondary Replica

SQL Sever version : 2016

Step 1
Change Readable Secondary Replica Setting to Read-intent Only

Step 2

Set up Read-Only Routing,

Read-Only Routing URL Format

Server 1 , TCP://server1.domain.us.dom:portno
Server 2 , TCP://server2.domain.us.dom:portno

Example

Select server 1 and add Server 2 in Read-only routing list

Select Server 2 and add Server1 in Read Only routing list

Read Only Routing List Testing


We tested Read-Intent Only configuration using SQLCMD and PowerShell

SQLCMD

Run cmd as administartor, Go to the path that SQLCMD is installed

C:\Program Files\Microsoft SQL Server\110\Tools\Binn> Sqlcmd -S SQLListener -d DatabaseName -U Login -P Password -K ReadOnly

PowerShell

Invoke-Sqlcmd -Query "select Top 100 * from dbo.TableName" -ConnectionString "Data Source=SQLListenerName;User Id=Login;Password=Password;Initial Catalog=Database;Integrated Security=False;ApplicationIntent=ReadOnly"

Thank you for reading my post.