creating read only second replica for SQL 2016

hi all,

I am now going to setup read only secondary replica for SQL 2016 AOG, I read that:

https://blogs.technet.microsoft.com/dataplatform/2016/02/11/sql2016-load-balanced-readable-secondary-replicas/

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server

it seems I need to setup both:
1) SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')

and

2) WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL01','SQL02'),'SQL04')));

both ?

I see :

To configure read-only routing for the secondary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the SECONDARY_ROLE option, as follows:

SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')

Open in new window


and

To configure read-only routing for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =(‘server’ [ ,...n ] ))

Open in new window


what is the use of 'READ_ONLY_ROUTING_URL' and 'READ_ONLY_ROUTING_LIST'  ?

it seems  I need to execute both command above ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
it seems I need to setup both:
what is the use of 'READ_ONLY_ROUTING_URL' and 'READ_ONLY_ROUTING_LIST'  ?
The 1st one (READ_ONLY_ROUTING_URL) indicates which server (Primary Replica) has the secondary read-only route list (READ_ONLY_ROUTING_LIST).
0
marrowyungSenior Technical architecture (Data)Author Commented:
sorry , don't understand, they both need ?
0
Metehan OzculluSQL Service ManagerCommented:
To support read-only routing if one of the replicas currently owns the primary role. You have to set both. Below is an example script from MS:

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH  
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH  
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));  

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH  
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH  
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));  

ALTER AVAILABILITY GROUP [AG1]  
MODIFY REPLICA ON  
N'COMPUTER01' WITH  
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));  

ALTER AVAILABILITY GROUP [AG1]  
MODIFY REPLICA ON  
N'COMPUTER02' WITH  
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
GO
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

marrowyungSenior Technical architecture (Data)Author Commented:
if we have 3 x nodes:  SWVD02DSQLPOC, SWVD03DSQLPOC, SWVD04DSQLPOC, should I use a  script like this:


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SWVD02DSQLPOC.loundy.lab:1433'));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD03DSQLPOC'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD03DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SWVD03DSQLPOC.loundy.lab:1433'));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD04DSQLPOC'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD04DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SWVD04DSQLPOC.loundy.lab:1433'));

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD03DSQLPOC','SWVD04DSQLPOC')));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD03DSQLPOC'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD02DSQLPOC','SWVD04DSQLPOC')));


ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD04DSQLPOC'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD03DSQLPOC','SWVD02DSQLPOC')));
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. What you do is configure each node to how they will behave when they got the Primary Replica role.
Meaning that when they are the Primary Replica, they will allow read-only access to Secondary Replicas and in which which order.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"in which which order."


as it is round robin, order should not be that matter, right? except secondary on diff site and there are network concern ?

also my script above all run on primary node ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
one thing, after I run the script on primary nodes I found setting on AOG group properties is not reflecting new setting once failover, should the order in the list change accordingly ?

SQL AOG read routing list
Now i see sth not very good that as my desktop domain and SQL server domain is different and if I set the READ_ONLY_ROUTING_URL using full server name, and I login to that serve locally and query the AOG via connector, it is working but not from the SSMS on my desktop! but I set READ_ONLY_ROUTING_URL using IP address, it only work from the SSMS on my PC but not  locally on that SQL server.

error is :

read only routing error.
any way to combine both ?

...
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://<server name>:1433'));

and

...
MODIFY REPLICA ON N'SWVD02DSQLPOC'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://<IP address>:1433'));

?

also how you test which server the read route routed to ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
one more thing is :

I read this one:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server#SSMSProcedure

and it say :

To configure connection access for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

where,

READ_WRITE

Open in new window


I think the write is already allowed on the primary , right ? why need to set it again?

this is my test SQL Aog Configuration.

read and write.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
after I run the script on primary nodes I found setting on AOG group properties is not reflecting new setting once failover, should the order in the list change accordingly ?
Did you connect to the new Primary Replica node after the failover? Or sometimes it's a SSMS's refresh issue and you may need to refresh it manually (F5).

I think the write is already allowed on the primary , right ? why need to set it again?
ALL is the default and it means it can also accept the Read-Intent connection as well. When you set to READ-WRITE it means that it won't accept Read-Intent connections (good to preserve the server load from Reporting, for example).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"Did you connect to the new Primary Replica node after the failover? Or sometimes it's a SSMS's refresh issue and you may need to refresh it manually (F5)."

from the desktop SSMS, I can see read-only routing, but in local SQL server SSMS, I can't see that !  probably my desktop SSMS is SQL 2017 SSMS and server one is 2016.

what is the diff between read-intent and read only ...

I select red-intent but by this script for SQL 2016:

SELECT replica_server_name
	, read_only_routing_url
	, secondary_role_allow_connections_desc
FROM sys.availability_replicas

Open in new window


say all read_only ! so it is the same, then why in the readable replica setting, there is a 'yes' ? no need, right?

also, how can you TEST it and make sure that the read routing is reading from OTHER servers and which server it is reading from ?


"ALL is the default and it means it can also accept the Read-Intent connection as well. "


my script above only do this:

WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Open in new window


should be ok ? no need to do:

WITH (PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Open in new window


again on each server?
0
marrowyungSenior Technical architecture (Data)Author Commented:
by running this:

SELECT	  AVGSrc.replica_server_name AS SourceReplica		
		, AVGRepl.replica_server_name AS ReadOnlyReplica
		, AVGRepl.read_only_routing_url AS RoutingURL
		, AVGRL.routing_priority AS RoutingPriority
FROM sys.availability_read_only_routing_lists AVGRL
INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
ORDER BY SourceReplica

Open in new window


what I got this:

SWVD02DSQLPOC	1	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC.test.local:1433	SWVD03DSQLPOC,1433
SWVD02DSQLPOC	2	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC.test.local:1433	SWVD04DSQLPOC,1433
SWVD03DSQLPOC	1	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC.test.local:1433	SWVD02DSQLPOC,1433
SWVD03DSQLPOC	2	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC.test.local:1433	SWVD04DSQLPOC,1433
SWVD04DSQLPOC	1	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC.test.local:1433	SWVD03DSQLPOC,1433
SWVD04DSQLPOC	2	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC.test.local:1433	SWVD02DSQLPOC,1433

Open in new window


should be ok ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I am reading this now:

https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/

 and the answer to this:

should be ok ? no need to do:

WITH (PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

again on each server?

Open in new window


should be a no! agree?

and look at this:

https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/

it said when define the load balancing read only,, I need to change this :

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD03DSQLPOC','SWVD04DSQLPOC')));

Open in new window


to this:

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SWVD03DSQLPOC','SWVD04DSQLPOC'),'SWVD02DSQLPOC')));

Open in new window


one additional primary replica in this list can help and I can see why.

and I still can't test if the round robin read only by running select @@servername twice, or I MUST define "ReadOnly" in the connection string  ?  I think the listener can automatically DETECT all read only access and route it to all secondary replica which for read only ?

e.g.:

sqlcmd -S AG40VS,50000 -E -d test -K ReadOnly

Open in new window


OR

Server= tcp:<secondary replica>; Database=test; IntegratedSecurity=SSPI; MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Open in new window



 ApplicationIntent=ReadOnly; ?

so has to setup something in application string to know it is read only or not ? so there are change on application side to make it take effect?
0
marrowyungSenior Technical architecture (Data)Author Commented:
now the final test today give me sth straight, from this link:

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/22/modifying-alwayson-read-only-routing-lists/

it said:

The client must be using the latest providers that support ApplicationIntent parameter:

o SQL Server Native Client Support for High Availability, Disaster Recovery

o JDBC 4

o .NET 4.5

o .NET 4 + 4.0.2 (or higher) update / 4.0.2 / 4.0.3

o .NET 3.5 SP1

Open in new window


and from this link:

https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/

it seems we need this:

Server= tcp:AG40VS,50000; Database=test; IntegratedSecurity=SSPI; MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Open in new window


it seems right but one thing, I do simple query:

SELECT TOP (1000) [PackageTypeID]
      ,[PackageTypeName]
      ,[LastEditedBy]
      ,[ValidFrom]
      ,[ValidTo]
  FROM [WideWorldImporters].[Warehouse].[PackageTypes];

  select @@SERVERNAME;

Open in new window


and I hope the result by   select @@SERVERNAME; can show me diff return each time I press F5 but it is not!

it seems that I have to right click the AOG listener name from the SSMS and select new query, then I use the query above to try, then EACH NEW QUERY SSMS session will connect to a diff SQL nodes inside that AOG , is that the intended behavoiur ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
ApplicationIntent=ReadOnly; ?

so has to setup something in application string to know it is read only or not ? so there are change on application side to make it take effect?
Yes, this is only to be used by applications and the only way to test it is connecting from an application (website, reporting server, ...).
For that the application should have the entry in their configuration string. Example:
Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Yes, this is only to be used by applications and the only way to test it is connecting from an application (website, reporting server, ...)."

it seems the SAME connection keep using the same AOG SQL node, right ?

so the application has to reconnect each time in order to use the AOG load balance?

"IntegratedSecurity=SSPI;"

I add this option when connecting, it has error, removing it will be ok, any idea?

how you test if the load balancing is working fine ? like batch it and returns a list on which server it is using ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Database=Db1"

also by this, this read only route connection will only round robin for any connection to Db1 user database only ?

what if an application will need to connect to more than one user database for round robin DB read connection ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

from this: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server#SSMSProcedure


"MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )"

do you use this ? in my script I post above, I don't use that. what is it for ?

and if I setup this:

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SWVD03DSQLPOC','SWVD04DSQLPOC'),'SWVD02DSQLPOC')));

Open in new window


so that if primary role is hosting at SWVD02DSQLPOC, read only replica will be load balanced between SWVD03DSQLPOC and SWVD04DSQLPOC ?

of I should only do this:

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD02DSQLPOC' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SWVD03DSQLPOC','SWVD04DSQLPOC')));

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
it seems the SAME connection keep using the same AOG SQL node, right ?

