SAP SQL Anywhere is a proprietary relational database management system (RDBMS) product from SAP. SQL Anywhere was known as Sybase SQL Anywhere prior to the acquisition of Sybase by SAP.

The SQL Anywhere network server is available for Windows, Linux, Unix, and Mac OS X. SQL Anywhere supports the TCP/IP network protocol.

Features[edit]

SQL Anywhere can be run on Windows, Windows CE, Mac OS X, and various UNIX platforms, including Linux, AIX, HP-UX and Solaris. Database files are independent of the operating system, allowing them to be copied between supported platforms. The product provides several standard interfaces (ODBC, JDBC, and ADO.NET) and a number of special interfaces such as PHP and Perl. The engine supports stored procedures, user functions (using Watcom SQL, T-SQL, Java, or C/C++), triggers, referential integrity, row-level locking, replication, high availability, proxy tables, and events (scheduled and system events). Strong encryption is supported for both database files and client-server communication.

Uses[edit]

SQL Anywhere is used in several contexts, including as an embedded database, particularly as an application data store. For example, it is used in IntuitQuickBooks,^[1] in network management products, and in backup products. Its ability to be used with minimal administration is a distinguishing feature in this role.It can be used as a database server for work groups or for small or medium-sized businesses. It can also function as a mobile database. It includes scalable data synchronization technology that provides change-based replication between separate databases, including large server-based RDBMS systems.

Technologies[edit]

SQL Anywhere Server is a high performing and embeddable relational database-management system (RDBMS) that scales from thousands of users in server environments down to desktop and mobile applications used in widely deployed, zero-administration environments.

Ultralite: UltraLite is a database-management system designed for small-footprint mobile devices such as PDAs and smart phones.

Mobilink: MobiLink is a highly-scalable, session-based synchronization technology for exchanging data among relational databases and other non-relational data sources.

QAnywhere: QAnywhere facilitates the development of robust and secure store-and-forward mobile messaging applications.

SQL Remote: SQL Remote technology is based on a store and forward architecture that allows occasionally connected users to synchronize data between SQL Anywhere databases using a file or message transfer mechanism.

Differences between SAP ASE and SAP Sybase SQL Anywhere[edit]

^[2]SQL Anywhere databases are designed for a full range of functionality including enterprise servers, embedded applications, desktop servers, remote offices, and mobile applications. The SQL Anywhere database is designed to be self managing, requiring no DBA to constantly maintain the database system. Everything is in the box with SQL Anywhere.

SQL Anywhere is a lot cheaper: purchase price, licensing fees, AND the price you won't have to pay for all the technical support. Especially because you only have one thing to buy.

The SQL Anywhere database is light weight, but still offers full enterprise features such as:

• High Availability
• Database Clustering
• Snapshot Isolation
• Materialized Views
• Built-in Web Services Server
• Self-tuning Query Optimizer

SAP ASE is a fully featured enterprise database server designed specifically for mission-critical data management systems and ASE offers features such as:

• High Availability
• Encrypted Columns
• Advanced Security
• Distributed Transactions
• Content Management
• Data Partitioning

For example, SAP ASE is having multiple components for respective functionality and expensive compared to SQL Anywhere: if you want to replicate data between Oracle and ASE, you have to buy ASE, then you have to buy Replication Server, then you have to buy the Oracle version of something call 'Replication Server Options'. With SQL Anywhere, MobiLink is in the box, and so is the interface for Oracle. And the docs are all in one Help file too.

History[edit]

• Initially created by Watcom as Watcom SQL.
• Version 3: 1992
• Watcom acquired by Powersoft in 1993; Watcom SQL shipped with their visual programming environment PowerBuilder
• Version 4: 1994 (Stored procedures, triggers)
• PowerSoft and Sybase merged in 1995: Watcom SQL was renamed SQL Anywhere.
• Version 5: 1995 (SQL Remote data replication; graphical administration tools)
• Version 6: 1998. Renamed Adaptive Server Anywhere (ASA). (multi-processor support, Java objects in the database)
• Version 6.0.2: 1999 (MobiLinkdata synchronization, UltraLite mobile database for Palm OS and Windows CE)
• Version 7: 2000 (dynamic cache, task scheduling and event handling, cross-platform administration tools)
• Version 8: 2001 (Volcano query optimizer, encrypted data storage and transmission)
• Version 9: 2003 (Index consultant, embedded HTTP server)
• Version 10: 2006 – renamed SQL Anywhere (high availability, intra-query parallelism, materialized views)^[3]
• Version 11: 2008 (full text search, BlackBerry support)^[4]
• Version 12: 2010 released by SAP Sybase (support for spatial data)^[5][6]
• Version 16: April 18, 2013 – (faster synchronization and improved security)^[7]
• Version 17: July 15, 2015; released by SAP ^[8]

