SQL Anywhere® Server Database Administration

1090 Pages·2007·6.29 MB·English

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.