Database Change Capture Adapter(CDC) JDBC connect string fails for SQL server Named Instance (but works for JDBC Table Construct)

[Sharad Honavar]

Thanks in Advance

In the Database Change Capture Embedded Adapter, the same connect string which works for a JDBC Table Construct(defined in the jdbc.sources.conf HOCON file)failsfor the CDC Adapter. Jdbc URL is - "jdbc:sqlserver://MyHost\MyNamedInstance;databaseName=myDB"

ERROR: InputAdapter: Adapter is shutting down: The connection to the host MyHost, named instance MyNamedInstance failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.

We are StreamBase 10.6, running MS SQLServer 2019with Named Instances enabled for dynamic ports, and running the Browser Service on UDP 1434 with no firewall blocking it(or else the Jdbc Tbale construct would also have failed,which it does not with same jdbc connect string).

As is customary in larger shops not using single default Instance and fixed ports.

[Steve Barber]

Hi,

I don't use this adapter much myself (and am not set up to try it now), but a quick glance at the documentation yields some ideas to look at.

1) The docs say "The TIBCO StreamBase Database Change Data Capture Input adapter allows a StreamBase application to monitor changes to an Oracle or SQL Server database (2005,2008,2010,2012,2014,2016)."

So, since you are using SQL Server 2019, it's a least worth putting in a Support Case to let the product team have a chance to consider whether the 10.6.x version of the adapter will even support 2019.

2) The docs say "Example MSSQL: jdbc:sqlserver://{host}:{port};databaseName={databaseName}"

 

Notably your URI is in a different form. Not sure if that makes a difference or not, or whether the adapter supports the form you are using, but worth thinking about, and maybe trying things out.

3) Just because a JDBC URI works with the JDBC Table data construct doesn't necessarily mean it's going to work the CDC adapter. I mean, it might, but you are making an unwarranted assumption there. Without going into too much detail about how TIBCO product teams actually do their work, it's worth knowing that the JDBC Table data construct and the CDC adapter were developed and are maintained by different teams of people (and these days, those teams are like 8,000 miles apart). So, no reason to assume that they work the same way. Try more things

 

Or ask Support as this is a reasonably obscure question, believe it or not.

[Steve Barber]

Hmm, the Microsoft docs for SQL Server URLs say to use a single backslash in your JDBC URL preceding the instance name, not a double backslash.

However, in HOCON, backslashes must be escaped using a backslash.

So if you copy/pasted from your working HOCON to the adapter property in Studio . . .

Maybe try it with just one backslash

jdbc:sqlserver://MyHostMyNamedInstance;databaseName=myDB

Just an idea.

[Steve Barber]

Hmm, the Microsoft docs for SQL Server URLs say to use a single backslash in your JDBC URL preceding the instance name, not a double backslash.

 

However, in HOCON, backslashes must be escaped using a backslash.

 

So if you copy/pasted from your working HOCON to the adapter property in Studio . . .

 

Maybe try it with just one backslash

 

jdbc:sqlserver://MyHostMyNamedInstance;databaseName=myDB

 

Just an idea.

[Sharad Honavar]

Actually single backslash did work. Strangely HOCON or form input they are both read in as strings, something to do with the parser which does it differently for the jdbc adapter I suppose.

 

The  MS doc says to use 'instanceName=myInst' which also works!

 

Now if you could solve my other post about the CDC adapter trying to Enable CDC on the Database and the Table even if those checkboxes are unchecked, and fail for needing dbo rights; I would worship the ground you walk on :)

[Sharad Honavar]

Actually single backslash that did work. Strangely HOCON or form input they are both read in as strings, something to do with the parser which does it differently for the jdbc adapter I suppose.

 

The  MS doc says to use 'instanceName=myInst' which also works!

 

Now if you could solve my other post about the CDC adapter trying to Enable CDC on the Database and the Table even if those checkboxes are unchecked, and fail for needing dbo rights; I would worship the ground you walk on :)

[Steve Barber]

Heh, glad that worked. I'll go look for your other post, haven't seen it yet. Though, to re-iterate expectations, I'm a TIBCO Streaming guy, not so much an MS SQL Server guy, so we'll see.

 

I don't really need worshippers, thanks -- too much responsibility -- but if we are ever fortunate enough to meet, beer is good. ;-)