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/
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
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.
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
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
I come across 2 strategies how this can be achieved, both are reliable but with minor advantage and disadvantage on both scenarios.
-- 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)
-- 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
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
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.
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
8/20/2020:
Rest API: https://4bes.nl/2020/08/23/calling-a-rest-api-from-powershell/
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!
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
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!
Blog for reference - Vijred 