Learn SQL Server 7.0 Administration

To achieve this goal, SQL Server consultant Jeffrey Garbus provides detailed information about designing and implementing a preventive maintenance regimen in order to ensure ongoing reliability, performance, and preparedness for disaster recovery. The book opens by introducing basic concepts, then quickly progresses to such advanced topics as configuring SQL Server for optimum performance, monitoring activity on the server, using the Database Consistency Checker, and backing up and restoring the database. Updating index statistics, using wizards to automate tasks, and replicating data are also covered.

Garbus? book is for anyone who must learn to maintain a SQL Server.

Contents

Introduction                                       xiii
  Definitions and Concepts                         1
    Client/Server Concepts                         1
    Database Management System (DBMS)              2
    SQL Server                                     2
    Systems Administration                         2
    Database Administration                        3
    Enterprise Manager                             3
  A Preventive Maintenance Regimen                 7
    What Needs to be Maintained?                   7
      Server-level Maintenance                     8
      Database-level Maintenance                   14
      Database Backups                             15
      Transaction Log Backups                      20
      DBCC                                         21
      Space Management                             23
      Database Maintenance Plan Wizard             25
      Table-level Maintenance                      33
      Monitor Growth Changes                       34
      Update Statistics                            34
      Table and Index Fragmentation                36
      Indexes                                      36
      Index Tuning Wizard                          38
    Alerts, Jobs, and Operators                    39
    Your Maintenance Checklist                     39
  Configuration Values                             41
    Memory Configuration Options                   41
    I/O Configuration Options                      44
    Configuring Server Performance Using           45
    Windows NT Options
      Maximize Throughput                          45
      Configure Server Tasking                     45
      Configure Virtual Memory                     46
  Activity Monitoring                              49
    Monitoring Tools                               49
      SQL Enterprise Manager                       49
      NT Performance Monitor                       55
  Monitoring Your Error Log                        59
    Accessing the Error Log                        59
    Error Log Messages                             61
      Severity Level Error Messages                63
    Accessing the Application Log                  65
  Disaster Recovery Plan                           67
    What Can Go Wrong?                             67
    Let's Fix the Problem!                         70
      Disaster/Remedy Grid                         70
      Recommended Approach                         71
  The Database Consistency Checker (DBCC)          73
    DBCC Commands                                  73
      DBCC CHECKALLOC                              74
      DBCC CHECKCATALOG                            76
      DBCC CHECKDB                                 77
      DBCC CHECKFILEGROUP                          79
  Backing Up and Restoring Your Database           83
    Database Backups                               83
      Backup Types                                 84
      Backup Strategy                              85
      Backup Approach                              87
      Standardizing Your Backup Procedures         87
      Backing Up the Databases                     88
      Adding Your Backup Device                    93
      A Behind-the-Scenes Look: Database Backups   95
      Back Up the Database                         96
      Back Up the Transaction Log                  97
      A Behind-the-Scenes Look: Transaction Log    99
      Backups
      The Master Database                          100
    Restoring Your Database                        100
      If a Disaster Has Struck                     102
  Transaction Log Management                       105
    Truncating the Transaction Log                 105
  Database Space Management                        111
    Monitoring the Size of Your Database           111
  Updating Index Statistics                        117
    The Cost-Based Optimizer                       117
    Commands                                       118
      Update Statistics                            119
      SP_AUTOSTATS                                 121
      SP_UPDATESTATS                               123
      DBCC SHOW_STATISTICS                         124
  Index Maintenance                                127
    Rebuilding Your Indexes                        127
      DBCC DBREINDEX                               128
  Alerts, Operators, and Jobs                      131
    SQL Server Agent                               131
      Creating New Alerts                          133
      Creating New Operators                       136
      Creating New Jobs                            138
Appendix A Some Rules to Work By                   143
    Blocking Problems                              143
    Blocking Process/Timeout                       144
    Indexing                                       145
    Indexing/6.5 - 7.0 Upgrade                     145
    Overallocated Memory                           146
    Installing SQL Server                          147
    SQL Server Version                             149
    Transaction Log is Full                        149
Appendix B For Further Information                 153
Appendix C The Wizards                             155
    The Create Database Wizard                     156
    The Database Maintenance Plan Wizard           160
      Specify the Database Backup Plan             163
      Specify the Transaction Log Backup Plan      164
    The Create Login Wizard                        167
    The Management Wizards                         170
      The Backup Wizard                            170
      The Create Alert Wizard                      177
Appendix D Replication                             183
    Types of Replication                           183
      Snapshot Replication                         184
      Transactional Replication                    184
      Merge Replication                            185
      Push vs. Pull                                193
Index                                              199