Dude Where Did my AD Account Go? Troubleshooting Duplicate SID's.

Hello Dear Reader!  I've been working on Setting up a Virtual Lab in using Virtual Box on my work laptop.  I originally thought of blogging through all of this but I've had a few issues.  I stepped away from this project at one point in time and was using a personal one.  However I really want to get this work on my Pragmatic Works laptop.

I may go back and do a step by step once I've got my feet underneath me, but for now I'll just be happy to have this up and running.

I've finally gotten my Domain Controller Online, Added two Servers to my Domain, and then I wanted to add a Domain Account as an Admin to each Server.  

So I logged onto the sever using the local admin, opened up Server Manager, opened up the Configuration tree, Opened Local Users and Groups, and Clicked on Administrators.

As I expected only the local admin account and a local SQL Server Account, that I'll be replacing with a domain account.

So I click add, type in SQLBalls, Authenticate to my domain to get the account added, and everything looks good.  I hit OK.

Hey where'd my AD account go?

So I went through the whole process again.  Click Add, added SQLBalls, validated against the Domain Controller, and then I get this error.

Well if my account is already in the group, then why isn't is showing up.  So I turn to trusty old DOS and open a Command Prompt Window and run net localgroup "administrators".

Sure enough my domain account is not in there.  It didn't take much searching on the Intrawebs to find other people with my issue.  After a little while I found people encountering this error when they had Cloned a VM.

The Domain Controller and the VM had the same SID's, Security Identifiers.  So to validate this I went and grabbed the handy sysinernals tool PSGetSID, to get this click here.  

Once I had downloaded this to my software share I put it on my Domain Controller and one of my other Servers.  I extracted it to a folder called PSGetSID, I know *how original*, navigated to the folder, and typed in psgetsid.

Now that I know what my SID for my Domain Controller is I need to find it for my other computer.  I extract the files and run the command and VOILA!

Sure enough I have duplicate SID's.  If you notice up at the top the Account name has a SID after it, before I click OK and it disappears.  That SID is the same one as both of my computers.

STEP 1 FIX! STEP 2 IT! REPEAT STEPS 1 & 2 UNTIL IT'S FIXED! 

So duplicate SID's are preventing me from adding one AD account to other computers on my domain. I had set up an image of Windows 2008 R2 that was my base image.  I had been keeping the widows update current, but I left it pretty much alone.  I would clone it before I taught a class, did a presentation, or experimented on really f***ing up doing non best practice things on my computer.

So Cloning the same image to make my Domain Controller led to this error.

"So Balls", you say, "How do you fix it?  And isn't there a better way to do things."

Yes Dear Reader there is.  I was saved by this blog by Ilija Brajkovic.  There is a tool called sysprep.  I should have run that before to clean up my base image before cloning it.  Now I can use it to change my SID.  I start out by pulling up run and typing in sysprep and click OK.

It will open up sysprep in it's windows folder.  I then double click on the sysprep.exe in order to launch the application.

Now that sysprep is open I make sure OOBE is selected, I need to click Generalize in order to generate a new SID, and I will also select Reboot.  Then Click OK. This ran very quickly for me.

As Ilija notes there will be some additional information to enter on reboot.  Note *I SHOULD HAVE DONE THIS RIGHT AFTER CLONING*  After reboot You will get prompted for the language choice.  *Warning this will reset your image to a factory setting, if you already have SQL Server Installed this will erase the instance.  This will detach drives, this will reset your TCP/IP Settings.  DO NOT DO THIS IF YOU DO NOT WANT TO WIPE CLEAN YOUR VM*

Then you check the box to Accepte the Agreement.

Then wait while your settings are finalized.

When I log back in my VM has been reset, hence the Enter System Out-of-Box Experience.  The software I had installed is still there. But I'm no longer on the domain, my computer name is changed, and hopefully my SID is different.  Lets run PSGetSID to validate that.

Excellent!  I've got my new SID.  I need to set my NIC card again to be on the right network, rename my server, add it to the domain, and reboot.  After that I can go back into the setup for my AD groups and add my User Account.


