Archive

Archive for the ‘AlwaysOn 2012’ Category

The Service Broker endpoint is in disabled or stopped state. Replica Fails to Join the Availability Group

September 18, 2015 Leave a comment

We recently configured Always On AG group and Listeners , using T-SQL we added a Replica which fails to join the AG Group

Evey time we try to join the AG Group it fails with “The Service Broker endpoint is in disabled or stopped state.” which is unrelated and misleading. To get to the root of the problem we start troubleeting the issue with Endpoints and ports

 

  • Check Mirroring Endpoint  exists and LISTENER_PORT is configured
  • Check Primary Replica ENDPOINT_URL , the ENDPOINT_URL should be using the same port as Mirroring Endpoint LISTENER_PORT

In our case Mirroring Endpoint  was missing from Primary and Secondary Replica. The DBA created the AG group manually which do not check if mirroring endpoint exists or not. The ENDPOINT_URL was configured with a port no which was not he default for Mirroring Endpoint

Primary Replica was configured with a different ENDPOINT other then the default mirroring end point, so we can’t modify it and we don’t want to drop AG group and listener.

 

So, to fix the issue we removed the secondary Replica from  AG Group , dropped Mirroring Endpoint, recreated mirroring endpoint with the port no of Primary Replica ENDPOINT_URL. Add Secondary Replica to the AG group and issues the alter Availability group [AG Name ] join command , which completed successfully.

 

 

Categories: AlwaysOn 2012

Enable AlwaysOn PowerShell

March 21, 2015 Leave a comment

 

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\nodename or hostname\instancename  -NoServiceRestart

Categories: AlwaysOn 2012

SQL Server AlwaysOn Troubleshooting Part -1

February 22, 2015 Leave a comment

 

Get Availability group information

 
T-SQL :

select name,group_id,health_check_timeout from master.sys.availability_groups

image

 

Get Availability group Replica information

T-SQL :

select replica_server_name,create_date,
       modify_date,endpoint_url,read_only_routing_url,avalibility_mode,
       failover_mode,session_timeout,backup_priority
from master.sys.avalibility_replicas

image

 

Get Availability group databases

T-SQL :

select database_name,is_failover_ready,is_pending_secondary_suspend,is_database_joined
from master.sys.dm_hadr_database_replica_cluster_states

select * from sys.dm_hadr_database_replica_states

 

image

 

Get Availability group listeners

T-SQL:

select * from sys.availability_group_listeners

image

 

Join all DMV’s for AlwaysOn configuration

T-SQL :

select ag.name,replicas.replica_server_name,replicas.availability_mode_desc,
         replicas.failover_mode_desc,replicas.session_timeout,
         agd.database_name,agd.is_database_joined,drs.is_local,
         drs.is_primary_replica,drs.synchronization_state_desc,
         drs.synchronization_health_desc,drs.database_state_desc,agl.dns_name,
         agl.port,agl.ip_configuration_string_from_cluster
            from       master.sys.availability_replicas replicas 
            inner join master.sys.availability_groups ag on replicas.group_id=ag.group_id
            inner join master.sys.dm_hadr_database_replica_cluster_states agd on agd.replica_id=replicas.replica_id 
            inner join master.sys.dm_hadr_database_replica_states drs on drs.replica_id=replicas.replica_id
            inner join master.sys.availability_group_listeners agl on agl.group_id=ag.group_id

 

image

Categories: AlwaysOn 2012

Configure Always On and Availability group Part 3

January 3, 2015 Leave a comment