so the application has to reconnect each time in order to use the AOG load balance?
No for both questions, if you provide the Listener name instead of the node name. In my example it's using the Listener name (MyAgListener).

I add this option when connecting, it has error, removing it will be ok, any idea?
What was the error? This SSPI is for use the current user domain account (Windows Authentication). I would expect that by now, majority of the applications will use this option.

how you test if the load balancing is working fine ? like batch it and returns a list on which server it is using ?
No. Check the activity in each node and confirm that both have more or less the same load.

what if an application will need to connect to more than one user database for round robin DB read connection ?
Depends on how your application needs to connect to the other databases.
If from the application itself then you'll need a connection string per database.
If from the SQL Server itself then it will connect to the database in the same SQL Server instance.

PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )"

do you use this ? in my script I post above, I don't use that. what is it for ?
I've already answered that on the top of this question. If you don't provide this information, it will simple use the default option (ALL).
0
marrowyungSenior Technical architecture (Data)Author Commented:
"No for both questions, if you provide the Listener name instead of the node name. In my example it's using the Listener name (MyAgListener).


I am using listener name too !

"What was the error? This SSPI is for use the current user domain account (Windows Authentication)"

this is the error:

when-setting-up-read-only-access-for.jpg"

"No. Check the activity in each node and confirm that both have more or less the same load"

this means whoisactive that or so_who2 that.

"If from the application itself then you'll need a connection string per database.
If from the SQL Server itself then it will connect to the database in the same SQL Server instance.
"

from application itself can be understand but it means we keep doing more than one of this:

Server= tcp:<IP address>,port; Database=<DB>; IntegratedSecurity=SSPI; MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Open in new window


for each application ...


"If from the SQL Server itself then it will connect to the database in the same SQL Server instance."

I don't understand that. you mean the read only will only connect to the primary SQL nodes if relies on SQL listener ?

"I've already answered that on the top of this question."

I think I confused,  and you can see from Metehan Ozcullu, example, which also don't have it.

you told me this:

ALL is the default and it means it can also accept the Read-Intent connection as well. When you set to READ-WRITE it means that it won't accept Read-Intent connections (good to preserve the server load from Reporting, for example).

Open in new window


you mean this set the primary server on it accept read only or read write only too ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
now I do a little test:

I failover the AOG primary to SWVD03DSQLPOC, then I try to do select @@servername.

my read-only replica definition is :

