Contents
Introduction ...............................xv
Chapter 1 System Administration Overview.........1
Architecture Overview .........................1
Responsibilities of the Sybase System Administrator.........2
Installing the Sybase Adaptive Server ...............2
Establishing Connectivity between Clients and Servers ......3
Installing and Maintaining User Databases .............3
Establishing Security for Adaptive Server Enterprise .......4
Maintaining and Troubleshooting Your ASE ............5
Establishing Disaster Recovery Procedures.............6
Monitoring ASE ...........................6
Use of Utilities............................7
Databases ................................7
System Tables Overview ........................8
System Tables in Special Uses Databases .............10
Sybase System Stored Procedures .................11
Chapter 2 Adaptive Server Installation ...........13
Step 1: Preparation ..........................14
Logical Page Size .........................15
Physical Devices ..........................15
Server Names............................16
Networking Information ......................16
Sybase Software Asset Manager ..................17
Sort Order .............................17
SSL in Adaptive Server ......................17
Step 2: File Transfer .........................19
Step 3: Running Installation .....................20
Interfaces File ...........................29
Runserver File ...........................32
Errorlog File ............................33
servername.cfg — Server Configuration File ...........34
Environmental Variables ......................34
v
Step 4: Front-End Installation ....................35
ODBC ...............................37
Starting the Server ..........................37
Normal Unix Startup ........................37
Normal NT Startup.........................38
Manual Unix Startup ........................38
Automatic System Boot ......................39
Verifying the Server Is Running ...................39
Adaptive Server for Unix .....................39
Adaptive Server for NT ......................40
Shutting the Server Down ......................40
Graceful Shutdowns ........................40
Shutdown with nowait .......................41
Maintenance Shutdowns ......................41
Global Variables ...........................42
Chapter 3 Defining Physical Devices .............43
The Need for Devices.........................43
Logical vs. Physical Devices....................43
Performance Implications .....................44
Creating and Dropping Devices ...................44
Creating Database Devices.....................45
Creating Default Devices .....................49
Dropping Database Devices ....................49
Raw vs. Filesystem ..........................50
Dsync Option .............................51
Device Limitations and Restrictions .................52
Master Device Creation........................52
Building the Master Device ....................53
Sybase Mirroring ...........................54
What Should You Mirror? .....................56
Disabling Mirroring ........................57
Remirroring ............................59
Software- and Hardware-Level Mirroring .............59
RAID .................................60
Volume Management .........................60
Maintenance and Ease of Use ...................60
Load Balancing...........................61
System Info ..............................61
System Procedures .........................61
System Tables ...........................63
vi
Contents
Disaster Recovery...........................64
For Geeks Like Us ..........................65
Sysdevices .............................65
Summary ...............................69
Chapter 4 Databases .......................71
System Databases ...........................72
Other Sybase-Provided Databases .................74
Creating a Database..........................75
The create database Command...................75
Creating a Default Database ....................76
Creating Database Logs on Separate Devices ...........77
Using the create database Command ...............78
Sizing a Database ...........................79
Altering a Database ..........................80
Database Options ...........................81
Setting Database Options .....................82
sp_helpdb System Procedure ...................83
Dropping a Database .........................84
Database Ownership .........................85
System Tables .............................85
sysdatabases ............................86
sysdevices .............................86
sysusages ..............................87
Who Can Create a Database .....................89
Summary ...............................89
Chapter 5 Database Logging and Recovery ........91
Transactions ..............................91
The Transaction Log ........................92
Transaction Modes .........................92
Transaction Control Statements ..................94
Checkpoint ..............................99
Recovery ...............................100
Recovery Interval .........................101
When the Transaction Log Is Full..................102
Thresholds ............................107
Suspended Transactions .....................108
Automatically Truncating the Log ................108
More Fun for Bits and Bytes Geeks.................109
Summary ...............................109
Contents
vii
Chapter 6 Security ........................111
ASE Security Levels.........................111
Operating System-Level Security ..................112
Server-Level Security ........................113
Requiring Digits in Password...................114
Special Logins/Users .......................114
Standard Roles ..........................116
Dropping Logins .........................119
The syslogins Table ........................119
sp_displaylogin ..........................119
sp_modifylogin ..........................120
Who Is Logged into the Server? .................121
The Kill Command ........................122
Database-Level Security.......................123
Guest User ............................124
Aliases ..............................124
Information on Users and Aliases ................125
Access Sequence .........................126
Database Access .........................127
Groups ..............................127
The sysusers Table ........................129
Transporting Databases ......................130
System Built-in Functions ....................131
Command Security ........................132
Object-Level Security ........................133
With Grant Option ........................134
Revoking Object Access .....................134
Granting Vast Permissions ....................135
Ownership Chains ........................135
Displaying Permissions ......................136
setuser Command .........................137
Fine-Grained Access Control (FGAC) ...............137
Access Rules ...........................137
Granting the set proxy Command ................142
The System Roles.........................143
User-Defined Roles ........................145
Column Encryption .........................152
Components of Encryption ....................152
Enabling Encryption .......................152
The sysencryptkeys Table ....................153
Creating New Tables with Encryption ..............154
Altering Existing Tables .....................155
viii
Contents
Using select into with Encryption ................155
Ciphertext Randomization ....................155
Performance Considerations ...................156
Joins ................................157
Decryption Permissions......................157
Integrated Security Overview ....................158
Summary ...............................158
Chapter 7 Auditing........................159
Auditing Overview .........................159
Installation of Auditing ......................160
The Sybsecurity Database and Tables................161
Sybsecurity Transaction Log ...................164
Sybsecurity Stored Procedures ..................164
The Audit Queue...........................168
Audit Database Storage Requirements ...............168
Creating Audit Tables ........................171
Single-Table Auditing ........................171
What to Do When the Audit Table Is Full ............172
Archiving Audit Records ......................173
Querying the Audit Trail ......................174
Using Audit to Determine Interactions with Third-Party
Software ...............................176
Additional Tips for Effective Auditing ...............177
Summary ...............................178
Chapter 8 Backing Up and Restoring............179
Roles and Responsibilities .....................180
How Often Should You Back Up a Database? ...........180
Backup Types ............................181
Backup Scenario ...........................182
Restore Scenario ...........................183
Backup Server ............................183
Remote Backup Server ......................184
Server Identification .......................185
Starting a Backup Server .....................185
Dump Devices ............................186
sp_addumpdevice .........................186
Dumping the Database ......................187
Dump Compression........................189
Dump Database Summary ....................191
Automatic Backups .........................192
Loading the Database ........................192
ix
Contents

Get Administrator's Guide to Sybase ASE 15 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.