github...
sqlcmd -S <ip> -d <dbname> -U <username> -P <password> -I
SELECT * FROM sys.schemas
SELECT TOP 10 * from dbo.info;
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
mssql-cli -S <ip> -U sa -P <password>
SELECT name FROM master.sys.databases
SELECT
SCHEMA_NAME(schema_id) As SchemaName ,
name As TableName
from sys.tables
ORDER BY name
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 into master.dbo.info values(1, 'abc');
insert into testdb.dbo.info values(1, 'abc');
select * from master.dbo.info;
select * from sys.databases;
and check for is_cdc_enabled column;
CREATE Database testdb
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
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')
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');
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