SQL Server数据库维护计划实战

需积分: 10 142 浏览量更新于2024-07-20 收藏 22.24MB PDF 举报

"Practical Maintenance Plans in SQL Server" 是一本由 Bradley Beard 撰写的书籍，专注于指导 SQL Server 数据库管理员如何设置和维护数据库维护计划。本书特别关注于超越简单的备份任务，强调全面确保数据的完整性和安全性。通过自动化 SQL Server Agent 的使用，读者将学习如何构建强大的、自动化的维护计划，从而对常见的数据库问题提供主动解决方案。书中涵盖了如何安排如备份和索引重建等常规任务自动运行，并提前预警可能出现的资源使用和查询性能问题。在 SQL Server 2016 的背景下，这本书提供了实用的技巧和策略，帮助 DBA 提升效率，确保数据库的稳定运行。书中的内容可能包括但不限于以下几个方面： 1. **SQL Server 维护计划基础**：介绍如何创建和配置维护计划，包括理解不同类型的维护任务，如备份、检查点、日志清理等。 2. **SQL Server Agent**：详述如何使用 SQL Server Agent 定时调度任务，以自动化日常维护工作，减少手动干预的需求。 3. **备份策略**：讲解如何设计和实施有效的备份策略，包括完整备份、差异备份和事务日志备份，以确保数据的安全和恢复能力。 4. **性能优化**：涵盖索引管理，包括重建和重新组织索引，以提升查询性能。还可能讨论监控和调整内存使用、磁盘I/O以及CPU资源。 5. **预警和通知**：介绍如何设置和配置警报系统，以便在资源使用过高或查询性能下降时，提前收到通知并采取措施。 6. **故障预防和恢复**：探讨如何进行数据库健康检查，以及在遇到问题时如何快速响应和恢复，包括使用数据库恢复模型和事务日志。 7. **最佳实践**：分享关于数据库维护的最佳实践，以防止常见问题，提高系统可用性，确保业务连续性。 8. **案例研究**：可能包含实际案例分析，展示如何应用这些维护策略来解决实际工作中的问题。 9. **监控和性能计数器**：教授如何使用 SQL Server 自带的监控工具（如 SQL Server Management Studio 和 Performance Monitor）来跟踪数据库的健康状态。 10. **安全性与审计**：讨论如何在维护计划中考虑安全因素，如备份加密和审计功能的设置，以保护敏感数据。通过阅读这本书，SQL Server 管理员可以掌握全面的维护计划知识，从基本任务到高级策略，提升他们的专业技能，确保公司数据的安全和高效运行。

About the Technical Reviewer

Mike McQuillan is a software and database specialist who lives with his

wife and daughter in the United Kingdom. Mike is a polyglot programmer

who began messing around with computers in the 1980s, first with an

Atari 800XL and then a Sinclair Spectrum. He took up databases in the

1990s, and quickly fell in love with SQL. He’s been working with SQL

Server since version 7 and he is an SQL Server MCSA.

When he’s not tinkering with computers, Mike and his family enjoy

lengthy walks around Cheshire with the family pups, Dolly and Bertie

(who keep his feet warm when he’s writing).

www.it-ebooks.info

Introduction

Have you ever wondered about what keeps your database running smoothly? We all hope the database

keeps running as well as it did as when it was first installed, but what really is happening under the covers?

More importantly, can we control what is happening? As it turns out, we certainly can control these events,

and they are extremely powerful tools that we can use to make sure that our databases stay in good shape.

This book will introduce you to the core concepts of creating a maintenance plan that will handle all

of the tasks necessary to keep your database functioning at 100%. There are exercises that you can run in

your own installation of SQL Server to ensure heightened data security and integrity by harnessing the

power of SQL Server Agent. With the proper amount of foresight and planning, database administrators of

any experience level will be confidently using the tools available in SQL Server to create extremely versatile

maintenance plans in a short amount of time.

What Is the Scope of this Book?

In the scope of this book, I am going to concentrate on using the SQL Server 2012 Management Studio

interface as much as possible. I am not going to get into a lot of scripting by hand; it will be point-and-click

as much as possible. There will be some areas where we will venture into the void, but I’ll keep that to a

minimum. To be clear, I will be working with the Maintenance Plan Wizard, and not from the Maintenance

Plan Design Surface. There will be times when I will focus directly on the design surface, but for the most

part, we will work with the wizard. Most of what I detail and outline here is transferable between the two

interfaces anyway, as you will soon see.

We are going to make a single maintenance plan that will automatically execute every day on a

set schedule. I am going to show you how we can manipulate the database engine into performing the

maintenance activities we choose on the schedule we choose. I am also going to show you how to expand

on the concept of reporting and logging, from the database point of view, to keep us aware of any possible

issues. In the end, we are going to have a maintenance plan that does everything we need it to do, period.

This will give us an assistant, so to speak, that runs these menial although important tasks without our

supervision. When I think of maintenance plans, I immediately think of the old Ron Popeil commercials

with “set it… aaaaaaand forget it!” That’s exactly what we do here. We are going to take the time to set it up

correctly, and examine the why and not just the how. We are then going to ensure that our reporting and

logging is set up correctly, so that we are aware of any issues. After that, it will be smooth sailing and you can

enjoy your newly augmented rock star DBA status.

Who Is this Book Written For?

This book was written for you, of course! It should be a refresher course for the majority of readers.

My hope is that this book will augment what you already know, and perhaps shed some light on some concepts

that you didn’t know. At a minimum, you should come away from this book with a clear understanding of

what a maintenance plan is, how it benefits a database, how to structure it correctly, how it runs, and how

to possibly make it run more efficiently. I would like to think that this isn’t the first you have read about

xix

www.it-ebooks.info

剩余287页未读，继续阅读

cuiyonghua

粉丝: 4
资源: 13

SQL Server数据库维护计划实战

[SQL Server] Microsoft SQL Server 2012 技术内幕 (英文版)

Practical SQL Queries for Microsoft SQL Server 2008 R2

The Cutting Edge of Big Data Analysis: The Practical Application of the Doris Database in the ...

[In-depth Exploration]: Implementing Advanced Query Techniques in MySQL with Python

Optimization Tips for Query Results in DBeaver

MySQL Triggers in Python: The Key to Automating Database Logic

Data Integrity Constraints and Index Optimization in MySQL with Python: Key to Improving Database ...

2000-2021年中国科技统计年鉴（分省年度）面板数据集-最新更新.zip

PPT保护工具PDFeditor专业版-精心整理.zip

Spring Boot Docker 项目：含项目构建、镜像创建、应用部署及相关配置文件，容器化部署.zip

最新资源