This SDI adapter is used to read and load SQL Server data and supports batch and realtime. It is based on the Sybase RepAgent for realtime capture of changes.

SDA usage

The remote source object in Hana is pretty much what one would expect. It asks for the SQL Server hostname and port, login credentials and a database name. The same parameters entered when connecting to a SQL Server database using any other tool.

Note: We talk about the MSsqlLogReaderAdapter here, not the ODBC driver!

/wp-content/uploads/2016/03/sqlserver1_908800.png

But let’s go though the settings slowly.


  • Rest should be self explanatory.The Instance Name is any arbitrary text. The logreader adapter will create a subdirectory with all its settings for this remote source. And since a remote source points to one database of a SQL Server Instance, there might be multiples.
  • Administraton Port is the port to be used by the RepAgent. Again, any will do as long as this port (plus the next higher number) is available. A common choice for the first remote source on this SQL Server instance is 13456, meaning it will be using the port 13456 and 13457.
  • Data Server used by the adapter. Typically the logreader adapter is installed on the SQL Server computer itself. Therefore, as seen from the adapter, the SQL Server instance is installed on the local host. Remember: The communication between Hana and the adapter hapens via the DPAgent. When configuring it, the remote server name was specified.
  • Port Number of the SQL Server instance. The default port for SQL Server’s first instance is 1433. To find out for sure, see below the installation chapter.
  • DAC Port Number: By default one higher than the standard port. Certain SQL Server operations are not allowed via a regular SQL connection, an Direct Administration Communication protocol is required for that. For pure SDA use cases it is actually not used.
  • Database Name with the data. This remote source points to one database of the SQL server instance only, an existing database has to be specified.
  • The Use Remote Database setting is again used for realtime only, it hints to the adapter if the adapter runs on the SQL Server computer or somewhere remote.

To find out the SQL Server Port being used, open the SQL Server Configuration Manager.

/wp-content/uploads/2016/03/sqlserver2_908945.png

With these settings the remote source can be browsed and all virtual table be created.

/wp-content/uploads/2016/03/sqlserver3_908877.png


The SDA part of the adapter is fairly simple. It gets a SQL statement in Hana syntax and translates it into SQL Server syntax, data is converted from source into Hana datatypes.

Hence the adapter supports all typical pushdowns, joins, where clauses, functions,…. really extensive.

It even supports insert/update/delete statements executed against the virtual table.

Installation of the Adapter

The adapter itself is part of the DPAgent installer, but its JDBC driver is missing as SAP cannot distribute software owned by somebody else. Therefore the MSSQLServer JDBC driver in version 4.0 has to be downloaded, extracted and then the JAR file is copied into the <DPAgent>/lib/ folder.

The download page is here: https://www.microsoft.com/en-us/download/details.aspx?id=11774

On the next page the 4.0 version should be selected for download and once available locally, the content can be extracted – the exe files are self packed archives either.

Locate the sqljdbc4.jar file and copy it into the lib folder of the location where the DPAgent was installed – by default that would be c:\usr\sap\dataprovagent.

/wp-content/uploads/2016/03/sqlserver4_908878.png

Now the Adapter can be deployed via the AgentConfig Tool (see here how to install it).

/wp-content/uploads/2016/03/sqlserver5_908914.png

Realtime

SQL Server supports one method to identify changes in the database, the CDC API. This adds a change table for every source table, thus every change in the original table causes one full row to be written into the change table. And when reading all changes, every single change table has to be queried to find out what kind of changes there had been. For a handful of tables that is okay but scanning 1000’s of change tables every second just to find out that nothing was changed? This is a huge overhead.

SQLServer also supports reading the transaction log, which contain all the changes the database made anyhow. So this would be a much better starting point. The command “dbcc LOGINFO” provides some insight and there are more hidden options (described e.g. here). But this is more for recovery purpose. One major issue with that other than being hard to decode the data is the fact that SQL Server might truncate the transaction log at any time. In case the reading of the log is too slow that would mean data can be lost. Not acceptable of course. But there is another SQL server feature, the Replication option, which works on transaction logs as well and for that Microsoft had to solve the log transaction problem as well – they do not truncate the log until the replication process is done. This proofs the fact that transaction log reading is an option, just very difficult.

Sybase has solved that problem already, hence the Logreader Adapter can utilize the RepAgent code as second option.

