Sybase Database Administrator's Handbook

Want to read this on your NOOK? Request as NOOK Book from the publisher

Thank you for requesting this book as a NOOK book from the publisher.

More About This Book

Overview
Editorial Reviews
Product Details
Related Subjects
Table of Contents
Preface

Overview

This is the first book that shows Sybase database administrators exactly how to manage complex multi- SQL Server environments. Helps you plan and document your server environment. Shows how to plan databa sesegments, lay out disks, controllers and disk partitions for optimal performance and reliability. Presents detailed procedures for day-to-day system maintenance, monitoring, and optimization. Presents upgrade strategies. Shows proven methods for database recovery, and how to troubleshoot communications problems among Sybase SQL servers. For Sybase database administrators, IS management, organizations that must train database administrators, IS management, other technical staff supporting or working with Sybase SQL, and for students of computer science and databases.

Read More Show Less

Editorial Reviews

Booknews

A guide to the decisions and procedures faced in moving from a single Sybase SQL server to a multi-server environment, for users familiar with the Sybase SQL Server 4.9x or System 10 on a UNIX operating system. Information is also included for users who are upgrading to System 11. It shows how to assess the organization's needs to design a multi-server system; offers practical advice for managing the transition and its impact on both computers and personnel; and provides post-transition advice on day-to-day operations, including installation, scripts, performance tuning, and utilities, as well as disaster recovery. Annotation c. Book News, Inc., Portland, OR (booknews.com)

Read More Show Less

Product Details

ISBN-13: 9780133574777
Publisher: Prentice Hall Professional Technical Reference
Publication date: 11/9/1995
Pages: 520
Product dimensions: 7.24 (w) x 9.61 (h) x 1.24 (d)

	Preface
	Acknowledgments
Ch. 1	Standard Server and Server Machine Environment	1
Ch. 2	Replication Server	47
Ch. 3	Installation of SQL Server from Scratch	73
Ch. 4	Physical Server Design	143
Ch. 5	Documenting a Server	209
Ch. 6	Physical Database Implementation	245
Ch. 7	The master Database Is Special	261
Ch. 8	SQL Server Recovery	275
Ch. 9	Performance and Tuning	329
Ch. 10	Capacity Planning	359
Ch. 11	Operational Details of SQL Server	391
Ch. 12	Database Administration Versus Database System Size	425
Ch. 13	Upgrading SQL Server	435
Ch. 14	Transitioning to System 10	495
Ch. 15	Scripts	511
Ch. 16	Recommended Reading	555
	Index	563

Read More Show Less

Preface

Preface

This book discusses the issues that anyone involved with the DBA team supporting a large multiserver environment needs to be aware of. Here we cover what is and is not discussed in the text.

This book discusses the issues that anyone involved with the DBA team supporting a large multi-server environment needs to be aware of. Here we cover what is and is not discussed in the text.

Purpose
Who Is This Book For?
What You Need to Know to Use This Book
What You Will Learn From This Book
Conventions
Terms
Versions of SQL Server
SQL Server System 11
Versions of Replication Server
Purpose
This book is intended for those persons who will take a small Sybase SQL Server 4.9.x or System 10 to a large Sybase SQL Server® 4.9.x or System 10 multiserver environment that will be administered by a team of DBAs.

Who Is This Book For?
Clearly, the DBA is one person that this book is intended for, but so is the manager who is trying to plan such growth and the financial officer who will have to pay for staff and hardware to support the system. The experiences described here will benefit DBAs with less experience, while the checklists and procedures will save the experienced DBA time.
It is assumed that the reader is familiar with the Sybase SQL Server and the commands used to control the server. The book does not provide a complete syntax for any of the server commands and as such is not intended as a beginning training text.
The Sybase courses are very good for this purpose, and this book is an excellent companion to the course material as it expands on, explains, and underscores the practical impacts of many things described in the course and the manuals.

What You Need to Know to Use This Book
This book is based on experiences with the Sybase SQL Server running on Sun® hardware using SunOS™ 4.1.3. The descriptions of the server and the procedures will work with the Sybase SQL Server on other UNIX® operating systems.
Every attempt has been made to make the discussion relevant to any Sybase SQL Server installation. When needed, commands or procedures that are specific to SunOS are identified as such.
You probably won't use and don't need to use all of the material presented all at once. Certain sections will be immediately relevant to your current situation. When a crisis hits, you can use the other sections as checklists of things to review as a way to isolate and fix the problems your system is experiencing.

