catalog

Most frequently used articles

AZURE – SQL – Frequently User SQL Commands: https://vijredblog.wordpress.com/2016/10/27/sql-commands-in-azure/

Azure – comonly used Powershell Commands:
Frequently used Azure SQL Powershell Commands: https://vijredblog.wordpress.com/2017/07/27/frequently-used-azure-powershell-commands/

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

How to execute SQL jobs only on Primary server when Always-On is used as HA solution – Can you guess second solution?

I come across 2 strategies how this can be achieved, both are reliable but with minor advantage and disadvantage on both scenarios.

  • Add extra step on each SQLAgent job to verify if the server is primary and execute next steps only if the server is primary. or exit the job!
    • This is popular option and widely used
    • Advantage: Very stable process, there is no dependency on job names and each job is independent.
    • Disadvantages: If a SQL Agent job is designed to run in a loop for days there is no easy way to stop the job
-- A . Simple if there is single AG on the Server
if (select primary_replica from sys.dm_hadr_availability_group_states)=(select SERVERPROPERTY ('ServerName'))
print 'true'
else RAISERROR('This is not the server you are looking for…',16,1)

-- B. This scenario works if there are more than 1 AG on given Server, Verify if DB is primary
if ( select is_local from sys.dm_hadr_availability_replica_states where is_local = 1 and role = 1 and replica_id IN ( select replica_id from sys.dm_hadr_database_replica_states where database_id =(
select database_id from sys.databases where name like 'DatabaseName'))) = 1
print 'true'
else RAISERROR('This is not the server you are looking for…',16,1)
  • Second option is to create additional SQL Agent job that Enables/ Disables required Jobs based on Always-On status.
    • This is less popular option but works good in specific scenarios
