SAP PI JDBC Adapter - support for MSSQL Mirroring?

[Former Member]

Hello, wondering if anyone has had experience supporting connection to an MS SQL Server mirroring setup. Basically the 3rd party is giving us two host names of SQL Servers.

In the Microsoft drivers they offer a parameter like: ";Failover Partner=PartnerServerName" which to my understanding handles the failover situation by basically swapping primary and fail-over server connection parameters if the primary cannot be reached.

I'm not sure what the capabilities are for our JDBC driver in PI. Can anyone with some practical experience and potential downsides please share your experience? If we cannot support the parameter maybe there is some recommendation we can make back to that team about a similar functionality we can support in PI.

I already looked through SAP Notes 801367 (JDBC paramaters) and 831162 - JDBC FAQ but not finding much info about it.

Many thanks,

Aaron

[iaki_vila · Accepted solution]

Hi Aaron,

I think your supposition is right according with Mocosoft documentation https://msdn.microsoft.com/en-us/library/aa342332(v=sql.110).aspx, and with java it seems to be possible (i haven't worked with this in PI, i'm sorry)

Have you installed the driver?, you can try with the example syntax of URL connection in the below documentation and to check if any exception is raised

Regards.

[former_member382978]

Hi Aaron,

another solution could be to directly write the failover server in the Communication Channel Connection Parameter as mentioned in http://www.jnetdirect.com/jsqlconnect-technical-reference.html#FailoverSupport.
For example the connection string could be like jdbc:JSQLConnect://%0Server1%1Server2%2Server3/database=Master/user=sa

So connection would be in the order written in the string. In this example It would connect first to Server1, if this Server isn't reachable it would connect to Server 2 and so on.

Regards,

Tobias

[Former Member]

Lets see if it works! I will update the thread later with a final solution.

[Former Member]

The JNetDirect driver does not seem to be honoring the syntax as stated in their documentation.

Technical Reference

PI cannot resolve the host when prefixed with %0

Here is another note dealing with the MS version of the JDBC driver:

https://launchpad.support.sap.com/#/notes/0001745895

Maybe this approach can bring us some success.