I have been the database administrator for a small foodservice equipment maintenance company for the past 5 years. Beginning with SQL 6.5, I have evolved up to SQL2005 and hope to continue learning about databases and database construction, management and administration. I have little formal training in SQL Server administration having learned much about SQL via on-the-job-training. I was especially interested in reviewing this book from my perspective because I feel like mine was a bit skewed from the normal DBA.
My initial reaction to this book is that it would make an awesome text book for a college course on SQL. It is written in simple language and assumes the reader has little to no knowledge of SQL or SQL Server. It is detailed without getting verbose about all aspects of SQL Server from making the initial decision to purchase it, configuring hardware, configuring the space and setting up security. There are a lot of screen shots to help illustrate certain points, but has very little TSQL code as it truly is a book about SQL Server 2005 administration.
The first parts of this book “Introduction to Microsoft SQL Server 2005” and “System Design and Architecture” assume the reader has no previous knowledge of SQL Server even from an historical point of view. The first part, Introduction to Microsoft SQL Server 2005 goes into detail about the who and what of SQL, it’s various editions, their individual capacity and limits; explores the different types of DBAs who might be involved with a typical “shop” and what their duties and areas of expertise should be. The second part gets into system designs and architecture starting with hardware requirements, moving into capacity planning and finishes up with installing, upgrading, patching and configuring SQL Server for your environment. I do wish I had read this book prior to installing our current version of SQL2005 for many reasons only one of which is my better understanding of its needs and requirements for best performance.
Beginning with part 3, Microsoft SQL Server 2005 Administration, you get into the “meat” of the book and here is where the established DBA is going to the most use out of it. It begins with an overview of database structure, defines and describes the system databases and provides instruction on creation of user databases. It provides some insight on database layout with examples and goes into database snapshots which are new to SQL Server 2005 and which, now that I know about them, can provide some seriously useful reporting potential. There are however, some gotchas with using database snapshots and all aspects should be considered before using them extensively.
Part 3 also delves into security, data integrity, and performance issues. It covers that essential task of backing up your data and the differences between the models. It discusses in detail the reasons for backups, the different kinds of backups and the reasoning behind which one is the best for your situation. It shows with examples the differing scenarios that backups are essential to cover.
Part 3 would not be complete without a thorough explanation and step-by-step instruction on restoring your data. It covers the importance of doing test restores on a periodic basis to ensure that your backups are viable and that your data is uncorrupted.
Backing up and restoring data is the tip of the iceberg when it comes to maintaining your database. Understanding transactions, rollbacks, locking, savepoints, checkpoints and data partitioning are all essential to the SQL2005 administrator and this book does not neglect these subjects. Part IV does an exemplary job of explaining the different aspects of committing transactions, locking, blocking and deadlocks.
Part V begins the journey into replication, integration, analysis and reporting. These are the future of SQL server – the pieces that take data and render it into a form that is suitable for consumption by humans.
Replication in previous versions of SQL Server could sometimes be temperamental and difficult to implement. The SQL Server 2005 Administrators Guide takes the mysteries of replication and renders it into a subject that is clearly written and while perhaps not simple to understand, at least isn’t shrouded in mystery, jargon or fictional account. The discussion even delves into dealing with other types of databases such as Oracle and DB2.
Analyzing data is a job all by itself and, for a DBA such as myself who must wear a 10 gallon hat, is essential to grasp the basics of in order to fulfill a job that has a wide coverage area of SQL. It would take more than a simple division in a book the scope of this one to get all the fine points, but a good beginning is made with, once again, clear language and solid examples. Other sources of information on this crucial subject are listed in the book for any interested in further pursuit of data analysis, integration and reporting services.
This chapter also covers notification services and Service Broker, two fairly new tools of SQL Server for keeping track of job completions and failures. Care and detail in installing these services is taken with clear instructions that are easy to follow.
Part VI covers availability of data, creating failover clusters and data mirroring. Log shipping is covered and using log shipping in conjunction with mirroring tactics is discussed in detail.
No book would be complete without a troubleshooting guide. This one, being a complete book contains just such a guide which covers simple to complex issues that may arise in the course of the day for any DBA. Performance aspects are giving high importance, because, without ready access to our data, what good really is our database. Some key issues surrounding performance tuning are discussed again with real-world examples of how tuning works and how to achieve the best results with the tools inherent to SQL Server. One tool of the DBA is the SQL logs, but sometimes interpreting the data contained within the logs is difficult at best. The Guide goes into reading the logs and how to determine the errors and what they mean.
Lastly, the book goes into such topics as hyperthreading, multicore processors, clustering, and scalability. It also explains using query plan to determine the effectiveness of queries written on the database and how they affect performance.
Microsoft SQL Server 2005 Administrator’s Companion is a book that is useful for both the newly minted as well as the old hand DBA. There is a wealth of data within the book to assist in planning and implementing a database of terabyte proportions with relative ease. If a DBA took the time to thoroughly read the book from cover to cover he or she would have a comprehensive knowledge of SQL Server 2005, Analysis Services, Integration Services as well as Business Intelligence.