-- Create a SQLAgent Job which needs to be enabled/disabled based on AG status. Following is sample
-- Update GivenJobNamePattern in below sample works 
DECLARE @TSQL NVARCHAR(MAX) 
DECLARE @UpateOneMore INT = 5
WHILE(@UpateOneMore > 1)
BEGIN
	SELECT TOP 1 @TSQL =  N'exec msdb..sp_update_job @job_name = ''' + j.name + ''', @enabled = ' + 
	 CASE WHEN (ars.role = 1 or ars.role is null )   and j.enabled =0 THEN '1'  -- Enable
		 WHEN  ars.role <> 1   and j.enabled =1 THEN '0'   -- Disable 
	 END  + ' ; '
	FROM msdb.dbo.sysjobs j with(nolock)
	INNER JOIN msdb.dbo.sysjobsteps s  with(nolock)
		ON j.job_id = s.job_id
	INNER JOIN sys.databases dbs WITH (NOLOCK) on dbs.name = s.database_name
	JOIN sys.dm_hadr_availability_replica_states ars WITH (NOLOCK) ON ars.replica_id = dbs.replica_id
	WHERE J.name like 'GivenJobNamePattern%' 
	if (@@ROWCOUNT > 0)
		EXEC sys.sp_executesql @TSQL
	ELSE 
		SET @UpateOneMore = 0

	SET @UpateOneMore = @UpateOneMore - 1
	WAITFOR DELAY '00:00:03'
END 


-- Create Alert, update job_id to start the job you just created
-- This alert will trigger when AG fails-over, start the job with job_id N'abc06cbf-0a32-458d-aee6-0f2956249e07'
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'AG Role Change', 
		@message_id=1480, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Application]', 
		@job_id=N'abc06cbf-0a32-458d-aee6-0f2956249e07'
GO

Ref: https://github.com/vijred/MSSQL/blob/master/AlwaysOn/AlwaysOn-SQLAgent-Usefulscripts.sql

Posted in High Availability, Productivity, SQL, SQL Query, SQL Server, SSMS, Uncategorized | Tagged , , | Leave a comment

How to Safeguard executables you download/install from internet

Have you ever installed an executable that is downloaded from internet? Most of the time executables will be available from third party sites, there is risk of the executable being corrupted or modified!

Best way to handle this problem is to validate the hash value of executable before installing on your local machine! you can install the tool from Microsoft to generate hash value of a file! Ref: https://docs.microsoft.com/en-gb/troubleshoot/windows-server/windows-security/fciv-availability-and-description

Example: exec hash value listed at https://windirstat.net/download.html matches with hash value of downloaded file!

#filehash #fciv #File Checksum Integrity Verifier utility

Posted in Productivity, Windows Server | Tagged , , , , | Leave a comment

Azure SQL Database Copy / Add replication takes forever

Azure SQL Database Copy / Add replication hangs.

If you copy or create replication across SQL Databases in Azure (SAAS), you may encounter replication being hung!

If replication is hung, we will not be able to perform a few operations like changing capacity of elastic pool, etc, it fails with below error:

The elastic pool 'PoolName' cannot be updated because one of its databases is performing a copy or geo-replication failover operation.

Solution is to delete the copy operation, by running DROP DATABASE [DBName] on the new server; Now, you can initiate the copy one more time or complete Elastic pool operation.

Ref: https://docs.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-using-transact-sql

Posted in Azure, Productivity, SQL, SQL Error, SQL Server | Tagged , , , , | Leave a comment

Practical use of PIVOT in MSSQL

I finally had a good use case to makeuse of PIVOT functionality!

Problem to address: Database integrity check on a very large database (12+ TB) with high transactions has been failing due to Fragmentation limit on sparse file (Snapshot file during DBCC Integrity Check). I wanted to check average log file size per hour per weekday which is reasonably relative to the number of transactions.

Following TSQL can be used to find average transaction log file size on a given database in readable format!

-- Find Average Log file size per Weekday and hour

DECLARE @DatabaseName NVARCHAR(80) = N'MyDatabase'
 SELECT BackupHour, [1] AS Sunday, [2] AS Monday, [3] AS Tuesday, [4] AS Wednesday, [5] AS Thursday, [6] as Friday, [7] as Saturday 
 FROM 
 ( SELECT  bs.database_name
         ,datepart(weekday,bs.backup_start_date) As BackupWeekDay
         ,datepart(HOUR,bs.backup_start_date) AS BackupHour
         ,CAST( AVG(bs.backup_size) / 1048576 / 1024 AS DECIMAL(10, 2) )  AS [Average_BackupSize_GB]
 FROM  msdb.dbo.backupmediafamily bmf
 JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
 JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
 WHERE   1=1 
 AND bs.[type] = 'L'
 AND bs.is_copy_only = 0
 and database_name = @DatabaseName
 GROUP BY bs.database_name
         ,datepart(weekday,bs.backup_start_date) 
         ,datepart(HOUR,bs.backup_start_date)  ) P 
 PIVOT  
 (  
 SUM (Average_BackupSize_GB)  
 FOR BackupWeekday IN  
 ( [1], [2], [3], [4], [5] , [6], [7])  
 ) AS pvt  
 ORDER BY pvt.BackupHour;  

Sample Output:

Code Ref: https://github.com/vijred/MSSQL/blob/master/Size/Average_LogSize_GivenDB_PivotView_Day_Hour.sql

Posted in Productivity, SQL, SQL Query, SQL Server | Tagged , , , | Leave a comment

Explore technology – 2020

  • Rest API : Level-100

8/20/2020:

Rest API: https://4bes.nl/2020/08/23/calling-a-rest-api-from-powershell/

Posted in Uncategorized | Leave a comment

How to Capture a browser trace for troubleshooting

I learned about capturing browser trace to troubleshoot. This is interesting;

Refer to the documentation at https://docs.microsoft.com/en-us/azure/azure-portal/capture-browser-trace to capturing network trace on a browser (Chrome / Edge / Safari / Firefox).

Once .HAR file is captured, this can be analyzed using multiple tools (Example: https://toolbox.googleapps.com/apps/har_analyzer/)

Overall, Developer Tools on any browser has abundant information to troubleshoot or trace a problem while debugging!

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

Handy Powershell commands for Azure SQL Database Failover groups

What is Failover Group for Azure SQL Database:

Failover group for Azure SQL Database is similar to a listener in On-Premise environment with Auto-Failover and Manual Failover capacity. to be more precise, this avoids additional effort to update connection string during failover event and is fast.

# Create a new Failover Group 
$failoverGroup = New-AzureRMSqlDatabaseFailoverGroup -ResourceGroupName RGName -ServerName DBServerName -PartnerResourceGroupName DRRGName -PartnerServerName DRDBServerName -FailoverGroupName DBFailoverGroupName -FailoverPolicy Manual

# How to add all databases from a given elastic pool to Failover Group
$databases = Get-AzureRmSqlElasticPoolDatabase -ResourceGroupName RGName -ServerName DBServerName ElasticPoolName ElasticPoolName
$failoverGroup = $failoverGroup | Add-AzureRmSqlDatabaseToFailoverGroup -Database $databases


# How to manually Failover a database
Set-AzureRmSqlDatabaseSecondary -ResourceGroupName DRRGName -ServerName DRDBServerName -DatabaseName DBName -Failover 
 PartnerResourceGroupName RGName

# Force failover with data loss
Get-AzureRmSqlDatabaseFailoverGroup -ResourceGroupName DRRGName -ServerName DRServerName -FailoverGroupName DBFailoverGroupName | Switch-AzureRmSqlDatabaseFailoverGroup -AllowDataLoss

# AZ module command to failover 
Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName DRRGName -ServerName DRServerName -FailoverGroupName DBFailoverGroupName -AllowDataLoss
 
#az module command to get FailoverGroup Name 
Get-AzSqlDatabaseFailoverGroup -ResourceGroupName RGName -ServerName DBServerName -FailoverGroupName DBFailoverGroupName

# Few more commands
# az module command to create new Availability Group
New-AzSqlDatabaseFailoverGroup
#

Note:

These commands are only for SAAS solution, not for managed instances.

Failover groups are created on SQL Server, include partner server and add databases to Failover Group.

Adding all databases from same elastic pool to same Failovergroup is not mandate but I do it for simplicity!

code block can be found at: https://github.com/vijred/Powershell/blob/master/Azure/SQLDatabaseFailoverGroups/SQLDatabaseFailoverGroups-Handycommands.ps1

Ref: https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-configure?tabs=azure-portal

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

Azure SQL Databases – Point in time recovery capability

long-term backup retention for Standard databases – Default value has been changed from 5 weeks to 1 week starting July 2019. This is for the databases created after July-2019.

What does it mean: If you create a new database after July 2019, you have the ability to point in time restore a database only for last 1 week.

This value can be changed back to 5 weeks with no additional cost, this can add additional value to business!

Ref: https://azure.microsoft.com/en-us/updates/default-backup-retention-period-for-dtu-based-azure-sql-databases-is-changing-soon/

Azure Portal, PowerShell or REST API

Posted in Uncategorized | Tagged , | Leave a comment

SQL Server – What to collect for troubleshooting root cause of a problem

when we have unexpected behavior from SQL Server impacting SQL availability, it will be crucial to understand if we want to restore the service ASAP or troubleshoot to find the root cause.

When we switch from troubleshooting to service restoration it is critical to collect information that can be used for problem diagnosis. Following are the 2 potential options for that!

  1. When the problem that took place recently, not active we can collect logs before logs are overwritten.
    1. Diagnostic tool log collection
      1. Typically Microsoft provides the tool (EXE):
    2. https://support.microsoft.com/en-in/help/926079/frequently-asked-questions-about-the-microsoft-support-diagnostic-tool
    3. Manually collect SQL Error Logs, Cluster Logs , System/Application event logs
    4. Custom script for data collection: https://raw.githubusercontent.com/vijred/Powershell/master/SQLServerRelated/Collect_Cluster_System_SQL_Logs.ps1
  2. PSSDiag: When the problem is currently in place (Performance issues are best fit for PSSDiag collection)
    1. Microsoft recommends custom PSSDiag to collect specific data. However you can create your own PSSDiag and collect information or you can create one for generic purpose data collection and use it.
    2. Ref: https://support.microsoft.com/en-in/help/830232/pssdiag-data-collection-utility

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

Find actively running SQL statements using Querystore-QueryID

TSQL to Find actively running SQL statements using QueryID

-- Find actively running SQL Statements based on QueryID (Querystrore - QueryID)

SELECT s.host_name, s.last_request_start_time ,s.last_request_end_time, r.start_time, * from sys.dm_exec_requests r
join sys.dm_exec_sessions s on s.session_id  = r.session_id
join [sys].[query_store_query] qsq on qsq.query_hash = r.query_hash
WHERE r.query_hash IS NOT  NULL
AND query_id = 78139
--
Posted in Performance, Querystore, Querystore, SQL, SQL Query, SQL Server | Tagged , | Leave a comment