Creating the AdventureWorksLT2012CDC Sample Database
The bundled sample 12 - MSSQL Change Data Capture SSIS Solution reads its source data from a SQL Server database named AdventureWorksLT2012CDC, with change data capture turned on. This page shows how to create that database from the Microsoft sample backup AdventureWorksLT2012.bak.
For general information about change data capture sources in BimlFlex, see Using SQL Server CDC as a Source with SSIS. For the list of bundled samples, see Sample Metadata.
The samples 66 - Databricks DeltaVault Pushdown Solution, 67 - Databricks DeltaVault Solution and 68 - Databricks DeltaVault SQL Solution also name the database AdventureWorksLT2012CDC. In each of them the connection that names the database has a linked service for an Azure SQL Database, and this page does not cover them.
What the Sample Needs
- The source connection
AWLT_SRCof sample 12 names the database AdventureWorksLT2012CDC. Its connection string points at the local server (Data Source=.). - The sample reads ten tables of the schema
SalesLT: Address, Customer, CustomerAddress, Product, ProductCategory, ProductDescription, ProductModel, ProductModelProductDescription, SalesOrderDetail and SalesOrderHeader. - Eight of the ten objects have the Object Type
CDC All. SalesOrderDetail and SalesOrderHeader have the Object TypeCDC Last. - For an object of type
CDC Allthe load that BimlFlex builds reads the functioncdc.fn_cdc_get_all_changes_SalesLT_<table>. ForCDC Lastit readscdc.fn_cdc_get_net_changes_SalesLT_<table>. - So each of the ten tables needs a capture instance with the default name, the schema, an underscore and the table name, such as
SalesLT_Address. The twoCDC Lasttables need net changes turned on.
Create the Database
-
Get the Microsoft sample backup AdventureWorksLT2012.bak. Microsoft publishes it with the SQL Server samples: AdventureWorksLT2012.bak
-
Restore it under the name AdventureWorksLT2012CDC. Read the two logical file names first, then restore with a new file for each. The folders in the script are examples. Use the folder that holds the backup and the folder for the database files on your server.
RESTORE FILELISTONLY FROM DISK = N'C:\Backup\AdventureWorksLT2012.bak';RESTORE DATABASE [AdventureWorksLT2012CDC]FROM DISK = N'C:\Backup\AdventureWorksLT2012.bak'WITH MOVE N'<logical data file name>' TO N'C:\Data\AdventureWorksLT2012CDC.mdf',MOVE N'<logical log file name>' TO N'C:\Data\AdventureWorksLT2012CDC_log.ldf'; -
Give the restored database an owner the server knows. A database restored from another server can keep an owner the server cannot resolve, and change data capture is then refused:
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT2012CDC] TO sa; -
Turn change data capture on for the database:
USE [AdventureWorksLT2012CDC];EXEC sys.sp_cdc_enable_db; -
Turn it on for each of the ten tables, with net changes, and leave the capture instance name at its default. Repeat this call with each of the ten table names:
EXEC sys.sp_cdc_enable_table@source_schema = N'SalesLT',@source_name = N'Address',@role_name = NULL,@supports_net_changes = 1;With
@role_name = NULLno role gates access to the change data. If you give a role name in place of NULL, the login that the connectionAWLT_SRCuses must be a member of that role or of db_owner.Net changes need a primary key or a unique index on the table. Each of the ten tables in the Microsoft backup has a primary key.
-
Make sure SQL Server Agent runs. SQL Server Agent must run for changes to be captured, since the capture job that fills the change tables is a SQL Server Agent job.
-
Point the connection
AWLT_SRCof the sample at the server that holds the database.