Powershell Workflow for Runbooks, Inline Script

Powershell workflow is the best way to program parallel processing using Powershell and/or in Azure run books.

Though the syntax is similar, there are few differences you need to be aware of.

Start with below link when you create your first workflow script:
Ref: https://docs.microsoft.com/en-us/azure/automation/automation-powershell-workflow

Learned about Dot Sourcing as well today. Simple example is listed below…

https://mcpmag.com/articles/2017/02/02/exploring-dot-sourcing-in-powershell.aspx

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

DBATools.IO – Powershell for SQL DBAs

DBATools.IO was one of the best things I started using in 2017 and I wanted to master this in 2018!

https://dbatools.io/functions/ includes the list of all commands in dbatools module!

Start using it if you have not started already.

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

SQL Alwayson Preferred Node workaround, Fallback script

We have Always-On configured between Node-1, Node-2 with Auto Failover enabled. Unfortunately one minor functionality was partially impacted when AG is primary on Node-2.

Business is very particular about not changing it to Manual Failover mode. End up creating a SQL job, scheduled it to run every 10 minutes on Node-1.
This script moves AG from Node-2 to Node-1 if Node-2 is healthy and in sync.


-- 

-- This script can be executed on AG Server. It validates if the server is secondary, connected and healty; If healthy it makes it primary 
-- This is the Failback job to be configured on SQL AG which needs to be primary

-- NOTE: If, Sync mode is Async, Failover parameter needs to be updated to ' FORCE_FAILOVER_ALLOW_DATA_LOSS' ; Data movement is suspended on secondary after the execution.



DECLARE @AGName nvarchar(80) 
DECLARE @FailoverCommand nvarchar(280) 

SET @AGName = 'AGName'


if EXISTS (SELECT 1
FROM sys.availability_replicas ar 
inner join sys.dm_hadr_availability_replica_states ags on ar.replica_id=ags.replica_id
JOIN sys.availability_groups AS ag 	ON ag.group_id = ar.group_id
WHERE name = @AGName
AND is_local = 1 -- LocalReplica
AND ROLE = 2 -- Secondary 
AND connected_state = 1 -- CONNECTED
AND synchronization_health = 2 -- HEALTHY
)
BEGIN
	SET @FailoverCommand = 'ALTER AVAILABILITY GROUP ' + @AGName + ' FAILOVER' -- ' FORCE_FAILOVER_ALLOW_DATA_LOSS' 
	EXEC (@FailoverCommand)
	PRINT 'AG '+ @AGName +' has been failed over to ' + @@SERVERNAME 
END
ELSE 
BEGIN

	if EXISTS (SELECT 1
	FROM sys.availability_replicas               ar 
	inner join sys.dm_hadr_availability_replica_states ags on ar.replica_id=ags.replica_id
	JOIN sys.availability_groups AS ag 	ON ag.group_id = ar.group_id
	WHERE name = @AGName
	AND is_local = 1 -- LocalReplica
	AND ROLE = 2 -- Secondary 
	)
	BEGIN	
		PRINT 'Failover of AG '+ @AGName +' not attempted as AG is not connected or not Healthy on ' + @@SERVERNAME 
	END
	ELSE 
	BEGIN
		PRINT 'AG '+ @AGName +' is Primary, Failover was not attempted on ' + @@SERVERNAME 
	END 
END

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

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

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