This time when I click OK it doesn't go away.  Alright Dear Reader, I hope you enjoyed this one, it was a lot of fun to figure it out!

Thanks,

Brad

SQL Live 360 Kicks off Today!

Hello Dear Reader!  Today in beautiful Orlando FL at the Loews Royal Pacific Resort the Live 360 Conference kicks off.  Live 360 combines 4 different conferences into one.  Visual Studio, SharePoint, SQL Server, and Cloud & Virtualization all have their own conferences.  I’m helping to kick off the SQL Live 360 by presenting not once, not twice, but three times today!  

Before we dive into the content I want to say a big Thank You to my wife Silva.  Every time I’m presenting at a conference she’s taking off work to be at home and manage the kiddos.  Without her none of this is possible for me.  Thanks Silva!

“So Balls”, you say, “What are you presenting on?”

Excellent question Dear Reader.  We will be kicking off the day with Trimming Indexes Getting Your Database in Shape, next up is Transparent Data Encryption Inside and Out in SQL Server 2012, and we end the day with the Page & Row Compression Deep Dive in SQL Server 2012.  Conferences are expensive and I want to make sure that you get the most out of your experience.  So to help you decide if you should be spending your time with me, I’m placing the decks and demos online now.  They are also live on the Resource Page.  Attendees should have a copy of all of this information on their conference CD, but just in case you didn’t find it here we go!

KICKING IT OFF WITH A BANG

If you’re in town for the conference I hope you get a chance to stop by.  Here are the abstracts for each presentation:

Trimming Indexes Getting Your Database In Shape

Indexes are a wonderful thing.  We should be using them, and we should be maintaining them.  But over time our production databases start to look a little pudgy around the mid-section.  Maybe they are a little bloated with Unused Indexes, maybe they have Duplicate Indexes, and possibly even Reverse Indexes.  The first step to fixing these problems it so see if you have them, and if you do the second is to set about fixing them. You could be costing yourself CPU cycles, I/Op’s, and space and never even know it.

Slide Deck, Demos

Transparent Data Encryption Inside and Out in SQL 2012

Security is a very important part of your job and in how data is utilized.  We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list.  Find out What it does and What it doesn’t do, How it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), What the X.509 Encryption Standard is and Why you should be careful of what you store and where, and other Advance Features as well as some tips on how to manage it.

Slide Deck, Demos

Page & Row Compression Deep Dive with SQL Server 2012

Page and Row Compression are powerful new tools. Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2, and Spatial Types in SQL 2012.  We can turn it on, we can turn it off, but we want more!  What are Access Methods and how in the SQL Engine do they affect Compression?  What are the “Gotchas” of Page Compression?  How does Compression differ in the way it treats Non-Leaf & Leaf Level pages?  What additional functionality did we get in DBCC Page, DMV’s, Perfmon Counters, and Extended Events to support our usage of Compression?  How do complex query plans affect Compression?  Come find out!

Slide Deck, Demos

LUNCH!

That’s not all, on top of having 3 great sessions to choose from if you’re still not sick of me we’ve got a speaker round table on Wednesday where I’ll be hosting a table on SQL Server 2012 and new features.  While I may not have presented on them there are a lot of great 2012 topics like Columnstore Indexes, Always On Availability Groups, Always On Clustering Improvements, and more that we could chat about.

Great SQL People like MVP’s Jen Stirrup(@JenStirrup | blog), Grant Fritchey (@GFritchey | blog), Allen White (@SQLRunr | Blog),  Thomas Larock (@SQLRockstar | Blog), and William Pearson (@Bill_Pearson) will be there hosting different tables as well!

WRAP IT UP

Okay with that being said, it should be a fun filled week of SQL Learning.  There will be some wonderful night time activities as part of the conference as well. 

  I won’t make it to all of them, Wednesday night is the baby’s Christmas play at her day care can’t miss that!  For the rest of the week though I look forward to getting to see you all.

