Attunity MS-SQL CDC – SQLCDCLGR configuration, registration and setup

Attunity’s Log reader (LGR) is the component that actually reads the MSQL transaction LOG.
All logged data subjected to ‘MS-REPLICATION’ is read and placed at the transient storage folder.
It is implemented as an independent standalone Windows Service.
Since its functionality is highly sensitive, it is endowed it with high availability features and fault tolerance thus attempting to stay ‘always up’.

LGR is a non-standard component. In a sense – it is a component which is extraneous to the CDC solution itself.
LGR service can be thought of as an MS-SQL server companion, much like MS Log reader server.

All LGR functionalities are materialized within a single executable file SQLCDCLGR.

The following command displays the program interface:

$>sqlcdclgr -?

Attunity R & D Jun-2006

SQLCDCLGR Transaction LOG mining service controller:
----------------------------------------------------
sqlcdclgr -s register -a <service-name> <input-file> Register a service and its input file
sqlcdclgr -s unregister -a <service-name> Unregister a service
sqlcdclgr -s start -a <service-name> Start service execution
sqlcdclgr -s stop -a <service-name> Stop service execution
sqlcdclgr -s restart -a <service-name> Restart service execution (=refresh parameters)
sqlcdclgr -p name <service-name> Display input file P_arameter name registered for a service
sqlcdclgr -p contents <service-name> Display input file P_arameter contents registered for a service
sqlcdclgr -p help Display help for parameters values assignment
sqlcdclgr -t T_ype an input file template
sqlcdclgr -b <input-file> Run the service in an online 'B_locking' mode, using input file
sqlcdclgr [-h|-?] Display this H_elp banner

Service input is held at: HKEY_LOCAL_MACHINE\SOFTWARE\Attunity\Attunity Server\Services


+-----------
| SQLCDCLGR Transaction LOG mining feature.
| Associated program is: C:\Program Files\Attunity\Server\BIN\sqlcdclgr.exe
+-----------

As seen above – “-s” stands for “service” operations and “-p” stand for “parameter” operation.
Other qualifiers will be explained also.

SQLCDCLGR program expects a single parameter which specifies an XML file holding the actual operational parameters.
The file specification should consist of a full absolute path!!
As SQLCDCLGR is registered as a Windows service, the input file specification is placed at the registry path as specified above.
The program associated with the service is SQLCDCLGR, which resides at the location displayed above.
This display depends on the PATH currently defined.

So – SQLCDCLGR expects an XML input.
Following command explains the structure and the contents of the expected parameters.
For further understanding of the role of each and every parameter – look at the product documentation.

$>sqlcdclgr –p help

<?xml version='1.0'?>

<serviceConfig>
<cdcOrigin
server='{Server machine}'
database='{Database name}'
user='{MS-SQL User login}'
password='{MS-SQL Password login}'
defaultOwner=''/>
<transientStorage
directory='{Directory where transient storage is maintained}'
maxFileSize = '{Maximum bytes per individual transient storage unit}'
totalSize = '{Total size limit for allocated transient storage}'
lowThreshold = '{Target space ratio (% from totalsize) for cleanup utility}'/>
highThreshold ='{Occupied space ratio (% from totalsize) for launching cleanup utility}'/>
<logging
directory='{Directory where LOG files are placed}' />
<control
batchSize = '{Max size of retrieved DBCC record sets}'
retryInterval='{Time interval between polling passes (Seconds, for partial batches only)}'
debugLevel='{Trace Loglevel: none | api | internalCalls | info | debug}'
traceDBCC='{true:false for controlling DBCC-ed data tracing}'
traceStatistics='{true:false for controlling statistics tracing}'/>
<detainer
detainingTimeInterval = '{Time (Sec) for detaining logged transactions}'
detainerTxnDurationLimit ='{Limit of longest detainer transaction duration}'
traceActivity = '{true:false for controlling detainer activity tracing}'/>
</serviceConfig>


As everything is now understood – one can create an input file template as shown below.
The input is organized in sections – as section per topic.
Parameters are introduced as attributes. Some are given as proposed defaults. Others are given as ‘place holders’ to be substituted by actual values.


$> sqlcdclgr -t >dmosrv.xml

<?xml version='1.0'?>

<!--
Following is a template input file for the MS-SQL CDC LGR transaction LOG miner.

1. Fill the mandatory attributes (assigned here with ?xxx?)
2. Modify operational attributes as needed.

Issue 'sqlcdclgr -p help' command for displaying a brief parameters values assignment help.