What You Will Learn From This Book
The DBA who is responsible for the administration of a Sybase SQL Server will find the information directly applicable to his or her job. While portions of the discussions relate directly to the SunOS UNIX environment, the commands used are described in sufficient detail that the equivalent commands for virtually any UNIX platform could be readily identified.
With the growth in the databases themselves comes an increase in the number of database servers as well. While a single server was fine for the pilot project, you now need to consider the implications of a single server failure and how to implement a standby system. Users in various parts of your growing business will want their data on their own server, located near the primary users and maintained by their own DBA. All of this causes more servers, oftentimes containing much of the same data, that must all communicate with each other. The DBA must learn about the new issues relevant to dealing with multiple servers.
This book is designed for use in the real world. Procedures are laid out to be followed step-by-step. Sufficient detail is included to ensure the procedures can be used on a variety of systems by all members of the DBA staff, from the most senior to the latest recruit. Further, these procedures make the process of training a new person much easier.
Real-world observations are included throughout the text and are denoted by the ;-) symbol. These observations are included to provide motivation to readers to pay attention to the subject and apply what is presented to their own systems. Viewed another way, these observations provide stark evidence of the price you may well pay if you choose to avoid the advice presented. Without these observations it is easy to dismiss the instruction as too detailed and perhaps even paranoid.
Disasters do happen and real people get fired over them.
The manuals included with the Sybase SQL Server (including the CD- based products SyBooks and AnswerBase) are excellent and cover the topic of a single server very well. This is their purpose and their duty. However, as your system grows to even two servers, there are new problems that just aren't addressed in the manuals.
This book covers these issues in detail and as such provides a bridge between the manuals with their server-centric view of the world to your multiserver environment. While the manuals are invaluable for documenting and describing all the options for a given command, they won't even begin to help you understand the large database environment.
As your business grows and expands, you will need to be familiar with the System 10 family of Sybase products. Whether you upgrade your existing servers from 4.9.x to System 10 or install a new server at System 10, you will face the hurdles of upgrading and installing. System 10 has many new features, and the upgrade process is more involved than simply installing another EBF. This book will cover the upgrade process in detail and will cover the important differences between the 4.9.x and System 10 versions of the Sybase SQL Server.
As with the many Sybase products available, there are an endless number of permutations of hardware, operating system software, middle-ware, and so on, that you may have in your environment. We make no attempt to address the issues that would arise in any given multivendor environment. We cover in great detail the issues relevant to administering a Sybase multiserver environment running UNIX.
While the examples are taken from a SunOS environment, care has been taken to make these examples useful in any UNIX environment. Wherever possible, we avoid the use of commands or tools that are only available in the SunOS environment.
Further, the function of the SunOS commands used is covered in sufficient detail that it should be easy to determine the equivalent commands or options in the UNIX environment that you are using.
This book does not cover any theory of databases, nor does it compare the functionality of any competing products. We assume you have (or were hired into an environment that already has) selected Sybase SQL Server. If you are in the process of selecting the DBMS for your environment, this book will assist you in determining what you need to have in place to maintain a large, multiserver Sybase system, but, it will not provide any comparisons to other DBMS vendors or their products. We assume you are at a point where you need information relevant to administering a Sybase environment, not in need of a survey of DBMS products.
Conventions

Sybase SQL Server commands are shown in the text in the following font:
disk init

Sybase SQL Server command syntax is shown in the following font:
sp_helpdb database_name sp_helpuser sa sp_addsegment segment_name, server_device sp_addsegment myseg0, server_device_1

Stored procedures supplied by Sybase as part of the SQL Server product are referred to as
sp_ sp_helpdevice

Stored procedures not supplied (or supported) by Sybase (described in Chapter 15 "Scripts") are referred to as
p_ p_devspace

The names of databases, segments, tables, and columns are shown in the following font:
master database system segment syslogins table segmap column

Operating system (OS) commands are shown in the following font:
dkinfo

Real-world experiences that you need to think about are preceded by
;-)

Terms
There are many terms that are used in the description of the Sybase SQL Server and its environment. Listed below are some terms that will be used throughout the book.
These terms are defined here to provide a standard set of definitions for the discussions that follow.

server
Within this book a server is a Sybase Server, and almost all the time it refers to a SQL Server. Occasionally the term "server" may refer to a Sybase Open Server such as the Backup Server that is part of the System 10 SQL Server.

server machine
Refers to the CPU, memory, disks and network interfaces that form the computer that actually executes the SQL Server.

