Creation of AlwaysOn group failing as already another AlwaysOn Group exists with the same name.

When you uninstall SQL Server without manually dropping the existing AAG group and trying to create an AAG group with the same name after reinstalling the SQL Server. or if the existing AlwaysOn group was not dropped correctly and if we try to create a new AAG group with the same name. We sometimes receive the below error: 

 
 

"Create failed for Availability Group 'DBAG'. 

 
 

The availability group 'DBAG' already exists. This error could be caused by a previous failed CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation. If the availability group name you specified is correct, try dropping the availability group and then retry CREATE AVAILABILITY GROUP operation. 

Failed to create availability group 'DBAG'." 

 
 

One of the reasons this error pops up is there will be a registry entry for the AAG name.  

 
 

To check if there is an entry in the registry for the AAG group with the same name already we can check in two ways: 

 
 

1. Query the "sys.dm_hadr_name_id_map". 

2. Check the registry location "HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap". 

3.  
    

When you query "sys.dm_hadr_name_id_map" and if you see an entry with the same AAG group name in my example if there is an entry with the name "DBAG" then we need to drop it before creating a new AG group with the same name. 

 
 

Select * from "sys.dm_hadr_name_id_map" 

Go 

 
 

Use the below command to drop the AAG group: 

 
 

DROP AVAILABILITY GROUP DBAG; 

 
 

After dropping, we can verify the registry location "HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap" to make sure there is no entry with the same AAG name.  

 
 

Then if we try to create the AG with the name "DBAG" it will allow us. 

 
 

Please share if there is any easy way to fix this error in the comments section. 

 
 

 
 

Thanks VV!!

Script to verify Lock Pages in Memory and Instant File Initialization.

  

There are several situations where we need to verify if Lock Pages in Memory and Instant File Initialization are enabled or not. Like while setting up new servers, sometimes during performance tuning and sometimes company policy verification and so on.

The general method is to go to run and dig deep in secpol.msc for Instant File Initialization and gpedit.msc for verifying Lock Pages in Memory.

Below SQL queries help in verifying Lock Pages in Memory and Instant File Initialization are enabled or not directly from SSMS:

 

-- Query to check Lock Pages In Memory

-- If sql_memory_model_desc column output is LOCK_PAGES then it means Lock Pages in Memory is enabled.

Select sqlserver_start_time, sql_memory_model_desc from sys.dm_os_sys_info

Go

-- Query to check Instant File Initialization

-- If instant_file_initialization_enabled column output is Y, it means  Instant File Initialization is enabled for that particular service.

 Select servicename,instant_file_initialization_enabled from sys.dm_server_services

Sample Output:

 

 

Note: These DMVs works from SQL Server 2012 SP4 & above versions.

 

Let me know in the comments section below if any other easier way to get these details, it will help me and readers.

 

Thanks VV!!

#LockPagesInMemory, #InstantFileInitialization #MSSQL #sql #sqlserver #script #sqlblog

PowerShell script that displays all SQL Server folder locations from the registry.

Recently I was working on an issue where the SQL Server agent was missing from the SQL Server configuration manager. After going through sqlauthority blog realized there was a wrong entry in the registry. Once I updated the SQL Server agent exe location in the registry with the correct location then I was able to see the SQL Server agent back in the configuration manager.

 

So after this task, I thought of writing a PowerShell script that can display all the SQL Server folder locations from the registry instead of searching manually. This script helps in displaying all the SQL Server registry entries of all the instances installed in the server where this script is being run. Also, it will store the output of the script by default in ‘C:\’ drive with the name “Script_Registry_Entries_1.1_OutPut.txt”.

 

NOTE: Please remember incorrect updating of the registry will cause permanent damage to the server and data. So, please test all the scripts in lower environments first.

 

This single script named Script_MSSQL_Registry_Entries_1.1.PS1 will query the registry and display below details of all the SQL Server instances installed in the current server.

 

1. Startup Parameters

2. AgentErrorLog

3. SQLBinaryRoot  

4. SQLDataRoot 

5. ExternalLibraries

6. SQLServerPath

7. SQLServerProgramDirectory 

8. SQLServerErrorDump 

9. SQLServerBackupDirectory

10.SQLServerBrowser 

11.SQLServerCEIPEXE

 

Please be careful while working with the registry. As any incorrect modification of the registry will cause data loss. 

 

IMPORTANT NOTE:

 

 ** This script will work only in Powershell ISE or Powershell 62 bit.

 ** It should be run as an ADMINISTRATOR.

 

I invite you to follow me @my blog: https://sqlserverstore.blogspot.com/ and @ Twitter: @vinayprasadv for more useful articles.

You can download the script from below:

Sample output screenshots:

I am working on updating this script further and will re-share details once I complete it.

This script will be very helpful when we need to verify the SQL Server root folder locations in the registry while working on tasks like database migrations, database upgrades, SQL server registry issues, SQL server services missing, and so on.

 

Thanks VV!!

#powershell #registry #mssql #sqlserver #sql #script #automation

When no one have sysadmin permission in the sql instance including you, what to do?

Recently I came across a situation where after a server migration when I logged into the SQL instance I cannot see the SQL Server agent. Verified the registry location of the SQL server agent and it has the correct location. Verified folder permissions and the SQL service account have all the required permissions on all the required folders. The server has 2 SQL instances and only one instance is missing the SQL server agent so impulsively I have restarted the SQL services and obviously, it didn’t help.

After verifying realized my account itself doesn’t have sysadmin permission on the instance. Then noticed none of the accounts in the instance have sysadmin permission and even ‘sa’ account details are lost.

So the situation is no login has sysadmin permission in the SQL instance below are the steps I followed to overcome this issue.

** Please make sure You must be a member server’s local administrator's group before performing these steps.

1.    Open SQL server configuration manager and open the properties of SQL server service of the instance you have this issue. In my case, the instance name is SQL2017.

2.    Now click on startup parameters.

3.    Now type –m in Add startup parameters section and click on Add startup parameters.

4.    Now restart the SQL services of the instance. Now the SQL instance will be started in single-user mode. Which allows any one member of the server’s local administrator to connect to the SQL instance as a sysadmin.

5.    Open SSMS and login to the instance with your windows login.

6.    Now grant sysadmin permission to your account.

7.    Go back to SQL server configuration manager and remove the –m startup parameter. By selecting the -m parameter and click on Remove.

8.    Restart the SQL services.

9.    Now you can log in back to SQL instance using ssms and your account will have sysadmin permission.

 The same can be achieved via SQLCMD as well.

 Please share how you fixed this issue in your environment in the comments section below.

 

Thanks VV!!

 

#backtobasics #sqlserver #mssqlserver #mssql #singleusermode #administrator