How to Abstract Dual-Replicated Shop Floor SQL Database Connection

[dan_kellackey]

One of our plants has replicated virtual servers that contain a shop floor database that must be queried for production posting to SAP.  The databases are replicated, with perhaps a 20s delay, and each has a separate IP address, but identical databases.

I am trying to think of an elegant way to switch SQL queries between the databases in the event that the primary server goes off-line.

A simple way is to write two identical queries, one for each database, and then do a server check and choose either the primary or secondary server query.  Of course this is messy, and you have to maintain two copies of each query, easy to make mistakes when making changes.

Ideally I would like to be able to programmatically change the SQL connection for the queries.  Since both databases are identical copies of each other (with slight delay), if I could figure out how to change the connection it would simplify my transactions.

Does anyone know how to change a SQL connection within a transaction? 

Seems like in the .irpt script assistant there is a .getServer and .setServer under the iCommand.getQueryObject() -> SQL.  I won't be creating a web page for my transactions, but this seems like a possible to lead to follow.  Looking in the workbench, it is not obvious to me.

Anyway to abstract the SQL connection?  I haven't used virtual server connections, thinking that these are only for linking to other MII NetWeaver databases.

I could always create two Linked SQL server connections from our MII database and change the query text using a [Param.x], but I would like to avoid that solution.

I am developing on MII15, so the new SAPUI5 tools are available, but again, they center around web development and I will be running transactions via MII scheduler.

Any suggestions?

Thanks,

Dan K

[dan_kellackey · Accepted solution]

With more digging I now see that in the workbench, withing the Link editor, the SQL query has a "Server" attribute, which I was simply able to change the text strings between the connection names and the proper database was queried.  Works as I had hoped.  Funny, as soon as I finished posting this question I had a second look and found the solution.

[jcgood25 · Accepted solution]

MII is parameter driven in many ways, so I'm glad to hear you sorted it out.  Another consideration, depending upon the way your database is structured would be to fully qualify the table or schema within the query itself, using [Param.x] (32 available).  The benefit with this would be a single connection pool for the data server.  In your case it sounds like you found the right approach since the DB's are separate, and here the naming convention of your data servers is key.