CDC and BIML Preparation of Database

CdcAndBimlPreparation

--1. Create database
CREATE DATABASE CDCTest;
GO

--2. Create CDC table using AdventureWorksDW2012 database
USE  CDCTest 
GO

SELECT * 
INTO DimDepartmentGroup_CDC
FROM AdventureWorksDW2012..DimDepartmentGroup
WHERE DepartmentGroupKey < 5


--3. Enable cdc on this database
USE  CDCTest 
GO

EXEC sys.sp_cdc_enable_db
GO



--4. Add a primary key to the DimDepartmentGroup_CDC table so we can enable support for net changes
USE  CDCTest 
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'PK_DimDepartmentGroup_CDC')
  ALTER TABLE  dbo . DimDepartmentGroup_CDC  ADD CONSTRAINT  PK_DimDepartmentGroup_CDC  PRIMARY KEY CLUSTERED 
(
     DepartmentGroupKey  ASC
)
GO

--5. Enable cdc on the DimDepartmentGroup_CDC table
USE  CDCTest 
GO

EXEC sys.sp_cdc_enable_table 
@source_schema = N'dbo',
@source_name = N'DimDepartmentGroup_CDC',
@role_name = N'cdc_admin',
@supports_net_changes = 1

GO

--6. Create a destination table in the 
USE  CDCTest 
GO

SELECT TOP 0 * 
INTO DimDepartmentGroup_Destination
FROM DimDepartmentGroup_CDC

--7. Create table to store the cdc state
USE  CDCTest 
GO

CREATE TABLE dbo.cdc_states(
	name nvarchar(256) NOT NULL,
	state nvarchar(256) NOT NULL
)
GO