Thursday, August 1, 2019

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.

No comments:

Post a Comment

How to add a Database to AlwaysOn Availability Group with four different options

To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...