So the adapter supports two replication technologies, “Native Mode” means reading the transaction log or “MSSQL CDC Mode” using the SQL Server CDC API.

For the MSSQL CDC Mode nothing has to be done. The adapter does issue the required SQL Server commands and starts reading the changes.

Setting up Transaction Log reading

For the transaction log reading, that is where SQL Server has no direct support and requires a few steps hence. Actually, the documentation describes these steps quite nicely, hence the steps are copied only but augmented with images and hints.

The first step is to create a user called DP_USER and make it a sysadmin.

use master

go

create login DP_USER with password =‘<password>

go

use <your database>

go

create user DP_USER for login DP_USER

go

EXEC sp_addsrvrolemember ‘DP_USER’, ‘sysadmin’

go

Aforementioned Direct Administration Connection (DAC) is used by the RepAgent to execute statements not possible via a regular SQL connection, hence needs to be enabled.

Log on to Microsoft SQL Server using the DP_USER login and change the Microsoft SQL Server Remote Admin Connections Configuration option to enable DAC to allow remote connections.

sp_configure ‘remote admin connections’, 1

go

reconfigure

go

Verify the remote admin connection string.

sp_configure ‘remote admin connections’

go

Make the SQL Server log files available to RepAgent

In Windows Explorer, navigate to the sybfilter driver installation directory. This directory is located at <DPAgent_root>\LogReader\sybfilter\system\winx64, where <DPAgent_root> is the root directory of the Data Provisioning Agent installation.

Right-click the sybfilter.inf and select “install” to install the sybfilter driver.

Under any directory <DPAgent_root>\LogReader\sybfilter\system\winx64 create a file named LogPath.cfg.

Add a system environment variable named RACFGFilePath and set its value to the full path of the configuration file.

In Windows Explorer, navigate to <DPAgent_root>\LogReader\sybfilter\bin\, and right-click the sybfiltermgr.exe file and then select “Run as administrator” to start the sybfilter driver management console.

To start the sybfilter driver, enter “start” in this screen.

Add the log file path to the sybfilter driver by typing “add serverName dbName logFilePath” in the sybfiltermgr program. For example, to add log file named pdb1_log.ldf for the database pdb1 on the data server PVGD50857069A\MSSQLSERVER, use this:

add PVGD50857069A\MSSQLSERVER pdb1 C:\Mssql2012\MSSQL11.MSSQLSERVER\MSSQL\DATA\pdb1_log.ldf

Restart Microsoft SQL Server to make the log file readable.

Execute the “check” command in the sybfiltermgr console screen.

/wp-content/uploads/2016/03/sqlserver6_909816.png

Open the Windows Services screen and stop all services related to the database instance, the database, the agent, all…

Right-click your Microsoft SQL Server instance and choose Properties

Under Start parameters, enter -m

Click Start to restart the instance in single user mode

Connect to Microsoft SQL Server using dedicated administrator connection (DAC)

Start SQL Server Management Studio without logging in and select File -> New -> Database Engine Query.

In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.

Complete the Authentication section, providing credentials for a member of the sysadmin group and then click Connect.

In case this connection is the first one the login will succeed and the connection has full control of the SQL Server instance including hidden features. To validate the server truly is in Single User Mode, type

use mssqlsystemresource

go

In case this hidden database cannot be found, either the server is not in single user mode or the connection is not using DAC.

Copy the script contents from <DPAgent_root>\LogReader\scripts\mssql_server_init.sql into the SQL Server Studio Query screen and execute the script to initialize the server for replication.

Stop and restart Microsoft SQL Server service back to normal mode by removing the -m option from above.

Using the realtime subscriptions

With the adapter being ready, it is just a matter of setting up a realtime subscription for the virtual table and doing something with the data. For this there are multiple options, starting from creating a .hdbreptask to simply replicate a few tables from this source database into Hana or using the previously imported virtual table in a realtime .hdbflowgraph.

Or simply using SQL commands which the UIs do under the cover as well.

create column table t_test1 like v_test1;

create remote subscription s_test1 on v_test1 target table t_test1;

alter remote subscription s_test1 queue;

insert into t_test1 select * from v_test1;

alter remote subscription s_test1 distribute;

select * from v_test1;

select * from t_test1:

In above example a table in Hana is created with the exact same structure and primary key as the source.

