This post is a follow-up of SQL Anywhere - Your Flight-Mode Database Server
For the top four settings (embedded, ad-hoc, server or occasionally connected) the same software can be utilized. The differences between these are various attributes that are more or less relevant depending on the environment. The embedded and occasionally connected environments are also habitats for the UltraLite deployment option.
Embedded
chriskleisath has provided many details on this in his blog "chriskleisath/blog". In brief, one of the intended use cases for SQL Anywhere is to be embedded in 3rd party applications. Its lightweight administration, wide range of eligible platforms and – what used to be very important in the past with operating environments like Windows on top of DOS – low resource requirements make it a perfect match for the requirements of this setting.
When looking at SQL Anywhere in the wider sense as a suite of products, the UltraLite deployment option is another very important part representing this aspect. Extremely lightweight (originally introduced for devices like a Palm Pilot consuming as little as 50k of memory) and integrated to the corporate IT through an occasionally connected synchronization mechanism (MobiLink), it enabled applications with a built-in database engine to be programmed utilizing standard SQL for the great-grandparents of today’s mobile devices.
In a classic computer environment, SQL Anywhere works on stand-alone and networked machines running with Windows, Linux (and other Unix flavors) or Mac OS (Intel). It uses database files binary compatible for each platform, allows the database server to be started on demand by connecting to the database and to adjust its resource utilization to the current ecosystem. The resource efficiency, which is vital for this operation setting, adds a benefit to the other settings as well.
Ad-hoc
This is the way I typically use it and the reason I suggest that everybody working with any RDBMS should consider having it installed on his or her workstation. Once the software has been installed, pass a database file to the database server executable and off it goes. No instance management required, an instance starts by starting the executable and ends by shutting it down. In network operation, the server can be addressed through a logical name via broadcast (within the range). In most cases nothing is required but the software installation and the database file.
You can have as many idle or running databases on your system as your file system and disk size permit. While the database is not started, it consumes the same amount of system resources as a Word document or a JPG photo – the disk space it occupies. Just as simple it can be backed up – simply copy the idle file to any media you like and have at hand. And when changing from one customer / project / department / solution to another, it’s as easy as closing the Excel sheet related to the first and opening the project plan associated with the second. The platform independence allows you to take a backup copy of a database running on an AIX, HP-UX or Solaris Server and work on it on your Windows, Linux or Mac OS workstation. Of course the same works the other way round as well: a database developed on your workstation can be rolled out to one of the server platforms simply by copying the file(s).
Once the database is started, use your ODBC, JDBC or OLE DB client of choice or the included client and administration utilities to connect to it. Run SQL statements or scripts in interactive or batch mode of DBISQL (“Interactive SQL”) or use the explorer style navigation and wizards from the GUI administration tool SybaseCentral. Populate your database from ODBC data sources available at your machine or out of ASCII files. Store the results of your queries in ASCII files, HTML tables or XML documents. Access table, column or procedure names using the Query Builder, pick lists or syntax completion. Evaluate the data you have collected using OLAP (Online Analytical Processing) functionality. All of this under your complete control. You’re the creator and owner of this database, you know the DBA’s credentials, you have the power and privilege to create work tables, materialize intermediate or final results and use the CPU power and RAM available on the database server machine that happens to be your workstation or notebook computer.
Usually, these ad-hoc environments have the database engine and the client utility or application sharing the same machine. Still, it is a complete client / server architecture. The database engine is one process, the client is another one. They communicate via well-defined interfaces, there is no difference in the way SQL statements are executed in such an environment or in one that features database engine and client on separate machines. The only difference occurs at the time the database client connection is established, where certain connection parameters may or may not be required.
Server
A SQL Anywhere (network) server might be just what you’ve operated as an ad-hoc instance – if you decided to use the network server engine, clients can technically connect to it from other machines on the network. But typically, a SQL Anywhere server is operated like other database servers: Continuously running on a machine that is up all the time except maybe for defined maintenance intervals.
SQL Anywhere provides the infrastructure to keep your database available 24x7. Even in the old days of Win NT 4 (that by some nerds seriously was doubted to provide anything close to continuous availability) I was working with a customer operating a galvanization site with an application using a SQL Anywhere database. The backbone of such an environment is a piece of software which works correctly, without strange breakdowns or memory leaks. For a database server, the muscle around the backbone is an administrator’s toolkit which allows administering the system without interrupting its operation.
The most vital part here is the Online Backup capability, which is available for Full and Incremental Backups. Both performance and reliability are improved by storing database file and transaction log on two separate drives. As long as the transaction log file survives and a proper backup mechanism is in place, the database can be restored without loss of committed transactions. If the database file survives, it can be restored to a consistent state where all transactions committed before the most recent checkpoint are guaranteed to persist, reducing the potential loss window to a moderate numberof minutes. To reflect the value of the transaction log, it is possible (for a slight potential performance penalty) to run the database with a mirrored transaction log, typically on a drive separate from the original transaction log. All redundancy on lower levels of storage architecture (like RAID) supplements this. But even with an infrastructure of two plain disks, the loss of one disk can be covered without losing a single committed transaction.
The next step towards a minimized window of loss is a Live Transaction Log Backup from a separate machine. Here, we can cover the loss of a complete machine with a few committed transactions lost. Even further, a High Availability (HA) Setup provides a Standby Database on a separate machine to almost immediately take over the role of a failed server database, process or machine. The characteristics of this Shared Nothing Architecture can be adjusted between synchronous (loss- free, but with performance impact) and asynchronous (with a light overhead but the risk of losing few transactions) operation. HA requires a Feature License.
SQL Anywhere Servers can utilize the resources of high end hardware by enabling the unlimited address space of 64bit operating systems, multi- threading and intra-query parallelism. However, some configuration defaults are designed to best fit more lightweight hardware, so it is recommended to pay more attention to the configuration of servers operated on high end systems, where the availability of an educated administrator is considered more likely.
Distributed and Occasionally Connected
Picking up a use case mentioned above in the “Embedded” section, I’ll briefly introduce two technologies closely related to SQL Anywhere (and bundled into the software package): MobiLink Synchronization and SQLRemote Replication. Both were introduced to provide reasonably up-to-date local data on mobile, occasionally connected computers or devices. Both rely on a hierarchical infrastructure which can be cascaded to multi-tier setups and facilitates easy extension of the mobile user crowd. The top of the hierarchy is represented by a Consolidated Database which contains the superset of all replicated / synchronized data and therefore can be considered a central live backup of all members.
SQLRemote is used for homogeneous SQL Anywhere scenarios. It is a technology so mature that it rarely has been mentioned in any update notes for years. Yet it continues to be used in large roll-outs with up to five digit numbers of users. It is completely transaction log based (which guarantees a low operation overhead) and I consider it the most efficient technology for this purpose as long as the requirements (SQL Anywhere only, very limited data transformation capabilities) are not an obstacle. This efficiency applies to both setup and operation.
MobiLink is used for heterogeneous scenarios (which does not exclude the homogeneous case). Here, the Consolidated Database can be an ODBC Data Source from a list of supported RDBMSs [see here for Unix] including DB2, Oracle, MS SQL, ASE, IQ, SQL Anywhere and – in the recently released version 16 – SAP HANA. The mobile nodes for MobiLink can be SQL Anywhere or UltraLite installation, the latter providing enterprise data onboard devices running iOS, Android or BlackBerry. Here, ample data transformations can be defined between consolidate and mobile nodes, but the development effort and operation overhead grow.
Both technologies provide mechanisms to recognize, handle and consolidate update conflicts caused by concurrent updates during the latency interval (in an approach similar to optimistic locking). They also can be used for continuously connected scenarios as long as the typical latency of a low number of minutes is acceptable.
Either of these two technologies will deserve a paper of its own, so feel free to ask for one in case you’re interested in more details.
Cloud
The cloud is the most recent operating environment for SQL Anywhere. This is a separate product called SQL Anywhere on Demand Edition (codenamed Fuji), which is based upon the same code line as the original database server (version 12). Supplemented by features like the above mentioned High Availability or Load Balancing, which are a perfect match for the Cloud infrastructure, the proven SQL Anywhere RDBMS technology keeps your porting efforts low. At the same time, the isolated multi-tenancy architecture guarantees that your data are separate from other tenants’ data.
Check out [http://sqlanywhere-forum.sybase.com/questions/12739/vendor-announcement-fuji-goes-ga] for more details about SQL Anywhere as the platform for SaaS.
Learn more about how to leverage leading-edge innovation with SAP Database Services
Stay in the conversation by following on SCN.
Follow our news on Twitter @SAPServices.