Refer to the product documentation as to specific settings, their roles and impact.
================================================== =================================

THIS CODE AND INFORMATION IS PROVIDED 'AS IS' WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
PURPOSE.

Copyright (c) 2000,2001,2002,2003,2004,2005,2006 Attunity Ltd. All Rights Reserved.

-->
<serviceConfig>
<cdcOrigin server='?xxx?' database='?xxx?' user='?xxx?' password='' defaultOwner='dbo'/>
<transientStorage directory='?xxx?' maxFileSize='1000000' totalSize='100000000' lowThreshold='65' highThreshold='85'/>
<logging directory='?xxx?'/>
<control batchSize='50000' retryInterval='1' debugLevel='none' traceDBCC='false' traceStatistics='false' />
<detainer detainingTimeInterval = '300' detainerTxnDurationLimit ='2147483647' traceActivity='false'/>
</serviceConfig>

Well – it is about time to feed in actual configuration parameters.
Look at the following example:

<?xml version='1.0'?>

<!--
Following is a template input file for the MS-SQL CDC LGR transaction LOG miner.

1. Fill the mandatory attributes (assigned here with ?xxx?)
2. Modify operational attributes as needed.

Issue 'sqlcdclgr -p help' command for displaying a brief parameters values assignment help.

Refer to the product documentation as to specific settings, their roles and impact.
================================================== =================================

THIS CODE AND INFORMATION IS PROVIDED 'AS IS' WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
PURPOSE.

Copyright (c) 2000,2001,2002,2003,2004,2005,2006 Attunity Ltd. All Rights Reserved.
-->
<serviceConfig>
<cdcOrigin server='hillel-xp' database='cdclog5' user='sa' password='' defaultOwner=''/>

<transientStorage directory='d:\dev\connect\prod\def\sqlcdctmp' maxFileSize='1000000' totalSize='100000000' lowThreshold='65' highThreshold='85'/>

<logging directory='d:\dev\connect\prod\tmp'/>

<control batchSize='50000' retryInterval='3' debugLevel='none' traceDBCC='false' traceStatistics='false'/>

<detainer detainingTimeInterval='300' detainerTxnDurationLimit='2147483647' traceActivity='false'/>
</serviceConfig>

Note that directory paths must be fully qualified. No logical substitution is allowed.

Now we are ready to register the service.
The service should have a name of choice – preferably the database name.

Issue the following:
D:\DEV\connect\src\adapters\sqlcdclog\sqlcdcexr>sq lcdclgr -s register -a cdclog5 D:\DEV\connect\src\adapters\sqlcdclog\sqlcdcexr\dm osrv.xml

The program response is self-explanatory:

+-----------
| SQLCDCLGR Transaction LOG mining feature.
| Associated program is : C:\Program Files\Attunity\Server\BIN\sqlcdclgr.exe
+-----------

Install(): Service 'cdclog5' installed
setServiceParameter(): Parameter 'D:\DEV\connect\src\adapters\sqlcdclog\sqlcdcexr\d mosrv.xml' has been set for Service 'cdclog5' at
HKEY_LOCAL_MACHINE\SOFTWARE\Attunity\Attunity Server\Services
addEventSource(): Key (+values) added : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\EventLog\Application\cdclog5
D:\DEV\connect\src\adapters\sqlcdclog\sqlcdcexr>


You can now go to the services pane (SettingàControl Panel à Administrative tools à Services) and see the service just registered.
Via the service ‘Properties’ you should go to the Recovery TAB and set all failure actions to ‘Restart’.
You should also make sure via the Log On TAB that the service logs on at account that allows access to the CDC-ed database.

SQLCDCLGR program interface offers a repertoire of common operations that can carried out at the command line.

For example – if one wishes to look at the parameter value of an SQLCDCLGR service – following will do:

D:\DEV\connect\src\adapters\sqlcdclog\sqlcdcexr>sq lcdclgr -p name CdcLog5


+-----------
| SQLCDCLGR Transaction LOG mining feature.
| Associated program is: C:\Program Files\Attunity\Server\BIN\sqlcdclgr.exe
+-----------

Service 'CdcLog5' is registered with input parameter 'D:\DEV\connect\src\adapters\sqlcdclog\sqlcdcexr\d mosrv.xml'
D:\DEV\connect\src\adapters\sqlcdclog\sqlcdcexr>


The “-b” option is a special one that allows SQLCDCLGR to run in a blocking interactive mode at the console.
This option can be useful for diagnostics and other needs of that kind.