References[edit]

1. ^'QuickBooks replaces its flat file system with SQL Anywhere from Sybase iAnywhere'. Sybase.
2. ^'SAP SQL Anywhere Software Solutions'. SAP.
3. ^'Sybase iAnywhere Announces the Release of SQL Anywhere 10'. Sybase press release. August 7, 2006.
4. ^'Sybase iAnywhere Announces Availability of SQL Anywhere 11'. Sybase press release. August 5, 2008.
5. ^'Sybase Advances its Mobile and Embedded Database Leadership Position with SQL Anywhere 12'. Sybase press release. June 29, 2010.
6. ^'SAP Software Solutions Business Applications and Technology'. SAP.
7. ^http://www.kessler.de/prd/sybase/Ianywhere16.pdf
8. ^'Announcing SQL Anywhere 17! SAP Blogs'. blogs.sap.com.

External links[edit]

SQL Anywhere Dvd player for mac that will run slow motion forward. 17.0 introduces several product-wide new and changed features to learn about, in addition to component-level changes.

Deprecated features will be removed in future versions. Alter your applications to use recommended feature replacements instead of relying on deprecated features.

Check the readme.txt file for additional information about the software, including feature information that was added after the documentation was completed.

• New HTML-5, database server monitoring tool: SQL Anywhere Cockpit

  The Cockpit is a database server monitoring tool that provides an up-to-date view of the availability, capacity, and performance of your database server. It runs in a browser and can provide information to the SAP DB Control Center. See SQL Anywhere Cockpit.

  Use the Cockpit to track who is logged on to a database server; display both database server and client statistics, and disconnect users from databases.

  The Cockpit runs on the same database server, which you want to monitor. It is light-weight and is designed to run continuously in the background so that it is always available. But, you can also easily start and stop it to investigate issues.

  The Cockpit supports version 17 and version 16 databases running on a version 17 database server. As described below database users must have the COCKPIT_ROLE user-defined role to connect to the Cockpit.

  The following changes have been made to support the SQL Anywhere Cockpit:

  • New database server option to start the SQL Anywhere Cockpit on your database server When starting your database server specify the -cdb database server option to start the SQL Anywhere Cockpit.See -cdb database server option.
  • New option for the sa_server_option to control the SQL Anywhere Cockpit on a running database server

    Use sa_server_option system procedure and the Cockpit parameter to start and stop the Cockpit on a running database server. See sa_server_option system procedure.

  • New COCKPIT_ROLE user-defined role required to use the Cockpit

    To connect to the Cockpit, users must have exercise rights to the COCKPIT_ROLE user-defined role. Newly created databases include this user-defined role, but by default no user is granted the exercise rights to this role. To use the Cockpit with version 16 databases running on a version 17 database server, add the COCKPIT_ROLE to the database, and then grant users the exercise right to this role.

    For convenience in the sample database the DBA user is granted exercise rights to this role.

    By default, the COCKPIT_ROLE user-defined role includes all the system privileges required to perform actions within the Cockpit. See COCKPIT_ROLE user-defined role.

  • Secure feature for managing the Cockpit

    Control whether users can start or stop the Cockpit, with the -sf database server option. This feature controls whether users can run the sa_server_option system procedure with the CockpitDB option. See -sf database server option and Cockpit security.

  • New database server properties to support the Cockpit

    The CockpitURL database server property returns the URL to access your Cockpit. The CockpitDB database server property returns the set of Cockpit options currently being used by the database server. See List of database server properties.

  • Removal of dbconsole

    The Cockpit replaces the graphical administration tool dbconsole, which has been removed from the software.