ALTER AVAILABILITY GROUP [SQL2K16AOG#1]
MODIFY REPLICA ON N'SWVD03DSQLPOC'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SWVD02DSQLPOC','SWVD04DSQLPOC'),'SWVD03DSQLPOC')));

the result should return it is SWVD02DSQLPOC or SWVD04DSQLPOC, bu t result is SWVD03DSQLPOC.

so it means read only routing is not working .

the connection string I use is :

ApplicationIntent=ReadOnly

Open in new window


even I check the routing list, which seems to be correct :

SELECT	  AVGSrc.replica_server_name AS SourceReplica		
		, AVGRepl.replica_server_name AS ReadOnlyReplica
		, AVGRepl.read_only_routing_url AS RoutingURL
		, AVGRL.routing_priority AS RoutingPriority
FROM sys.availability_read_only_routing_lists AVGRL
INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
ORDER BY SourceReplica
 

Open in new window


result
SWVD02DSQLPOC	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC:1433	1
SWVD02DSQLPOC	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC:1433	1
SWVD02DSQLPOC	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC:1433	2
SWVD03DSQLPOC	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC:1433	1
SWVD03DSQLPOC	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC:1433	1
SWVD03DSQLPOC	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC:1433	2
SWVD04DSQLPOC	SWVD03DSQLPOC	TCP://SWVD03DSQLPOC:1433	1
SWVD04DSQLPOC	SWVD02DSQLPOC	TCP://SWVD02DSQLPOC:1433	1
SWVD04DSQLPOC	SWVD04DSQLPOC	TCP://SWVD04DSQLPOC:1433	2

Open in new window


now I tried to login to the SQL server and check if the result is the same as the result I run from SSMS desktop, I found interesting thing.

if I am here: SWVD02DSQLPOC  and it will be reading from 'SWVD04DSQLPOC' and I  failover to SWVD04DSQLPOC and try to see if other replica than 'SWVD04DSQLPOC' will be the reading comes from. it will stay the same as SWVD04DSQLPOC , so when failover to SWVD04DSQLPOC , the read only still on SWVD04DSQLPOC .

not a good result, huh ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
finally I found there are 2 x problems.

1) xxxxing funny that SSMS on the desktop of the same version can show DIFFERENT result than the one on local SQL server SSMS !
2) the load balancing of SQL 2016 read only is not good. e.g.  I am using node 2 and it should rout the read request to node 3 and then 4, it can be a long long time before it reroute to nodes 3, it may jump to node 4 first, if I stop the SQL server service on node 4 it jump to node 3, which is good but once I turn node 3 back on it don't jump back to node 3 any more.
3) I need to stop node 4 manually before it route to node 3.. it will hangs for 1-2 sec before goes to nodes 3 once i stop node 4.

so this is not a real load balancing, but it will route the read to other nodes and node 4 access will only come back after more than 10 minutes.

any idea on that ?

might be need to test SQL 2017 on that.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Weird error. What application are you using to connect to the SQL Server database?
You might need to replaced the parameter with Trusted_Connection=true:
Server=tcp:MyAgListener,1433;Database=DbName;Trusted_Connection=true;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

you mean the read only will only connect to the primary SQL nodes if relies on SQL listener ?
Read Only connections won't go the Primary Replica when Secondary Replicas are configured for ReadIntent.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Weird error. What application are you using to connect to the SQL Server database?

just SSMS.

"Read Only connections won't go the Primary Replica when Secondary Replicas are configured for ReadIntent."

that's why I don't understand why this should be here:

RIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

Open in new window


why we need to tell it again ? primary is for read write already.

as you can see from my last post, any way to improve the load balancing better so connection REALLY goes go EACH node evenly  ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
SSMS doesn't use connection strings.
Check this article to learn how to connect to a Secondary Replica with the ApplicationIntent=ReadOnly.

primary is for read write already.
That means it won't accept ReadOnly connections on the Primary Replica. Default values is ALL (when you don't provide nothing) that allows read-write and read-only connections on the Primary Replica.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"SSMS doesn't use connection strings."

it does:

connection string
I follow this link : https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/

"That means it won't accept ReadOnly connections on the Primary Replica"

 if primary replica do not accept write, what it do ? I think set it to READ_ONLY is good when it is a chained AOG as the second AOG primary shouldn't be read write, right ? but that case, second AOG's primary act like distributor, so should be read - write too, agree?


"Trusted_Connection=true:"

should this one give load balancing better ? for me, it still the same.


so by this :

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=WideWorldImporters;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Open in new window


I am not sure if any WRITE operation will router to primary automatically as it seems only for read only operation, like SSRS.

so we only need this connection string for the reporting web application ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I meant, you don't need to build a connection string as you're building for applications.
You just provide the parameters and internally it will create the necessary connection string. And the article I've posted before it shows how to do it.

if primary replica do not accept write, what it do ?
But it accepts. Why are you telling it doesn't?

"Trusted_Connection=true:"