If you are in town I hope you get to stop by and say “Hi BALLS!”, and have a great time!

Thanks,

Brad

PASS Summit Page & Row Compression Deep Dive

Hello Dear Reader!  Today is the Big day for me here at the PASS Summit 2012.  I'll be presenting the Page & Row Compression Deep Dive today at 9:45 am until 11:00 am in room 305-TCC.

The last day of the Summit is upon us and with it almost over I get more an more anxious to get back home and see my family.  Soon we will be in the weekend and, for most of us, we will no longer be in Seattle.  I've got some good stuff that I'm cooking up just to make sure that you have plenty of compression goodness to play around with.

"So Balls", you say, "You did this presentation last year, why should I attend?"  

Great Question Dear Reader!  Away we go! .




PAGE & ROW COMPRESSION DEEP DIVE

First let's start with the abstract:

Page and row compression are powerful new tools. Introduced in SQL Server 2008 RTM and enhanced with Unicode compression in SQL Server 2008 R2, the compression tools now work with SQL Server 2012's spatial types. We can turn compression on, and we can turn it off – but we want more! 

What are access methods and how in the SQL Engine do they affect compression? What are the “gotchas” of page compression? How does compression differ in the way it treats leaf and non-leaf level pages? What additional functionality did we get in DBCC Page, DMVs, Perfmon Counters, and Extended Events to support compression? And how do complex query plans affect compression? Come to this session to find out!

There are a lot of very cool and deep things we did last year, but as the year goes along I keep trying to dig deep down and find some really cool stuff for us to look at.  The same thing is great but this year I want to push it even deeper.

I've got 3 things to tempt you Dear Reader:

1. We will go deeper into the structure of a record this year
2. We will discuss Mid & End Page Splits and What they mean for Compression
3. We will tackle Statistics in Compressed Indexes and how Page Compression attempts affect them.

I've got new demo's that I'll be rolling out.


My Slide deck and demo's are already live on the PASS Website click here to get them.  Here is a link on the Resources page for them as well, click here, and go down to the PAGE & ROW COMPRESSION DEEP DIVE header.  Under that I've got a section for PASS Summit 2012, the latest version of the slide deck as well as all of the demo scripts are there as well!

Have a great last day and safe travels home Dear Reader, I hope to see you this morning!

Thanks,

Brad

SQL PASS Summit 2012 Keynote Day 1

Hello Dear Reader!  I'm sitting in the Keynote now writting up a summary of the event.  More to come!

DBA Question of the Day: Statistic

http://www.flickr.com/photos/_barney/5177975707/

Hello Dear Reader, yesterday I posed the question to you what are Statistics?  We could get down and dirty of how the internals of SQL Server use Statistics (and we will), but first let’s talk about the concept because it is at heart a very simple one.

In America we are in my favorite season, Fall.  The weather is cool but not to cold.  The leaves turn beautiful colors, the smell of wood burning in a fire place, fire pit, or the general smokey scent that goes with the great out doors this time of year always springs to mind.

Unfortunately we are also in my least favorite time of the every 4 years, Election Season.  But Election Season does tie nicely into our subject of the day.  Statistics are nothing but POLLS!  In election season they say things like, “Do you like this Candidate?  Do you like this particular issue? Do you believe they kill puppies when people aren’t looking?”, and we get a break down of Yes, No, and Undecided.

In SQL Server Statistics are SQL’s way of Polling our Data. 

GATHERING STATISTICS:

SQL Server: “I See you are a column Called First Name”

Registered Column: “Yes I am”

SQL Server: “Mind if I ask you a few questions (QUERIES) and create a Poll based off of how you answer?”

Registered Column: “Go Right Ahead”

Breaking New Folks Registered Column has 28% of his data between Alex and David, only 5% between David and Nick, and a WHOPPING 77% between Opie and Zachary!

USING STATISTICS:

