Failed to open file C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\devenv.exe.config.tmp

Issue:-

SQL Server 2008 R2 setup failed while update from SP1 to CU5

Adding Replica of Multisubnet gives error operation encountered SQL Server error 19456 and has been rolled back.

Issue:-

None of the IP addresses configured for the availability group listener can be hosted by the server 'Node3SQL03'. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted on a public cluster network for this server.

Failed to join local availability replica to availability group 'SQL0040DAG01'.  The operation encountered SQL Server error 19456 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 19456)

Failed to join local availability replica to availability group 'SQL0040DAG01'.  The operation encountered SQL Server error 19456 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.

Disconnecting connection from Node3SQL03...

Issue:-

Servers in Multi-subnet clustering with AlwaysOn Configured in Multi-subnet Envirnment
DataCenter-1	IP	DataCenter-2	MgmtIP
Node1SQL01	192.168.1.10	Node3SQL03	192.168.2.12
Node2SQL02	192.168.1.11	Node4SQL04	192.168.2.13

and existing Availability group is -- SQL0040DAG01

The requirement is to add 3rd replica{Node3SQL03} in existing AG which belongs to a different data center and hence in the different network.

I encountered below error, while adding Availability Replica in AG in Multi-subnet environment:

None of the IP addresses configured for the availability group listener can be hosted by the server 'Node3SQL03'. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted on a public cluster network for this server.

Failed to join local availability replica to availability group 'SQL0040DAG01'. The operation encountered SQL Server error 19456 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 19456)



Failed to join local availability replica to availability group 'SQL0040DAG01'. The operation encountered SQL Server error 19456 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.

Disconnecting connection from Node3SQL03...

And

Connecting to Node3SQL03...

Msg 41158, Level 16, State 3, Line 2

Failed to join local availability replica to availability group 'SQL0040DAG01'. The operation encountered SQL Server error 41106 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.

Disconnecting connection from Node3SQL03...

Reason of this issue:-

For this AG, which is  implemented in multi-subnet cluster, existing listener was configured only with  one of the subnet IPaddress [192.168.1.*], which belongs to datacenter 1.

 This error message popped up while configuring the replicas in another subnet, and the reason for error message was that the listener was configured with only one subnet IP address.

Hence there is a need to add IP address for another subnet in this existing listener.

Resolution:-

So once we will configure listener with  IPaddress of second subnet, as shown in the figure -1, we will be able to successfully add replica of the secondary subnet’s [192.168.2.*].

To do this, I clicked on existing listener SQL0040DAG01 –> Property --> and added an IP address of 192.168.2.14(Datacenter2)

Then Click OK.

Now try to add 3rd replica on existing AG, the issue will get resolve.

Hope this will help to someone who has this issue.

Thanks for reading... 

Manual Failover of Availability Group to Disaster Recovery site in Multi-Site Cluster

Manual Failover of Availability Group to Disaster Recovery site in Multi-Site Cluster

Availability replica is in disconnected state

How to create SQL logins in AlwaysOn Environment

       How to create Login’s in case of AlwaysOn Database Server.

-------------------------------------------------------------------------------------------------------------------------------------------------

The Process is:-

Availability group (SQL0040AAG01) has 4 replica names

1.            SQLNode1 -- Primary

2.            SQLNode2  -- Secondary

3.            SQLNode3  -- -- Secondary

4.            SQlNode4 -- -- Secondary

Whereas SQLNode1 is Primary replica and other 3 are secondary replica.

Then, the logic is for other 3 replica login  SID should match...

/* On the PRIMARY replica */ [SQLNODE1] USE [master] GO CREATE LOGIN [TestDBA] WITH PASSWORD=N'Test@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [Adven_pub] GO CREATE USER [TestDBA] FOR LOGIN [TestDBA] GO USE [Adven_pub] GO ALTER ROLE [db_datareader] ADD MEMBER [TestDBA] GO USE [Reservation] GO CREATE USER [TestDBA] FOR LOGIN [TestDBA] GO

In primary replica  using this site – > https://support.microsoft.com/en-us/kb/918992

get an script for sp_help_revlogin 

Execute this complete stored procedure in master database, from Primary replica

then In primary replica  use below command :-

sp_help_revlogin 'TestDBA'

you will get an output like this :-

/* sp_help_revlogin script 

** Generated Sep 30 2015 12:47PM on SQLNode1 */


-- Login: TestDBA
CREATE LOGIN [TestDBA] WITH PASSWORD = 0x0200670719A986569F7AC81D280F060E1DF59E55DE89DD83905F9443DD752329EC77AA7038C6D816D46A07C952F9D7F2BF7DEFDFB5744659F566543A790624712C82A457BBA3 HASHED, SID = 0xC6E83AFC0F3A674BB70D0279A60291DB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Connect SQLNode2 

copy create login script  to secondary replica:-

and execute this in 

use master 

go

-- Login: TestDBA
CREATE LOGIN [TestDBA] WITH PASSWORD = 0x0200670719A986569F7AC81D280F060E1DF59E55DE89DD83905F9443DD752329EC77AA7038C6D816D46A07C952F9D7F2BF7DEFDFB5744659F566543A790624712C82A457BBA3 HASHED, SID = 0xC6E83AFC0F3A674BB70D0279A60291DB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Permissions will get replicated automatically to secondary replica using AlwaysOn methodology.

Replicate the same process in another secondary replica for that database... .

If user exists in secondary replica and no one is using this login or password mismatch error comes or orphan login issue comes  then

Connect  secondary replica SQLNode2 

Copy this to secondary replica:-

Use master 

go

DROP LOGIN [TestDBA]

GO

-- Login: TestDBA

CREATE LOGIN [TestDBA] WITH PASSWORD = 0x0200670719A986569F7AC81D280F060E1DF59E55DE89DD83905F9443DD752329EC77AA7038C6D816D46A07C952F9D7F2BF7DEFDFB5744659F566543A790624712C82A457BBA3 HASHED, SID = 0xC6E83AFC0F3A674BB70D0279A60291DB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Thanks for reading and Happy Learning :) 

SQL Server setup fails on SQLBrowserConfigAction_install_ConfigNonRC_CPU32

Issue:- SQL Server setup Installation  gets fail with below error highlighted in details.txt file.

How to detect Head Blocker

How to detect  head Blocker