should this one give load balancing better ? for me, it still the same.
No. This is only to use your current Windows account to connect to the database. But where are you using this parameter? Can't be in SSMS for sure.

I am not sure if any WRITE operation will router to primary automatically as it seems only for read only operation, like SSRS.
If you want to connect with write permissions then you mustn't use ApplicationIntent=ReadOnly. If you use it then it will connect to a Secondary Replica and never to the Primary Replica.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"But it accepts. Why are you telling it doesn't?
"

Yeah, but I tested it before.

http://www.sqlerudition.com/how-to-connect-ssms-to-alwayson-secondary/, just one 'ReadOnly' already can do the job ? ok, I am referring to connection parameter, is it diff from connection string you are talking about ?

then why needs so much ?

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=<DB naem>;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Open in new window


I need to ask application team to put this in their application connection string:

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=<DB naem>;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Open in new window

?

"But it accepts. Why are you telling it doesn't?"

 I means by default we need to re-route read only to other node, which is the feature of SQL AOG, I just don't believe why we need an option to set primary to read only too! primary should allow read write, right ? this give me additional concern.

"But where are you using this parameter? "

exactly the same place from your link : http://www.sqlerudition.com/how-to-connect-ssms-to-alwayson-secondary/

is not ? what I mean is, it doesn't make an diff to me.

"If you want to connect with write permissions then you mustn't use ApplicationIntent=ReadOnly"

you mean this make read only operation will route to secondary nodes already and the rest of operation, e..g write operation, already handle by SQL server automatically to primary nodes only! no need to worry about it ALSO route to secondary node too ?


how has one more last thing to do on SQL server 2016 AOG , other than read only routing, load balancing read only , what else should I implement on SQL Server 2016 AOG to let the company fully utilize that potential of SQL 2016 AOG?

I will test SQL 2017 on the same thing later.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think I'm starting to understand what's confusing you.

By default, when you connect through a Listener, it will ALWAYS connect you to the Primary Replica. This is the default behaviour, when you don't use the ApplicationIntent=ReadOnly parameter neither use another Listener.

What's good with AOG is that you can redirect read only requests (as Reports) to a Secondary Replica, avoiding the load on the Primary Replica. For that you usually configure Secondary Replicas to accept the ApplicationIntent=ReadOnly, so in your Reports you must use this parameter in their connection strings to indicate the AOG to redirect this Report query to a Secondary Replica following the order that you've configured in the Primary Replica.
Another option is to create a Listener to point only to a specific Secondary Replica or Replicas and then use the connection string of the Reports to use this Listener to perform their queries, instead of the default Listener.
0
marrowyungSenior Technical architecture (Data)Author Commented:
wait, is parameter is diff from connection string ? please give example here based on what I post. it is the SAME to me .

"when you don't use the ApplicationIntent=ReadOnly parameter neither use another Listener."

what is this mean, sorry ?

"Another option is to create a Listener to point only to a specific Secondary Replica or Replicas and then use the connection string of the Reports to use this Listener to perform their queries, instead of the default Listener."

I think this is what I am doing here, right ?:

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=<DB naem>;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Open in new window


?
or you are saying different connector for diff purpose ? why need 2 if one already can serve ? the second listener also need parameter to work with for read only connection right ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
or you are saying different connector for diff purpose ?
Right. Is what I'm saying.

why need 2 if one already can serve ?
In another question, you was asking about working with many Listeners and now you're asking to work only with one :)
But you're right, if you can work with one, then better. I just gave you another option in case you want to separate Listeners functions (one for write, other one for read only).

the second listener also need parameter to work with for read only connection right ?
If you want to use it only for read only purposes, then yes.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"In another question, you was asking about working with many Listeners and now you're asking to work only with one :)"

man , I want to find out pros and cons.

so if I want the read only operation for all user database goes to the read replica, I shouldn't use this anymore as connection string:

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true; Database=<DB naem>;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

Open in new window


but this:

Server= tcp:<SQL AOG listener>,1433; Trusted_Connection=true;  MultiSubnetFailover=True; ApplicationIntent=ReadOnly;

which do not have this :

Database=<DB naem>; 

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
also , anyway to make the round robin read only operation more evenly load balance between secondary replica?

any other feature of SQL 2016 out of the box feature you setup out of the box and happy to suggest me to test it ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I want to find out pros and cons.
There are none. It only depends on how do you want to use them. Anyone that you choose will work correctly. You might like one more than the other but there are not really pros and cons of one over another.