Now the next time we ask a question (QUERY) we (THE QUERY OPIMIZER) have an expected number of people in a particular demographic (VALUE RANGE).  We know if our candidate wants to know how all the Opie’s through Zachary’s will answer a question (QUERY), we can plan on how to best collect that information (AKA HOW THE QUERY OPTIMIZER CREATES A QUERY PLAN).  We can then figure out how many people we need to send out (WORK THAT NEEDS TO BE DONE SORT, SPOOLS, HASH’s) in order to collect that data.  For example we need less people to collect data from David to Nick (NESTED LOOP JOIN) than we do to collect data from Opie to Zachary (HASH JOIN).

OUT OF DATE STATISTICS:

Now that we have our Poll, the next time we have a question (QUERY), the folks on the new screen will say, our expected result was 77% when we selected the range of Opie’s and Zachary’s however we found that only 58% actually resided there.

Our population was moving (DATA WAS BEING UPDATED/INSERTED/DELETED) and our Statistics were not up to date.  If we had a plan to collect our data (QUERY PLAN) using a lot of people to go out in the community and collect polls we may have sent out to many and over allocated our resources (PICKED A BAD PLAN IE HASH JOIN INSTEAD OF NESTED LOOP).  If we still had 77% of Opie’s to  Zachary’s our polling plan (ESTIMATED ROWS RETURNED) 

would be good, but it wasn’t (ACTUAL ROWS RETURNED).

UPDATING STATITICS:

So our Statistics were out of wack on our poll.  Something was off.  If we had a big plant closure in our town or a big company laid off a lot of people (PURGE PROCESS ON A TABLE), then we would expect some population shift.  If we knew 20% of people (20% OF ROWS IN A TABLE) were going to be laid off we could expect some would move in with other family members or move to find new work.  We would probably send people out in the community to get new polls (AUTO UPDATE STATITICS) and find out what the new data was for.  We found 58% Alex and David,  25% between David and Nick, and  17% between Opie and Zachary

Regeneration of Statistics causes us to re-think our plan to Poll Opie’s to Zachary’s (QUERY RECOMPLIATION TO GET A NEW QUERY PLAN) in order to send the right amount of people out to ask questions (QUERY) to get our candidate some information (GET OUR DATA).  Now we see that we need much less people (NESTED LOOP JOIN) to poll Opie thru Zachary than we previously did (MERGE JOIN) and our polling plan (QUERY PLAN) reflects that.

GETTING INTERNAL

http://www.flickr.com/photos/photo645a/3995665841/

Now that we have a general idea of how things work let’s spell it out a purely in SQL Server Language.   Clustered Indexes and Non Clustered Indexes automatically have statistics generated for their key columns.  However there are more columns in a table than just indexed columns.

SQL places those Statistics in an object named a Histogram.  A Histogram contains entries (will only ever have a max of 200) that show data values spread over a range.  This allows the Query Optimizer when constructing a plan to say, “Statistics, I’m going to run this query on this table how many rows can I expect to get back?” and then plan accordingly.

We have the following table named Students with columns StudentID, SSN, FirstName, LastName, MiddileIntial, BirthDate, and Gender.  Clustered Index on StudentID (no debate in indexes right now this is just a demo J ).

*All of the code to create a Students table along with other and generate random data was uploaded to my resources page yesterday as a part of my Trimming Indexes Getting Your Database In Shape presentation.  Download that code and play around with it however you like!

                create table students(

                   studentID int identity(100000,1)

                   ,ssn char(9)

                   ,FirstName varchar(50)

                   ,MiddileInitial char(1)

                   ,LastName varchar(100)

                   ,BirthDate datetime

                   ,Gender char(1)

                   ,constraint pk_students_studentID primary key clustered (studentID)

                   )

If we insert a couple rows into this table (*go get the code!) and then go look at SSMS.

We see that we have statistics created for my primary key.  If you right click on the Statistics and open them up and then click on Details you will see a whole host of information.  You can see when the statistics were generated, when they were last updated and what the range is.

You can see that my Average Length is 4.  That is because my Primary Key on column StudentID is an INT or a 4 byte fixed length value.  You can see in my range what my RANGE_HI_KEY is to my RANGE_ROWS.  

