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



Thursday, 2 March 2017

Step By Step Logshipping configuration


Log shipping provides a means to maintain a secondary server on an automated basis
using a chain of transaction log backups. This chapter explains the basic confi guration of
log shipping along with considerations for configuring the failover and failback within a log
shipping environment.

In this article, I will show you a step by step procedure to configure Log-shipping.

Primary Database
The primary database is accessible to applications and accepts transactions. Transaction
log backups are taken on a periodic basis and copied to the server hosting the secondary
database.

Secondary Database
The secondary database, also referred to as the standby, is normally inaccessible, and
transaction log backups from the primary database are restored on a continuous basis.
The secondary database can be in two different modes: Standby Mode or No Recovery
Mode. When the secondary database is in Standby Mode, users can connect to and issue
SELECT statements against the database. When the secondary database is in No Recovery
Mode, users cannot connect to the database. In either mode, you can restore transaction
logs to the secondary database. You cannot restore transaction logs when users are
connected to the database, so you should not use Standby Mode for high-availability
architectures.

Monitor Server
The monitor server, which is optional within a log shipping architecture, contains a set of jobs
that send alerts when the log shipping session is perceived to be out of sync.

How to enable Log-shipping between two SQL instances in DC and DR site.

Step 1:

Take full backup and log backup of LSDB database in primary instance (SQL1) and restore backups to secondary instance (SQL2) in standby mode.

Open Windows Explorer on the primary and create a share named LS_Primary
Grant Full Control permissions on this share to the SQL Server service account on
the primary as well as Read permissions to the SQL Server Agent service account on
the secondary





Step 2:

Open Windows Explorer on the secondary and create a share named LS_Secondary.
Grant Full Control permissions on this share to the SQL Server service account and the
SQL Server Agent service account on the secondary.

Step 3;

Verify that the database is configured for either the Full or Bulk-logged recovery model. If it is not, change the recovery model to Full. Start SSMS, connect to Primary instance (SQL1) within Object Explorer, right-click the database (LSDB) and choose Properties. Below Select A Page, select Transaction Log Shipping.

Step 4:

Select the Enable This As A Primary Database In A Log Shipping Configuration check
box and click Backup Settings. In the Network Path To Backup Folder text box, enter the Universal Naming Convention (UNC) path to the share you created in step 1. The Backup Folder Is Located On The Primary Server text box, enter the physical path to the directory in which your backups will be stored. Click OK to close the Transaction Log Backup Settings dialog box.

Select schedules

Delete files older than                                    72 hours

Alert if no backups occurs with in                  45 minutes



Step 5:

Click Add to add a new secondary. Click Connect and connect to SQL2; leave the name of the secondary database set to LSDB. You can select No, the secondary database is initialized as we have already restored full backup of database in standby mode in secondary instance.
selected.




Step 6:

Click the Copy Files tab. Set the destination folder that the transaction log backups will be copied to and change the copy interval as per your requirement

.

Step 7:

Click the Restore Transaction Log tab. Verify that the Standby mode is selected, set delay alert
as per your requirement. Click OK to close the Secondary Database Settings dialog box.
Click OK to generate the log shipping configuration.







Run back up job on primary SQL instance and copy, restore jobs in secondary instance. Create the log shipping jobs and alerts, and start log shipping. Verify that backups are going to the correct folder, are copied from LS_Primary to LS_Secondary correctly, and are restored to SQL2.






Step 8:

Right click Primary and Secondary instance, Select reports and click transaction log-shipping status.

This will show you Log-shipping sync status for database between two instances.