Error Creating Availability Group – Error: 41042 (Fixed)

While Building 50 Servers with 10 AG Groups in each Server we had to uninstall SQL and re-install again to address one of the server configuration issue. While re-installing we had challenges creating the availability group.

Final solution is to delete the AGNames from Failover Cluster Manager (Resources) and delete registry entry @ HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap.

Interestingly, when we create first Availability group, all 10 Registry entries were restored @ HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap. We had to delete appropriate entry from Registry and create availability group, repeat this process for all 10 AGs being created.

Referring to https://connectbeta.microsoft.com/SQLServer/feedback/details/2451275/uninstall-does-not-remove-ag-registry-hadragnametoldmap-and-cluster-entries, workaround/recommendation is to delete the AGs before uninstalling the SQL Server.

Please Vote to fix this bug at https://connectbeta.microsoft.com/SQLServer/feedback/details/2451275/uninstall-does-not-remove-ag-registry-hadragnametoldmap-and-cluster-entries!

Advertisements
Posted in High Availability, SQL, SQL Error, Uncategorized | Tagged , , , , | Leave a comment

Interesting observation with DATETIMEOFFSET data type in SQL

Calculating daily max values of a table will be different if you are using/storing the data in DATETIMEOFFSET format.

Basic difference is, while comparing the data between DATETIMEOFFSET and DATE (or DATETIME), DATETIMEOFFSET value will be converted to UTC and compared. Example: is greater than

We can use SWITCHOFFSET and convert DATETIMEOFFSET value to UTC and compare to address the problem.

Below is quick example where I had a challenge and the fix.


--

DECLARE @mydatetimeoffset DATETIMEOFFSET = '2017-11-05 21:30:00.00 -04:00'
DECLARE @mydatetimeLocal DATETIME = '2017-11-06 00:30:00.00'

SELECT @mydatetimeoffset as mydatetimeoffset, convert(date,@mydatetimeoffset) as mydatetimeoffset_date
SELECT @mydatetimelocal as mydatetimelocal, convert(date,@mydatetimelocal) as mydatetimelocal_date

if(@mydatetimeoffset > convert(date,@mydatetimelocal))
	SELECT '<' + convert(varchar(50),@mydatetimeoffset) + '> is greater than <' + convert(varchar(50),convert(date,@mydatetimelocal)) + '>'

	
	DECLARE @Days_DataCapture int = 7

-- problematic query
	SELECT EPlName , convert(date,EndDateTime) as DataDate ,max(EPValue) AS EPValue 
	from
	 Table ep WITH (NOLOCK)
	WHERE EndDateTime > convert(date,dateadd(dd,@Days_DataCapture*-1,getdate()))
	AND EndDateTime < convert(date,getdate())
	GROUP BY EPlName , convert(date,EndDateTime)
	ORDER BY  2 

-- Fix 
	DECLARE @Days_DataCapture int = 7
	SELECT EPlName , convert(date,SWITCHOFFSET (EndDateTime, '+00:00') ) as DataDate ,max(EPValue) AS EPValue 
	from
	 Table ep WITH (NOLOCK)
	WHERE EndDateTime > convert(date,dateadd(dd,@Days_DataCapture*-1,getdate()))
	AND EndDateTime < convert(date,getdate())
	GROUP BY EPName , convert(date,SWITCHOFFSET (EndDateTime, '+00:00') )
	ORDER BY  2 

--

FYI:
DatetimeOffset_1

Posted in Script, SQL, SQL Query, Uncategorized | Tagged , , , , | Leave a comment

Onenote weekly backup

I have created Onenote on sharepoint, shared it across virtual team. All members of the project were actively using this.

To my horror, I realize one of the section was accidentally deleted 😦

By default Onenote will be backed up every week on local machine (retains 2 backups) @C:\Users\%username%\AppData\Local\Microsoft\OneNote\16.0\Backup\.

Different team members had different day latest backup, restored the latest available 🙂

Yes, we lost a day worth of data but glad, was able to recover months worth of data…

Posted in knowledge, Productivity, Uncategorized | Tagged , , | Leave a comment

Conditional Formatting – Excel

Simple tip on using Conditional formatting.
This sample is to format a cell if it exceeds certain threshold (which is a different column on the same row).

1. Identify first row, first column where the formula needs to be created.
2. Home -> Conditional Formatting -> New Rule
3. Use a formula to determine which cells to format
4. Enter the Formula (=IF(D4>$B4,1,0)): against D4 if Column B has reference value. -> Select the Format -> OK
5. Home -> Conditional Formatting -> Manage Rules -> change the Applied to values from single cell to multiple values (Example: =$D$4:$ZE$58)

 

 ConditionalFormatting_Sample

There are multiple options with Conditional formatting which are self explanatory and easy to use. got explore…

Posted in Productivity, Uncategorized | Tagged , , | Leave a comment

Frequently used Azure powershell commands