For my 200 different samples you can see how many rows fall in that data DISTINCT RANGE ROWS.

If I said to the Query Optimizer

SELECT

     studentid

FROM

     dbo.students

WHERE

     studentid between 104030 and 108969

I would expect to get back 4940 rows, BUT my statistics are OUT OF DATE and do not refelect that.  So when I execute my query, and include actual execution plan, this is what I get back.

My options at this point are to update my statistics.

     UPDATE STATISTICS dbo.students pk_students_studentID WITH FULLSCAN

And now my query plan looks like this.

As you can see the Optimizer expected the number of results it got back.  In my Query Plan (a simple trivial one), the statistics did not shift my outcome.  But had I joined on the Courses table or the Grades table it could have completely changed my plan.

TWITTER IT UP

So the question on twitter yesterday that spawned all of this was should I delete old statistics?  My answer to that is no.  You should update them.  The Histogram is not normally a big space consuming object.  They are not like unused Indexes.  Unused indexes occur IO, they must be maintained as the base structure is updated.  This costs your system.  Statistics just off the query optimizer a path, if the statistics are old and the range is still valid leave them be. 

Whenever a query comes along you will save the optimizer the trouble of regenerating them.  Because if they are not there we have to create them, but that is an example for another day.

As always Thanks for stopping by!

Thanks,

Brad

DBA Question of the Day

When I used to work in an office I had a stack of flash cards and occasionally I'd grab a few, see if I still knew them and the answers and I'd walk around and discuss them with some of the other DBA's.


At Pragmatic Works I do this on our DBA DL list and I've been debating doing an occasional Question of the day series.  This Blog is inspired by a #SQLHELP conversation that I just saw my friend Mike Fal (@Mike_Fal| Blog) have regarding this very subject.  So here we go, first topic we will tackle in the old bag of flash cards:

What are Statistics and how are they used?  

Answer tomorrow.


Thanks,Brad

PASS DBA Virtual Chapter Deck and Demo's Live

Hello Dear Reader, I wanted to say a quick Thank You to the PASS DBA Virtual Chapter for having me  today.  My Deck and Demo's are up on the Resource Page.  Thank You to the 150 people who took the time to attend as well!  We couldn't do this without you.

Thanks Again,

Brad

PASS DBA Virtual Chapter Trimming Indexes, Getting Your Database in Shape

Hello Dear Reader!  Tomorrow at 12 noon eastern I’ll be presenting for the PASS DBA Virtual chapter, click here to sign up for the meeting.

If you aren’t a member of/or familiar with PASS it is the ProfessionalAssociation for SQL Server.  PASS put’s together great things for us throughout the year like SQL Saturdays, which are put together by local PASS User Groups (click here to find the one in your area) groups that meet FREE monthly and have presentations on different SQL topics, The PASS Summit (Largest SQL Server conference in the world!), 24 Hours of PASS (free 12 spans of great training), and the PASS Virtual Chapters.  Virtual Chapters range 16 different subjects and 3 different languages, soon to be four different languages!

Joining PASS doesn’t cost you a dime, and I don’t get a penny for it, but it opens the door to a large amount of free technical content and training.  If you are not familiar I’d encourage you to click on the above links and become familiar with PASS today!

“So Balls”, you say, “What is this presentation you’re doing?”

Glad you asked Dear Reader!  I’m presenting for the DBA Virtual Chapter, 1 of the 16, and my subject is Trimming Indexes, Getting Your Database in Shape.

I’LL TAKE THE #2 SUPER SIZED

Here’s the abstract and then we’ll talk a little more:

“Indexes are a wonderful thing. We should be using them, and we should be maintaining them. But over time our production databases start to look a little pudgy around the mid-section. Maybe they are a little bloated with Unused Indexes, maybe they have Duplicate Indexes, and possibly even Reverse Indexes. The first step to fixing these problems it so see if you have them and if you do the second is to set about fixing them. You could be costing yourself CPU cycles, I/Op's, and space and never even know it.”