Add Secondary Replica

 

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. :Connect SQL03\GOSGPRDSQL02 use [master] GO GRANT CONNECT ON ENDPOINT::[Mirroring_Endpoint] TO [DBPROX\administrator] GO :Connect SQL02\GOSGPRDSQL01 IF (SELECT state FROM sys.endpoints WHERE name = N'Mirroring_Endpoint') <> 0 BEGIN ALTER ENDPOINT [Mirroring_Endpoint] STATE = STARTED END GO use [master] GO GRANT CONNECT ON ENDPOINT::[Mirroring_Endpoint] TO [DBPROX\administrator] GO :Connect SQL02\GOSGPRDSQL01 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\GOSGPRDSQL02 USE [master] GO ALTER AVAILABILITY GROUP [GLBAGSQL0203] ADD REPLICA ON N'SQL02\GOSGPRDSQL01' WITH (ENDPOINT_URL = N'TCP://SQL02.dbprox.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)); GO :Connect SQL02\GOSGPRDSQL01 ALTER AVAILABILITY GROUP [GLBAGSQL0203] JOIN; GO

Categories: AlwaysOn 2012

Configure Always On and Availability group Part 2

January 2, 2015 Leave a comment

Listener Configuration

 

An availability group listener is a virtual network name (VNN) to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. An availability group listener enables a client to connect to an availability replica without knowing the name of the physical instance of SQL Server to which the client is connecting. The client connection string does not need to be modified to connect to the current location of the current primary replica.

An availability group listener consists of a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses. Only the TCP protocol is supported by availability group listener. The DNS name of the listener must also be unique in the domain and in NetBIOS. When you create a new availability group listener it becomes a resource in a cluster with an associated virtual network name (VNN), virtual IP (VIP), and availability group dependency. A client uses DNS to resolve the VNN into multiple IP addresses and then tries to connect to each address, until a connection request succeeds or until the connection requests time out.

#Create Availability Group Listener

TSQL :

USE [master] GO ALTER AVAILABILITY GROUP [GLBAGSQL0203] ADD LISTENER N'GLBAGSQL0203LST' (WITH IP ((N'192.192.0.14', N'255.255.255.0')) , PORT=1522); GO

image

image

 

image

 

PowerShell :

#Creating AlwaysOn availability group Listener Import-Module SQLPS [string[]] $ServerList= "SQL03\GOSGPRDSQL02" #SQL Instance Name on Availability Group is Online $Agname='GLBAGSQL0203' #Availability Group Name $Port='1522' #Mention Listener Port Number (By Default it listens on 1433) $ListenerName='GLBAGSQL0203LST' #Listener Name $StaticIP='192.192.0.14' #Static unique IP for Listener) $Subnet='255.255.255.0' #Listener IP Subnet $StaticSubnetIP=$StaticIP+'/'+$Subnet $serverObjects = @() $replicas = @() foreach ($Server in $ServerList) { write-host '' write-host '[ '$Server' ]' -ForegroundColor Yellow Write-Host "[-]Creating SMO Object for Server: $Server" `n $serverObject=New-Object Microsoft.SQLServer.Management.SMO.Server($Server) if ($serverObject) {Write-Host " |-SMO Object for Server: $Server created successfully" -ForegroundColor Yellow `n} #Create Availability Group Listener Write-Host "[-]Creating Availability Group Listener for : $Server" `n $Path='SQLSERVER:\SQL\'+$Server+'\AvailabilityGroups\'+$Agname Write-host $Path $sqagl=New-SqlAvailabilityGroupListener -Name $ListenerName ` -StaticIp $StaticSubnetIP ` -Path $Path if ($sqagl -eq $null) { Write-Host " |-Availability Group Listener $Agname created successfully" `n } Write-Host "[-]Setting Availability Group Listener for : $Server" `n $sqaglpath='SQLSERVER:\SQL\'+$Server+'\AvailabilityGroups\'+$Agname+'\AvailabilityGroupListeners\'+$ListenerName $saglp=Set-SqlAvailabilityGroupListener -Port $Port ` -Path $sqaglpath if ($saglp) { Write-Host " |-Availability Group Listener for : $Server configured successfully" `n } }

Categories: AlwaysOn 2012

Configure Always On and Availability group Part 1

January 1, 2015 Leave a comment

Enable AlwaysOn and Configure Availability Group with Primary Replica

# Check Always On  is enabled

PS C:\> Set-Location SQLServer:\SQL\SQL03\GOSGPRDSQL02 PS SQLSERVER:\SQL\SQL03\GOSGPRDSQL02>Get-Item . |Select IsHADREnabled |Format-Table -AutoSize

image

# Enable always on if not enabled.

PS C:\> Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\SQL03\GOSGPRDSQL03

image

#SQL Server services should be recycled  (Stopped and started)

image

#Check AlwaysOn is Enabled.

image

#Create Database Mirroring Endpoint @ Primary SQL Instance

image

 

#Create Database Mirroring Endpoint @ Secondary SQL Instance

image

 

#Create Availability Group  with Primary Instance as Replica (Secondary Replica will be added later)

T-SQL

USE [master] GO CREATE AVAILABILITY GROUP [GOSGPRDAGSQL02SQL03] WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY) FOR REPLICA ON N'SQL03\GOSGPRDSQL02' WITH (ENDPOINT_URL = N'TCP://SQL03.dbprox.local:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)); GO

 

Powershell

$primaryReplica = New-SqlAvailabilityReplica -Name "SQL03\GOSGPRDSQL02" -EndpointURL "TCP://SQL03.dbprox.com:5022" -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Version 12 -AsTemplate New-SqlAvailabilityGroup -Name "GLBAGSQL0203" -Path "SQLSERVER:\SQL\SQL03\GOSGPRDSQL02" -AvailabilityReplica @($primaryReplica)

 

image

 

image

 

#Availability Group service is create and can be seen in the Windows Cluster

 

image

Categories: AlwaysOn 2012