Setting up AG on default and named instance

Question

Setting up AG on default and named instance

Dhruva_51

SSChampion

Points: 10521
More actions
August 26, 2014 at 3:07 pm

#290158

plan to setup AG on in both default and named instance of two nodes. Can we do it ? any suggestions ?

Viewing 10 posts - 1 through 10 (of 10 total)

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

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

muthyala_51 (8/26/2014)
plan to setup AG on in both default and named instance of two nodes. Can we do it ? any suggestions ?

yes you can, each instance must be on a separate node and the nodes must be part of the same Windows cluster

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

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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 2

Perry Whittle (8/27/2014)
muthyala_51 (8/26/2014)
plan to setup AG on in both default and named instance of two nodes. Can we do it ? any suggestions ?
yes you can, each instance must be on a separate node and the nodes must be part of the same Windows cluster

perry,

what i was trying to setup was AG b/w two default instaces and AG b/w two named instances running on two nodes.

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

I'm assuming this is your configuration

ServerA ServerB

SQLInstanceDefaultA SQLInstanceDefaultB

SQLInstanceNamedA SQLInstanceNamedB

And you want

AvailabilityGroup1 AvailabilityGroup2

SQLInstanceDefaultA SQLInstanceNamedA
SQLInstanceDefaultB SQLInstanceNamedB

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

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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 4

Perry Whittle (8/27/2014)
I'm assuming this is your configuration
ServerA ServerB
SQLInstanceDefaultA SQLInstanceDefaultB
SQLInstanceNamedA SQLInstanceNamedB
And you want
AvailabilityGroup1 AvailabilityGroup2
SQLInstanceDefaultA SQLInstanceNamedA SQLInstanceDefaultB SQLInstanceNamedB

Yes, you are right. I am able to setup the AG but the listener which I set to point to the primary replica of named instance is always pointing to the default instance on that node.

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

Please send me the outputs of these queries

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT'AlwaysOn Group ' + QUOTENAME(AG.NAME) + ' has the following members;'

FROM SYS.availability_groups AG

UNION

SELECT'Replica ''' + ar.replica_server_name + ''' identified by Endpoint ''' + ar.endpoint_url +

' is set to Availability mode ''' + ar.availability_mode_desc +

''' with ''' + ar.failover_mode_desc + ' failover' +

' failover. Session timeout is ' + CAST(ar.session_timeout AS VARCHAR(25)) + ' secs.' +

' Primary role connections set to ''' + ar.primary_role_allow_connections_desc + '''' +

'Secondary role connections set to ''' + ar.secondary_role_allow_connections_desc +

'Read Only Routing URLs set to ' + ISNULL(ar.read_only_routing_url, 'None')

from sys.availability_replicas ar

SELECT@@SERVERNAMEAS QryLocalInst

, AG.NAMEAS AOGroupName

, AR.replica_server_nameAS AOReplicaName

, DM_ARREP.role_descAS ReplicaRole

--, DB_NAME(DM_DBREP.database_id)AS DatabaseName

, CASE

WHEN DM_ARREP.is_local = 0 THEN 'Local'

ELSE 'Not Local'

ENDAS Is_Local

, AR.endpoint_urlAS EndPointURL

, AR.availability_mode_descAS AvailMode

, AR.failover_mode_descAS FailOverMode

, AR.primary_role_allow_connections_descAS PriRoleConns

, AR.secondary_role_allow_connections_descAS SecRoleConns

, AR.backup_priorityAS BackupPriority

, AR.read_only_routing_urlAS RORouteURL

, DM_DBREP.synchronization_state_desc

, DM_DBREP.synchronization_health_desc

, CASE

WHEN DM_DBREP.is_commit_participant = 0 THEN 'ASynch nmode'

WHEN DM_ARREP.is_local = 1 AND DM_DBREP.is_commit_participant = 1 THEN 'Tran Commit InValid (Secondary)'

ELSE 'Valid on Primary Only'

ENDAS Is_Commit_Participant

, DM_DBREP.database_state_desc

, DM_DBREP.is_suspended

, DM_DBREP.suspend_reason_desc

, DM_DBREP.log_send_rate / 1024.00AS LogSendRateMB

, DM_DBREP.log_send_queue_size

, DM_DBREP.redo_rate / 1024.00AS RedoRateMB

