AlwaysOn ReadIntent via SSMS not routing?

Question

AlwaysOn ReadIntent via SSMS not routing?

Adam Bean

One Orange Chip

Points: 26631
More actions
November 15, 2014 at 8:30 pm

#313035

Hey all,
I setup an AG with a listener (AO1) on three servers.
Server1 = Primary, Readable Secondary = No
Server2 = Secondary, Readable Secondary = Read-intent only
Server3 = Secondary, Readable Secondary = Read-intent only
Connecting to AO1 with default settings, I end up on Server1.
If I attempt to connect to AO1 with specifying ;ApplicationIntent=ReadOnly (with or without specifying the db name) in SSMS, still lands me on Server1.
What am I missing here?
Also on topic, in this scenario I would actually want Server1 to be a readable secondary (in the event of a fail-over). So If I did set it as Yes, even If I do get the issue above working, won't I always land on Server1 then?
Thanks!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

can you provide details of the scripts you used to define the read only routing configuration (obfuscate any computer names and domain, etc)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Just went through a new setup:

CREATE DATABASE MyDB4

BACKUP DATABASE MyDB4 TO DISK = 'c:\Backup\MyDB4.bak'

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:Connect SQL01\AO1

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0

BEGIN

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\svcSQLAdm1]

GO

:Connect SQL02\AO2

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0

BEGIN

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\svcSQLAdm1]

GO

:Connect SQL03\AO3

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0

BEGIN

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\svcSQLAdm1]

GO

:Connect SQL01\AO1

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

GO

:Connect SQL02\AO2

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

GO

:Connect SQL03\AO3

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

GO

:Connect SQL01\AO1

USE [master]

GO

CREATE AVAILABILITY GROUP [AG4]

WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)

FOR DATABASE [MyDB4]

REPLICA ON N'SQL01\AO1' WITH (ENDPOINT_URL = N'TCP://SQL01.contoso.local:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),

N'SQL02\AO2' WITH (ENDPOINT_URL = N'TCP://SQL02.contoso.local:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)),

N'SQL03\AO3' WITH (ENDPOINT_URL = N'TCP://SQL03.contoso.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));

GO

:Connect SQL01\AO1

USE [master]

GO

ALTER AVAILABILITY GROUP [AG4]

ADD LISTENER N'DBP_AOTEST4' (

WITH IP

((N'192.168.201.26', N'255.255.255.0')

)

, PORT=1433);

GO

:Connect SQL02\AO2

ALTER AVAILABILITY GROUP [AG4] JOIN;

GO

:Connect SQL03\AO3

ALTER AVAILABILITY GROUP [AG4] JOIN;

GO

:Connect SQL01\AO1

BACKUP DATABASE [MyDB4] TO DISK = N'\\SQL01\Backup\MyDB4.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

GO

:Connect SQL02\AO2

RESTORE DATABASE [MyDB4] FROM DISK = N'\\SQL01\Backup\MyDB4.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5

GO

:Connect SQL03\AO3

RESTORE DATABASE [MyDB4] FROM DISK = N'\\SQL01\Backup\MyDB4.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5

GO

:Connect SQL01\AO1

BACKUP LOG [MyDB4] TO DISK = N'\\SQL01\Backup\MyDB4_20141116175437.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

GO

:Connect SQL02\AO2

RESTORE LOG [MyDB4] FROM DISK = N'\\SQL01\Backup\MyDB4_20141116175437.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5

GO

:Connect SQL02\AO2

-- Wait for the replica to start communicating

begin try

declare @conn bit

declare @count int

declare @replica_id uniqueidentifier

declare @group_id uniqueidentifier

set @conn = 0

set @count = 30 -- wait for 5 minutes

if (serverproperty('IsHadrEnabled') = 1)

and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)

and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'AG4'

select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

while @conn <> 1 and @count > 0

begin

set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

if @conn = 1

begin

-- exit loop when the replica is connected, or if the query cannot find the replica status

break

end

waitfor delay '00:00:10'

set @count = @count - 1

end

end try

begin catch

-- If the wait loop fails, do not stop execution of the alter database statement

end catch

ALTER DATABASE [MyDB4] SET HADR AVAILABILITY GROUP = [AG4];

GO

:Connect SQL03\AO3

RESTORE LOG [MyDB4] FROM DISK = N'\\SQL01\Backup\MyDB4_20141116175437.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5

GO

:Connect SQL03\AO3

-- Wait for the replica to start communicating

begin try

declare @conn bit

declare @count int

declare @replica_id uniqueidentifier

declare @group_id uniqueidentifier

set @conn = 0

set @count = 30 -- wait for 5 minutes

if (serverproperty('IsHadrEnabled') = 1)

and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)

and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'AG4'

select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

while @conn <> 1 and @count > 0

begin

set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

if @conn = 1

begin

-- exit loop when the replica is connected, or if the query cannot find the replica status

break

end

waitfor delay '00:00:10'

set @count = @count - 1

end

end try

begin catch

-- If the wait loop fails, do not stop execution of the alter database statement

end catch

ALTER DATABASE [MyDB4] SET HADR AVAILABILITY GROUP = [AG4];

GO

I connect to the listener "DBP_AOTEST4" with no changes.

SELECT @@SERVERNAME

= SQL01

Connect to DBP_AOTEST4 with ;ApplicationIntent=ReadOnly

SELECT @@SERVERNAME

= SQL01

Connect to DBP_AOTEST4 with ;ApplicationIntent=ReadOnly + default database = MyDB4

SELECT @@SERVERNAME

= SQL01

What am I doing wrong?

Thanks

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

you have added a listener but I can't see the scripts to configure your readonly routing?

See my guide at this link[/url] and scroll down to the section "Configure ReadOnly Routing" for more info

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Will try later; however I defined the read intent via wizard and it's in the AG creation script above. You have to go back and state read intent a second time to make it work? Seems counter productive?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Adam Bean (11/16/2014)
it's in the AG creation script above.

mmm, nope, just been through your script and there are no readonly routing urls or secondary\primary configuration scripts. Please read my guide

Adam Bean (11/16/2014)
You have to go back and state read intent a second time to make it work? Seems counter productive?

you just need to run the appropriate routing config scripts.

Incidentally what user accounts are your AO group replicas running under. In order for the Listener network name SPN to function properly all replicas must use the same service account.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

Adam Bean

One Orange Chip

Points: 26631

November 16, 2014 at 4:08 pm

#1758850

Ok cool, I'll check into it when I get home.

Thanks

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

how you getting on?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

Hi Perry,

So first and foremost, why does using the GUI and specifying the Readable Secondary not take into account any of this? Am I missing something in the AG setup, or is manually building the routing and preferences a mandatory step?

I'm assuming the latter; however ...

-- Configure ReadOnly Routing