CDC (change data capture) for Microsoft SQL server

sql-server-cdc.md

MSSQL CLI

github...

UI

https://dbeaver.io/

sqlcmd -S <ip> -d <dbname> -U <username> -P <password> -I

get schema list

SELECT * FROM sys.schemas

Get Data from Table

SELECT TOP 10 * from dbo.info;

Docker

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<pass>' -e 'MSSQL_PID=Express' -p 1433:1433 -d microsoft/mssql-server-linux:latest


docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<pass>' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<pass>' -e 'MSSQL_PID=Enterprise' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest

Connect

mssql-cli -S <ip> -U sa -P <password>

Show Databases

SELECT name FROM master.sys.databases 

Show tables with their schema

SELECT 
  SCHEMA_NAME(schema_id) As SchemaName ,
  name As TableName 
from sys.tables 
ORDER BY name

Create Table

CREATE TABLE tempdb.dbo.info (
    id  INT PRIMARY KEY,
    value VARCHAR(50) NOT NULL,
);

CREATE TABLE master.dbo.info (
    id  INT PRIMARY KEY,
    value VARCHAR(50) NOT NULL,
);

CREATE TABLE info (
    id  INT PRIMARY KEY,
    value VARCHAR(50) NOT NULL,
);

Insert

insert into master.dbo.info values(1, 'abc');
insert into testdb.dbo.info values(1, 'abc');

Select

select * from master.dbo.info;

Check if CDC Enabled

select * from sys.databases;

and check for is_cdc_enabled column;

Create Database

CREATE Database testdb

Enable CDC

sys.sp_cdc_enable_db

sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name   = 'info',
    @role_name     = ',
    @supports_net_changes = 1

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'info',  
@role_name     = NULL,  
@supports_net_changes = 1  

Get Changed Data

DECLARE @from_lsn binary (10), @to_lsn binary (10)

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_info')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_info(@from_lsn, @to_lsn, 'all')
ORDER BY __$seqval

start lsn = 0x0000002400000CC8003C end lsn = NULL

select * from cdc.fn_cdc_get_all_changes_dbo_info(0x0000002400000CC8003C, NULL, 'all')

Taken from Striim doc

https://docs.striim.com/en/sql-server-setup.html

USE mydb
EXEC sys.sp_cdc_enable_db
CREATE LOGIN loguser WITH PASSWORD = 'Umniah@123' 
CREATE USER loguser FOR LOGIN loguser
EXEC sp_addrolemember @rolename=db_owner, @membername=loguser

USE testdb;
GO

DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_info')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time'
FROM [cdc].[fn_cdc_get_all_changes_dbo_info]
  (@from_lsn, @to_lsn, N'all');

Note

For Start LSN Value, we might get invalid arguments error. That was probably caused by running CDC multiple time;

For Getting Last LSN value, sql server agent service should be started which can be done as:

/opt/mssql/bin/mssql-conf set sqlagent.enabled true 

Then restart the mssql-server service If inside docker, just restart docker

References:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/introduction-to-change-data-capture-cdc-in-sql-server-2008/