SQL Anywhere® Server Database Administration Published: March 2007 Copyright and trademarks Copyright © 2007 iAnywhere Solutions, Inc. Portions copyright © 2007 Sybase, Inc. All rights reserved. iAnywhere Solutions, Inc. is a subsidiary of Sybase, Inc. iAnywhere grants you permission to use this document for your own informational, educational, and other non-commercial purposes; provided that (1) you include this and all other copyright and proprietary notices in the document in all copies; (2) you do not attempt to "pass-off" the document as your own; and (3) you do not modify the document. You may not publish or distribute the document or any portion thereof without the express prior written consent of iAnywhere. This document is not a commitment on the part of iAnywhere to do or refrain from any activity, and iAnywhere may change the content of this document at its sole discretion without notice. Except as otherwise provided in a written agreement between you and iAnywhere, this document is provided “as is”, and iAnywhere assumes no liability for its use or any inaccuracies it may contain. iAnywhere®, Sybase®, and the marks listed at http://www.ianywhere.com/trademarks are trademarks of Sybase, Inc. or its subsidiaries. ® indicates registration in the United States of America. Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries. All other company and product names mentioned may be trademarks of the respective companies with which they are associated. Contents About This Manual ...................................................................................... ix SQL Anywhere documentation .................................................................................. x Documentation conventions ................................................................................... xiii Finding out more and providing feedback ............................................................ xvii I. Starting and Connecting to Your Database ............................................ 1 Tutorial: Using the Sample Database ........................................................................ 3 Lesson 1: Make a copy of the sample database ................................................... 4 Lesson 2: Start the SQL Anywhere database server ............................................. 5 Lesson 3: Display the Server Messages window .................................................. 6 Lesson 4: Stop the database server ...................................................................... 7 Summary ............................................................................................................... 8 Running the Database Server .................................................................................... 9 Introduction to running SQL Anywhere database servers ................................... 10 Starting the database server ................................................................................ 14 Some common options ........................................................................................ 16 Stopping the database server .............................................................................. 27 Starting and stopping databases ......................................................................... 29 Running the server outside the current session .................................................. 30 Running authenticated SQL Anywhere applications ............................................ 41 Troubleshooting server startup ............................................................................ 47 Error reporting in SQL Anywhere ......................................................................... 49 Connecting to a Database ........................................................................................ 51 Introduction to SQL Anywhere database connections ......................................... 52 Connecting from Sybase Central, Interactive SQL, or the SQL Anywhere Console utility ...................................................................................................... 56 Simple connection examples ............................................................................... 58 Working with ODBC data sources ....................................................................... 66 Connecting from desktop applications to a Windows CE database .................... 73 Connecting to a database using OLE DB ............................................................ 74 Connection parameter tips ................................................................................... 76 Troubleshooting connections ............................................................................... 78 Copyright © 2007, iAnywhere Solutions, Inc. iii SQL Anywhere® Server - Database Administration Using integrated logins ........................................................................................ 87 Using Kerberos authentication ............................................................................. 95 Client/Server Communications .............................................................................. 105 Supported network protocols ............................................................................. 106 Using the TCP/IP protocol ................................................................................. 107 Using the SPX protocol ...................................................................................... 113 Adjusting communication compression settings to improve performance ......... 114 Troubleshooting network communications ........................................................ 116 The Database Server ............................................................................................... 119 The SQL Anywhere database server ................................................................. 120 Connection Parameters and Network Protocol Options ..................................... 205 Connection parameters ..................................................................................... 206 Network protocol options ................................................................................... 240 II. Configuring Your Database ................................................................. 263 Working with Database Files .................................................................................. 265 Overview of database files ................................................................................. 266 Using additional dbspaces ................................................................................. 268 Using the utility database ................................................................................... 272 SQL Anywhere Environment Variables ................................................................. 277 Introduction to SQL Anywhere environment variables ....................................... 278 File Locations and Installation Settings ................................................................ 293 Installation directory structure ............................................................................ 294 How SQL Anywhere locates files ....................................................................... 296 Registry and INI files .......................................................................................... 299 International Languages and Character Sets ....................................................... 303 Localized versions of SQL Anywhere ................................................................ 304 Understanding character sets ............................................................................ 310 Understanding locales ....................................................................................... 315 Understanding collations ................................................................................... 318 International language and character set tasks ................................................. 323 Character set and collation reference information ............................................. 328 Managing User IDs and Permissions .................................................................... 337 Database permissions overview ........................................................................ 338 Managing individual user IDs and permissions ................................................. 342 iv Copyright © 2007, iAnywhere Solutions, Inc. SQL Anywhere® Server - Database Administration Managing connected users ................................................................................ 352 Managing groups ............................................................................................... 353 Database object names and prefixes ................................................................ 360 Using views and procedures for extra security .................................................. 362 Changing ownership on nested objects ............................................................. 365 How user permissions are assessed ................................................................. 367 Managing the resources connections use ......................................................... 368 Users and permissions in the catalog ................................................................ 369 Database Options .................................................................................................... 371 Introduction to database options ........................................................................ 372 Database Properties ................................................................................................ 477 Understanding database properties ................................................................... 478 Physical Limitations ................................................................................................ 517 SQL Anywhere size and number limitations ...................................................... 518 III. Administering Your Database ............................................................ 521 SQL Anywhere Administration Tools .................................................................... 523 Sybase Central .................................................................................................. 524 Interactive SQL .................................................................................................. 539 Using text completion ........................................................................................ 576 Using the fast launchers .................................................................................... 579 SQL Anywhere Console utility ........................................................................... 580 Checking for software updates .......................................................................... 582 Database Administration Utilities .......................................................................... 585 Administration utilities overview ......................................................................... 587 Backup utility (dbbackup) ................................................................................... 590 Data Source utility (dbdsn) ................................................................................ 595 Erase utility (dberase) ........................................................................................ 604 File Hiding utility (dbfhide) ................................................................................. 606 Histogram utility (dbhist) .................................................................................... 608 Information utility (dbinfo) .................................................................................. 610 Initialization utility (dbinit) ................................................................................... 611 Interactive SQL utility (dbisql) ............................................................................ 619 Interactive SQL utility (dbisqlc) .......................................................................... 623 Language Selection utility (dblang) .................................................................... 625 Copyright © 2007, iAnywhere Solutions, Inc. v SQL Anywhere® Server - Database Administration Log Transfer Manager utility (dbltm) .................................................................. 627 Log Translation utility (dbtran) ........................................................................... 633 Ping utility (dbping) ............................................................................................ 638 Rebuild utility (rebuild) ....................................................................................... 641 Server Enumeration utility (dblocate) ................................................................. 642 Server Licensing utility (dblic) ............................................................................ 645 Service utility (dbsvc) for Windows .................................................................... 648 Service utility (dbsvc) for Linux .......................................................................... 654 SQL Anywhere Broadcast Repeater utility (dbns10) ......................................... 658 SQL Anywhere Console utility (dbconsole) ....................................................... 661 SQL Anywhere Script Execution utility (dbrunsql) ............................................. 663 SQL Anywhere Support utility (dbsupport) ........................................................ 665 Start Server in Background utility (dbspawn) ..................................................... 672 Stop Server utility (dbstop) ................................................................................ 674 Transaction Log utility (dblog) ............................................................................ 676 Unload utility (dbunload) .................................................................................... 679 Upgrade utility (dbupgrad) ................................................................................. 691 Validation utility (dbvalid) ................................................................................... 694 IV. Monitoring Your Database ................................................................. 697 The SQL Anywhere SNMP Extension Agent ......................................................... 699 Introduction to the SQL Anywhere SNMP Extension Agent .............................. 700 Understanding SNMP ........................................................................................ 701 Using the SQL Anywhere SNMP Extension Agent ............................................ 705 SQL Anywhere MIB Reference ............................................................................... 713 The SQL Anywhere MIB .................................................................................... 714 RDBMS MIB Reference ........................................................................................... 737 The RDBMS MIB ............................................................................................... 738 V. Maintaining Your Database ................................................................. 745 Backup and Data Recovery .................................................................................... 747 Introduction to backup and recovery .................................................................. 748 Understanding backups ..................................................................................... 752 Designing backup procedures ........................................................................... 755 vi Copyright © 2007, iAnywhere Solutions, Inc. SQL Anywhere® Server - Database Administration Configuring your database for data protection ................................................... 765 Backup and recovery internals .......................................................................... 768 Backup and recovery tasks ................................................................................ 774 Creating a maintenance plan ............................................................................. 795 Automating Tasks Using Schedules and Events ................................................. 797 Introduction to using schedules and events ....................................................... 798 Understanding events ........................................................................................ 799 Understanding schedules .................................................................................. 800 Understanding system events ........................................................................... 802 Understanding event handlers ........................................................................... 806 Schedule and event internals ............................................................................ 808 Event handling tasks .......................................................................................... 810 SQL Anywhere High Availability ............................................................................ 813 Introduction to database mirroring ..................................................................... 814 Using the SQL Anywhere Veritas Cluster Server agents .................................. 833 VI. Security ................................................................................................ 839 Keeping Your Data Secure ..................................................................................... 841 Introduction to security features ......................................................................... 842 Security tips ....................................................................................................... 844 Controlling database access .............................................................................. 846 Auditing database activity .................................................................................. 854 Running the database server in a secure fashion ............................................. 858 Encrypting a database ....................................................................................... 859 Keeping your Windows CE database secure .................................................... 869 Transport-Layer Security ........................................................................................ 871 Introduction to transport-layer security .............................................................. 872 Setting up transport-layer security ..................................................................... 875 Creating digital certificates ................................................................................. 876 Encrypting SQL Anywhere client/server communications ................................. 882 Encrypting MobiLink client/server communications ........................................... 887 Certificate utilities ............................................................................................... 893 VII. Replication .......................................................................................... 901 Copyright © 2007, iAnywhere Solutions, Inc. vii SQL Anywhere® Server - Database Administration SQL Anywhere as an Open Server ........................................................................ 903 Open Clients, Open Servers, and TDS .............................................................. 904 Setting up SQL Anywhere as an Open Server .................................................. 906 Configuring Open Servers ................................................................................. 908 Characteristics of Open Client and jConnect connections ................................. 915 Replicating Data with Replication Server .............................................................. 917 Introduction to using SQL Anywhere with Replication Server ........................... 918 Tutorial: Replicate data using Replication Server .............................................. 921 Configuring databases for Replication Server ................................................... 930 Using the LTM ................................................................................................... 933 VIII. SQL Anywhere for Windows CE ...................................................... 943 SQL Anywhere for Windows CE ............................................................................ 945 Installing SQL Anywhere on a Windows CE device .......................................... 946 Using the Windows CE sample applications ..................................................... 949 Connecting to a database running on a Windows CE device ............................ 955 Configuring Windows CE databases ................................................................. 961 Running the database server on Windows CE .................................................. 971 Using the administration utilities on Windows CE .............................................. 972 SQL Anywhere feature support on Windows CE ............................................... 980 Index .................................................................................................................................. 985 viii Copyright © 2007, iAnywhere Solutions, Inc. About This Manual Subject This book covers material related to running, managing, and configuring SQL Anywhere databases. It describes database connections, the database server, database files, backup procedures, security, high availability, and replication with Replication Server, as well as administration utilities and options. Audience This manual is for all users of SQL Anywhere. It is to be used in conjunction with other manuals in the documentation set. Copyright © 2007, iAnywhere Solutions, Inc. ix About This Manual SQL Anywhere documentation This book is part of the SQL Anywhere documentation set. This section describes the books in the documentation set and how you can use them. The SQL Anywhere documentation The complete SQL Anywhere documentation is available in two forms: an online form that combines all books, and as separate PDF files for each book. Both forms of the documentation contain identical information and consist of the following books: ♦ SQL Anywhere 10 - Introduction This book introduces SQL Anywhere 10—a product that provides data management and data exchange technologies, enabling the rapid development of database-powered applications for server, desktop, mobile, and remote office environments. ♦ SQL Anywhere 10 - Changes and Upgrading This book describes new features in SQL Anywhere 10 and in previous versions of the software, as well as upgrade instructions. ♦ SQL Anywhere Server - Database Administration This book covers material related to running, managing, and configuring SQL Anywhere databases. It describes database connections, the database server, database files, backup procedures, security, high availability, and replication with Replication Server, as well as administration utilities and options. ♦ SQL Anywhere Server - SQL Usage This book describes how to design and create databases; how to import, export, and modify data; how to retrieve data; and how to build stored procedures and triggers. ♦ SQL Anywhere Server - SQL Reference This book provides a complete reference for the SQL language used by SQL Anywhere. It also describes the SQL Anywhere system views and procedures. ♦ SQL Anywhere Server - Programming This book describes how to build and deploy database applications using the C, C++, and Java programming languages, as well as Visual Studio .NET. Users of tools such as Visual Basic and PowerBuilder can use the programming interfaces provided by these tools. ♦ SQL Anywhere 10 - Error Messages This book provides a complete listing of SQL Anywhere error messages together with diagnostic information. ♦ MobiLink - Getting Started This manual introduces MobiLink, a session-based relational-database synchronization system. MobiLink technology allows two-way replication and is well suited to mobile computing environments. ♦ MobiLink - Server Administration This manual describes how to set up and administer MobiLink server-side utilities and functionality. ♦ MobiLink - Client Administration This manual describes how to set up, configure, and synchronize MobiLink clients. MobiLink clients can be SQL Anywhere or UltraLite databases. ♦ MobiLink - Server-Initiated Synchronization This manual describes MobiLink server-initiated synchronization, a feature of MobiLink that allows you to initiate synchronization or other remote actions from the consolidated database. x Copyright © 2007, iAnywhere Solutions, Inc.