November 24, 2021 at 4:17 am
Hi guys,
what I am getting null , I should be getting 'NOTPRIMARY'
select ' if cast(serverproperty(''machinename'') as nvarchar(20)) in ('+ stuff((
SELECT ', N''' +
CASE WHEN (CS.replica_server_name IS NULL OR CS.replica_server_name ='' OR CS.replica_server_name=NULL) THEN N'NOTPRIMARY' ELSE CS.replica_server_name END
+ '''' --, CS.join_state_desc, RS.role_desc, RS.operational_state_desc, RS.connected_state_desc, RS.synchronization_health_desc
FROM sys.availability_groups_cluster AS C
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS CS
ON CS.group_id = C.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS RS
ON RS.replica_id = CS.replica_id
where role_desc= 'SECONDARY' AND C.name ='x'
for xml path('')
),1,1,'') +') BEGIN ' + char(13) as '--chksvrs'
November 24, 2021 at 4:18 am
oh and run it on secondary node, thanks in advance
November 24, 2021 at 1:12 pm
If you run just the inside part of the query, as follows, what result do you get:
SELECT CASE
WHEN
(
CS.replica_server_name IS NULL
OR CS.replica_server_name = ''
) THEN
N'NOTPRIMARY'
ELSE
CS.replica_server_name
END
FROM sys.availability_groups_cluster C
INNER JOIN sys.dm_hadr_availability_replica_cluster_states CS
ON CS.group_id = C.group_id
INNER JOIN sys.dm_hadr_availability_replica_states RS
ON RS.replica_id = CS.replica_id
WHERE RS.role_desc = 'SECONDARY'
AND C.name = 'x';
I don't have HA configured, so cannot properly test.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 24, 2021 at 2:22 pm
the result is nothing, thnx
November 24, 2021 at 2:34 pm
is c.name really = to 'x'?? what does this return?
SELECT CASE
WHEN
(
CS.replica_server_name IS NULL
OR CS.replica_server_name = ''
) THEN
N'NOTPRIMARY'
ELSE
CS.replica_server_name
END,
c.name
FROM sys.availability_groups_cluster C
INNER JOIN sys.dm_hadr_availability_replica_cluster_states CS
ON CS.group_id = C.group_id
INNER JOIN sys.dm_hadr_availability_replica_states RS
ON RS.replica_id = CS.replica_id
WHERE RS.role_desc = 'SECONDARY'
--AND C.name = 'x'
;
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 24, 2021 at 2:35 pm
OK, so now you understand why you did not get 'NOTPRIMARY', I assume?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 24, 2021 at 2:37 pm
i got it, thnx
November 24, 2021 at 2:46 pm
x= alwayson group name.
the query is to return list of Secondary replicas for specific ag , if you run on secondary it should return none for the said AG
November 24, 2021 at 2:58 pm
As an aside, as you're using SS 2019, I suggest you start using STRING_AGG() rather than the legacy FOR XML PATH method.
Makes your code much easier to read.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 24, 2021 at 4:25 pm
Thanks for your help and advise.
November 26, 2021 at 5:30 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply