SQL Server数据库防御性编程

需积分: 1 100 浏览量更新于2024-07-27 收藏 3.23MB PDF 举报

"防御性数据库编程与SQL Server" 在数据库管理领域，SQL Server是一个重要的关系型数据库管理系统，由微软公司开发并提供。本资源聚焦于使用SQL Server进行防御性数据库编程，旨在帮助管理员和开发者构建更健壮、安全且性能优秀的数据库应用。防御性编程是一种预防性的编程策略，其目标是在代码编写阶段就考虑到潜在的问题，从而减少错误和漏洞的发生。在SQL Server中，这通常涉及到以下几个方面： 1. 安全性：确保只有授权的用户或应用程序可以访问数据库。这可以通过设置严格的权限和角色、使用参数化查询防止SQL注入攻击、以及定期更新和审核登录账户来实现。 2. 数据完整性：通过约束（如主键、外键、唯一性约束）和触发器来维护数据的准确性和一致性。同时，使用事务处理确保多步骤操作的原子性和一致性。 3. 性能优化：通过合理设计索引、使用存储过程、优化查询语句、监控和调整数据库配置参数来提升查询速度和系统响应时间。 4. 可恢复性：定期备份和日志管理是确保数据可恢复的关键。理解不同备份类型（如完整备份、差异备份、事务日志备份）及其恢复模式，并结合适合业务需求的策略进行实施。 5. 高可用性：利用SQL Server提供的高可用性技术，如镜像、故障转移群集、Always On可用性组等，保证服务的连续性和故障快速恢复。 6. 监控和性能分析：使用SQL Server自带的工具（如动态管理视图、性能计数器、SQL Server Profiler）进行性能监视，及时发现并解决性能瓶颈。 7. 错误处理和异常捕获：在T-SQL代码中使用TRY-CATCH结构来捕获和处理错误，确保程序的健壮性。 8. 更新和维护：保持SQL Server实例的最新状态，及时安装安全更新和补丁，以抵御新出现的威胁。 Alex Kuznetsov的《防御性数据库编程与SQL Server》一书，由Hugo Kornelis进行技术审查，详细阐述了这些主题，并提供了实践指导。这本书不仅适合SQL Server管理员，也对开发者和数据库架构师有很高的参考价值，帮助他们在实际工作中应用防御性编程原则，提高系统的稳定性和安全性。通过遵循书中的建议，可以显著降低因数据库问题导致的业务风险，提高整体IT系统的可靠性。

Introduction

• if handling errors on SQL Server, keep it simple where possible; set XACT_ABORT to

ON and use transactions in order to roll back and raise an error

• if you wish to use TRY…CATCH, learn it thoroughly, and watch out for problems such

as errors that cannot be caught, doomed transactions, the need to change the error

number when raising errors, and so on.

Ch. 09: Surviving Concurrent Queries (Paid editions only)

A query that works splendidly in isolation can often fail miserably when put to work in

a live OLTP system, with real life concurrency. To make a bad situation worse, in many

cases such errors are subtle and intermittent, and therefore very dicult to reproduce

and understand. This chapter considers the case of reporting queries running against

tables that are being simultaneously modiﬁed, demonstrates how inconsistent results

can be returned, assesses the impact of various isolation levels, and considers how best

the defensive programmer can defend data integrity, while minimizing deadlocks.

Ch. 10: Surviving Concurrent Modiﬁcations (Paid editions only)

Just like queries, modiﬁcations that work perfectly well in the isolated world of the

test database, can suddenly start misbehaving intermittently when run in a production

environment under conditions of concurrent access. The chapter covers some of the

problems that might occur when "competing" connections try to simultaneously update

the same data, and how to avoid them:

• lost modiﬁcations, a.k.a. lost updates – such problems occur when modiﬁcations

performed by one connection are overwritten by another; they typically occur

silently, and no errors are raised.

• resource contention errors – such as deadlocks and lock timeouts

• primary key and unique constraint violations – such problems occur when

diﬀerent modiﬁcations attempt to insert one and the same row.

Introduction

What this book does not cover

Throughout the book I stress the importance of creating testable and fully-tested code

modules. However, the focus of this book is on writing resilient T-SQL code, not on the

implementation of unit tests. In some cases, I will describe which unit tests are required,

and which checks must be wrapped as unit tests and must run automatically. However, I

will not provide any speciﬁc details about writing unit tests.

When many people think of defensive programming, they tend to think in terms

of vulnerabilities that can leave their code susceptible to "attack." A classic example

is the SQL Injection attack, and the coding techniques that reduce the likelihood

of a successful SQL Injection attack are excellent examples of defensive database

programming. However, there already are lots of very useful articles on this subject,

most notably an excellent article by Erland Sommerskog, T C  B 

D SQL. The focus of this book is on very common, though much less publicized

vulnerabilities that can aﬀect the resilience and reliability of your code.

Due to the ﬁrm focus on defensive coding techniques, there is also no coverage in this

book of what might be termed the "documentation" aspects of defensive programming,

which would include such topics as documenting requirements, establishing code

contracts, source control, versioning, and so on.

Finally, in this book I stay focused on practical examples. While some background

material is occasionally required, I've strenuously tried to avoid rehashing MSDN.

If you are not familiar with the syntax of some command that is used in the book, or you

are unfamiliar with some terminology, MSDN is the source to which you should refer.

Code examples

Throughout this book are code examples demonstrating various defensive programming

techniques. All examples should run on all versions of SQL Server from SQL Server 2005

upwards, unless speciﬁed otherwise. To download all the code samples presented in this

book, visit the following URL:

:.-.RGBAKD_C.

剩余301页未读，继续阅读

mysky322

粉丝: 0
资源: 1

SQL Server数据库防御性编程

40集SQL Server 基础入门视频教程 SQL Server 数据库基础入门必备课程

sql server2019安装包

SqlServer连接工具

sql server与c#软件连接

SQL SERVER 2005启动SQLserver失败

sql server 没有sql server跟踪器

sql server和sql server jdbc有什么区别

SQLserver 2000 对应的SQL Server Native Client 版本

SQL Server Management Studio如何连接SQL Server

sql server rs可以用sql server dev替代吗

最新资源