Administrator's Guide to Sybase ASE 15

Administrator's Guide to Sybase ASE 15

by Jeffrey Garbus, Ashish Gupta
Released April 2006
Publisher(s): Jones & Bartlett Learning
ISBN: 9781449612740

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description


Administrator's Guide to Sybase ASE 15 is a practical systems administration guide for the newest release of the ASE product. Designed to provide all administrators with an authoritative guide, topics include installation, defining physical and Sybase-mirrored devices, transaction logging and modes, security, auditing, database backup and restoration, troubleshooting, maintenance, and more. After reading this book, you should be able to perform any task that encompasses the creation or maintenance of a server.
With this book any administrator will understand the responsibilities of a system administrator; find out how to set limits on server resources using the Resource Governor; learn how to prioritize tasks with the Logical Process Manager; use data partitioning to enable finer control over data placement, maintenance, and management.

Table of contents

  1. About the Authors
  2. Contents (1/2)
  3. Contents (2/2)
  4. Introduction
  5. Chapter 1: System Administration Overview
    1. Architecture Overview
    2. Responsibilities of the Sybase System Administrator
      1. Installing the Sybase Adaptive Server
      2. Establishing Connectivity between Clients and Servers
      3. Installing and Maintaining User Databases
      4. Establishing Security for Adaptive Server Enterprise
      5. Maintaining and Troubleshooting Your ASE
      6. Establishing Disaster Recovery Procedures
      7. Monitoring ASE
      8. Use of Utilities
    3. Databases
    4. System Tables Overview
      1. System Tables in Special Uses Databases
      2. Sybase System Stored Procedures
  6. Chapter 2: Adaptive Server Installation
    1. Step 1: Preparation
      1. Logical Page Size
      2. Physical Devices
      3. Server Names
      4. Networking Information
      5. Sybase Software Asset Manager
      6. Sort Order
      7. SSL in Adaptive Server
    2. Step 2: File Transfer
    3. Step 3: Running Installation (1/2)
    4. Step 3: Running Installation (2/2)
      1. Interfaces File
      2. Runserver File
      3. Errorlog File
      4. servername.cfg — Server Configuration File
      5. Environmental Variables
    5. Step 4: Front-End Installation
      1. ODBC
    6. Starting the Server
      1. Normal Unix Startup
      2. Normal NT Startup
      3. Manual Unix Startup
      4. Automatic System Boot
    7. Verifying the Server Is Running
      1. Adaptive Server for Unix
      2. Adaptive Server for NT
    8. Shutting the Server Down
      1. Graceful Shutdowns
      2. Shutdown with nowait
      3. Maintenance Shutdowns
    9. Global Variables
  7. Chapter 3: Defining Physical Devices
    1. The Need for Devices
      1. Logical vs. Physical Devices
      2. Performance Implications
    2. Creating and Dropping Devices
      1. Creating Database Devices
      2. Creating Default Devices
      3. Dropping Database Devices
    3. Raw vs. Filesystem
    4. Dsync Option
    5. Device Limitations and Restrictions
    6. Master Device Creation
      1. Building the Master Device
    7. Sybase Mirroring
      1. What Should You Mirror?
      2. Disabling Mirroring
      3. Remirroring
      4. Software- and Hardware-Level Mirroring
    8. RAID
    9. Volume Management
      1. Maintenance and Ease of Use
      2. Load Balancing
    10. System Info
      1. System Procedures
      2. System Tables
    11. Disaster Recovery
    12. For Geeks Like Us
      1. Sysdevices
    13. Summary
  8. Chapter 4: Databases
    1. System Databases
      1. Other Sybase-Provided Databases
    2. Creating a Database
      1. The create database Command
      2. Creating a Default Database
      3. Creating Database Logs on Separate Devices
      4. Using the create database Command
    3. Sizing a Database
    4. Altering a Database
    5. Database Options
      1. Setting Database Options
      2. sp_helpdb System Procedure
    6. Dropping a Database
    7. Database Ownership
    8. System Tables
      1. sysdatabases
      2. sysdevices
      3. sysusages
    9. Who Can Create a Database
    10. Summary
  9. Chapter 5: Database Logging and Recovery
    1. Transactions
      1. The Transaction Log
      2. Transaction Modes
      3. Transaction Control Statements
    2. Checkpoint
    3. Recovery
      1. Recovery Interval
    4. When the Transaction Log Is Full
      1. Thresholds
      2. Suspended Transactions
      3. Automatically Truncating the Log
    5. More Fun for Bits and Bytes Geeks
    6. Summary
  10. Chapter 6: Security
    1. ASE Security Levels
    2. Operating System-Level Security
    3. Server-Level Security
      1. Requiring Digits in Password
      2. Special Logins/Users
      3. Standard Roles
      4. Dropping Logins
      5. The syslogins Table
      6. sp_displaylogin
      7. sp_modifylogin
      8. Who Is Logged into the Server?
      9. The Kill Command
    4. Database-Level Security
      1. Guest User
      2. Aliases
      3. Information on Users and Aliases
      4. Access Sequence
      5. Database Access
      6. Groups
      7. The sysusers Table
      8. Transporting Databases
      9. System Built-in Functions
      10. Command Security
    5. Object-Level Security
      1. With Grant Option
      2. Revoking Object Access
      3. Granting Vast Permissions
      4. Ownership Chains
      5. Displaying Permissions
      6. setuser Command
    6. Fine-Grained Access Control (FGAC)
      1. Access Rules
      2. Granting the set proxy Command
      3. The System Roles
      4. User-Defined Roles (1/2)
      5. User-Defined Roles (2/2)
    7. Column Encryption
      1. Components of Encryption
      2. Enabling Encryption
      3. The sysencryptkeys Table
      4. Creating New Tables with Encryption
      5. Altering Existing Tables
      6. Using select into with Encryption
      7. Ciphertext Randomization
      8. Performance Considerations
      9. Joins
      10. Decryption Permissions
    8. Integrated Security Overview
    9. Summary
  11. Chapter 7: Auditing
    1. Auditing Overview
      1. Installation of Auditing
    2. The Sybsecurity Database and Tables
      1. Sybsecurity Transaction Log
      2. Sybsecurity Stored Procedures
    3. The Audit Queue
    4. Audit Database Storage Requirements
    5. Creating Audit Tables
    6. Single-Table Auditing
      1. What to Do When the Audit Table Is Full
    7. Archiving Audit Records
    8. Querying the Audit Trail
    9. Using Audit to Determine Interactions with Third-Party Software
    10. Additional Tips for Effective Auditing
    11. Summary
  12. Chapter 8: Backing Up and Restoring
    1. Roles and Responsibilities
    2. How Often Should You Back Up a Database?
    3. Backup Types
    4. Backup Scenario
    5. Restore Scenario
    6. Backup Server
      1. Remote Backup Server
      2. Server Identification
      3. Starting a Backup Server
    7. Dump Devices
      1. sp_addumpdevice
      2. Dumping the Database
      3. Dump Compression
      4. Dump Database Summary
    8. Automatic Backups
    9. Loading the Database
      1. Loading a Corrupted Database
      2. Online Database
      3. Load Database Summary
    10. Creating a Database for a Restore
    11. Dumping Transactions
      1. Dump Transaction Options
      2. Dump Transaction Activities
    12. Loading Transactions
    13. Up-to-the-Minute Recovery
      1. Load Transaction
      2. Steps
    14. Point-in-Time Recovery
    15. sp_volchanged
    16. Monitoring the Log
      1. Last-Chance Threshold
      2. Free-Space Thresholds
      3. Aborting vs. Suspending Transactions
    17. Restoring the master Database Step by Step
    18. Quiesce Database
      1. Warm Standby
    19. Database Recovery Scenarios
      1. Answers for the Scenarios
    20. Summary
  13. Chapter 9: Resource Governor
    1. Enabling Resource Limits
    2. Time Ranges
      1. Adding Time Ranges
      2. Simplifying the Creation of Time Ranges
      3. Modifying Time Ranges
      4. Removing Time Ranges
    3. Creating a Limit
      1. Choosing a Limit Type
      2. Choosing the Type of Enforcement
      3. Choosing an Action
      4. Choosing a Scope
    4. Limit Hierarchies
      1. Examples of Limits
      2. Viewing Limits
      3. Changing Limits
      4. Removing Limits
    5. System Tables
      1. spt_limit_types
      2. systimeranges
      3. sysresourcelimits
    6. Summary
  14. Chapter 10: Logical Process Manager
    1. What Is the Logical Process Manager?
    2. Logical Process Manager Execution Classes
      1. Base Priority
      2. Engine Affinity
      3. Timeslice (Quantum)
    3. Logical Process Manager Objects
    4. Logical Process Manager Procedures
      1. sp_addexeclass
      2. sp_dropexeclass
      3. sp_bindexeclass
      4. sp_unbindexeclass
      5. sp_setpsexe
      6. sp_clearpsexe
    5. Engine Groups
      1. sp_addengine
      2. sp_dropengine
      3. More Detailed Examples
    6. Logical Process Manager Conflicts and Precedence
      1. Important Exception to the Precedence Rule
    7. General Recommendations
  15. Chapter 11: Memory Configuration and Tuningand
    1. Pre-12.5 Memory Utilization
    2. Memory Utilization, 12.5 and Later
      1. Logical, Physical, and Max Memory
    3. Configuration System Tables
    4. sp_configure Output (1/4)
    5. sp_configure Output (2/4)
    6. sp_configure Output (3/4)
    7. sp_configure Output (4/4)
      1. Displaying Configuration Values
      2. Configuration File
    8. Dynamic vs. Static Options
    9. Memory-Related Variables
    10. Recommended Configuration Settings
      1. Sizing Cache
      2. Memory Guidelines
      3. Calculating Procedure Cache
      4. Sample Server Configuration
    11. Summary
  16. Chapter 12: Named Caches
    1. Data Cache
    2. Tuning Cache
    3. Benefits of Named Caches
    4. Creating a Named Cache
    5. Buffer Pools
      1. Creating Buffer Pools
      2. Using Buffer Pools
      3. Wash Area
    6. Binding
      1. Binding to a Named Cache
      2. Dropping Cache Bindings
      3. Retrieving Information on Bindings
    7. Transaction Performance and Named Caches
      1. Spinlocks
    8. Cache Notes
      1. Sizing a Cache for a Transaction Log
      2. Benefits of Assigning tempdb to Its Own Data Cache
      3. Creating and Configuring Caches and Pools
      4. Guidelines for Cache Configuration
    9. Tuning Ideas
    10. Summary
  17. Chapter 13: Data Partitioning in ASE 15
    1. What Is Data Partitioning?
    2. Why Use Data Partitioning?
      1. Reducing the Cost of Managing and Maintaining Databases
      2. Data Availability
    3. Index Partitioning
    4. How and When to Use Data Partitioning
    5. Summary
  18. Chapter 14: Remote Server Management
    1. Remote Access
      1. Server Naming
      2. Configuring the Server to Allow Remote Access
      3. Login Mapping
    2. CIS — Component Integration Services
      1. Remote Server
      2. Local Storage
      3. Proxy Databases
      4. Remote Access Setup Example
      5. Using CIS for Remote Procedure Calls
    3. ASE Variable Page Size Issues
    4. Creating Tables from System Files
    5. File Access
    6. Summary
  19. Chapter 15: Preventative Maintenance Regimen
    1. Periodic Maintenance
    2. Server-Level Maintenance
      1. Activity Monitoring
      2. MDA Tables
      3. What to Monitor
      4. Monitoring the System Errorlog (1/2)
      5. Monitoring the System Errorlog (2/2)
      6. Resource Verification
      7. Software Maintenance
      8. Recording Run-Time Data
    3. Database-Level Maintenance
      1. Scheduling Database Maintenance
      2. How to Run dbcc (1/2)
      3. How to Run dbcc (2/2)
      4. Understanding the Output from dbcc Commands
      5. Using dbcc checkstorage (1/2)
      6. Using dbcc checkstorage (2/2)
      7. Maintaining the dbccdb Database
      8. Generating Reports from dbccdb (1/2)
      9. Generating Reports from dbccdb (2/2)
      10. Database Dumps
      11. Disaster Recovery Plan
      12. Log Management
      13. Space Management
      14. Script Maintenance
      15. Verifying Dumps
    4. Table-Level Maintenance
      1. Updating Statistics
      2. Indexes
    5. Summary
  20. Chapter 16: Troubleshooting
    1. The Server Will Not Come Up
    2. Some Users Claim the Server Stopped
    3. Server Is Up, Some Users Cannot Gain Access
    4. Processing Slows Down or Stops
    5. Some Database or Databases Cannot be Accessed
    6. Users Cannot Access Objects
    7. Summary
  21. Chapter 17: Tools
    1. bcp
    2. defncopy
    3. optdiag
    4. Sybase Central
    5. Interactive SQL
    6. Summary
  22. Appendix A: CSPDBA Sample Test (1/2)
  23. Appendix A: CSPDBA Sample Test (2/2)
  24. Appendix B: CSPDBA Sample Test Answers (1/4)
  25. Appendix B: CSPDBA Sample Test Answers (2/4)
  26. Appendix B: CSPDBA Sample Test Answers (3/4)
  27. Appendix B: CSPDBA Sample Test Answers (4/4)
  28. Appendix C: Tips (1/11)
  29. Appendix C: Tips (2/11)
  30. Appendix C: Tips (3/11)
  31. Appendix C: Tips (4/11)
  32. Appendix C: Tips (5/11)
  33. Appendix C: Tips (6/11)
  34. Appendix C: Tips (7/11)
  35. Appendix C: Tips (8/11)
  36. Appendix C: Tips (9/11)
  37. Appendix C: Tips (10/11)
  38. Appendix C: Tips (11/11)
  39. Appendix D: Other Resources
  40. Index (1/2)
  41. Index (2/2)

Product information

  • Title: Administrator's Guide to Sybase ASE 15
  • Author(s): Jeffrey Garbus, Ashish Gupta
  • Release date: April 2006
  • Publisher(s): Jones & Bartlett Learning
  • ISBN: 9781449612740