We are building a client server application where the client reports activities back to the server for logging purposes. Thus, insert/update doesn't have to be immediate and can happen on a predefined schedule (in minutes). The client may also query the database, but mostly for specific records not for a large datasets.
It is expected that there will be a lot of information going from the clients to the Server on a frequent basis.
Now for the fun part - the client is a C++ App, what is the best way to structure the database calls ?
* Embedded –(Embedded C that is wrapped in C++) ;
* Stored procedures that are invoked by the Server based on ID and prm that are recived from the client;
* SQL statements that are defined in external XML file and are executed by the client or server
* Other ?
The main concern is ease of implementation and manageability from development perspective.
In addition, our design calls for a queue system on the server side which accumulates the updates from the agents and does the database write on occasion (based on load, priority etc) - does this makes sense ? what are the "best practices"?
I, and probably most, would go for the second of your bullets. The main thing is your database and application are now decoupled. If this is large scale then great - your DB specialists work on the SQL side of things and your C++ specialists work on the C++ side of things. If it is not, then it still means that one side of your client server architecture is insulated against changes to the other.
For your last question - "best practice" depends on your specific requirements. If you are reporting on this data than typically best practice would not involve asynchronous changes like this but like I say - depends on your needs.