Refering to DB on another server

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Refering to DB on another server

Post by barry.marcus »

We have duplicate databases and duplicate Vortex apps on two servers, each with it's own installation of Texis. I am thinking of a synchronization scheme in Vortex that would run DML SQL (inserts updates and deletes) against the database on the OTHER server from either installation. For example, I might want to read data from a table on server A (THIS server, if the code that's running is on server A) and insert it into the same table on server B (the OTHER server). In simplified pseudo-code, it might be something like:

<sql db=$pathToDbOnThisServer $selectStatement>
...build an insert statement from select values here...
<sql db=$PathToDbOnOtherServer $insertStatement></sql>
</sql>

This will run on either one server or the other, but I'd like to be able to run it on either server.

Can this be done? If so, should the paths to the databases on the remote servers use mapped network drives (these are Windows installations) or use \\servername\sharename syntax?

Perhaps I'm doing something wrong, but not having success with either syntax yet.

Thanks.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Refering to DB on another server

Post by John »

Not directly. A database can only be directly accessed on the machine hosting it.

You can call a script on the other machine to execute the SQL. The complexity of each end will depend on your needs, from a simple <submit> to queuing up the changes and processing the queue. Our Search Appliance uses a queue so that if the second server is temporarily unavailable it can catch up when it is back.
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Refering to DB on another server

Post by barry.marcus »

How do I spawn, from code on one server (SERVERA), a Texis process on another server (SERVERB). I'm thinking something along the lines of (this code would be in a function on SERVERA):

<exec \\SERVERB\MORPH3\texis.exe -d \\SERVERB\MyDBDir \\SERVERB\MyCodeDir\remoteProcess>
</exec>

Does something like this makes sense?

I would also background the spawned process so that the spawning process returns and ends immediately after starting the remote process.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Refering to DB on another server

Post by mark »

Nothing like that.
The typical method is to have a webserver running on serverb and vortex script(s) to service requests. Then servera can <submit> commands and data to serverb. The format of those command and data packets is defined by the send and receive scripts you write. You can do something as simple as csv or as fancy as xml.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Refering to DB on another server

Post by mark »

Or you can use no "format" as such and just put every item into a different variable so you don't have to parse anything.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Refering to DB on another server

Post by barry.marcus »

OK. I got submit to work as you described. However, there is one issue... I want to spawn the remote process and then return immediately to the calling function (i.e., spawn the remote process asynchronously), which is why I thought of using exec in the first place. In our case the remote process may take several minutes to complete, and I don't care at all about the anything that the submit function returns. Is there a way to do this with <submit>?

Note that the URL that is submitted does not point to an HTML form at all. It points to a script on the remote server that performs a number of database maintenance functions. Once the calling function spawns this remote process, it's done.

Thanks.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Refering to DB on another server

Post by barry.marcus »

Actually, never mind. An obvious way to do this just occurred to me...

I can use <submit> on SERVERA to launch a *spawning* function on SERVERB, which in turn backgrounds my lengthy database maintenance process on SERVERB. In other words (in pseudocode), on SERVERA:

<submit METHOD=POST URL=$urlToSpawnDBMaintOnSERVERB>

On SERVERB:

<a name=spawnDBMaint>
<exec BKGND texis doDBMaint></exec>
</a>

<a name=doDBMaint>
<vxcp timeout -1>
... Lengthy DB Maint process here ...
</a>

This works great! The function on SERVERA that issues the <submit> returns immediately and terminates as desired.

Thank you again for your help.

Barry
Post Reply