, DM_DBREP.redo_queue_size

, DM_DBREP.last_commit_time

, al.dns_name

, ala.ip_address

, ala.ip_subnet_mask

, ala.is_dhcp

, ala.network_subnet_ip

, ala.network_subnet_ipv4_mask

, ala.network_subnet_prefix_length

, ala.state_desc

FROM SYS.availability_groups AG

INNER JOIN SYS.AVAILABILITY_REPLICAS AR ON AG.group_id = AR.group_id

INNER JOIN SYS.DM_HADR_AVAILABILITY_REPLICA_STATES DM_ARREP ON AR.group_id = DM_ARREP.group_id AND AR.replica_id = DM_ARREP.replica_id

INNER JOIN SYS.DM_HADR_DATABASE_REPLICA_STATES DM_DBREP ON AR.group_id = DM_DBREP.group_id AND AR.replica_id = DM_DBREP.replica_id

INNER JOIN sys.availability_group_listeners al on AG.group_id = al.group_id

INNER JOIN sys.availability_group_listener_ip_addresses ala ON al.listener_id = ala.listener_id

--WHERE DM_DBREP.is_local = 1

ORDER BY AR.replica_server_name

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

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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 6

AlwaysOn Group [FB2014] has the following members;

Replica 'SQL2012-01\SQL2014' identified by Endpoint 'TCP://SQL2012-01.xxxx.com:5028 is set to Availability mode 'SYNCHRONOUS_COMMIT' with 'AUTOMATIC failover failover. Session timeout is 10 secs. Primary role connections set to 'ALL'Secondary role connections set to 'NORead Only Routing URLs set to None

Replica 'SQL2012-02\SQL2014' identified by Endpoint 'TCP://SQL2012-02.xxxx.com:5028 is set to Availability mode 'SYNCHRONOUS_COMMIT' with 'AUTOMATIC failover failover. Session timeout is 10 secs. Primary role connections set to 'ALL'Secondary role connections set to 'NORead Only Routing URLs set to None

QryLocalInstAOGroupNameAOReplicaNameReplicaRoleIs_LocalEndPointURLAvailModeFailOverModePriRoleConnsSecRoleConnsBackupPriorityRORouteURLsynchronization_state_descsynchronization_health_descIs_Commit_Participantdatabase_state_descis_suspendedsuspend_reason_descLogSendRateMBlog_send_queue_sizeRedoRateMBredo_queue_sizelast_commit_timedns_nameis_dhcpnetwork_subnet_prefix_lengthstate_desc

SQL2012-02\SQL2014FB2014SQL2012-01\SQL2014SECONDARYLocalTCP://SQL2012-01.xxxx.com:5028SYNCHRONOUS_COMMITAUTOMATICALLNO50NULLNOT SYNCHRONIZINGNOT_HEALTHYASynch nmodeNULL0NULL0NULL00NULLLISN-SQl-03024ONLINE

SQL2012-02\SQL2014FB2014SQL2012-02\SQL2014PRIMARYNot LocalTCP://SQL2012-02.xxxx.com:5028SYNCHRONOUS_COMMITAUTOMATICALLNO50NULLSYNCHRONIZEDHEALTHYTran Commit InValid (Secondary)ONLINE0NULLNULLNULLNULLNULL25:22.5LISN-SQl-03024ONLINE

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

i see no readonly routing urls, no secondary connections allowed and no listener details in the output from the 2nd query!

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

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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 8

Perry Whittle (8/27/2014)
i see no readonly routing urls, no secondary connections allowed and no listener details in the output from the 2nd query!

ip_addressip_subnet_maskis_dhcpnetwork_subnet_ipnetwork_subnet_ipv4_masknetwork_subnet_prefix_lengthstate_desc

10.200.262.89255.255.255.0010.200.262.0255.255.255.024ONLINE

Can we have a setup something like this ? I am also facing problem while adding db to the AG group on the named instances. It's taking too long for some reason. I can see the database in restoring mode in the secondary replica. But the AG wizard still stuck at the adding db step(last step). This a test db with a single table with one row in it.

Perry Whittle SSC Guru Points: 234040 More actions · Answer 9

Wheres the information for the second availability group?

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

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

Setting up AG on default and named instance

Cookies on SQLServerCentral