Real World SQL Server Administration with Perl [Paperback]

Linchi Shea (Author)

Book Description

Publication Date: July 2003 | Series: Real World

This book is about solving SQL Server administrative problems with Perl. Linchi has designed it to consist of a series of scenarios covering a wide range of SQL Server administrative topics. These are real world scenarios that you hear DBAs discussing on the SQL Server forums or newsgroups, and scenarios that DBAs run into in their day-to-day development or production environments. Readers will walk away from this book with two things: a DBA toolkit with well over 150 Perl scripts, and problem solving techniques you can apply to create even more tools for attacking other SQL Server administrative problems. Most of the sample problems are described and addressed in the context of SQL Server 7 or SQL Server 2000, but they are generally version independent. The next major release of SQL Server ("Yukon")may change some of the details, but will not invalidate the nature of the presented approaches or solutions. All the Perl scripts are completely open and can be easily adapted to work with any version of SQL Server.

Editorial Reviews

Review

From the reviews:

"This is aimed at the DBA who is already running an efficient SQL server, but who wants to be forearmed against any future problems. … The aim of the book is to provide a toolkit of around 150 Perl scripts that can then be adapted and applied to a wide variety of SQL server problems … . There are also appendices that list useful resources … . The book as a whole could be quite useful … ." (Linux Magazine, March, 2004)

"The book demonstrates the capabilities of Perl by presenting individual scenarios, each designed to highlight a particular useful feature and how it might be applied to common SQL Server administration tasks. … Overall, this is a well-rounded book providing a good balance of SQL Server administration task specific information, backed up with some useful Perl-specific text." (Adrian Hibbert, Computer Bulletin, March, 2004)

About the Author

