Healthy SQL.pdf

(11992 KB) Pobierz
www.itbookshub.com
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
www.itbookshub.com
Contents at a Glance
About the Author ...................................................................................................xvii
About the Technical Reviewer ................................................................................xix
Acknowledgments ..................................................................................................xxi
Foreword ...............................................................................................................xxv
■
Chapter 1: Introduction to Healthy SQL .................................................................
1
■
Chapter 2: Creating a Road Map ..........................................................................
19
■
Chapter 3: Waits and Queues...............................................................................
43
■
Chapter 4: Much Ado About Indexes ...................................................................
81
■
Chapter 5: Tools of the Trade: Basic Training ....................................................
115
■
Chapter 6: Expanding Your Tool Set...................................................................
149
■
Chapter 7: Creating a SQL Health Repository ....................................................
187
■
Chapter 8: Monitoring and Reporting ................................................................
231
■
Chapter 9: High Availability and Disaster Recovery ..........................................
279
■
Chapter 10: Sur viving the Audit ........................................................................
323
Index .....................................................................................................................
369
vii
www.itbookshub.com
CHAPTER 1
Introduction to Healthy SQL
Microsoft SQL Server has become an eminent relational database management system (RDBMS) in the
marketplace. The SQL Server engine has come a long way from being just another RDBMS; it’s an end-to-
end business intelligence platform with built-in options for reporting; extract, transform, load (ETL); data
mining; and high availability/disaster recovery (HA/DR). Its rich feature set provides a comprehensive
solution for deploying, managing, monitoring, maintaining, auditing, reporting, and backing up and
restoring databases; building data warehouses; and more! You will learn all about this as you journey
through this book toward achieving healthy SQL.
Whether it’s small, medium, or large enterprise infrastructures, it is common for organizations to
have deployed multiple SQL Server instances in their environments, and as companies deploy various
applications, many have had to deal with what’s known as
SQL sprawl.
This is the 1:1 ratio explosion
of deploying every new application to its own SQL Server. The implementation of multiple SQL Server
instances has made it difficult for database administrators to manage and maintain them. It is not
uncommon to eventually embark on a SQL Server database consolidation project with the assistance of a
qualified SQL Server database professional.
Another common scenario is that third-party vendors will deploy their applications on back-end SQL
Server instances and leave their clientele to their own devices. They will install SQL Server even though it’s
doubtful that they know what the proper installation and configuration settings are. The goal of a vendor is
to install and set up its application without regard for the ongoing health or performance of SQL Server. This
is known as “Set it and forget it.”
In a situation of “Set it and forget it,” as the application usage increases, as new records are continuously
inserted, updated, and deleted, and as users place demand on the application, response times will
eventually slow down, as will the overall performance of the SQL Server database back end. Indexes become
fragmented, excessive page splitting occurs, and backups are not properly configured.
All these issues that occur on the back-end SQL Server instance frustrate users, and organizations don’t
usually have the database administrator (DBA) skill set in-house to fix these performance problems. Often
the application vendor will not even have the right know-how or database expertise required to resolve these
issues. In these situations, there are a myriad of questions that companies and database professionals need
to ask themselves. For example, can SQL Server perform better? Can the application scale if it is necessary?
Do we have a recent backup? Is there a disaster recovery plan? Can the database be restored?
Enter the DBA
In addition to the previous questions, businesspeople, chief executive officers (CEOs), analysts, customer
service personnel, and the like, might even ask, “What is a DBA?” To a nontechnical CEO, the acronym DBA
traditionally stands for “doing business as.” According to Wikipedia, this means that the trade name, or
fictitious business name, under which the business or operation is conducted and presented to the world is
not the legal name of the legal person (or people) who actually owns the business and is responsible for it.
Obviously that’s not what we mean by DBA in this book.
1
www.itbookshub.com
CHAPTER 1
■
INTRODUCTION TO HEALTHY SQL
Most businesspeople today recognize the importance that technology plays in their business and the
need for people to manage that technology, but often the distinction blurs among the individual information
technology (IT) roles. People assume the appointed IT director, chief information officer (CIO), or chief
technical officer (CTO) will take care of all the IT needs, without ever coming in contact with the DBA.
Even at the department level, there is a balance between the business department and the IT
department. In reality, they depend on each other—the business to fund IT and IT to support the business.
The perception often is that the business needs are more important than the IT needs. The chicken-vs.-egg
question becomes, does the business drive IT, or does IT drive the business? It is my opinion that IT is the
most critical need because trying to run the business without IT, let alone the databases, is untenable.
Often in traditional corporate firms, IT serves as a “cost center” and supports the business. Every time
the IT department assesses that it needs more resources such as hardware, software, and human capital in
order to adequately support the business, the business in some cases perceives it as “whining again” for a
bigger budget and sucking capital out of the bottom-line profit.
However, the value of the IT department can be enhanced by making all of the IT functions “billable,” or
part of the charge-back system. Once IT becomes billable, the business is motivated to assign more work and
create new, often unnecessary projects because of individual budgets, and suddenly the value of everyone in
IT goes up.
The division of labor balances out, in that you will not blindly throw more work at your IT employees,
hire more employees, or raise the salary of those already employed. This also depends upon the size of
the organization. In smaller firms, newer, more modern CEOs are a bit savvier and willing to invest in IT
infrastructure and human resources to expand their business.
In my personal experience, once I took the helm of the database infrastructure as the organization’s
first official DBA. (Perhaps there should be a plaque somewhere with my name on it!) It was a well-known
financial services firm, with more than $75 billion in assets, and the CEO did not know what a DBA was. The
company chugged along with SQL sprawl in the form of exponential growth in its SQL Server infrastructure,
with a combination of overworked system administrators, developers, and even somewhat semi-technical
business application owners all managing the SQL Server instances. Obviously, this didn’t scale, and the
need for a DBA became abundantly clear.
So in sum, the DBA is the IT professional who performs all activities related to ensuring and
maintaining a healthy and secure database environment. The DBA has several responsibilities related
to the well-being of the database environment including, but not limited to, designing, implementing,
maintaining, and monitoring the database system. When a database system begins to perform sluggishly,
the DBA must be able to identify and resolve performance bottlenecks, as well as be able to do performance
tuning and optimization.
Moreover, the DBA must ensure the continuation of database functions, the integrity of the database,
and that databases are backed up regularly and are easily restorable. The DBA is instrumental in establishing
policies, procedures, and best practices, and in enforcing their use across the enterprise. As database
platforms have become more sophisticated over the years, DBAs have inherited more and more tasks and
responsibilities than ever before. Nowadays for example, you can be a hybrid DBA performing overlapping
database functions for your company. You’ll learn throughout this book about the roles and responsibilities
of the traditional DBA as defined here in this section.
Who Cares?
Why should you care about health checks? The answer is short and simple. If you are a database
administrator or anyone who is in charge of managing one or more SQL Server instances, then you should
care because you are the guardian of the company’s data. You are vested with the huge responsibility to
make sure that data is maintained, always available, easily retrievable, and quickly attainable.
If that data is lost, corrupted, compromised, or not available when needed, your neck is on the line and
the company’s business could be jeopardized. It is not an overstatement to suggest that billions of dollars are
at risk, as well as the very business that employs you, if that data is not properly guarded and secured.
2
www.itbookshub.com

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin