Availability Group Fail Over date/time – TSQL

I have been using this quick script to verify AG Status Change details from error log.

--


DECLARE @EndDate DATETIME = GETDATE() 
DECLARE @StartDate DATETIME = dateadd(dd,-31,GETDATE() )

exec xp_readerrorlog 0						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 1						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 2						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 3						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 4						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 5						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

--
Advertisements
Posted in High Availability, SQL, SQL Query, SQL Server, Uncategorized | Tagged , , , , , | Leave a comment

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!

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

#


# Login into Azure shell 
Login-AzureRmAccount
Select-AzureRmSubscription -SubscriptionName "SubscriptionName"


# 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" 



# Update Database information (Size Update)
 Login-AzureRmAccount # This statement can be avoided in CloudShell (https://portal.azure.com -> CloudShell)
$resourcegroupname = "resource-Group-Name"  
$SubscriptionId = "12121212-sdfdf-dfdfd-dfedffd-kdfck3kjnfhk"   
$DBNAme = "DBName"  
$ServerName = "SQLServerName"  
$dbsizeBytes = 268435456000 # 250 GB     

Select-AzureRmSubscription -SubscriptionId $SubscriptionId -ErrorAction Stop  
Set-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -DatabaseName $DBNAme -ServerName $ServerName -MaxSizeBytes $dbsizeBytes
#



#   
# Update Elastic pool (Size Update)
Login-AzureRmAccount
 
$resourcegroupname = "Resource-group-name"
$SubscriptionId = "9374836468-ASDFJDF-DFK4-ASEFJJDF-JF4fedCDFD" 
$ElasticPoolName = "PoolX"
# $StorageMB = 307200  # 300 GB
$StorageMB = 204800  # 200 GB
 
Select-AzureRmSubscription -SubscriptionId $SubscriptionId -ErrorAction Stop
Set-AzureRmSqlElasticPool -ResourceGroupName $resourcegroupname -ServerName stmdbn2ac001 -ElasticPoolName $Elasticpoolname -StorageMB $StorageMB
# Note: Servername is included in the Update command, not as parameter 
#




## Restoring to point in time from Deleted Azure SQL Database : https://vijredblog.wordpress.com/2018/05/23/how-to-point-in-time-restore-azure-sql-database-after-deleting-the-database/ 


# List of all Alerts configured in given Resource Group
Get-AzureRmAlertRule -ResourceGroup "stratustime-prod-resource"  | Where-Object {$_.Name -like "Filter*"}  

# List of Disabled Alerts configured in given Resource Group
Get-AzureRmAlertRule -ResourceGroup "resourcegroupname"  | Where-Object {$_.Name -like "Filter*"}  

#Get-AzureRmAlertRule -ResourceGroup "resourcegroupname"  -DetailedOutput | Where-Object {$_.Name -like "FilterCriteria*" -and $_.Properties.Status -ne "Enabled"}  | select Name




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

Posted in Azure, Powershell, Uncategorized | Tagged , , , | 1 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