August 26, 2014 at 3:07 pm
plan to setup AG on in both default and named instance of two nodes. Can we do it ? any suggestions ?
August 27, 2014 at 2:04 am
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" 😉
August 27, 2014 at 6:11 am
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.
August 27, 2014 at 7:40 am
I'm assuming this is your configuration
ServerA ServerB
SQLInstanceNamedA SQLInstanceNamedB
And you want
AvailabilityGroup1 AvailabilityGroup2
SQLInstanceDefaultB SQLInstanceNamedB
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 27, 2014 at 7:56 am
Perry Whittle (8/27/2014)
I'm assuming this is your configuration
ServerA ServerB
SQLInstanceDefaultA SQLInstanceDefaultBSQLInstanceNamedA SQLInstanceNamedB
And you want
AvailabilityGroup1 AvailabilityGroup2
SQLInstanceDefaultA SQLInstanceNamedASQLInstanceDefaultB 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.
August 27, 2014 at 8:29 am
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" 😉
August 27, 2014 at 9:21 am
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
August 27, 2014 at 10:13 am
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" 😉
August 27, 2014 at 11:08 am
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
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.
August 27, 2014 at 12:32 pm
Wheres the information for the second availability group?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply