Connecting to SAP ASE database

[mandzyu]

HI,
I am trying to connect the exporter to the SAP ASE database, and am having some issues at the final stage. I was wondering if anyone has ever tried this. Since SQL Server and ASE share many similarities, and one is kind of based on the other, I thought this would be pretty simple.

I added https://github.com/SAP/go-ase and https://github.com/thda/tds to the drivers list. Built the exporter, and updated the exporter.yml with this data source name

data_source_name: 'tds://<username>:<password>@<hostname>:<port>/<database_name>'

In my case this looks like

  data_source_name: 'tds://sapusr:sappass4AWS@saphost:4901/AWS'

I've tried many different formats. Switching tds in the DSN for sap, removing the DB name, trying different ports, etc. And I am seeing this error

curl localhost:9399/metrics
No metrics gathered, [from Gatherer #1] context deadline exceeded

I set the exporter log level to debug, and I am seeing these logs

ts=2023-04-17T16:13:10.360Z caller=klog.go:84 level=debug func=Infof msg="Database handle successfully opened with 'tds' driver"
ts=2023-04-17T16:13:20.361Z caller=klog.go:84 level=debug func=Infof msg="Error gathering metrics: [from Gatherer #1] context deadlineexceeded"

Seems similar to this issue: #179

If I update the data_source_name to something like this. (change to localhost from actual host name)

data_source_name: 'tds://sapusr:sappass4AWS@localhost:4901/AWS'

I see the the error

ts=2023-04-17T16:19:09.657Z caller=klog.go:84 level=debug func=Infof msg="Error gathering metrics: [from Gatherer #1] dial tcp [::1]:4901: connect: connection refused"

This leads me to believe the connection parameters were correct earlier before. But still, not sure at what point it is going wrong. If anyone has encountered this, or has any tips, it would be greatly appreciated.

[burningalchemist]

Hi @mandzyu,

The fact that you get Database handle successfully opened with 'tds' driver is generally a sign that the exporter was able to pick the driver (the wording might make a false impression, we need to change this).

However, since you get context deadline exceeded, it means that the connection hasn't been eventually established (despite hitting the right location). It's possible there are missing or incorrect properties on the DSN (I see you tried different options, I'd probably also check parameters like ssl or alike).

Also, I noticed that goase driver has the scheme name registered as ase in their code, so I'd try to connect using goase with the ase://<username>:<password>@<hostname>:<port>/<database_name> and try some parameters on top as I suggested above.

Please give it a try, and let me know, I'm happy to help.
I also noted some improvements for logging I'm gonna put into work. 👍

[mandzyu]

Спасибо Сергей,
I have tried ase, in the schema and got the same result.

One thing to note. I first tried using the https://github.com/SAP/go-ase driver. The official go driver for ASE, but any time I ran it, I got
No metrics gathered, [from Gatherer #1] sql: unknown driver "tds" (forgotten import?) when calling the curl command. I added "github.com/SAP/go-dblib/tds" to the list of drivers, but was still receiving the same issue.

It was at this point that I noticed your documentation:
UPDATE: Since v0.9.0 sql_exporter relies on github.com/xo/dburl package for parsing Data Source Names (DSN).

I checked dburl, and saw that they are using https://github.com/thda/tds as the go driver for ase. When I added this to the imports, and removed the official driver, I was able to get past the issue of sql: unknown driver "tds" (forgotten import?), but now I am getting context deadline exceeded.

I am starting to think it might be something with this driver: thda/tds. On their documentation, they state:
"This is a beta release. This driver has yet to be battle tested on production workload. Version 1.0 will be released when this driver will be production ready"
There have been no updates to this driver since 2020.

Do you think it could be an issue with using this driver?
I tried using the official SAP driver, but I think because the exporter uses the dburl package, it tries to use that driver, instead of the one I provide. This is also my first experience using go, so I am unsure if maybe I am doing something wrong with the imports.

I'm wondering why dburl would use an unofficial driver that is not receiving maintenance?

[burningalchemist]

It's a bit complicated here, dburl doesn't use any drivers directly (these are just recommendations), that's why we have to add drivers on our side. But each driver has a driverName to register. So, technically, it should be all fine.

What do you think if we schedule a call, it'd be easier to explain, and maybe we could iterate faster on trying things out. These DBs are tricky to integrate, of course. But I'm interested in solving the problem.

Another alternative might be to sketch a small program with debug lines to understand the connection behaviour to fix it.

Let me know how do you think we can proceed? :)

P.S. I noticed your feature request on AWS SSM thing 👍 We can discuss it soon, I'm bit busy this week, but I'll try to provide my feedback/opinion this week. :)

[mandzyu]

Yes if we can set up a call that would be great. I am in the PST time zone. I see that you are located in Berlin. 8:30 AM would work for me. That should be 5:30 PM for you. Any weekday would work for me as long as I know a day or two in advance.
What video call software do you prefer? Zoom?

[burningalchemist]

I'm generally available (except this Friday's evening) during the day, but I can join the call late at night as well. Zoom, Google Meet, Microsoft Teams, doesn't matter. Please send an email to oss(at)szyubin(dot)me and we arrange it. 😃 This week it's probably just Thursday, but next week any day would work. 👍

[burningalchemist]

Hey @mandzyu,

Please checkout this branch ase-temp (link). We have a single driver (go-ase only) for now. So you just need to checkout the branch, and build with make build. With -log.level=debug you should also observe the DSN which is passed to the driver. Be careful, it exposes the password (we'll remove it later). Please let me know if it worked. 👍

On my end, I have two PRs and one open issue:

• Issue to go-ase to support URI DSNs out of the box;
• PR to dburl to treat ase scheme to connect with a new driver;
• PR to sql_exporter as a workaround until the go-ase issue is fixed. I might also provide it as a patch for now since it's just 3 lines. We'll see 👍

Cheers,
Sergei

[mandzyu]

Just ran the curl command. Successfully connected to the DB and returning metrics. Thank you so much for the help

sapawssd /sybase/AWS% curl localhost:9399/metrics
# HELP mssql_local_time_seconds Local time in seconds since epoch (Unix time).
# TYPE mssql_local_time_seconds gauge
mssql_local_time_seconds 1.682010222e+09

[burningalchemist]

Great! I'll keep you posted on the above updates, and when ready, I'll cut a new release. 👍

[burningalchemist]

@mandzyu - go-ase driver issue has been fixed in the upstream, that was quick. 👍 The remaining parts from my side are coming soon. :)