User's Guide Part III. Using SQL Anywhere Chapter 19. Using Transactions and LocksSome of the computers on your network might be portable computers that people take away from the office or which are occasionally connected to the network. There may be several database applications that they would like to use while not connected to the network.
Clearly, they cannot update the database server while they are not connected to the network. They can, however, take a copy of the database file and make updates to the copy using the SQL Anywhere server Standalone database engine running on the portable computer. Later, on returning to the office, the transaction log can be translated into a command file and applied to the database server.
This type of process is fully automated by the new SQL Remote replication system, described in chapters "Introduction to SQL Remote Replication" and "SQL Remote Administration".
| Required software You must purchase a copy of SQL Anywhere Standalone and SQL Remote for each machine on which you wish to deploy a client application running on the SQL Anywhere Standalone database engine. |
There are potential problems with applying translated transaction logs. When machines are connected to the network, locking prevents conflicting updates to records in the database. When a user makes changes to a copied database, there is no such protection. Consider what happens when two users update the same record, or one user deletes a record that another user updates. You can design applications to avoid this sort of problem by having each user update an isolated subset of the data, but you need to be aware of the possibility.
One solution is to use the -v command line switch when starting the database engine on the portable computer. This causes the engine to record the previous values of every column whenever a row of the database is updated. When the transaction log is translated (using DBTRAN) every UPDATE will have a WHERE clause specifying the value of every column in the row. The row will not be updated if any value has been changed by another user. ISQL will display a message if an UPDATE does not affect any rows, indicating that the particular database row has already been updated or deleted by someone else. When this happens, your update procedure will need manual intervention to resolve the potential conflict.
SQL Remote has full conflict detection capabilities and the ability to resolve conflicts through conflict triggers.
If you want to deploy applications that work on information taken from a database file, but which do not require applying updates to a multiuser database, you can install the SQL Anywhere Desktop Runtime System on each computer.
The runtime database engine supports the full range of data manipulation language commands, such as INSERT, DELETE and UPDATE, but does not employ a transaction log. Consequently, updates made on the local database cannot be applied to a multiuser database.
| Required software To use the single-user runtime database engine on an unlimited number of machines, you need to have purchased the SQL Anywhere Desktop Runtime System. |
With large database files, it may help to use DBSHRINK to compress the database before making a copy. In this case you will need to use a write file with the copied database (see DBWRITE).
You could also use a subset of the large database by creating an extraction procedure that builds a database that contains only the data needed by one person. As long as the table names and column names are identical, translated transaction logs from the smaller database can be applied to the main database.