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..



  

The cluster either has not been verified or there are errors or failures in the verification report

sometime when you add a node to the cluster you get below error:-

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

Rule Check Result

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

Rule "Microsoft Cluster Service (MSCS) cluster verification errors" failed.

The cluster either has not been verified or there are errors or failures in the verification report. Refer to KB953748 or SQL Server Books Online for more information.

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

OK  

You will get error like this:-

Workaround: -

To work around this issue, you must fix the problem that caused validation to fail. If you can determine that the problem that caused validation to fail can be fixed later, you might want to use the command line installation option in this article to ignore the error message, and to try to install the SQL Server 2008 failover cluster instance. If you do this, before using the system again you must still fix the underlying problem that caused validation to fail.

Note If you try this command line installation option and SQL Server Setup fails, make sure that the cluster hardware configuration is valid, and then contact Microsoft Customer Support Services (CSS) for more help.

At a command prompt, change to the hard disk drive and to the folder that contains SQL Server Setup (Setup.exe). Then, type one of the following commands to skip the validation rule:

For an integrated failover Add-Note setup, run the following command on each node that is being added:

   Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster

For an advanced or enterprise installation, run the following command:

    Setup /SkipRules=Cluster_VerifyForErrors /Action=CompleteFailoverCluster

If you receive this validation failure when you add a node to an existing failover installation, run the following command on each node that is being added:

    Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode

Note Setting up a SQL Server 2008 failover cluster instance on a Windows Server 2008-based failover cluster that contains errors in the Windows Server 2008 Cluster Validation Report is unsupported. For a SQL Server 2008 failover cluster instance to be in a supported scenario, the Windows Server 2008 Cluster Validation Report cannot contain errors. Confirm with Microsoft CSS that the cluster configuration is in a supported state.

How to creates a cluster log file for all nodes, or a specific a node, in a failover cluster in user local time.

How to creates a log file for all nodes, or a specific a node, in a failover cluster.

Change SQL Servers Authentication Mode with PowerShell using SMO

# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'sqlnodedev1'
[string]$nm = $s.Name
[string]$mode = $s.Settings.LoginMode

write-output "Instance Name: $nm"
write-output "Login Mode: $mode"


#Change to Mixed Mode
$s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

# Make the changes
$s.Alter()



PowerShell Script – Restart the SQL Server Agent Service (Using SMO)
-----------------------------------------------------------------------------------------

save the content of below highlighted in a  file.
file name SQLServerAgent_restart.ps1

Example Execution: .\SQLServerAgent_restart.ps1 ServerName

param([String]$ServerName)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null

#Create a new Managed computer object for the instance
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $ServerName

$sqlagnt = $mc.Services['SQLSERVERAGENT']

Write-Host "Stopping SQL Server Agent"

$sqlagnt.Stop()
start-sleep -s 10
$sqlagnt.Start()

Write-Host "Started SQL Server Agent"



PowerShell Script – Restart the default  SQL Server Service (Using SMO)
---------------------------------------------------------------------------------------------

save the content of below highlighted in  red in a  file.
file name SQLServer_restart.ps1

Example Execution: .\SQLServer_restart_restart.ps1 ServerName


param([String]$ServerName)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null

#Create a new Managed computer object for the instance
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $ServerName

$sqlservr1 = $mc.Services['MSSQLSERVER']

Write-Host "Starting SQL Server"

$sqlservr1.Stop()
start-sleep -s 10
$sqlservr1.Start()

Write-Host "Started SQL Server"


Now we have to change it to suppose X list of servers in one shot, then how we will do
---------------------------------------------------------------------------------------------------
$srvlist = @'

Server1

Server2

Server3

'@

foreach ($srvnm in $srvlist)
  {

 # Connect to the instance using SMO

 $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srvnm

 #Change to Mixed Mode

 $s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

 # Make the changes

 $s.Alter()
 }

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How to move Tempdb database to another drive using T-SQL in AZURE so you will have low cost for SQL Server

Go to local D:\ drive and add  NT Service\MSSQLSERVER to security and give full access.

Then execute the output of  below command to move the file..

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''D:\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');

Then go to Azure portal and deassociate the disks, so that you will low cost for SQL Server for testing..

new-object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.

Issue : when you connect to SQl Server using  SMO in Powershell you get below error..
-------

The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough available worker threads

 In the event of excessive worker thread contention and long term failures to start a worker, after 15 minutes there will be a logged message in the error log.  It is message id 35217, and in your error log the message will read:

“The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough available worker threads. This may degrade AlwaysOn Availability Groups performance.  Use the "max worker threads" configuration option to increase number of allowable threads.”

Follow this link :- 

https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/07/15/monitoring-sql-server-2012-alwayson-availability-groups-worker-thread-consumption/

take a decision to increase CPU.

Thanks for reading.. 

Specific SQL statements is running from long however it should actually complete in few seconds

How to fine tune a Select / Update or Delete statements in SQL Server