This is a request for the best target for my research and testing. Here is the situation:
We provide software to our clients and part of that package is a stand-alone SQL Anywhere server on their hardware on their network. The client application is currently installed on each of their computers and it communicates with their server.
One of the ideas we have been considering is providing some standard data to them and possibly even keeping it up to date as it changes. Obviously we would want this added maintenance to be as easy and reliable for us as possible, not having to touch each client database as the data changes. Currently the discussion centers around one or two tables that would be standardized across all of our clients.
I have experimented a bit with pushing the data out to text (usually csv), checking the destination columns, and then importing from the text file, but this seems unnecessarily complicated. Ideally (I think) we would keep a database on our network and update it and that would send the data out to tables within the clients' existing databases. The requirements would not be instantaneous, even an overnight lag time would be acceptable. I have been reading about SQL Remote as a way to "Publish" data to a remote database, but most of the discussion seems focused on publishing to mobile databases rather than another standalone database. Also, the SQL Remote seems to initially require an extract from the parent database, rather than syncing a table between two existing databases.
There are some tools referred to as Replication, but that seems to be more of a backup or failover strategy.
Is there another product or tool I should be evaluating? Is SQL Remote the answer but with some configuration to avoid running two databases at a client site (one of their data and one that syncs with ours). I just need some pointing in the right direction I think.
Though we have been using SQL Remote for years, I would not recommend this here. IMHO, it would tie the standalone databases too strongly to your sample database. (This could be handled, of course, but it would seem a very uncommon SQL Remote scenario). The same would apply to MobiLink, methinks.
Instead, what about using a web client procedure in the client's databases? That procedure could try to connect to your sample database (running as a publicly available database with the builtin web server or with whatever web interface you choose) and would just extract new data as it is available? And it could be run on a regular schedule to check for new contents...
answered 09 Aug '11, 17:19
Good ISV style question!! :)
All the applications we have worked on are commercial style applications for vertical markets where the database resides on the server at said office, and the application is installed on the workstation.
We've also looked at options on how to keep our system tables updated with the latest information. These could be simple rate descriptions, tax rates, surcharge descriptions, etc.
I would say that sql-remote would probably not be the option you would want to use. That's not to say you couldn't. But you would be looking at each database that you sell being a replicating database to your master/consolidated database. You could set up the subscriptions for the tables that you need, but you would be dealing with making sure all your replication information was delivered correctly and I could see that becoming a HUGE pain the rear end.
Mobilink on the other hand could be another option to look at. You can just set up scripts that send data from the consolidated database to to the remotes. That way you're not worried about needing to replicate information from the remotes back to the consolidated. I've personally just studied this, but it seems to be the option when you want to transfer data from a master database to a database that is outside your walls. You could look at possibly using the direct row handling.
Another way might be the simplist but crudest. You could just download a sql script to a particular directory on the server. When the server starts (or some other event occures) you could fire off an event that runs that script from ISQL. You can even run ISQL in silent mode so the user doesn't see it. You could set up some constraits around the table that would keep you from entering data more then once. Heck, you could do all sorts of things.
Feel free to contact me back if you have any additional questions!! Would love to hear what direction you go since we're doing this as well.
Another alternative would be to use a proxy table or remote data access:
That is your client databases would contain a proxy table that is linked with the according table in your public sample database. In general, accessing remote data is quite slow (particularly over a WAN), but if there's just need to access this data now and then, this might be okay. By design, the data would be up-to-date as there's no replication of data here: The clients would directly access your data.
A different approach would be to use a remote STP to copy your sample data and store it locally. That would be somewhat similar to the web client function approach.
answered 09 Aug '11, 18:01
This is not actually an answer but a conceptual question:
AFAIK all SQL Anywhere's synchronization facilities (SQL Remote, MobiLink and Replication Server) do enforce some kind of tight coupling between a central database and a remote one. Basically, the central database has to keep track of the existence and synchronization state of all its remotes.
For two-way synchronization, that's absolutely necessary IMHO - you wouldn't want to synchronize with an unknown counterpart. - Obviously, that implies some kind of maintenance on the central database and the possibility that both sites get out of order and have to be re-synchronized.
For other situations, it's often enough for one database (say db2) to being able to extract some data from another one (db1). If db2 can't connect to db1 for some reason, well, then that's temporarily acceptable, and the imported data will stay not-up-to-date for a while. Usually, db1 does not even have to know that there are other databases that use its data. (And if so, it should not know...) - Apparently, this is a much looser coupling.
So I would like to ask you whether your setup would tend more to the first or the second approach - i.e. how much has your "sample database" have to know about your customomer databases?
answered 10 Aug '11, 17:29
After much testing and fiddling I did get the SQL remote to exchange data between databases. The noted difficulty is that this would require every customer database to be a spawn of a master database we maintain, or running two databases at the customer site.
At Volker's suggestion above I started fiddling with the web server and client procedures built into the database and was able to come up with something much simpler where the master database has the table available as a select defined in a web procedure. The client database can use a client web procedure inside an openxml call to read the output webpage as rows and columns and then you are free to insert, update, manipulate etc. into the client database.
I had the best results with using SOAP as the output type on the web server database rather than xml. For some reason using XML with openXML did not come in nearly as cleanly as the SOAP with openXML.
Maybe that's why it's called SOAP?
Mobilink will be the next target of testing.
answered 24 Aug '11, 15:30
Here's another question for you SIGER MAT.
How often do you envision changes to the "master" database?
I have a suggestion for you to consider. Why not have the client's download the latest updates to their database? Many applications give users the responsibility to "Search for Updates."
So you would have a button, lets say, on some window, the about window, lets say, titled "Check for Updates".
Or you could send the clients an email with a HTTP link in the message which would download an installation program, making the changes to the database.
It all depends on how much and how often you need to update the database, trying to anticipate future needs as well.
answered 10 Sep '11, 19:36