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

Integrate Full data copy and CDC extract in SQL Server

This post is to provide insight into full table copy and CDC extraction integration.

Problem statement – Working on an automation effort to use CDC and copy data from source to destination. I will be dealing with 1000s of databases and 10s of tables in each database, new databases will be added daily and I can not stop any transactions. plan is to copy full table for the first time and subsequent changes to be captured using CDC Data. Biggest challenge is to find first lsn value to get first transaction.
Traditional solution is to Stop all transactions to the database for a period to find accurate lsn but I have no option to stop transaction.
After research (Thanks to Charlieface and allmhuran – https://stackoverflow.com/questions/73057627/how-to-fully-automate-cdc-in-sql-server), best solution is accept there will be either data loss or duplicates and handle accordingly.

Conceptually, capture datetime (checkpointDatetime) just before initiating full copy. Using checkpointDatetime find most relevant lsn during first extract!
If missing data is acceptable scenario to handle use smallest greater than or largest less than or equal.
If Duplicate data is acceptable scenario to handle, use smallest greater than or largest less than.

Ref –
How to convert datetime to lsn: https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-cdc-map-time-to-lsn-transact-sql?view=sql-server-ver16
Validation: https://github.com/vijred/MSSQL/tree/master/CDC/CDC_IntegrationTest

Posted in Uncategorized | Leave a comment

Automate jagent/Goldengate monitor agent in windows using powershell – create_ogg_agent_instance

Installing goldengate monitoring agent is well documented @ https://docs.oracle.com/goldengate/m12212/gg-monitor/GGAIN/creating-and-configuring-instance1.htm#GGAIN129.

However following are the changelings that needs attention.

  • How to avoid UI while installing Goldengate Monitoring Agent –

We have an option to use Silent install using response file and this is documented @

https://docs.oracle.com/cd/E40329_01/install.1112/e35859/silent_install.htm#ASINS334

Example: java -jar -Xmx1024m C:\oracle\my_jagent\fmw_12.2.1.2.0_ogg\fmw_12.2.1.2.0_ogg.jar -silent -responseFile c:\oracle\myresponsefile.rsp

  • Installing Monitoring Agent Instance

Unfortunately, this has no option to use response file to completely automate it. One of the workaround is to use following workaround. Start install process in background and pass parameters

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") 
 Start-Process -FilePath C:\oracle\gg12c\ogg_agent\createMonitorAgentInstance.bat

# Wait the application start for 3 seconds 
Start-Sleep -m 3000
 
# Send keys
[System.Windows.Forms.SendKeys]::SendWait("C:\Goldengate")
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
Start-Sleep -m 3000


[System.Windows.Forms.SendKeys]::SendWait("C:\oracle\gg12c\agent_inst2")
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
Start-Sleep -m 3000

[System.Windows.Forms.SendKeys]::SendWait("startMonitorAgentInstance_202110081324.bat")
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
Start-Sleep -m 5000
 
 
# Wait menu opening for 0.5 sec 
Start-Sleep -m 500

Ref – https://github.com/vijred/MSSQL/tree/master/CDC/CDC_IntegrationTest

Posted in Uncategorized | 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