Monday 17 April 2017

The Connection to the Primary Replica is not Active


In this post, I’m going to talk an issue that I found when joining replica or database on secondary replica to availability group.  This error mostly appears when we try to join the adding replica to availability group or database to availability group using GUI and T-SQL.

Error 1


TITLE: Microsoft SQL Server Management Studio
  ------------------------------
  Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)
  ------------------------------

  ADDITIONAL INFORMATION:
  Failed to join the database 'AGTEST' to the availability group 'AG01' on the availability replica 'SQL2'. (Microsoft.SqlServer.Smo)
  ------------------------------
  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  ------------------------------
  The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error:

In below screenshot, joining the database abc to SQL2 failed with this error when creating a new Availability Group.

Error 2


TITLE: Microsoft SQL Server Management Studio
  ------------------------------
  Failed to join the database 'AGTEST' to the availability group 'AG01' on the availability replica 'TREDSHOST08'. (Microsoft.SqlServer.Smo)
  ------------------------------
  ADDITIONAL INFORMATION:
  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  ------------------------------
  The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 35250)

In the below screenshot, joining database abc to SQL2 failed with this error.


Resolution

To solve the issues, follow these steps.

Step 1

Query the sys.dm_hadr_availability_replica_states for the last_connect_error_number which may help you diagnose the join issue. Depending on which replica was having difficulty communicating, you should query both the primary and secondary for the local replica
--connect secondary replica
select r.replica_server_name, r.endpoint_url,
       rs.connected_state_desc, rs.last_connect_error_description, 
       rs.last_connect_error_number, rs.last_connect_error_timestamp 
 from sys.dm_hadr_availability_replica_states rs 
  join sys.availability_replicas r
   on rs.replica_id=r.replica_id
 where rs.is_local=1
In the below screenshot, the secondary instance SQL2 was not able to communicate with SQL1

Step 2

The endpoints were created with the two dedicated IP's but the CREATE AVAILABILITY GROUP statement used the FQDN in the ENDPOINT_URL section. Once I changed the ENDPOINT_URL to use the IP's of the dedicated NIC (TCP://172.16.35.11:5022), the AG started working properly with the dedicated network.
--connect  primary replica
ALTER AVAILABILITY GROUP [AG01]
MODIFY REPLICA ON 'TREDSHOST07' WITH (ENDPOINT_URL = 'TCP://172.16.35.10:5022')
GO
ALTER AVAILABILITY GROUP [AG01]
MODIFY REPLICA ON 'TREDSHOST08' WITH (ENDPOINT_URL = 'TCP://172.16.35.11:5022')
GO

In the below screenshot, I have modified the endpoint URL on both replicas.






















Read My Complete Article "Here"





Ganapathi varma Chekuri
Lead SQL DBA, MCP
Email: gana20m@gmail.com

Linkedin 

Sunday 9 April 2017

Step by Step Configuring a Dedicated Network for Availability Group

One of my clients came up with the requirement to isolate the replication traffic from the public network. The advantage of using a separate network for replication is reduced latency and replication can still run without any latency issues during times of heavy network traffic issued by other applications or backup activities. In this article, I will show a procedure to setup a dedicated network for Availability Group communication
SQL Server 2012 AlwaysOn Availability Groups require a database mirroring endpoint for each SQL Server instance that will be hosting an availability group replica and/or database mirroring session. We have two nodes SQL1 and SQL2 in a Windows failover cluster. Each node has a standalone SQL Serverinstance installed and configured with an Always On AG. Each node also has a separate virtual network adapter for public communication, a virtual network adapter for WSFC communication, and a virtual network adapter that we’ll dedicate to availability group communication.
In the below screenshot, the Node1 (SQL1) Availability Group dedicated NIC IP address is 172.16.35.10. The Node2 (SQL2) Availability Group dedicated NIC IP address is 172.16.35.11.







Reconfigure Communication to a Dedicated Network

Step 1:
Identify the endpoint name configured for Always On replication. By default, “Hadr_endpoint” is the endpoint name. In below screenshot, we see this. 


Read My Complete Article "Here"





Ganapathi varma Chekuri
Lead SQL DBA, MCP
Email: gana20m@gmail.com

Linkedin 


Tuesday 4 April 2017

Step by Step Configuring AlwaysOn with Log Shipping

One of my clients came up with a requirement to use log shipping along with an AlwaysOn Availability Group. They cannot extend an AlwaysOn replica to the disaster recovery site for a few reasons listed below;
  • Infrastructure or staffing to maintain WSFC configurations between different sites
  • Delayed Recovery – In Log-shipping we can have definite delay on secondary database, SLAs on RPO, RTO force a fast recovery from manual error which only can be realized with delayed recovery restoring the transaction log backups on one instance of the HA/DR strategy
We have two SQL Servers in AlwaysOn AG Group SQL1 and SQL2 in Primary site for HA. One SQL Server instance SQL3 in secondary site for disaster recovery. The primary site is already setup with a two node AlwaysOn Availability Group (AG) with SQL1 as the primary replica and SQL2 as the secondary replica. 
In the below screenshot, SQL1 is the primary replica and SQL2 is the secondary replica

To configure AlwaysOn Availability Groups, Reference: http://mssqllover.blogspot.in/2017/02/alwayson-availability-groups-step-by.html

How to setup Log-Shipping on Database part of AlwaysOn Availability Groups

Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. By default, Alwayson backup preferences are set to prefer the secondary. You have to change backup preference and select as primary. In an AlwaysOn Availability Group, BACKUP LOG supports regular log backups only. COPY_ONLY transaction log backups are not supported on secondary replicas.
Right click on the AG group and select backup preference. Select the primary as the backup preference.


Read My Complete Article "Here"





Ganapathi varma Chekuri
Email: gana20m@gmail.com

Linkedin