Add additional IP addresses to a load balancer for more than one availability group

Add additional IP addresses to a load balancer for more than one availability group and Listener.

Failed to bring availability group 'agadven' online. The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online

Issue:- While configuring AlwaysOn in Azure Environment, sometime users get below error.

Problem:-

Disconnecting connection from NODE2...

Connecting to NODE2...

Disconnecting connection from NODE2...

Connecting to NODE1...

Msg 41131, Level 16, State 0, Line 70

Failed to bring availability group 'agadven' online.  The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 70

Failed to create availability group 'agadven'.  The operation encountered SQL Server error 41131 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Disconnecting connection from NODE1...

Connecting to NODE2...

Msg 41044, Level 16, State 1, Line 82

Availability group name to ID map entry for availability group 'agadven' cannot be found in the Windows Server Failover Clustering (WSFC) store.  The availability group name may be incorrect, or the availability group may not exist in this Windows Server Failover Cluster.  Verify the availability group exists and that the availability group name is correct and then retry the operation.

Msg 41158, Level 16, State 3, Line 82

Failed to join local availability replica to availability group 'agadven'.  The operation encountered SQL Server error 41044 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 NODE2...

Resolution:-

To resolve this issue, use one of the following methods.

Method 1: Use manual steps

if [NT AUTHORITY\SYSTEM] is not present in SQL server Logins Folder

Create a logon in SQL Server for the [NT AUTHORITY\SYSTEM] account on each SQL Server computer that hosts a replica in your availability group.

Grant the [NT AUTHORITY\SYSTEM] account the following server-level permissions:
Alter Any Availability Group
Connect SQL
View server state

Note Make sure that no other permissions are granted to the account.

Method 2: Use script

To create the [NT AUTHORITY\SYSTEM] account, run the following in a query window:

USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

To grant the permissions to the [NT AUTHORITY\SYSTEM] account, run the following in a query window:

GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]


After doing this, my issue resolved

Thanks for reading.

The network connection between your computer and the VPN server could not be established because the remote server is not responding.

   Azure Site to Site VPN in Azure gives below error in your  RRAS Server.

Unable to show XML. The following error happened: Unexpected end of file has occurred. Line 24137, position 629.

when you execute below query to get system_health status and when click on RingBuffer

SELECT CAST(xet.target_data as xml) FROM sys.dm_xe_session_targets xet 

JOIN sys.dm_xe_sessions xe 

ON (xe.address = xet.event_session_address) 

WHERE xe.name = 'system_health' 

you get below error

TITLE: Microsoft SQL Server Management Studio

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

Unable to show XML. The following error happened:

Unexpected end of file has occurred. Line 24137, position 629.

One solution is to increase the number of characters retrieved from the server for XML data.

 To change this setting, on the Tools menu, click Options.

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

BUTTONS:

OK

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

In order to resolve this issue:-

On SSMS – Click on Tools > options> Query Results >> SQL Server >>General >> Results to Grid >> XML data > Unlimited.

Open a new SSMS window and execute the above query again and issue will not come.

Thanks for Reading..   

How to encrypt virtual disks on a Windows VM using Power Shell

How to encrypt & Disable virtual disks on a Windows VM using Power Shell

How to add a Secondary NIC on a VM in Azure

How to add a Secondary NIC on a VM in Azure
===================================

I have a Virtual Machine named sqlnode1 and it is on ResourceGroupName  rakctlrg
The Virtual Machine has a NIC interface whose IP address is 10.0.0.4
The customer comes and say that they want to add a Extra NIC on the server.
How we will add a Extra NIC on the server.

Below is the Step by Step process to ADD a Secondary NIC on the server.
======================================================
1. Go to shell.azure.com
2. login to shell.azure.com


#First store the Azure VM in the a $VM variable

$vm = Get-AzureRmVm -Name "sqlnode1" -ResourceGroupName "rakctlrg"

#Make existing NIC as a Priamry NIC.
================================

$VM.NetworkProfile.NetworkInterfaces.Item(0).primary = $true
Update-AzureRmVM -ResourceGroupName "rakctlrg" -VM $VM

# Get info for the back end subnet

$myVnet = Get-AzureRmVirtualNetwork -Name "rakctlrg-vnet" -ResourceGroupName "rakctlrg"
$backEnd = $myVnet.Subnets|?{$_.Name -eq 'default'}

# Create a virtual NIC
====================

$myNic3 = New-AzureRmNetworkInterface -ResourceGroupName "rakctlrg" `
    -Name "myNic3" `
    -Location "centralus" `
    -SubnetId $backEnd.Id

# Get the ID of the new virtual NIC and add to VM
$nicId = (Get-AzureRmNetworkInterface -ResourceGroupName "rakctlrg" -Name "MyNic3").Id


Before executing below command, you need to stop the VM.
Then execute the below command

#Add a AzureRMVMNetworkInterface
=============================
Add-AzureRmVMNetworkInterface -VM $vm -Id $nicId | Update-AzureRmVm -ResourceGroupName "rakctlrg"


Then start the VM, you will find one more NIC in the server.


you will observe one extra NIC has been added in Azure VM.

Important point to remember:-
=======================

1. VM should be in deallocated state.
2. we can add multiple NIC in a VM, but it depends on the size of VM, example D2s v3  can support maximum of 2 NIC.
3. when adding 2nd NIC, first NIC should be on primary NIC.

Thanks for Reading..

Add a NIC to an existing VM in Azure gets error Update-AzureRmVm : Virtual machine sqlnode1 must have one network interface set as the primary. ErrorCode: VirtualMachineMustHaveOneNetworkInterfaceAsPrimary

while adding additional NIC to the existing VM in Azure  i received  below error.

Error that comes in Powershell..


Update-AzureRmVm : Virtual machine sqlnode1 must have one network interface set as the primary.
ErrorCode: VirtualMachineMustHaveOneNetworkInterfaceAsPrimary
ErrorMessage: Virtual machine sqlnode1 must have one network interface set as the primary.
StatusCode: 400
ReasonPhrase: Bad Request
OperationID : b4b511bd-5333-4f18-b04c-22a8bf627923
At line:1 char:52
+ ... e -VM $vm -Id $nicId | Update-AzureRmVm -ResourceGroupName "rakctlrg"
+                            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : CloseError: (:) [Update-AzureRmVM], ComputeCloudException
+ FullyQualifiedErrorId : Microsoft.Azure.Commands.Compute.UpdateAzureVMCommand

In order to resolve this issue, we have to execute below steps:-

=============================================

$vm = Get-AzureRmVm -Name "sqlnode1" -ResourceGroupName "rakctlrg"
$VM.NetworkProfile.NetworkInterfaces.Item(0).primary = $true
Update-AzureRmVM -ResourceGroupName "rakctlrg" -VM $VM

Important point to note:-
=================


1. VM should be in deallocated state.
2. we can add multiple NIC in a VM, but it depends on the size of VM, example D2s v3  can support maximum of 2 NIC.
3. when adding 2nd NIC, first NIC should be on primary NIC.


Thanks for Reading..