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.
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'))
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
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)
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
SET @UpateOneMore = 0
SET @UpateOneMore = @UpateOneMore - 1
WAITFOR DELAY '00:00:03'
-- 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'
EXEC msdb.dbo.sp_add_alert @name=N'AG Role Change',
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!
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,  AS Sunday,  AS Monday,  AS Tuesday,  AS Wednesday,  AS Thursday,  as Friday,  as Saturday
( 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
AND bs.[type] = 'L'
AND bs.is_copy_only = 0
and database_name = @DatabaseName
GROUP BY bs.database_name
,datepart(HOUR,bs.backup_start_date) ) P
FOR BackupWeekday IN
( , , , ,  , , )
) AS pvt
ORDER BY pvt.BackupHour;
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
# 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
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!