server logical device (tape or disk)
A device as seen by the SQL Server. Note that SQL Server only knows about devices that are assigned to it and there isn't necessarily any direct correlation between a SQL Server device and a physical disk for example.

server device
Same as server logical device

spindle
Another term for a physical disk.

physical device
Refers to a physical disk or tape drive as opposed to a logical device, such as a parti-tion of a physical disk that is assigned to a server device.

SQL Server user `sa'
The SQL Server login that has ultimate authority and is used by the DBA.

server machine SA
The person who installs, maintains, and repairs the hardware and operating system software that supports the SQL Server and its associated products.

database system
The entire collection of server machines, SQL Servers, networks, clients, backup systems, and so on, that work together to support a business.

failover
The process of moving from a primary to a standby SQL Server. Also, the process of moving from a server device to its mirror.

Versions of SQL Server
While there are many differences between SQL Server 4.9.x and System 10, almost all of the material presented here applies to both versions. With the exception of the material that is specific to installing or upgrading 4.9.x (or System 10) specifically, all the other topics are applicable to both versions. Whether you are running 4.9.x or System 10, you need to be concerned with the issues and solutions regarding documenting a server, capacity planning, assigning segments to server devices, partitioning disks, and recovery.
Further, few environments support only 4.9.x or System 10 SQL Server. The typical environment supports both versions at the same time with new servers of both kinds being installed and some 4.9.x servers being upgraded to System 10.

SQL Server System 11
The next major release of the SQL Server is System 11. Although the details of the new features and command syntax are not official at this time, the important changes to the server are well established. We discuss the new features of System 11 that will affect the DBA and show how these new features relate to the material presented throughout this book. There will be many sources of the details of any new system tables or stored procedures once the product is officially released, but you can prepare for the new server version now by considering the important changes that are being made.
System 11 incorporates changes to the SQL Server to support thousands of concurrent users, high OLTP transaction volumes, very large databases, and the ability to add resources and see immediate improvements in performance for a server running on multiprocessor hardware. SQL Server System 11 is a performance release specifically aimed at improving SQL Server performance on Symmetric Multiprocessor (SMP) server machines. There is little new functionality but many changes to improve server performance. We review the changes listed in the table below, as well as the upgrade path to System 11.

Cache
Size of Disk I/O Units
Transaction Log
Table Slicing
Lock Management
Engines
Updates to Tables
Query Optimizer
Dirty Reads
Upgrading to System 11
Cache
SQL Server performance improves when more of the data pages needed to satisfy a query are already in the data cache. This caching reduces the amount of physical I/O the server must do. With the current server design, there is only one data cache for the entire server. Data pages that are needed for the current query(s) are brought in from disk, and pages that have been in cache are moved back to disk to make room.
If more pages are needed from disk than the cache has room for, then all of the data pages currently in cache will be moved back to disk, for example, when a large table is scanned. Thus, the data pages for frequently accessed tables, or other objects, are all back on disk and need to be reloaded into cache. This requires lots of physical I/O, which reduces server performance.
System 11 introduces named or dedicated caches. Instead of one cache for the server, you can create individual caches dedicated to specific database objects. That is, you can isolate objects that are most often accessed by an application and dedicate a portion of cache to store the objects. So, even if the server needs to load more pages into cache than there is room for, the dedicated caches that you have established will not be flushed from cache. This feature allows you to optimize cache much more than you can now. Also, you can set up dedicated caches with different buffer sizes depending on the needs of the objects to be stored in the cache. You will be able to specify smaller buffers for a cache supporting OLTP activity and larger buffers for cache that supports queries needing larger amounts of data. All of this reduces the physical I/O needed for your most important queries and prevents other users/applications from moving critical database objects out of cache.

Size of Disk I/O Units
Currently the server does all I/O in chunks of 2 Kb. Further enhancing the dedicated cache scheme, the System 11 server can support I/O in chunks up to 16 Kb. This scheme will improve server performance by reducing the physical I/O needed to move data from disk to cache and back.

Transaction Log
The existing SQL Server sends all entries for the transaction log through one process to be written to the transaction log for the database, so that multiple transactions in the database might be waiting for the one and only process to write their changes to the transaction log. The System 11 server will dedicate a portion of memory to storing the transaction log records of each transaction in the database.
When a transaction actually commits, the transaction log records that have been created in memory will be written to the database transaction log on disk. This process is transparent to the users and applications and can be performed by multiple processors at the same time. This feature will greatly reduce the performance bottleneck created by contention for the transaction log.

Table Slicing
Whenever a new row is inserted into a table, the current server places the new row on the end of the table on disk. All applications that are making inserts will therefore be contending for the last pages of the table on disk as they wait for locks to make their inserts. With System 11, as you create a new table, you can specify a number of par-titions or slices for that table. Then, as rows are inserted, they can be inserted into each of the slices, not just at the end of the table; multiple processors can each insert into the same table at the same time. This improves performance by eliminating contention and blocking for the last pages of the table.
Note that this new approach may require that you dump, drop, and recreate tables to benefit from this performance improvement.

Lock Management
Another bottleneck in the current server design is the way locks are managed. Since all locks are controlled by a single utility, all server users that need locks are forced to deal with, and wait for, the single lock manager to get to their request. In SQL Server 11, the process of lock management is done in parallel. The parallel lock manager will allow parallel access to various types of locks to further reduce contention among users within the server.

Engines
While the current server can utilize multiple engines, one engine was always assigned to dealing with all the network duties. This meant that there could be contention among the other engines as they waited for network access. System 11 is more flexible in that all the engines will be able to deal with network I/O requests, eliminating the contention for network access.

Updates to Tables
Currently, there are very limiting restrictions on the conditions under which the server will perform a row update in place. (Update in place means that under certain circumstances the server will make a row update without moving the row out on disk.) When these conditions are not met, the server performs a row update by deleting the existing row and reinserting the row with the updated value(s); the row will be added to the end of the table, just like a new row. The System 11 server removes all restrictions on update in place, which allows the server to avoid all the I/O involved with deleting existing rows and inserting them (moving them) at the end of the table.

Query Optimizer
The query optimizer is now more efficient in its need for and use of temporary tables to store intermediate results, that is, the server creates fewer and smaller temporary tables to satisfy subqueries. Further, the reports generated by the optimizer that tell you what the query plan is will be enhanced to better aid performance and tuning efforts.

Dirty Reads
When many concurrent users need to read and write to the database, lots of locks are held and many users are blocked, waiting for locks to be released. To reduce this contention, SQL Server 11 will allow you to specify ANSI SQL 92 Isolation Level 0, which means reads do not acquire locks of any kind. Users that need to read from a table won't acquire locks and, therefore, won't block users that need to write. The reverse would also be true. Depending on the application, this can greatly reduce the contention caused by locks held and users blocked.
Note the implications for your users. The fact that reads don't acquire locks means the table may be updated while you are reading; if you were to read from the table again, there is no mechanism to ensure that you will see the same results of the read. That is why the change is called allowing "dirty" reads, that is, reads from data pages that have changed but have not been committed. Since the read acquires no locks, it isn't clear whether or not a data page reflects a committed transaction. You must be sure that this ambiguity is acceptable for the applications that will read from the database.

Upgrading to System 11
Users will be able to upgrade from SQL Server 4.9.2 directly to System 11, but the upgrade path will involve the same steps and procedures that are currently involved with upgrading from 4.9.2 to System 10 (see "Upgrading SQL Server, Upgrading 4.9.x to System 10"). Since a database dump made from a 4.9.2 server can't be loaded into a System 10 server, the same situation will apply to upgrading a 4.9.2 server to System 11.

Versions of Replication Server
Chapter 8 "Replication Server" reflects the way the product was up to version 10.0.3, using rs_install and rs_setup_db for installation and adding databases to the replication system. With Replication Server Release 10.0.3, you use sybinit for installation and adding databases.
This change can be confusing, especially when you are using a 4.9.2 SQL Server as part of the replication system. In such a system, the SQL Server is installed and reconfigured (if needed) with sybconfig, but sybinit is used for Replication Server. If a System 10 SQL Server is also a part of your database system, then you would use sybinit for the System 10 SQL Server as well.
Replication Server Release 10.1 now uses rs_init for installation and addition of data-bases. The 10.1 release supports encrypting Replication Server user passwords.
Note, however, that if you are upgrading to Replication Server 10.1, the upgrading process, which can encrypt the passwords, does not delete any existing configuration files that contain the unencrypted passwords. You must manually delete any such files. Further, once you have used this feature of Replication Server 10.1 you cannot regress to a previous release of the product.

Read More Show Less

Customer Reviews

Be the first to write a review

( 0 )

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

If you find inappropriate content, please report it to Barnes & Noble

Sybase Database Administrator's Handbook

Overview

More About This Book

Overview

Editorial Reviews

Booknews

Product Details

Related Subjects

Table of Contents

Preface

Customer Reviews

5 Star

4 Star

3 Star

2 Star

1 Star