• SQL Anywhere Profiler, a new database development and troubleshooting tool

  The Profiler is a development tool for tuning and troubleshooting your database applications. Use it to quickly view SQL statements that are currently running in your database, compare run times of stored procedures, and analyze how the objects in your database interact with each other. The Profiler supports version 17 databases.

  The Profiler replaces most of the application profiling administration tools in SQL Central.

  The following changes have been made to support the Profiler.

  • The Application Profiler and the SQL Anywhere Performance Monitor in SQL Central have been removed from the software and have been replaced with the Profiler. If you open Profiler and connect to a pre-version 17 database, the Application Profiling tools are not available. Similarly, if you open a database server, the Performance Monitor tab has been removed.

    If you want to use these features with a pre-version 17 database, use the version of SQL Central that matches the version of your database.

  • The database tracing feature is deprecated. The diagnostic tracing levels optimization_logging and optimization_logging_with_plans are ignored by the software. The Database Tracing Wizard in the SQL Anywhere plug-in for SQL Central has been removed. The new Profiler contains the ability to a record and view database operations.

  • The Deadlocks tab and Auditing tabs in the SQL Anywhere plug-in for SQL Central have been removed. The new Profiler contains a Blocking tab that shows both blocking and deadlock information. You can still generated auditing information, use the CREATE TEMPORARY TRACE EVENT SESSION statement to generate an ETD file, and then view the file using the dbmanageetd utility.

  • New SYS_RUN_PROFILER_ROLE system role is required to connect to the Profiler Newly created databases include the user-defined role SYS_RUN_PROFILER_ROLE system role. This role is required to connect to the Profiler. By default no user is granted the exercise rights to this role. For convenience in the sample database the DBA user is granted exercise rights to this role.See System roles.

• Removal of default DBA/sql user, and change to default minimum password length

  Previously, when you created a new database, if you did not specify a DBA user for the database, a user was automatically created with user ID DBA and password sql. Now, the database server requires you to supply a user ID and password. In addition, the minimum password length for users has changed from 3 to 6, but you can override this length at database creation time.

  These changes are designed to improve security in new databases. If you have databases with a DBA/sql user ID, then change it because this is the user login that a malicious user is likely to try when attempting to access a database.

  • CREATE DATABASE statement

    The DBA USER and DBA PASSWORD clauses, used for specifying the user ID and password for initial database DBA user, are now required when creating a database. Also, a new optional clause, MINIMUM PASSWORD LENGTH, has been added to allow you to override the (new) default minimum password length of 6. See CREATE DATABASE statement

  • Initialization utility (dbinit)

    The -dba option, used for specifying the user ID and password for initial database DBA, is now required when creating a database. Also, a new option, -mpl, has been added to optionally allow you to override the (new) default password length of 6. For example, -mpl 3 sets the minimum password length to 3. See Initialization utility (dbinit).

  • min_password_length database option

    The default length for the min_password_length database option is now 6 instead of 3. See min_password_length option.

  • -su database server option and the utility database (utility_db)

    The -su database server option, used to set the password for the utility database (utility_db), now accepts the option of specifying password; user-ID, password; or, alternatively, none. The password must be 6 characters or longer. This restriction is true even when using the personal database server (dbeng17.). See -su database server option and The utility database (utility_db).

  • util_db.ini file

    The support for the util_db.ini file, deprecated in version 10, has been removed. Use the -su option to set the password for the utility database (utility_db). See -su database server option.

  • Sample database

    The sample database (demo.db), used extensively throughout the documentation examples and tutorials, as well as by many of the software samples, continues to have a DBA/sql user to allow continued compatibility. This is true for any database created using the newdemo.bat script (newdemo.sh on Linux). As well, databases created using newdemo.bat have a minimum password length of 3 instead of 6. See The sample database.

  • SQL Anywhere 17 Demo data source

    Previously, when using the SQL Anywhere Demo ODBC data source to connect to the demo.db database in the administration tools, utilities, or in SQL statements, you did not need to specify the password (sql) because the password was stored as a setting in the ODBC data source. Now the database requires the password sql when you use the SQL Anywhere 17 Demo ODBC data source.

  • SQL Anywhere 17 CustDB data source

    Previously, when using the SQL Anywhere CustDB ODBC data source to connect to the demo.db database in the administration tools, utilities, or in SQL statements, you did not need to specify the MobiLink user ID (ml_server) and password (sql) because the user ID and password were stored as a setting in the ODBC data source. Now the database requires the user ID ml_server and password sql when you use the SQL Anywhere 17 CustDB ODBC data source.

  • Documentation examples and tutorials

    Any SQL, command line, or programming examples in the documentation that show connections to, or creation of, SQL Anywhere databases other than the sample database specify a user ID and password for a DBA user. Previously, these examples either did not specify a DBA user, or gave DBA/sql for the user ID and password. In the latter case, the examples have been changed to use DBA/passwd for the DBA user ID and password, to satisfy the new default password length requirement of 6.

    All other documentation examples that connect to the sample database, or any database created using the newdemo script (for example, the database used in the application profiling tutorials) remain the same, since these databases continue to have the DBA/sql user ID and password.

    This includes tutorials and examples in products like MobiLink and UltraLite, where connections to, or creation of, SQL Anywhere databases take place.

  • MobiLink, UltraLite, Relay Server, and SQL Remote products

    There are no changes to these products as a result of changing the default password length, or eliminating the default DBA/sql user ID and password when initializing a database.

  • Most SQL Anywhere and MobiLink samples and sample databases included with the software avoid embedded passwords

    Whenever possible, the samples included with the software are designed to demonstrate best practices regarding the handling of database passwords. For security reasons, you should never include passwords within your application code. Therefore most samples avoid embedded passwords. When a password is necessary to build or run a sample, you are required to provide the database password.

    For example, many samples use a demonstration database (demo.db) that is included in the root of the samples directory. For this database, the database administrator user ID is DBA and the password is sql (note that passwords are case sensitive).

    In some sample Microsoft Visual Studio projects, database credentials are still included in configuration files to support Microsoft Visual Studio design-time features which require the ability to connect to the sample database in the background. Good practice dictates that this sensitive configuration information be removed from the application project once software development has been completed.