Few of my frequently used powershell commands on Azure

#
# All Logs 
Login-AzureRmAccount
Get-AzureRmSubscription | sort SubscriptionName | Select SubscriptionName
Select-AzureRmSubscription -SubscriptionName ContosoSubscription
Get-AzureRmLog -StartTime 2017-07-26T01:00  | Where-Object {$_.Authorization.Scope -like "*databases/DBNAME"} | Sort-Object EventTimestamp


# Creating and deleting Elastic pools in Azure environment
 Login-AzureRmAccount
 $resourcegroupname = "SubscriptionName"
 $SubscriptionId = "SubscriptionID sf43rfe-dsf3reds-dfr3rd-dfedr433re"
 Select-AzureRmSubscription -SubscriptionId $SubscriptionId -ErrorAction Stop
 new-azurermsqlelasticpool -resourcegroupname $resourcegroupname -servername "myserverdr005"  -elasticpoolname "Server05-Pool04" -edition "standard" -dtu 200 -databasedtumin 0 -databasedtumax 100;
 Remove-AzureRmSqlElasticPool -resourcegroupname $resourcegroupname -servername "myserverdr005"  -elasticpoolname "Server05-Pool04" 




#

Ref:
https://docs.microsoft.com/en-us/azure/search/search-manage-powershell

Posted in Azure, Powershell, Uncategorized | Tagged , , , | Leave a comment

Most common challenges and solution with Availability group listener and multisubnetfailover

Industry started using SQL availability groups on most of their environments, DBA is coming across multiple issues related to AGListener connectivity.

This blog explains 2 major challenges and solutions in detail:
https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/

I recommend option-1 to enable client to use multisubnetfailover option to experience seamless fail over. If this is not feasible for any legacy reasons use option-2 to make sure AGListername is configured with single IP in DNS and change the TTL to shorter duration (Example: 1 minutes; Assuming a maximum 1 minute downtime is accepted)

To Summarize different categories:
1. MultiSubnetFailover is missing (Ref: https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/)
2. Client is not capable of using multiSubnetFailover, register Single IP in DNS – Ref: https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/
3. TransparentNetworkIPResolution resulting timeout after 500 ms for DNSlookup.
Resolution is to set this property to false (This is only after .NET Framework 4.6 or later) Ref –
https://blogs.msdn.microsoft.com/dataaccesstechnologies/2016/05/07/connection-timeout-issue-with-net-framework-4-6-1-transparentnetworkipresolution/

How to find .NET Framework version: https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/how-to-determine-which-versions-are-installed

Additional resources:
https://technet.microsoft.com/en-us/library/dd197575(v=ws.10).aspx

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/sql-server-multi-subnet-clustering-sql-server

Posted in Learn_Powershell, SQL, SQL Server, Uncategorized | Tagged , , , , , | Leave a comment

Azure Scripting errors after upgrading Powershell to V4 – Select-AzureRmProfile / Save-AzureRmProfile

After upgrading Powershell to V4, few of my scripts started failing. Few updated commandlets are missing backward compatibility, Select-AzureRmProfile/ Save-AzureRmProfile are couple of the broken commandlets.

You can replace them with Import-AzureRmContext, and Save-AzureRmContext.

Code block I use for reference:

#
         $myprofile = "C:\tmp\" + [Environment]::UserName + "_Profile.json"
          $SubscriptionId = “asdfsadfsd”

#        Login-AzureRmAccount
        if(test-path $myprofile)
        {
            try
            {
                Import-AzureRmContext -Path $myprofile
                Select-AzureRmSubscription -SubscriptionId $SubscriptionId -ErrorAction Stop
            }
            catch
            {
                Login-AzureRmAccount
                Save-AzureRmContext -Path $myprofile -Force
            }
        }
        else
        {
            Login-AzureRmAccount
             Save-AzureRmContext -Path $myprofile -Force
        }
      Select-AzureRmSubscription -SubscriptionId $SubscriptionId -ErrorAction Stop
#

Ref:
Ref: Breaking changes: https://github.com/Azure/azure-powershell/blob/preview/documentation/release-notes/migration-guide.4.0.0.md

Additional Documentation: https://docs.microsoft.com/en-us/powershell/module/azurerm.profile/get-azurermcontext?view=azurermps-3.8.0

https://arcanecode.com/2017/04/19/what-happened-to-save-azurermprofile/#comment-128901

Still working on one more problem with Get-AzureRmSqlElasticPoolDatabase after Powershell upgrade to V4….

Error: Save-AzureRmContext : The term ‘Save-AzureRmContext’ is not recognized as the name of a cmdlet, function, script file,
or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and
try again.

Select-AzureRmProfile : The term ‘Select-AzureRmProfile’ is not recognized as the name of a cmdlet, function, script
file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct
and try again.

Posted in Azure, Powershell, SQL Server, Uncategorized | Tagged , , , , , | Leave a comment