If you’ve been a DBA for a while you will inevitably inherit a system where you find indexes being used in less than optimal ways.  A lot of this is created by turn over in a company, going with all of the suggestions from DTA (Database Tuning Advisor), or having too many cooks in the kitchen.

It is possible to get things like Reverse Indexes, Duplicate Indexes, and unused Indexes.  You may be asking,  “What do those terms mean?  What secret ninja SQL Language are you speaking?  I know Clustered and Non-Clustered, but what-in-the-sam-hell is a REVERSE index!?”

It’s alright Dear Reader, no new secret terms.  A Duplicate index is just an Index where the physical structure exists more than once on a table.  Take the following Table:

CREATE TABLE Students(

          studentID int identity(1,1) primary key clustered

          ,ssn char(9)

          ,firstName varchar(50)

          ,middleInitial char(1)

          ,lastName varchar(100)

          ,gender char(1)

          )

If we created a Non-Clustered Index on the SSN column and we called it nclx_Students_SSN, and then someone else made a Non-Clustered Index on the SSN column and called it nclx_Students_SSN2 we would have a duplicated index.

“But Balls”, you say, “I would never do that!”

Of course not, you wouldn’t ever do that on purpose.   As code gets migrated from Dev to Production perhaps the Developer or Jr DBA adds an index that they didn’t realize you already has in place.  Or maybe you get a query plan with a “Missing Index hint” in Dev, only that index had been created as an urgent Production change, and never got implemented in Dev.  Migration comes around and as long as the names are different, WHAMO, you have two Non-Clustered Indexes on your SSN Column.

This example might not seem that bad, but imagine a 50 row table with a duplicate Non-Clustered Index on 5 rows, 10 rows, or 15 rows.  That’s a lot of extra data having to be persisted to disk and maintained.

"I wish my abs..I mean... databases where in shape"

Using the previous table let’s know make a Reverse Index.  We’ll create a Covering Non-Clustered index for a stored procedure that requires the SSN, FirstName, and LastName fields.  Some farther down the road you’ve left that company and a new developer is writing a different block of code and a different stored procedure and they create their Non-Clustered Index on LastName, FirstName, and SSN.

Then you have Unused Indexes.  These are the indexes that it seemed like a good idea to build, but nobody is using them.  In some places you have code that gets retired, but we still need the database structures,  in the Data Modeling phase Indexes were designed that were not used, or Database Tuning Advisor recommended it and it just wasn’t used.

Finding these are important because we are maintaining them, but the slackers do not contribute to our query performance. 

WRAP IT UP

So our goal for the hour will be to discuss Indexes make sure that we have a good foundation in them and what they store so we can understand why these 3 types of indexes are bad, and then use some scripts and DMV’s to identify them.

I'm also doing this presentation in a much longer format for SQL Live 360 in December of this year, as well as a couple more presentations.  Click on this link to check out Live 360!

I hope you’ll get a chance to stop by and join us!

Thanks Again,

Brad

DBA Study Guide

http://www.flickr.com/photos/caledonia09/4999119065/

 Hello Dear Reader, over here at Pragmatic Works we’ve been growing like weeds.  For the most part we are looking for Sr level people for Sr. Level positions.  Part of that process is interviewing.  Going for a job as a Sr. Consultant is a bit different than going for a job as a DBA. 

Today’s market for DBA’s is quite good.  If you are looking there are jobs out there.  A lot of the time after weeks or months of interviews when the “ideal” candidate has not been found you tend to lower the requirements.   It’s the Animal House “We need the dues” moment. 

Only for a business it is we need a butt in the seat.  You start asking the questions can we find someone with the right attitude, someone who can learn, someone who might not be at the level we want, but we can work with.  Often you can find a diamond in the rough and grow that person into the experience level you wanted.

In the Consultant biz it’s a bit different.  You can do that for Jr or Mid level jobs, but Sr level positions require you to really know your stuff.  You cannot expect a person to know everything, and one person’s Sr is another person’s Jr.  Not to mention there is a wide area of DBA expertise to be considered.  But we have to draw a line in the sand, and Knowledge is very important.