Then a remote subscription is created loading all change data into the target table.

With the alter remote subscription .. queue command the adapter is told to start capturing changes in the source.

Then the current data is copied from the source into the target table – else a source update statement would not find a row to update in the target.

And finally with the distribute the changes are really starting to be applied to the target table and from now on the target table will be in sync with the SQL Server table.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Paul Joubert

    I am getting and error when I execute sybfiltermgr.exe ERROR: The config file is inaccessible. I am sure it refers to LogPath.cfg. What might make it inaccessible. Windows authorizations seams to be fine.

    (0) 
    1. Charles Archibong

      I had the same error but after including LogPath.cfg in the system variable full path and restarting the machine the error is gone. Some users suggested a restart was not neccesary.

      (0) 
  2. Alex Truscott

    Thanks Werner, great article,  I got all the steps working apart from one.  I wonder if you or anyone have seen this error before when connecting an sql2014 db and trying the generate the virtual tables in the remote data sources. 

    Capture.PNG

    Do I not need to add any ID&PW?,  I have tried all sorts of things but cannot get past this.

    I’m only working on the trial HCP account so not important.

    Thanks again Werner.

    Alex

    (0) 
  3. Pankaj Singh

    Hi Werner,

    I a getting below error while i am trying to create remote source using MssqlECCAdapter. Any input would be a great help. Thanks

    SAP DBTech JDBC: [403]: internal error: Cannot get remote source objects: SDA open() error: String index out of range: 0

    Pankaj

     

    (0) 
  4. Neil Wylie

    Hi,

    We’re trying to use replication tasks in HANA via a RDA to CDC-enabled SQL Server to update physical tables in HANA. We have successfully installed the Data Provisioning Agent and the changes to the data are visibleĀ in the virtual tables, but replication is not occurring into the physical tables. We’re fairly sure that we have the DPA set up correctly (as we can see the changes in the virtual tables) and that CDC is running correctly in SQL Server as the CDC tables are populating, but when executing the replication task with replication behaviour in realtime, not only do we not see replication into the physical tables, it seems to be stopping CDC and appears to delete the CDC tables in SQL Server! Upon checking the repagent.log file the following error occurs:

    I. 2017/02/01 00:53:54.781 INFORMATION com.sybase.ra.ltm.LTM Replication Agent changed to <ADMIN> state.
    I. 2017/02/01 00:53:54.781 INFORMATION com.sybase.ra.lr.mssql.RAMLogReader Transaction LogReader is starting the <T_LRINIT> Thread.
    W. 2017/02/01 00:53:54.828 WARNING com.sybase.ra.lr.mssql.RAMLogReader The Replication Agent DDL user is not configured. Therefore, the dsi_replication_ddl configuration in Replication Server must be enabled for DDL replication to succeed.
    I. 2017/02/01 00:53:54.828 INFORMATION com.sybase.ra.lr.mssql.RAMLogReader Transaction LogReader is starting the <T_LRSENDER> Thread.
    I. 2017/02/01 00:53:54.844 INFORMATION com.sybase.ra.lr.mssql.RAMLogReader Transaction LogReader is starting the <T_TRANREADER> Thread.
    I. 2017/02/01 00:53:54.860 INFORMATION com.sybase.ra.lr.mssql.RAMLogReader Transaction LogReader is Replicating logged Transactions.
    I. 2017/02/01 00:53:54.860 INFORMATION com.sybase.ra.lr.mssql.RAMLogReader Transaction LogReader Internal Thread <T_LRINIT> Exited.
    T. 2017/02/01 00:53:54.969 Device com.sybase.ds.mssql.device.Device Opening device at <D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SON_DB_REPL_log.ldf>.
    E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.device.LogDevice The log file <D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SON_DB_REPL_log.ldf> is being locked by SQL Server process.
    E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.device.LogDevice Use sybfilter driver to break the lock first.
    E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.device.LogDeviceConta Failed to synchroniz log device with message com.sybase.ds.mssql.device.FileLockedException.
    E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.device.LogDeviceConta Failed to synchroniz log device with message com.sybase.ds.mssql.log.device.SyncDeviceException.
    E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.MSSqlLogReader com.sybase.ds.mssql.log.device.SyncDeviceException

    Has anyone come across this problem before or know of a solution?

    (0) 

Leave a Reply