Chapter 38 – Successfully Implementing Kerberos Delegation

Cross posted from the SQLPespectives blog put together Richard Rodriguez, Chris Shaw, and Jeremy Lowell for a chapter by chapter walk-through of the SQL Server MVP Deep Dives book. 

MVP Scott Stauffer does a fantastic job of explaining what Kerberos delegation is, the issues it solves, and provides an extensive list of resources to close out the chapter.

This is the best resource I have found for understanding Kerberos and how to implement it.  K. Brian Kelley has an excellent article on SQLServerCentral that is good, but not quite as in depth as this chapter.  Scott lays out the process logically beginning with how and why he came to understand the process, taking you through what Kerberos is, what issues Kerberos delegation solves, and providing a business scenario that Kerberos will solve.

Kerberos is a complex topic which Scott makes simple in his section Understanding Kerberos Delegation.  He explains SPN’s (Service Principal Names), permissions needed to work with SPN’s, and the tools you will need.

Scott very clearly takes you through implementing Kerberos from beginning to end.  He includes great screen shots and code snippets along with explaining WHY you need to go through each of the steps.

Scott also included a section on validating your delegation setup using a tool called DelegConfig.  This tool is run on the client and web tiers and will validate that Kerberos delegation is configured correctly and working.

Finally Scott provides 3 pages of resources to use to learn about and implement Kerberos delegation which includes: tools, blog posts, TechNet articles, MS Help & Support articles, white papers, and webcasts.

This chapter is really a one-stop shop for people who need to understand and implement Kerberos delegation.

Book Review: Crunch Time

About a month ago I went to Barnes & Noble to pick up a copy of Seth Godin’s book, Tribes: We Need You to Lead Us and, having some extra time, I was browsing the discount areas and I came upon Crunch Time: 8 Steps to Making the Right Life Decisions at the Right Times by Ken Lindner.  Since I’m in a time where I have some decisions to make and I’m also focusing on reading books about leadership I thought this might be an interesting read, especially at 50% off.

The book is broken down into 3 sections:

1. Exploring Your Decision-making Foundation

   The author uses this section to talk about the first 2 steps. Identifying when you need to make a decision and "clearly and concisely stating what the issue is". For instance, you are in the store and you pass an end aisle display with Double-stuff Oreos and there is a big sign that says, "Special Offer". Do you buy the cookies? Now there are many things that go into making the decision, but that is the core issue.

2. The Crunch Time Strategies

   Here the author uses most of the book to explain Step 3, Identify, Explore, and then Apply the Appropriate Crunch Time Strategies.  This has 24 sub-steps that I won’t list here.  To sum it up, be constructive, be disciplined, be objective, and evaluate decisions based on your long-term goals.

3. Completing the Process

   This breaks down the final 5 steps in about 30 pages. To sum it up, make sure your decisions are based on accurate information, what you value, and what you really want.

I honestly had a hard time getting into the book because it has a "touchy-feely" tone. The author talks a lot about making "self-enhancing" decisions and decisions that take you where you want to go in your "heart of hearts". That's not to say that the author doesn't give you some good ideas and a framework to build on, it's just that he worded it in a way that is more self-help than professional development. 

What did I learn from this book?  First, have goals, have a decision-making process (he provides his in this book), and evaluate every decision using the process and make decisions that lead to the fulfillment of your goals.

Would I recommend that you buy this book?  Not if you had to pay full –price, but for $5.00 it wasn’t a bad read once you get past the writing style.  I did learn some things and it dove tails pretty well with Tribes and the book I’m currently reading, Visioneering by Andy Stanley

Book Review: SQL Server 2008 Query Performance Tuning Distilled