Can you answer some of the following questions:

1. What is a heap?
2. What is a Clustered Index, a Non-Clustered Index, and what are the differences between the two?
3. What is a Page Split?  
4. What is a Forwarding Pointer?
5. Why do they matter?

If you cannot then I wanted to toss out some learning resources that cover a wide breath of area.  This is similar to the Microsoft Certification exams where they say know how to Baseline a server, couple different ways to skin that cat, so I know there are a LOT of different things to each very general area.

(*Note no actual cats were skinned in the process of writing this blog).  

This is just a collection of books that I’ve read over the years.  Some go in depth in particular areas, some are general and cover many.   My buddy Mike Davis (@MikeDavisSQL | Blog) wrote a similar list for BI folks if you are interested in that click here to read more.

But I wanted to toss them out so if you are looking for a good book you can find one.  Just looking to grow in a particular area?  Then these will help you as well.

Internals:  If you are looking for a book on Internals you cannot go wrong with Kalen Delaney(@SQLQueen | Blog).  The 2012 Internals book is due out in November, and I can’t wait to read it.  This book has many wonderful contributors and is well worth the money even though a new one is on the way out.  I cannot recommend this book enough.

http://www.amazon.com/Microsoft%C2%AE-SQL-Server%C2%AE-2008-Internals/dp/0735626243/ref=sr_1_1?ie=UTF8&qid=1349186501&sr=8-1&keywords=sql+server+2008+internals

Internals/Extended Events/Troubleshooting:  Christian Bolton (@ChristianBolton | Blog) put together an All-Star team for this book (a 2012 edition is due out soon as well).  It not only covers internals but tools to diagnose them from some of the Premier experts in the field.  I put this neck and neck with any book.  If you work with SQL Server 2008/R2 you should own a copy.

http://www.amazon.com/Professional-Server-2008-Internals-Troubleshooting/dp/0470484284/ref=sr_1_2?ie=UTF8&qid=1349186501&sr=8-2&keywords=sql+server+2008+internals

Query Tuning:  Grant Fritchey (@GFritchey | Blog) is a damn nice guy.  I don’t understand why people think he’s a Scary DBA, (Grant thanks for the advice on the Katana collection and sharpening swords in front of the daughter’s boyfriend before dates, priceless).   I just don’t understand the scary thing at all.  Regardless of his disposition Grant is the guy that wrote the book on Query Tuning and Execution Plans.  He is a master in this field and the only people I would regard higher are the people Grant would recommend.

http://www.amazon.com/Server-2012-Query-Performance-Tuning/dp/1430242035/ref=sr_1_1?s=books&ie=UTF8&qid=1349186863&sr=1-1&keywords=sql+grant+fritchey

Clustering:  Alan Hirt(@SQLHA | Blog) is to clustering what Grant Fritchey is to Query Tuning.  I’ve attended Alan’s pre-con’s, read his books, and watched his generous and free advice via #SQLHelp.  If you are working in clustering you should have Alan’s book it will point out best practices and save you head ache’s (I’m looking at you government SOC’s Image when setting up a 2008 Cluster).

http://www.amazon.com/Server-Failover-Clustering-Experts-Voice/dp/1430219661/ref=sr_1_1?ie=UTF8&qid=1349186559&sr=8-1&keywords=sql+server+clustering

Replication: I wanted to recommend a replication book however, I haven’t purchased this one.  My friend and co-worker Chad Churwell (@ChadChurchwell | Blog) is one of the smartest replication guys I've ever met and he recommends it. I’m making the recommendation because of Chad and I have done more replication as a Consultant that I did as a DBA.  I’ve set it up, I’ve fixed it, I’ve learned how to find out when it’s broken, what broke it, and why.  I’d also bet I’m not alone.  I’ve only read the free pre-view of the book and chapter wise it summarizes everything I’m looking for an Expert in Replication (other than experience).