• SSL version 3.0 no longer supported

  SSL version 3.0 is no longer supported. Use TLS version 1.0 or later instead.

• Strong encryption now achieved using OpenSSL
  Note These changes were released in version 12 and version 16 Support Packages.

  SQL Anywhere version 16.0 up to build 1695 and version 12.0.1 up to build 3986 included a Certicom encryption module that provided strong encryption used throughout the software. Now, SQL Anywhere includes an OpenSSL encryption module for the strong encryption. The Certicom encryption module has been removed.

  Read the following descriptions to determine how you may be impacted by this change.

  • FIPS-certified encryption now requires the private key of an identity file to be encrypted using AES

    • OpenSSL supports FIPS-certified AES encryption for the private key of an identity file. New servers using the FIPS-certified OpenSSL encryption module will not start when using an identity file that has its private key encrypted with 3DES. You must re-encrypt the identity file using AES. To do this, run a command similar to the following using an upgraded viewcert utility:

      The new and old passwords can be the same. See Certificate Viewer utility (viewcert).

    • The sample server identity file (rsaserver.id) and client identity file (rsaclient.id) have been modified so that the private keys are encrypted using AES rather than 3DES.

    • Trusted root certificate files specified using trusted_certificates do not need to be modified.

    • Versions of the server that use the Certicom encryption module do not start when using an identity file that has its private key encrypted using AES.

    • See also FIPS-certified encryption technology.

  • TLS handshake

    With Certicom, a client could trust any certificate in the signing chain and the TLS handshake would succeed. With OpenSSL, the client must trust the root certificate in the chain.

  • Self-signed certificates must now have the Certificate Signing attribute set

    Self-signed certificates must now have the Certificate Signing attribute set when using the identity encryption option (for example, the -x mlsrv and -xs dbsrv options). To determine if a certificate the Certificate Signing attribute is set, use the viewcert utility and look for the Certificate Signing attribute in the Key Usage portion of the output. If your self-signed certificates do not have the Certificate Signing attribute set, then regenerate the certificates.

  • Create Certificate utility (createcert) now uses AES encryption instead of 3DES

    The Create Certificate utility (createcert) now uses AES rather than 3DES encryption for encrypting the private key in the server identity file.

    A new option, -3des, has been added to the Create Certificate utility. This option creates a 3DES-encrypted server identity file that can be used by database servers in previous releases. New servers using FIPS-certified encryption cannot start using 3DES-encrypted certificates; however, if you are not using FIPS-certified encryption, then you can use 3DES-encrypted certificates. See Certificate Creation utility (createcert).

  • View Certificate utility (viewcert) now uses AES encryption instead of 3DES

    The View Certificate utility (viewcert) now uses AES rather than 3DES encryption when you specify the -p option to PEM-encode the output and when you specify the -ip and -op options to set the password.

    The View Certificate utility supports a new option, -3des, that encrypts output and passwords using 3DES instead of AES.See Certificate Viewer utility (viewcert).

  • Database server now loads the FIPS-certified encryption libraries at startup on 32-bit Windows

    Previously, the 32-bit database server loaded the FIPS-certified encryption libraries on Windows only when needed. Now, on 32-bit Windows, the server always attempts to load these libraries at startup. If loading fails, then no error is returned unless an attempt is made to use FIPS-certified encryption. On 64-bit Windows, the 32-bit database server no longer supports FIPS-certified encryption. Use the 64-bit database server instead. FIPS-certified encryption is separately licensed.

  • Deploying FIPS-certified encryption

    If you are deploying FIPS-certified encryption, then there are new shared libraries to deploy; these files are included in your software. The former files, sbgse2.dll and libsbgse2.so, are no longer installed by the software. The new files to deploy are:

    • 64-bit Windows: libeay32.dll, ssleay32.dll, and msvcr100.dll

    • 32-bit Windows: libeay32.dll, ssleay32.dll, and msvcr90.dll

    • Linux: libcrypto.so and libssl.so

    Note On Windows, although 32-bit and 64-bit FIPS-certified OpenSSL libraries for encryption are provided, use the 64-bit libraries on a 64-bit system.

    See Files required for Embedded SQL clients.

  • MobiLink-related changes and information
    • Connecting to a MobiLink server using client-side certificates now requires the Digital Signature certificate attribute to be set

      TLS connections to a MobiLink server using client-side certificates now require the client-side certificate to have the Digital Signature attribute set. If the attribute is not set, then the connection will fail.

      To determine if a certificate has the Digital Signature attribute set, use the View Certificate utility (viewcert) and look for the Digital Signature attribute in the Key Usage portion of the output. If your client-side certificates do not have the Digital Signature attribute set, then you must regenerate the certificates. See Certificate Viewer utility (viewcert).

    • FIPS-certified end-to-end encryption now requires the private key to be encrypted using AES

      If the private key file provided to a MobiLink server by the e2ee_private_key file option of the -x command-line option is encoded using 3DES and the server is using FIPS-certified encryption, then the private key file needs to be regenerated with the private key encrypted using AES.

    • How to update a MobiLink deployment that uses non-FIPS-certified TLS (includes HTTPS) and client-side certificates
      1. If your client-side identity certificates do not have the Digital Signature attribute set and the client connects directly to the MobiLink server, then you must regenerate and deploy client-side certificates with the Digital Signature attribute set.
      2. Update the server-side binaries.
      3. Update the client-side binaries.
    • How to update a MobiLink deployment that uses FIPS-certified TLS (includes HTTPS) and client-side certificates

      These steps update the client identity certificates twice if the Digital Signature attribute is missing from client-side identity certificates. This procedure can make the update less disruptive because synchronizations can continue without having to coordinate the client-side and server-side updates to occur at the same time.

      1. If your current client-side identity certificates do not have the Digital Signature attribute set and the client connects directly to the MobiLink server, then you must regenerate and deploy client-side certificates with the Digital Signature attribute set.
      2. Update the server-side binaries (remembering to include the new FIPS-certified encryption libraries) and deploy server identity certificates with AES-encrypted private keys.
      3. Update the client-side binaries (remembering to include the new FIPS-certified encryption libraries) and deploy client identity certificates with AES-encrypted private keys.
    • How to update a MobiLink deployment that uses FIPS-certified end-to-end encryption
      1. Regenerate the primary key file referenced by the e2ee_private_key encryption option.
      2. Shut down the MobiLink server.
      3. Update the MobiLink server binaries, remembering to include the new required FIPS-certified encryption libraries.
      4. Change the e2ee_private_key option to point to the new private key file (or replace the old file), updating the e2ee_private_key_password, if required.
      5. Restart the MobiLink server.
  • UltraLite changes

    UltraLite no longer supports FIPS-certified encryption on Windows Mobile.

• SAP replaces Sybase in Windows registry entries

  Previously, registry entries were recorded under SoftwareSybase. Now, these registry entries appear under SoftwareSAP.

  For example, HKEY_LOCAL_MACHINESoftwareSybaseSQL Anywhere16.0 becomes HKEY_LOCAL_MACHINESoftwareSAPSQL Anywhere17.0.

  For 64-bit Windows, SoftwareWow6432NodeSybase becomes SoftwareWow6432NodeSAP.

• SAP JRE replaces Oracle JRE in the software
  Note This change was released in a version 16 Support Package.

  Previously, the software included an Oracle JRE for clients to use. Now, the software uses the SAP JRE.

  The SAP JRE may perform differently than the Oracle JRE. Use the java_vm_options option (SQL Anywhere), and/or the -sl java option (MobiLink) to optimize your Java VM startup settings.

• JDK 8 recommended for JDBC application development

  Previously, version 1.7.0 of the Java Development Kit (JDK 7) was recommended for use in JDBC applications. Now, JDK 8 is recommended.

• Support for Windows XP removed

  SQL Anywhere is no longer supported on Windows XP.

• Support for Windows Vista removed

  SQL Anywhere is no longer supported on Windows Vista.