Linchi Shea graduated with a bachelor’s degree in electrical engineering and computer science and a master’s degree in information systems from the University of British Columbia, Vancouver, Canada. Currently serving as a vice president of Merrill Lynch & Co., Shea is the SQL Server technical team lead and is active in many SQL Server communities via the SQL Server Worldwide User’s Group (http://SSWUG.org). When not automating and solving DBA problems with Perl, Shea is an avid tennis player and a regular reader of newspaper bridge columns. Linchi Shea is a Microsoft MVP.

Product Details

• Paperback: 832 pages
• Publisher: Apress; 1 edition (July 2003)
• Language: English
• ISBN-10: 159059097X
• ISBN-13: 978-1590590973
• Product Dimensions: 9.3 x 7 x 1.6 inches
• Shipping Weight: 2.8 pounds (View shipping rates and policies)
• Average Customer Review: 5.0 out of 5 stars  See all reviews (3 customer reviews)
• Amazon Best Sellers Rank: #1,900,058 in Books (See Top 100 in Books)

Customer Reviews

Most Helpful Customer Reviews

5 of 5 people found the following review helpful:

5.0 out of 5 stars Great resource when working SQL Srvr and/or Perl on Windows, March 15, 2005

By 

Kevin R. Wittmer "Kevin" (Chicago, IL USA) - See all my reviews
(REAL NAME)   

This review is from: Real World SQL Server Administration with Perl (Paperback)

Real World SQL Server Administration with Perl focuses on how Perl can be applied to perform administration tasks involving SQL Server. Shea has provided a great detail of material in nearly all major areas of SQL Server administration. Subtopics include migrating data, comparing and sorting database objects, analyzing log files, analyzing and generating SQL code, monitoring SQL Servers, and managing SQL Servers in an enterprise environment. In every subtopic, Shea succeeds in demonstrating how Perl can be used to build tools to perform a particular administrative task. A common theme found throughout the book is to use Perl to bridge the gap between standard and third-party SQL Server administration management tools with Perl scripts.

The first three chapters of the book along with Appendix B review the basics of Perl as well as Perl modules. Much focus is given to Windows specific modules included in ActivePerl, the leading freeware Perl runtime environment from ActiveState. Over the course of chapters 2 and 3, Shea details how to use Perl to connect both with ADO and ODBC (as part of this, he touches upon using COM from Perl). Also introduced in these chapters are utility routines that Shea has developed to support the tools that he provides in later chapters.

Starting in chapter 4, Shea launches into the gist of the book, SQL Server administration. The first topic covered is data migration focusing on the bulk copy program (BCP). As examples, he demonstrates the importing of both the SQL Server error log as well as the Windows PerfMon log. Changing gears in chapter 5, the comparison of table columns, column indexes, contraints, stored procedures is introduced. In each case, at least one script is shown as an example. Later in chapter 5, Linchi provides a comprehensive script that compares several database objects in one script execution pass. His intent in this chapter is to address a common DBA task, comparing database schemas for change management whether it be for application trouble-shooting or upgrading deployments. He then concludes chapter 5 with scripts for generating stored procedure call trees and procedure dependency lists.

Chapter 5 is jammed packet with several cool Perl scripts including the following:

1. Table columns - compareColumns.pl
2. Table indexes - compareIndexes.pl
3. Constraints - compareConstraints.pl
4. Store Procedures - compareSPs.pl
5. Schemas - The first comparison rolled into script, compareDBs.pl
6. Stored Procedure Call Tree (callTree.pl)
7. Stored Procedure (Package) Dependency Lister (sortSPs.pl) - this Perl script will scan a group of stored procedures and identify the dependencies of other stored procedures.
8. Producting a Reference Tree for a Table (tableTree.pl) - this Perl script will generate a table dependency graph of other tables that are referred to by their foreign keys.

In chapter 6, Shea switches to a different track, analysis of the internals of a SQL Server database. Here he dives into more advanced facilities such as the trace flag 1200 - useful for debugging the locking behavior of a particular row - and the DBCC PAGE command, as well as details that help identify potential database problems. Once again, scripts are provided to help analyze the information that can be gleaned from SQL Server.

Chapters 7 and 8 deal with analysis and generation of SQL code. In starting off on these series of topics, Shea introduces filerQueryPlans.pl, a script that analyzes the output generated by the SQL Profiler tool. He also provides scripts for identify queries with excessive joins and dynamic SQL (reviewSP.pl). indexSummary.pl is another script that is quite useful, this script scans the a query plan and analyze how a given SQL query utilizes indexes. This script thus helps you to determine which indexes are used, not used, as well as how often a script is used. SQL code generation is also covered with examples that generate T-SQL code from a configuration file, database schema, from data in a table, existing T-SQL script files, and other SQL-DMO object instances. Perl script code to split files containing multiple stored procedures into a single file for each procedure is also provided here.

Chapters 9 and 11 are companion chapters. Chapter 9 introduces log file analysis of the SQL Server error logs, Windows event logs (grepEventLog.pl), DBCC output files, log files from scheduled jobs, SQL Server agent files, replication error log files, and agent output files. Other types of log files include Windows cluster log files, application server log files, as well as third-party log files. In this chapter the author discusses and develops scripts for alerting based on evaluations of the SQL Server error log, local drive space, database server availability and cluster. Then as a follow-up in chapter 11, Linchi develops scripts for monitoring of these areas with the ability to notify via SMTP e-mail.

Chapter 12, Managing SQL Servers in the Enterprise, is the last chapter. This chapter provides material and scripts for managing many SQL server instances in an enterprise environment. As the reader has come to expect, this chapter is also packed with useful scripts for managing SQL server instances as well as system and user accounts.

ListSysadmin.pl - Lists Sysadmin accounts and logins
scanForSQLServer.pl - Scan for SQL Server machines on a network
scanNullSAPasswords.pl - Scanning for sa accounts with null passwords
trackSQLAccounts.pl - Tracking SQL Server Service accounts
findOpenShares.pl - identify file shares on your SQL Server systems that are wide-open

In conclusion, the book Real SQL Server Administration with Perl is a wonderfully useful book for DBAs managing SQL Server databases. I have come across no other book targeted at a particular community within the world of Microsoft and Windows that demonstrates the power and usefulness of Perl. In addition to packing the book with incredibly useful Perl scripts for SQL Server administration, I also find myself using the first few chapters as well as the appendix as a general reference to programming Perl in the Windows environment.

3 of 3 people found the following review helpful:

5.0 out of 5 stars Fantastic Perl/Windows/SQL Server book, June 9, 2004

By 

M. Schmidt "mschmi" (St. Louis) - See all my reviews
(REAL NAME)   

This review is from: Real World SQL Server Administration with Perl (Paperback)

What a fantastic, well written, example laden book. If you are a SQL Server DBA or Developer (or Windows Admin or Power User) and know or wish to learn Perl to help automate many of your proactive and reactive monitoring tasks then this book is for you. Shea is one smart cookie and I'm really impressed by his book.

2 of 4 people found the following review helpful:

5.0 out of 5 stars Very practical, November 19, 2003

By 

Albert Li "quickhand" (Princeton, NJ United States) - See all my reviews
(REAL NAME)   

This review is from: Real World SQL Server Administration with Perl (Paperback)

This book contains a lot of good and practical perl code. It really help me a lot.