http://www.amazon.com/Server-2008-Replication-Experts-Voice/dp/143021807X/ref=sr_1_sc_1?s=books&ie=UTF8&qid=1349187006&sr=1-1-spell&keywords=sql+server+repiclation

Mirroring:  I would put Robert Davis(@SQLSoilder | Blog) in the realm of Mirroring what Grant and Alan are to their respective books.  Robert has blogged incredibly useful and real world information about mirroring.  AND YES I understand that Always On Availability Groups are the way to go.  However, not everybody is on SQL 2012, and a solid understanding of Mirroring allows you to better understand all the goodness that is Always On Availability Groups.

http://www.amazon.com/Server-2008-Mirroring-Experts-Voice/dp/1430224231/ref=sr_1_1?s=books&ie=UTF8&qid=1349186777&sr=1-1&keywords=sql+mirroring

Hardware and Virtualization:  When it comes to hardware you don’t get much better than Glenn Berry(@GlennAlanBerry | Blog).  From his free Assessment Scripts on SQL Server Performance (Glenn's is here) is essential when you go onto a new server for the first time and try to holistically figure out what is going on right and wrong.  The first chapter alone taught me more about CPU’s and which to choose than years of experience had.  I was able to use this knowledge immediately.

http://www.amazon.com/SQL-Server-Hardware-Glenn-Berry/dp/1906434638/ref=sr_1_1?s=books&ie=UTF8&qid=1349186800&sr=1-1&keywords=sql+hardware+glenn+berry

Performance Indexing: Jason Strate (@StrateSQL | Blog) and Ted Krueger (@Onpnt | Blog) are incredibly smart guys.  SQL MVP’s, years of experience, and deep knowledge all combine to give you an answer to the age old question ‘What should I index and Why?’.  Indexing is a core thing that DBA’s should know about.  Adding, removing, finding good ones, and identifying bad ones are important.  Not to mention the answer to all of my previous questions are in this book.

http://www.amazon.com/Expert-Performance-Indexing-Server-Apress/dp/1430237414/ref=sr_1_fkmr0_1?s=books&ie=UTF8&qid=1349186724&sr=1-1-fkmr0&keywords=sql+server+expert+practices+2012

SQL Server 2012/ SQL Azure/Powershell:  I work with some pretty smart guys.  SQL MVP’s, Consultants, and their friends are just as smart.  These two books are a collabertaive effort between brilliant people  Adam Jorgensen (@AJBigData | Blog), Brian Knight (@BrianKnight | Blog), Jorge Segarra (@SQLChicken | Blog), Patrick Leblanc (@PatrickDBA | Blog), Aaron Nelson (@SQLVariant | Blog), Julie Smith (@JulieChix | Blog)…And MORE (sorry for the people I left out)!  If you are looking for information on SQL 2012 and how to use it go to the Bible and their other book on Professional Administration.

http://www.amazon.com/Microsoft-SQL-Server-2012-Bible/dp/1118106873/ref=sr_1_10?s=books&ie=UTF8&qid=1350569874&sr=1-10&keywords=sql+server+2012

http://www.amazon.com/Professional-Microsoft-Server-2012-Administration/dp/1118106881/ref=sr_1_8?s=books&ie=UTF8&qid=1349186653&sr=1-8&keywords=sql+server+replication

 WRAP IT UP!

A lot of books I know and no I don’t expect you to read all of them before an interview, but there are a lot of common theme’s in the world of SQL Server.  A good expert should be EXCITED about what they learn about.  They should be able to pick something tell me what they know, and I’d like them to do it in a way that I’m excited about it by the time they finish.

I love going to SQL Saturday’s, PASS Events, and Conferences because they make me excited to learn.  And I really love to learn.  Find something that you are passionate about, and learn it really good.  That kind of learning and passion is infectious and is exactly what makes all of the authors I’ve mentioned such great SQL Server professionals. 

Hopefully, whether you’re looking for a job or not, it will help you find something that you love to learn about.

Thanks,

Brad