SQL Server CDC – Change Data Capture
Configuring EIQ RTIS Through the EIQ Update Configuration Tool
The first step is to make sure SQL Server Agent is running. SQL Server Agent is the component of SQL Server that handles scheduling jobs and other automated tasks. Without this being activated, change data capture does not work.
Next, open SQL Server and select ‘POLICE’ under Databases.
Right-click and select ‘New Query…’.
Here, execute a query to activate Change Data Capture in SQL Server.
Run the following query:
Use POLICE
GO
exec sys.sp_cdc_enable_db
GO
exec sp_cdc_enable_table @source_schema =N'dbo',@source_name = 'LOPERSON', @role_name = null, @filegroup_name = null, @supports_net_changes = 1;
GO
When this command is run, it creates a default CDC table for the specific table name.
It is possible that SQL Server will throw the following error message after this query is executed:
Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.
This error is caused by an issue with privileges. The user executing the query must have sysadmin privileges. A way around this is to change the owner of the database. To do that, execute the following query:
EXEC sp_changedbowner 'sa'
GO
‘SA’ is the default sysadmin account in SQL Server. It should be able to access all of the data sources.
By right-clicking the POLICE database and selecting ‘Properties’, you can see that the ownership of the database has changed. Ownership can be reverted once Change Data Capture is activated.
Now, running the original query will succeed. You can confirm that the CDC table has been activated by executing the following query.
SELECT name, is_cdc_enabled
FROM sys.databases
A ‘1’ under the ‘is_cdc_enabled’ column means that CDC is enabled for that database.
The new CDC tables are now visible in the System Tables folder after expanding the POLICE database node.
You can also query the new CDC LOPERSON table to see the new columns by executing the following query:
select * from cdc.dbo_LOPERSON_CT
The new tables will start with ’ _$’.
Now that change data capture has been activated in SQL Server and has been enabled for the desired tables in the data source, EIQ RTIS must be configured to pick up the messages and update the index. This is done through the EIQ Update Configuration Tool.
· First, make sure that EIQ RTIS is running. This can be checked in ‘Services’.
· Next, open the EIQ Update Configuration Tool and log in using the proper credentials.
On the main page of the tool, users are presented with two options. They can either create a new task entirely, or, if there is already a task monitoring the desired VDS, they can just create a new task item.
The tool already has a task watching the POLICE_VDS, so create a new task item.
· Right-click the task and select ‘New Item…’
· Name the task ‘cdc_police’.
· Under Task Item Type, select ‘Query Based Polling’ from the drop-down menu.
· Click ‘Next’.
· Specify the name of the data source and the username and password.
· Select SQL SERVER as the platform.
· Then, specify how often EIQ RTIS should poll for messages.
The Initial Timestamp box is not necessary for monitoring SQL Server databases.
EIQ RTIS searches for transaction logs to update all of the tables in the data source by default. It is possible to configure table specific data like entering a particular column name and a custom value.
· When all the desired tables are configured, click Finish.
You can see the new task item added under the POLICE task.
With RTIS configured, go back to SQL Server. You can execute queries on the data source to INSERT, DELETE, or UPDATE data. EIQ RTIS is now configured to poll for changes in the data source and will update the indexes in real-time. In order to test this, use the same queries used to test MSMQ as outlined in Scenario 2: EIQ Index Real-Time Update.
Right-click on the POLICE data source and select ‘New Query’. Then input the following query:
INSERT into LoPerson VALUES (100000, 'Camille', 'Monica', 'Keane', '1980-01-01','123456789','TX','7654321','1990-01-01','312 UTA Blvd', 'Arlington', 'TX', '76010','Record inserted',NULL)
The message at the bottom will show if the query was successful. You can also check to see if the CDC tables are updating through a simple SELECT query. Execute the following query:
select * from cdc.dbo_LOPERSON_CT
Now, connect to the EIQ Server Query Tool to verify that EIQ RTIS updated the index.
Create a new query and connect to the POLICE_VDS, or whichever VDS you configured EIQ RTIS to poll through the EIQ Update Configuration Tool.
Now, execute the following query:
SELECT * FROM MyTable WHERE First_Name = ‘Monica’
‘Monica’ and all of the information related to her has been inserted into the index. Next, try sending an update message to EIQ RTIS. Change the first name in the record to ‘Maya’ by executing the following query in SQL Server.
UPDATE LoPerson SET Firstname = 'Maya' WHERE Firstname='Monica'
Return to the EIQ Server Query Tool to see the updated data. Execute the following query:
SELECT * FROM MyTable WHERE First_Name = ‘Maya’
Now try deleting the data from the data source. Execute the following query in SQL Server:
DELETE from loperson WHERE person_ID = 100000
Check to see if the data has been deleted from the index by executing the following query in the EIQ Server Query Tool:
SELECT * FROM MyTable WHERE First_Name = ‘Maya’
EIQ RTIS query-based polling depends on the presence of date-time/timestamp columns for tracking changes in the data source. This means that there must be a column with the DATETIME/TIMESTAMP data type in the monitored table with values that accurately reflect the record change events. EIQ RTIS will look for the updated timestamp for any changes and then fetch the new data to update the indexes. Note that polling will not be able to return any deleted records. There is another option available in EIQ RTIS for recognizing the deleted records and applying changes to indexes.
· Using SQL Plus, connect to the database you want EIQ RTIS to monitor.
· Add a timestamp column to the table using the following command:
o
ALTER
TABLE table_name
ADD (column_name data_type);
· Set the value of the new column to reflect the current time:
o
UPDATE table_name
SET column_name = ‘DD-MON-YY HH.MI.SS.FFFFFF AM or PM’
· Query for the data to make sure it was inserted properly.
These steps need to be repeated for every table in the database that needs a timestamp column. Once every timestamp column has been added and/or updated, configure EIQ RTIS to poll for the updates.
Using the EIQ Update Configuration Tool, create a new task and task item to monitor the data source.
· Log into the EIQ Update Configuration Tool and enter the appropriate credentials.
· Right-click on ‘Task’ and select ‘New Task’ from the context menu.
· Enter the name of the new task and make sure the ‘Use Virtual Data Source’ box is selected.
· Select the Virtual Data Source that this task needs to monitor from the list.
· Make sure that the ‘Maintain link index’ box is checked if the VDS has any link indexes configured on it.
· Click ‘Next’.
· Click ‘Finish’ to create the new task.
Next, create a task item for the new task.
· Right-click the created task and select ‘New Item’ from the context menu.
· Enter a name for the new task item.
· Make sure the Data Source Alias is correct.
· Select the task item type ‘Query-Based Polling’ from the drop-down menu.
· Click ‘Next’.
· Select ‘Other’ as the data source platform. This option is what specifies timestamp polling.
· Enter the Data Source Name and the User ID and Password for that data source.
· Specify the Poll Interval.
· Set the Initial Timestamp.
o The initial timestamp is used in case only columns are selected and values are not entered. EIQ RTIS polls for updates to the data source that come after this timestamp.
The table specific data section displays the available tables in the index. Two columns can be monitored in each table. For example, if a table has a Created_On and Modified_On column, both can be selected and monitored using the same task item, and the same polling configurations.
· Select a column name by double clicking the column name field. A combo box appears to display the available columns.
· Select the filter column. EIQ RTIS looks for changes in this column
If a column name is selected under the table name, then that table is considered for the time stamp polling updates.
· Double click the Value field near the column name for the same row. An edit box is displayed for entering the value.
· Enter the timestamp or appropriate value for the filter column. If no value is entered, then Initial timestamp value is taken.
· Click ‘Finish’ to create the task item.
The list of selected columns and tables are displayed in the right side of the tool.
· Right-click the task item and select ‘Start’ from the context menu.
A green arrow means that the task item started successfully and EIQ RTIS is now polling for updates to the index.
Now that the EIQ Update Configuration Tool is configured to poll for updates to the timestamps, begin performing INSERTS, UPDATES, and DELETES. When the task is started successfully, RTIS will poll the data source and check for any INSERTS, UPDATES, and DELETES after the specified timestamp. If there are any changes in the data source, then it will be picked up and updated in the index automatically. The documentation will follow the same INSERT, UPDATE, and DELETE guidelines as the SQL Server example with small edits made so that they are compatible with Oracle.
· Connect to the data source using SQL Plus or SQL Developer.
· Execute the following query:
INSERT into police.loperson VALUES (100000, 'Camille', 'Monica', 'Keane', '01-JAN-80','123456789','TX','7654321','01-JAN-90','312 UTA Blvd', 'Arlington', 'TX', '76010','Record inserted',NULL, systimestamp);
Commit;
There are three important differences to note. The first is that Oracle requires a different date format, so the inserted data has dates changed to the DD-MON-YY format. The second is the final inserted record. Because there is now a timestamp column in the database, the timestamp column must also have an inserted value. Using the value ‘systimestamp’ will insert the current timestamp of the system. The third is when dealing with Oracle, the database must be committed for changes to take effect.
· Now, query using the EIQ Server Query Tool to see if the timestamp was updated.
The LAST_UPDATE column is the SuperSchema mapping for the LASTUPDATED column created earlier. As you can see, the column has been updated to contain the date and time of the INSERT.
· Next, update the first name of the record to Maya. Execute the following query:
UPDATE police.loperson SET firstname = ‘Maya’, LASTUPDATED = systimestamp where firstname = ‘Monica’;
Commit;
For the UPDATE command to work properly, the timestamp column must also be updated. It can be updated using the value ‘systimestamp’ in the query.
· Now, check the EIQ Server Query Tool to make sure the UPDATE worked.
The first name was updated to Maya and the timestamp column was updated to the date and time the UPDATE was polled.
· Finally, delete the record from the database by executing the following query:
DELETE FROM police.loperson WHERE person_id = 100000;
Commit;
· Check the EIQ Server Query Tool to verify that the record has been completely removed.
Go through the same steps above to create a new task item, or right-click an existing task item and select ‘Edit’.
In order to schedule polling, the data source platform must be specified ‘OTHER’, even if the data source is a SQL Server data source. This will allow polling by timestamp. For Oracle data sources, deselect the ‘Allow SQL Timestamp’ checkbox. For SQL Server data sources, select ‘Allow SQL Timestamp’.
Observe the Allowed Format and enter the date and time accordingly. Enter the polling interval in seconds and then enter Initial Timestamp as follows:
For Oracle:
· 20-OCT-17 03.29.37.29 PM (DD-MMM-YY HH.NN.SS.MS AM OR PM) Note the “.” before the milli second, though milli second may not be needed.
For SQL Server:
· 2017-10-08 12:10:30.000 (YYYY-MM-DD HH:NN:SS.MS) Note the “.” before the milli second, though milli second may not be needed.
Set the start date for polling by selecting the desired date from the drop-down calendar.
Set the start time for polling by selecting the hour, minute, second or time of day fields and entering the desired time, or use the up and down buttons on the right of the box.
Polling starts once the scheduled Timestamp is reached, and the update task is running. If the update task is not running, it will not initiate or start the polling even if the item has been created. If no timestamp is entered in the schedule polling field, then polling starts immediately and will poll in the interval provided.
For Table Specific Data:
Select the desired Schema Name/Table Name row and double click the Column Name field. Select the appropriate timestamp column for polling. This is important otherwise the polling will not happen.
Selecting or deselecting the “Show Only Timestamp Columns” checkbox, allows users to toggle whether or not they see all of the columns in the table within the down-down box under Column Name, or just the timestamp columns in the table.
Click ‘Finish’ and wait for the task to be created.
Each table in the table specific data section can specify that the selected column is a native date column. This specification will optimize the queries to the EIQ Server for the native dates.
Double click the ‘No’ in the appropriate row to reveal the check box. Select it and then click off of it. The Native Date Column for that row will now say ‘Yes’.
The EIQ Product Suite also uses query-based polling to delete records from the index. The ‘Schedule Polling’ section of the Query-based Polling application window allows users to enable deleting records and schedule a separate frequency for detecting them. When EIQ RTIS detects that the record is no longer in the data source, that record is removed from the index.
Check the box next to ‘Enable Deletes’ in the Schedule Polling section of the application window. This will tell EIQ RTIS to look for records that are missing in the backend. Deletes will poll based on the ‘Schedule Polling’ timestamp in an interval specified by the frequency. Delete frequency is specified in days. Deletes can be a taxing process depending on the size of the data source, limiting it to days will alleviate some of that.
Copyright © 2019 , WhamTech, Inc. All rights reserved. This
document is provided for information purposes only and the contents hereof are
subject to change without notice. Names may be
trademarks of their respective owners.