so if I want the read only operation for all user database goes to the read replica, I shouldn't use this anymore as connection string:
No. That's a wrong statement. If you want to use ReadOnly operation then ApplicationIntent=ReadOnly is required. If you don't provide a database name, it means it will connect to the default database for that user (by default is master).

anyway to make the round robin read only operation more evenly load balance between secondary replica?
I don't think SQL Server provides this feature.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"If you don't provide a database name, it means it will connect to the default database for that user (by default is master)."

so if their application connect to more than one DB for read only , what should we do on the connection string ?

"I don't think SQL Server provides this feature."

ok, I think then my SQL server 2016 installation and configuration is done.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
so if their application connect to more than one DB for read only , what should we do on the connection string ?
One connection string per database. Is how it works. Or then, provide the complete path for other database objects. Example:
SELECT *
FROM table1 t1
    INNER JOIN OtherDBName.dbo.table2 t2 ON t1.key = t2.key

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
"One connection string per database"

I am not sure if an application can have multiple connection string, can't be, right ?

"Or then, provide the complete path for other database objects. Example:"

by link server you mean ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I am not sure if an application can have multiple connection string, can't be, right ?
Why not? Since you have a connection object for each of the databases, that shouldn't be a problem

by link server you mean ?
You'll only need Linked Server to connect to a database in another SQL Server instance. If it's in the same instance you just need to provide database name.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Why not? Since you have a connection object for each of the databases, that shouldn't be a problem"

as I am not developer,  I really didn't know it.

so by reading all your message, I think there will be at least 2 x connection string per application as one without 'ApplicationIntent=ReadOnly;' , that one use for write operation to primary, right?


"You'll only need Linked Server to connect to a database in another SQL Server instance. If it's in the same instance you just need to provide database name."

yeah, by link server anyway.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
so by reading all your message, I think there will be at least 2 x connection string per application as one without 'ApplicationIntent=ReadOnly;' , that one use for write operation to primary, right?
No, man. There's no rule for the number of connection strings. It only depends on your needs or the application needs.
0
marrowyungSenior Technical architecture (Data)Author Commented:
you know, by that read only string, it seems that one only used by read only operation but not write, in order to make system operatable, I think I need one more for write operation, agree?

or just one with read-only, that's it ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
you know, by that read only string, it seems that one only used by read only operation but not write, in order to make system operatable, I think I need one more for write operation, agree?
I think that you're making a confusion here. You don't need the ReadOnly bit to connect to an AOG database. That's only used if you want to send a respective part of the application (e.g. Reports) to query a Secondary Replica to off load the Primary Replica.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"That's only used if you want to send a respective part of the application (e.g. Reports) to query a Secondary Replica to off load the Primary Replica."

e..g reporting service /application only ?

if it is normal read write connection, no connection string is needed?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
if it is normal read write connection, no connection string is needed?
Ofc is needed but then you just provide the Listener Name instead of the Instance Name. The ReadOnly bit it's only a way to say "who's connecting through this connection can't make changes on the records" and that's mostly happens when you just need to perform Reports.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"The ReadOnly bit it's only a way to say "who's connecting through this connection can't make changes on the records" and that's mostly happens when you just need to perform Reports."

ok, I will tell tester to consider 2 x connection string:

1) normal connection if no read only application
2) read only application.

tks
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks

how can award the other contributor ? Metehan  ? EE close ticket method recently keep changing.
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

I found out that when I selecting a lot of data from a table and during this time the AOG failover, SSMS will gives error and I have to do the select again.

how can AOG help us to transparent this kind of error message and let the select wait there until failover done and read /write operation keep going ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the error? Timeout?
Anyway, I would suggest you to open a new question about that since you won't get new Experts to check this one.
0
marrowyungSenior Technical architecture (Data)Author Commented:
this is network connection message:

Msg 10054, Level 20, State 0, Line 14
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 。)

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's  a typical error message when a connection looses the contact from the server.
Definitely open a new question so all Experts can have the same chance to help you with this.
0
marrowyungSenior Technical architecture (Data)Author Commented:
this one: https://www.experts-exchange.com/pendingQuestions/51763/select-operation-drop-when-SQL-server-AOG-failing-over.html#questionAdd 

please wait until it is port.

also I have question on SQL server on Azure and compare with MariaDB on AWS, please take a look.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.