Sybase DBA Companion [Multiple-component retail product]

Preface.


Acknowledgements.


1. SQL Server Overview.


Introduction. SQL Server Versions. SQL Server 4.9.2. SQL Server System
10.
SQL Server System
11. Microsoft SQL Servers 4.2 and 6.0. Future Versions of
SQL Server. RDBMS Concepts. RDBMS Concepts. Relational. (Structured Query
Language SQL). Database Objects. NULL Values. Transactions. Recoverability.
Locking. Multi-user. RDBMS Concepts and SQL Server 4.9.2. Server. Databases.
Operations. Scalability. Performance. Replication. Backup Server.
Compatibility. Roles. New System. Databases. Security. SQL Server System
11.
Multiple Processors. Named Caches. Configurable I/O size. Transaction Log.
Partitioned Tables. Server Monitoring and Tuning. Compatibility. SQL Server
Future Releases. Scalability. Performance. Operational. Decision Support.
Distributed Database. Pure Speculation. Summary. Points to Remember.



2. System 11 Features.


System 11 Features. Scalability. Named Caches. Configuration. DBA Features.
If You Do Nothing...System 11 New Features. Things That System 11 Still
Doesn't Do. Summary.



3. System 11 Scalability.


System 11 Multiple Network Engines (MNE). SQL Server Engines. System 11 and
Network I/O. Server Engines and User Connections. Optimal Number of Engines.
Engine Scheduling in SQL Server. Uniprocessor Server Machine Platforms.
System 11 Transaction Log. User Log Cache. Transaction Log Buffer Pool.
Oldest Outstanding Transaction. Transaction Log Issues. Examples. User Log
Command Syntax. Lock Management and System 11. Dirty Reads. Configurable Lock
Promotion. Parallel Lock Manager.



4. System 11 Named Caches.


Buffer Manager and Named Caches. Introduction. Overview of Pre-Systemm 11
Usage. Named Cache. Default Data Cache. Database Objects Bound to a Named
Cache. Hash Tables. Syntax for Creating, Modifying, and Deleting Named
Caches. Named Cache Issues. Which Objects to Bind. Examples. Named Caches
Command Syntax. Buffer Manager and Large I/Os. Buffer Pools. When to Use
Large I/Os. Creating Buffer Pools to Enable Large I/Os. Buffer Pool Details.
Large I/O issues. Examples. Buffer Pools Command Syntax. Query Optimization
and the System 11 Buffer Manager. Introduction. Prefetch Cache Strategy.
Fetch-and-Discard Cache Strategy. Query Optimization and Cache Strategy.
Cache Strategy Issues. Query Optimization Improvements.



5. Configuring SQL Server System
11.


Configuration File. Advantages of Configuration Files. Recovery and
Configuration Files. Configuration File Issues. Configuration Files and Named
Caches. Configuration File Structure. Server Startup Configuration File
Errors. sp_configure. Overview of Pre-System 11 Usage. Permissions. Syntax.
Configuration File Read Errors. Staying Out of Trouble with the read
Subcommmand Output Hierarchy. sp_configure Output. sp_configure and the
read and verify Subcommands. Output Value. Compatibility. SQL Server
Errorlog MessagesŅsp_configure. SQL Server Errorlog MessagesŅsp_configure
with Configuration File. Overall Server Configuration.



6. System 11 DBA Features.


Database Dumps. Loading System 10 Database Dumps. Database Off-line/On-line.
Examples. Database On-line/Off-line Command Syntax. sp_sysmon. Introduction.
Overview of Use. System Tables. sysattributes. syspartitions. syslogshold.
Table Partitioning. Table Partitioning. When to Use. Creating a Partitioned
Table. Restrictions. Issues.



7. The System Databases.


System Databases. sybsystemprocs Database. sybsecurity Database. master
Database. The master Database and Master Device. Sizing the Master Device.
The logsegment of the master Database. Master Device Name and Mirroring.
Master Device and disk init. Master Device and Default Server Disk Devices.
Loading a Database Dump of the master Database. Moving Master Device to
Larger Partition. Clearing Server Configuration in master Database. System
Databases and Server Devices. Mirroring of System Databases.



8. Physical Server Design.


Introduction. SQL Server Versions. SQL Server 4.9.2. SQL Server System
10.
SQL Server. System
11. Overall Server Setup. Port Numbers. Device Names.
Physical Disks. Standard Disk Partitioning. Disk Partitioning and Sun
Operating System Issues. Disk Partitioning versus Physical Disk Size. Raw
Partitions versus File Systems. SQL Server Logical Disk Devices. Disk
Partitioning. Disk Controllers. Database Location on Disks and Controllers.
Transaction Log. Locating Multiple Databases. Locating the System Databases.
Initializing Server Devices. Database Segments. Why Segments Exist. The
Transaction Log Segment. Large Object on a Separate Segment. Segments and the
create database Server Command. Segments and the alter database Server
Command. Creating a User-Defined Segment. Segments and Capacity Planning.
Addition of Space to a User-Defined Segment. Final Notes on Segments.
Transaction Log Issues. Transaction Log on Separate Server Device.
Transaction Log Not on Separate Device. Sizing the Transaction Log. Mirroring
Server Devices. How to Lay Out the Devices and Seg ments of a Server. Why You
Should Be Hesitant to Add Database Space. Summary.



