Saturday, 22 July 2017

Resolve Network Binding Order Warning in failover cluster

In this post, I’m going to talk about an issue that I found while running the cluster validation report. You may receive below warning message that includes information about the cause of the warning, which can help you to resolve the issue.
Rule “Network binding order” generated a warning.
The domain network is not the first bound network.  This will cause domain operations to run slowly and can cause timeouts that result in failures.  Use the Windows network advanced configuration to change the binding order.

In the below screenshot, the warning says the network card used to connect to your domain network is not at the top of the network binding order.

Resolution

Step 1

Go to run and type ncpa.cpl to open network connections. Now press Alt+F and select advanced options then click on Advanced and Advanced SettingsWhen the Advanced Settings window opens, you will be on the Adapters and Bindings tab, which allows you to change the binding order for the network interfaces on the server.

Step 2:

In this case the Microsoft Failover Cluster Virtual Adapter is bound before the domain network and still the cluster validation report shows you warning. In the below screenshot, the TeamNIC_MGMT NIC adapater is my domain network and was already at the top of the binding order.

Read My Complete Article "Here"



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

Linkedin 


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
Lead SQL DBA, MCP
Email: gana20m@gmail.com

Linkedin 




Thursday, 23 March 2017

TempDB Database On a Local Disk for an FCI


Link Below:


TempDB Database On a Local Disk for an FCI


This article will be helpful in configuring TempDB on a local Disk instead of shared storage in SQL Server 2012/2014. This will reduce the I/O requests from the shared storage and overall it can improve performance.

Monday, 20 March 2017

Backup and Restore Strategies in SQL Server : A Brief Introduction

Establishment of a successful business requires the proper maintenance of the data. SQL Server is one the popular platform for the same. In SQL Server, a proper strategy has been introduced for the management of data. The Main reason of introducing SQL Server backups and restore strategies in SQL Server is to enable the users to regain the database, which is corrupted. However, restoring and backing up of data must be done in particular environment and must work with the resources that are available. This is the reason users need a reliable strategy to backup and restore the database in SQL Server.

Tuesday, 7 March 2017

Webinar: Performance Troubleshooting Using Wait Statistics by Paul Randal



Hi Folks, 

Join Webinar on Webinar: Performance Troubleshooting Using Wait Statistics. Tue 3/07/2017 from 10:30 PM to 11:45 PM IST

Click [Here] to join