I recently finished reading the Apress book SQL Server 2008 Performance Tuning Distilled by Grant Fritchey and Sajal Dam.  I consider Grant to be a personal friend in addition to his being one of the top SQL Server experts.  I have not had the opportunity to meet Grant’s co-author, Sajal Dam, although the fact that he worked with Grant on this book makes me place him in high regard.  Unlike the other 2 book reviews (Murach’s JavaScript and DOM Scripting and Apress Pro SQL Server 2008 Administration I have done, this is a book I purchased. 

Overall Impression

This was a fantastic book.  I agree with what Brent Ozar (@BrentO) said in his review:

My gauge of an amazing book is simple: if I’ve got a question, and I reach for the book BEFORE I search the web, then it’s an amazing book.
Several times in the last two weeks, I reached for this book first.

I have also reached for this book several times since I purchased it, when I was baselining servers or looking for ways to improve performance.

Chapter Details

Chapter 1 – SQL Query Performance Tuning

A great introduction to the process of performance tuning and common performance killers.  I especially like the way the authors present the concept of “Good Enough” Tuning, where the performance may not have reached the theoretical optimum, but is “good enough” for your purposes.   It sets up the rest of the book very well.

Chapter 2 – System Performance Analysis

The authors present some of the tools you can use to measure the performance of your system including Performance Monitor and Dynamic Management Views.  They present a good list of performance monitor counters to watch and the general thresholds that indicate a potential bottleneck.  Not only are the thresholds presented, but potential resolutions as well.  I refer back to this chapter often.  They also present the best outline for creating a baseline that I have read. 

Chapter 3 – SQL Query Performance Analysis

From system analysis now to query analysis.  This chapter covers Profiler, or as Grant prefers to call it, SQL Trace, analyzing execution plans, and query cost (client statistics, execution time, statistics IO).  The authors present steps to use each of these tools to improve individual query performance.

Chapter 4 – Index Analysis

The authors do a great job of starting with an explanation of indexes to advanced indexing techniques including filtered indexes and special indexes on full-text, spatial and xml data types.  Experienced DBA’s will recognize the techniques described for designing and choosing indexes and for beginners it is like having an expert along side them.

Chapter 5 – Database Engine Tuning Advisor

A short look at the tool provided by Microsoft to analyze a workload and present index recommendations.  A good tool, but one to be used carefully.

Chapter 6 – Bookmark Lookup Analysis

A look at, what is in my opinion, a very common cause of poor performance, bookmark (key/RID) lookups.  The authors define and then show how to troubleshoot and resolve bookmark lookups.  A must read chapter.

Chapter 7 – Statistics Analysis

One of the longer chapters in the book and rightly so as Statistics are a poorly understood subject and, in some cases, hard to understand and analyze.  This had been one of my weaker areas in SQL Server and this chapter certainly helped me become more comfortable with it.  I know that there is at least one query I have used an index hint on that, were I to go back, I would probably remove because updating statistics would cause it to use that index.

Chapter 8 – Fragmentation Analysis

Great coverage of what  causes fragmentation and how to remove it.  One of the best explanations of Fill Factor and it’s significance

Chapter 9 – Execution Plan Cache Analysis

Another of the longer chapters as this is an important skill/art to understand.  The authors explain how all the steps involved in creation and re-use from generation to aging out.  Then they explain how to optimize queries for caching.  Again this is a learned skill, almost more of an art.

Chapter 10 – Stored Procedure Recompilation

A good companion to chapter 9 as execution plan caching and stored procedure recompilation are related.  The authors do a good job showing how to find what causes recompilation and then how to avoid it.

Chapter 11 – Query Design Analysis

The authors go into the art of designing queries optimally.  One of the key section talks about how to use indexes effectively.   Another section also discusses things that are part of good database design (Domain and Referential Integrity) help with query performance.  These are often neglected because they are “enforced” by the application, so they need to be emphasized.

Chapter 12 – Blocking Analysis

Blocking is, in my opinion, one of the most misunderstood aspects of the relational database as it is a core component to guarantee the ACID properties, yet it is often confused with Deadlocking (covered in chapter 13).  The authors do an excellent job covering all areas of locking, ways to capture blocking data, and ways to reduce it through indexes, isolation levels, and partitioning.

Chapter 13 -  Deadlock Analysis

Another example of good organization as it follows the Blocking Analysis chapter.  The authors do a good job providing an explanation and example of deadlocking.  I especially liked the section on Using Error Handling to Catch a Deadlock, so you can handle the deadlock in your T-SQL.  The authors also give the best ways to avoid deadlocks and the caveats with each.

Chapter 14 – Cursor Cost Analysis

A great chapter on the cost and benefits of using cursors.  Since SQL Server is designed to work with sets, using cursors is not usually the best solution, but the authors do a good job explaining how cursors work and how to write the best performing cursors in the cases you may need to use them.

Chapter 15 – Database Workload Optimization

The authors take you through the process of applying the information presented throughout the book.

Chapter 16 – SQL Server Optimization Checklist

The authors present a short summary of each of the optimization techniques presented in the book.  Once you’ve read the book you can just refer back to the final chapter to get guidance and, if needed, go deeper into the area you are using by referring back to an earlier chapter.

Conclusion

A really great book to learn how to make your SQL Server perform better.  It has information for every level of SQL Server professional from beginner to expert.  I particularly liked the summary chapters (15, 16) at the end of the book.  This is a book I think every SQL Server professional should have on their bookshelf.

Book Review: Apress Pro SQL Server 2008 Administration

Several months ago I received a copy of the Apress book, Pro SQL Server 2008 Administration (Amazon) by Ken Simmons (@kensimmons), a friend of mine, and Sylvester Carstarphen (this is my disclaimer that I didn’t buy the book).  Well, I finally finished the book and can post my thoughts.

General Impression

This was a good book, but my first thought was that I expected more from a “Pro” level book.  I’m not sure if this is because my expectations were too high, or because I underestimated my own knowledge.  What the book does cover it covers well and the writing is excellent, but I didn’t think that a “Pro” level book would include doing an install or upgrade from the GUI, which this book includes and I thought that there were a lot of pages on what I would consider basic level tasks (security, indexing, backup & restore).  

The Book

I liked how the book starts with a three chapter section, Introducing Microsoft SQL Server 2008, covering new features, pre-installation, and high-availability options. 

Part 2 – Getting Started

Three chapters on Installing, Upgrading, and Post-Installation. The end of chapter 5, Upgrading to Microsoft SQL Server 2008, where the authors covered post-upgrade tasks like changing compatibility levels, updating statistics, page-verify settings, etc.. was excellent as these are often forgotten or overlooked tasks. 

Part 3 – Administering Microsoft SQL Server 2008

The meat of the book with seven chapters on Multi-server Administration, Managing Security, Administering Database Level Objects, Indexing, Backups, Restore Strategies, and Automating Routine Maintenance. 
Chapter 7 which covers Multi-server Administration, including Policy Based Management and Central Management Servers was excellent, although I think more time could have been spent on these features, especially if less time was spent on the GUI install and upgrade. 
Chapters 8-11 did a good job explaining the mechanics and the how-to’s of security, managing objects, indexing, and backups, but I assumed this would have been covered in a beginner or intermediate level book and a pro book would have spent more on the strategies and why’s of these topics. 
Chapter 12 on Restore and Recovery Strategies does spend more time on the reason for the different restore options and did a better job hitting higher-level topics like Page restores, piecemeal restores, and online-restore options which is why this is one of my favorite chapters. 
Chapter 13 – Automating Routine Maintenance, was one I was a bit disappointed in.  It does a good job of covering Database Mail, SQL Server Agent, and Maintenance plans, but I thought that there would be more on writing and using scripts like Ola Hallengren’s Maintenance scripts or Michelle Ufford’s (@sqlfool) index maintenance script to show how the author’s or others automate and plan for maintenance.

Part 4 – Troubleshooting and Tuning

Three chapters on monitoring, auditing, and managing query performance.
Chapter 14 covers monitoring and does a good job explaining the tools available (Perfmon, DMV’s, DBCC, Profiler/Trace), including the counters and events to watch.  I especially liked how the authors showed how to get performance counter information both from PerfMon and the DMV’s.  Baselining is discussed but one thing is missing, and I haven’t found it anywhere yet, is how/where to store your baseline data for comparison later.  I know I could figure something out, but I want that one to be given to me.
Chapter 15 covers auditing.  Actually it covers the new enterprise edition only SQL Server Audit.  It’s a good chapter and covers it well, I guess, but since I don’t run enterprise edition, I didn’t really play with it all.  I still wish the book had covered some other ways like SQL Trace, the Default Trace, and Common Criteria auditing which are still available even at the non-enterprise level.
Chapter 16 was on managing query performance and included hints on using Profiler and PerfMon together, using the standard performance reports in SSMS, DMV’s, query hints, plan guides, and Resource Governor.  All in all a very informative chapter on performance monitoring/tuning for an administration book.  The chapter does mention another Apress book, SQL Server 2008 Query Performance Tuning Distilled (Amazon), as a more detailed resource for this information (I have it and it is, a review will be coming).

Part 5 - Conclusion

Two chapters covering Secrets to Excelling as a Professional DBA and What’s Next which were excellent as they talk about knowing your limitations, communicating, being a leader, and taking charge of your own professional development.  These are all areas where I can see myself needing growth.

My Conclusion

I recommend the book as I think people at all but the extremely low or extremely high levels will get something from it, but people moving from SQL Server 2000 to 2008 and junior DBA’s will get a lot from it.

Book Review: Murach’s JavaScript and DOM Scripting by Ray Harris

In early September I received a complimentary copy of Murach’s JavaScript and DOM Scripting by Ray Harris (Amazon) to review.  I got the book because my friend, Andy Warren, passed my name along to the publisher when they asked him to review the book.  He knew I was attempting to learn JavaScript, so passed along this opportunity to me.

This is the first Murach book I have read and it definitely has an interesting format.  The left page is text and the right page is code, examples, and summary of key points.  It took a couple of chapters to get used to the format, but once I did I found it to be very helpful.  For some of the early chapters I was able to just skim the right page to pick up the concepts as the content was already familiar to me.

The book is broken down into four sections:

1. Introduction to JavaScript Programming
2. JavaScript Essentials
3. Dom Scripting
4. Other JavaScript Skills

In the first section, Introduction to JavaScript Programming, you get the basics of web development including XHTML, CSS, and beginning JavaScripting.  As an inexperienced web programmer I found this information invaluable.  I’ve always been confused by CSS this book helped me to gain a basic understanding so I can now read, understand, and intelligently edit CSS pages.  An experience web developer will be able to skim/skip much of this section, but as a relative newbie, I ate it all up.

In section two, JavaScript Essentials, you delve deeper into JavaScript functionality.  Getting input and displaying output, working with native objects, control statements, arrays, functions, objects, regex, exception handling, and data validation.  Some of the topics in this section, like control statements, are common to other languages so I was able to skim parts of this section and just use the examples and summary on the right hand page.

In section three, DOM Scripting, you really get into the deeper topics.  This is where you really get to hone your skills and take advantage of the power of the DOM and scripting.  You learn to manipulate the DOM, CSS, and build libraries you can re-use to do this.  You get understandable examples and exercises that lead you through the concepts and help you build working examples of a slide show, manipulating tables, and animations.  From here to the end of the book, I would imagine that even experienced JavaScript developers would learn something.

In section four, Other JavaScript Skills, you learn how to manipulate the browser and leverage existing JavaScript libraries like jQuery to extend your applications.

The book was was easy to read and the examples and exercises were good.  Being a Microsoft developer I was a little disappointed that it the book did not really talk about using JavaScript with Visual Studio, but I guess that is to be expected.  I also didn’t think enough time was spent on showing how to deal with the situation when a user has disabled scripting in their browser.  The last thing was that I found the instructions in some of the exercises were too vague and while I finished them in a manner that worked, I wasn’t sure if I had done it correctly.  The finished scripts were included in the download, but I would have liked to have seen it in the book as an appendix.

So I did have a couple of issues, but overall a really good book.  I think it works for beginners all the way to experienced web developers.  The more you know you can “mine” the parts of the book that address your weak areas.  I’d recommend the book and would buy other Murach books based on my experience with this one.

RedGate SQL Refactor

I was fortunate enough to receive a copy of RedGate's SQL Toolbelt, which includes SQL Prompt, SQL Compare, SQL Data Compare, SQL Refactor, SQL Packager, SQL Data Generator, SQL Doc, SQL Backup, SQL Response, SQL Multi Script, and the SQL Comparison SDK. I have used SQL Prompt (intellisense for SQL), SQL Compare, and SQL Data Compare in the past and have always liked RedGate's products.

Today I had the opportunity to use SQL Refactor to rework several tables in a a database. My co-worker had asked that a table, in a database in development, could be renamed. As you probably know, there is no thing as a simple table rename, especially when it is one of the core tables in the design. In the past I would have to go through every relationship, trigger, view and stored procedure and make the changes, but today, with SQL Refactor, I was able to do it much faster using the Smart Rename function. SQL Refactor plugs into SSMS, so if you right-click on an object you get a Smart Rename option. Once selected you are prompted to provide the new name, and Refactor finds references to the table in foreign keys, triggers, views, and stored procedures, and generates a script for all necessary changes. It does the same for column renames as well. Even with this tool I spent my entire afternoon making changes. Mainly because this one table had references to several other tables, thus every place there was table_id, I needed to make the change to that table as well as Refactor does not rename columns in other tables. Still I would have been looking at probably a full day plus of work without the tool.

The only change I would, and will, suggest to RedGate is to allow you to rename multiple columns in a table at one time so one script is generated instead of having to rename each column separately for the refactoring to be done.

The next tool I will be trying is SQL Response. I'll probably let you know how it goes.