SQL Anywhere® Server Database Administration

1108 Pages·2006·6.92 MB·English

SQL Anywhere® Server
Database Administration
Published: October 2006
Copyright and trademarks
Copyright © 2006 iAnywhere Solutions, Inc. Portions copyright © 2006 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 ...................................................................... 8
Summary ............................................................................................................... 9
Running the Database Server .................................................................................. 11
Introduction .......................................................................................................... 12
Starting the database server ................................................................................ 16
Some common options ........................................................................................ 18
Stopping the database server .............................................................................. 30
Starting and stopping databases ......................................................................... 32
Running the server outside the current session .................................................. 33
Troubleshooting server startup ............................................................................ 44
Error reporting in SQL Anywhere ......................................................................... 46
Connecting to a Database ........................................................................................ 49
Introduction to connections .................................................................................. 50
Connecting from Sybase Central, Interactive SQL, or the SQL Anywhere
Console utility ...................................................................................................... 54
Simple connection examples ............................................................................... 56
Working with ODBC data sources ....................................................................... 64
Connecting from desktop applications to a Windows CE database .................... 70
Connecting to a database using OLE DB ............................................................ 71
Connection parameter tips ................................................................................... 73
Troubleshooting connections ............................................................................... 75
Using integrated logins ........................................................................................ 84
Copyright © 2006, iAnywhere Solutions, Inc. iii
SQL Anywhere® Server - Database Administration
Using Kerberos authentication ............................................................................. 93
Client/Server Communications .............................................................................. 103
Supported network protocols ............................................................................. 104
Using the TCP/IP protocol ................................................................................. 105
Using the SPX protocol ...................................................................................... 111
Adjusting communication compression settings to improve performance ......... 112
Troubleshooting network communications ........................................................ 114
The Database Server ............................................................................................... 117
The SQL Anywhere database server ................................................................. 118
Connection Parameters and Network Protocol Options ..................................... 205
Connection parameters ..................................................................................... 206
Network protocol options ................................................................................... 242
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
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
Introduction to international languages and character sets ............................... 304
Understanding character sets in software ......................................................... 310
Understanding locales ....................................................................................... 314
Understanding collations ................................................................................... 317
Understanding character set conversion ........................................................... 322
International language and character set tasks ................................................. 323
Character set and collation reference information ............................................. 328
Managing User IDs and Permissions .................................................................... 335
Database permissions overview ........................................................................ 336
Managing individual user IDs and permissions ................................................. 340
iv Copyright © 2006, iAnywhere Solutions, Inc.
SQL Anywhere® Server - Database Administration
Managing connected users ................................................................................ 351
Managing groups ............................................................................................... 352
Database object names and prefixes ................................................................ 359
Using views and procedures for extra security .................................................. 361
Changing ownership on nested objects ............................................................. 364
How user permissions are assessed ................................................................. 366
Managing the resources connections use ......................................................... 367
Users and permissions in the catalog ................................................................ 368
Database Options .................................................................................................... 371
Introduction to database options ........................................................................ 372
Database options ............................................................................................... 377
Compatibility options .......................................................................................... 382
Synchronization options ..................................................................................... 386
SQL Remote options ......................................................................................... 387
Replication Agent options .................................................................................. 388
Alphabetical list of options ................................................................................. 389
Database Properties ................................................................................................ 475
Understanding database properties ................................................................... 476
Physical Limitations ................................................................................................ 517
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 launcher ...................................................................................... 579
SQL Anywhere Console utility ........................................................................... 580
Checking for software updates .......................................................................... 582
Database Administration Utilities .......................................................................... 585
Administration utilities overview ......................................................................... 587
The Backup utility .............................................................................................. 590
The Data Source utility ...................................................................................... 595
The Erase utility ................................................................................................. 605
The File Hiding utility ......................................................................................... 608
Copyright © 2006, iAnywhere Solutions, Inc. v
SQL Anywhere® Server - Database Administration
The Histogram utility .......................................................................................... 610
The Information utility ........................................................................................ 612
The Initialization utility ........................................................................................ 614
The Interactive SQL utility .................................................................................. 622
The Language Selection utility ........................................................................... 628
The Log Transfer Manager ................................................................................ 631
The Log Translation utility .................................................................................. 637
The Ping utility ................................................................................................... 643
The Rebuild utility .............................................................................................. 647
The Server Enumeration utility .......................................................................... 648
The Server Licensing utility ................................................................................ 651
The Service utility .............................................................................................. 654
The SQL Anywhere Broadcast Repeater utility ................................................. 665
The SQL Anywhere Console utility .................................................................... 668
The SQL Anywhere Script Execution utility ....................................................... 670
The SQL Anywhere Support utility ..................................................................... 672
The Start Server in Background utility ............................................................... 680
The Stop Server utility ....................................................................................... 682
The Transaction Log utility ................................................................................. 685
The Unload utility ............................................................................................... 689
The Upgrade utility ............................................................................................. 701
The Validation utility ........................................................................................... 704
IV. Monitoring Your Database ................................................................. 709
The SQL Anywhere SNMP Extension Agent ......................................................... 711
Introduction to the SQL Anywhere SNMP Extension Agent .............................. 712
Understanding SNMP ........................................................................................ 713
Using the SQL Anywhere SNMP Extension Agent ............................................ 717
SQL Anywhere MIB Reference ............................................................................... 727
The SQL Anywhere MIB .................................................................................... 728
RDBMS MIB Reference ........................................................................................... 751
The RDBMS MIB ............................................................................................... 752
V. Maintaining Your Database ................................................................. 759
vi Copyright © 2006, iAnywhere Solutions, Inc.
SQL Anywhere® Server - Database Administration
Backup and Data Recovery .................................................................................... 761
Introduction to backup and recovery .................................................................. 762
Understanding backups ..................................................................................... 766
Designing backup procedures ........................................................................... 769
Configuring your database for data protection ................................................... 779
Backup and recovery internals .......................................................................... 782
Backup and recovery tasks ................................................................................ 788
Creating a maintenance plan ............................................................................. 808
Automating Tasks Using Schedules and Events ................................................. 809
Introduction ........................................................................................................ 810
Understanding events ........................................................................................ 812
Understanding schedules .................................................................................. 813
Understanding system events ........................................................................... 815
Understanding event handlers ........................................................................... 819
Schedule and event internals ............................................................................ 821
Event handling tasks .......................................................................................... 823
SQL Anywhere High Availability ............................................................................ 827
Understanding database mirroring .................................................................... 828
Using the SQL Anywhere Veritas Cluster Server agents .................................. 846
VI. Security ................................................................................................ 853
Keeping Your Data Secure ..................................................................................... 855
Security features overview ................................................................................. 856
Security tips ....................................................................................................... 858
Controlling database access .............................................................................. 860
Auditing database activity .................................................................................. 868
Running the database server in a secure fashion ............................................. 872
Encrypting a database ....................................................................................... 873
Keeping your Windows CE database secure .................................................... 883
Transport-Layer Security ........................................................................................ 885
Introduction ........................................................................................................ 886
Setting up transport-layer security ..................................................................... 889
Creating digital certificates ................................................................................. 890
Encrypting SQL Anywhere client/server communications ................................. 898
Encrypting MobiLink client/server communications ........................................... 904
Copyright © 2006, iAnywhere Solutions, Inc. vii
SQL Anywhere® Server - Database Administration
Certificate reader utility [readcert] ...................................................................... 911
Certificate generation utility [gencert] ................................................................ 912
VII. Replication .......................................................................................... 917
SQL Anywhere as an Open Server ........................................................................ 919
Open Clients, Open Servers, and TDS .............................................................. 920
Setting up SQL Anywhere as an Open Server .................................................. 922
Configuring Open Servers ................................................................................. 924
Characteristics of Open Client and jConnect connections ................................. 931
Replicating Data with Replication Server .............................................................. 933
Introduction to replication ................................................................................... 934
Tutorial: Replicate data using Replication Server .............................................. 937
Configuring databases for Replication Server ................................................... 946
Using the LTM ................................................................................................... 949
VIII. SQL Anywhere for Windows CE ...................................................... 959
SQL Anywhere for Windows CE ............................................................................ 961
Installing SQL Anywhere on a Windows CE device .......................................... 962
Using the Windows CE sample applications ..................................................... 965
Connecting to a database running on a Windows CE device ............................ 971
Configuring Windows CE databases ................................................................. 975
Running the database server on Windows CE .................................................. 985
Using the administration utilities on Windows CE .............................................. 986
SQL Anywhere feature support on Windows CE ............................................... 994
Index .................................................................................................................................. 999
viii Copyright © 2006, 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, security, 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 © 2006, 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 comprehensive
package that provides data management and data exchange, 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.
♦ 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, security, backup procedures, security, 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 those
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
applications.
♦ 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 © 2006, iAnywhere Solutions, Inc.