9. SQL Server Recovery.


Introduction. SQL Server Versions. SQL Server 4.9.2. SQL Server System
10.
SQL Server System
11. Recovery Planning Revolves around the Cost of Downtime.
The Database Is Nothing without the Transaction Log. Recovery Is Transaction
Based. Standby Server Strategies. master Database Not for Users. The Use of
dbcc. Mirroring. Archiving. More Server Devices Is Better. Recovery Notes.
Backup Server. Database Dumps. Transaction Log Dumps. Logical Dumps and
DataToolsÕ SQL BackTrack. Recovery Matrix.



10. Performance and Tuning.


Introduction. SQL Server Versions. SQL Server 4.9.2. SQL Server System
10.
SQL Server System
11. Using sp_sysmon. Step-by-Step Use of sp_sysmon.
sp_sysmon. Kernal Utilization. Task Management. Transaction Management. Lock
Management. Data Cache Management. Procedure Cache Management. Recovery
Management. Disk I/O Management. Output of sp_sysmon. Cache Configuration
Suggestions. Theory Is Nice. What You Can Do in the Real World. Indexes and
Queries. Spreading Segments over Server Devices. Spreading Individual Tables.
Archiving. Decision Support Server. Standard Set of Application Transactions.
SQL Monitor. Other Built-in SQL Server Performance Tools. Application
Transparent Server Tuning. Preventing Downtime.



11. Capacity Planning.


Introduction. SQL Server Versions. The Overall Database System. The
Individual Server. Real World ExampleThe Overall. Database System. Real
World ExampleThe Individual Server. Capacity Planning for the Database
System.



12. Operational Details of SQL Server.


Introduction. SQL Server Versions. SQL Server 4.9.2. SQL Server System
10.
SQL Server System
11. Thresholds. Interfaces File. Converting SunOS
Interfaces File to Solaris. Solaris Interfaces File. Converting From Solaris
Interfaces File Entry to SunOS. Converting From SunOS. Interfaces File Entry
to Solaris. SunOS Interfaces File. Communication between Servers. Creating
Stored Procedures from SQL Script or defncopy Output. What sysusages Means.
Objects on a Segment. Reading the Errorlog. Modifying System Tables Manually.
bcp command. Free Space in a Database. Full Transaction Log or Other Segment
(1105 Error).



13. Installing and Upgrading.


SQL Server. Introduction. SQL Server Versions. SQL Server 4.9.2. SQL Server
System
10. SQL Server System
11. Installing SQL Server. Preparing for
Installation. Installing SQL Server 4.9.2. Installing SQL Server System
11.
Installing SQL Server System
10. Post-Installation Tasks. Installation
Errors. Upgrading SQL Server Overview. Introduction. SQL Server Versions.
Installation Guide, Release Notes, and EBF Cover Letter. Sybase Technical
Support. Risks of the Upgrade Process. EBF Upgrades. Version Upgrades.
Preparing to Upgrade 4.9.2 to System 11 (or System 10). Upgrading 4.9.2 to
System 11 (or System 10). Post Upgrade 4.9.2 to System 11 (or System 10).
Regressing from Failed System 11 (or System 10) Upgrade. Upgrading SQL Server
4.9.2 to System
10. Upgrading SQL Server System 10 to System 11Full Upgrade.
Upgrading SQL Server System 10 to System 11Loading System 10 Database
Dumps.



14. Scripts.


General Scripts. System 11 Production Scripts. SQL Server Versions. SQL
Server 4.9.2. SQL Server System
10. SQL Server System
11. Dump Database
Transaction Log (dumplog). Dump Databases for SQL Server 4.9.2 (dumpdb_492).
Warning. Load Databases for SQL Server 4.9.2 (loaddb_492). Update Statistics
on All Tables. update_statistics_all_tables). Dump Database Creation Script
(dump_db_create). Run dbcc Checks (checkdb). Dump Database Creation Script
(dump_db_create). Run dbcc Checks (checkdb). Dump Data from System Tables
(dump_systables) Stored Procedure to Dump Database Creation Script
(p_dbcreate). Stored Procedure to Check Mirrors (p_mirror). Stored Procedure
to Output Device Space (p_devspace). Stored Procedure to Output All Segments
on Devices (p_servermap). Dump Databases (dumpdb). Load Databases (loaddb).
Create Stored Procedure to Monitor Database Load (p_dbload). SQL Server
Startup Script. System 11 Dump Database (dump_list_dbs). Dump Transaction Log
(logdumb_listof_dbs). Truncate Transaction Log (trunclog_listof_dbs). Remove
Old F iles (remove_old_files). Update Statistics (update_listof_dbs). Update
Statistics (update_listof_dbs). Executing dbcc (dbcc_listof_dbs). Scanning
Server Errorlog (scan_errorlog). Dumping Server Configuration
(dump_server_config). Monitor Report (monitor_report). Execute sp_sysmon
(execute_sp_sysmon). Server Auto-Restart. Crontab Entries for Scripts. Index.