70-462: Administering Microsoft SQL Server 2012 Databases

This morning I passed the 70-462: Administering Microsoft SQL Server 2012 Databases exam.  I feel quite happy with this accomplishment for many reasons, least of which is being woken up at 2 AM by my son and not being able to go back to sleep until about 4:30 or 5 – and still being a functioning human being at 6:30 AM when I woke up to get ready and at 8:30 AM when I was at the test center and ready to get started.  Such is the life when you have a newborn.  But I wouldn’t trade it for anything.

Speaking of which, preparing for this exam is something I’ve done off and on for the past month and a half or so with a gap of about 3 weeks in the middle.  I would guess overall I spent about 40 hours on it.  As a set up for practicing for the exam, particularly for practicing all of the high availability stuff which I do not feel particularly strong in, I downloaded and set up several virtual servers with Windows 2012 Core.  I  learned how to interact a bit better with the system through powershell, figured out the domain controller and active directory enough to create a domain and users, etc.  I’m still NOT great at any of this, but I felt accomplished getting the environment setup enough to talk to each other.  I was prepared.  I was going to do all sorts of cool tests! … I pretty much ignored it.  But it was about 10 hours of the 40 I spent.

Once the lab environment was set up, I think I started out relatively strong by looking over the exam objects and trying to determine what areas I needed to look at most.  I didn’t necessarily want to do the same thing I did with the querying test and study things which I was already relatively strong in as I felt a little bit like I wasted time with that test.  Looking at the list, however, it seemed like there was enough on it that I needed a refresher on that I determined that I ought to try to do a complete review.

So, I read the entire book Microsoft Press Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases book by Orin Thomas, Peter Ward and boB Taylor.   I’m not entirely sure how I feel about the book.  On the one hand, I believe that it is better than the Querying book (which I thought was really pretty bad).  On the other, it still feels a bit like it traded in depth and breadth of knowledge for a type of tutorial follow along.  This is probably really great for some people.  I found myself reading along and promising myself that I would come back and do them after I finished reading the whole book (I didn’t).  I do feel like this was a good refresher book, but I’m curious how much I would have learned from it if it were all I had.  Several of the reviews for it on amazon say that you HAVE to follow the links provided in the text in order to do so.

After finishing up with the book, I went ahead and took several of the practice tests that come with the book.  I did four in all; one the first night after finishing, a second on the second night, and two the night before the exam.  On each, I felt a little bit stronger on the questions I was seeing – but I was seeing a lot of the same questions again and again.  I did make sure to review the answers, both for those I got right and those I got wrong.  Some of the questions definitely had better explanations than others, but it was a good exercise overall.   Note: I saw at least one question that the answer was wrong… so, if you are fairly certain that you are correct, double check online and try to recreate the answer.  The people behind the practice tests are human too.

Finally, I watched the born to learn video*.  This video provided me with one awesome bit of knowledge that I really should have caught on to but hadn’t to this point.  Every answer has to pass the Plausibility requirement.  That means that every answer has to be, or at least have been at one point in time, a valid thing.  Therefore, all of the times in the past for practice tests where I sat back trying to guess if there really was a dmv named XYZ I was not using all of the tools available to me since it HAS to actually exist if it is presented.  Now the question is whether or not it still exists and whether or not it would be useful in the current scenario.  Upon hearing this I double checked just to make certain.

I can’t talk much about the exam itself because it is under NDA.  With that said, I believe that I can comment that my version of the test had a number of questions within the given range (40-60) and that I felt a lot better about the way it was written and the content than I did about the Querying test.  This had a few questions where you needed to know syntax or ordering or whatnot, but a large portion of the test dealt with theory and knowing which technology or setting to apply.  In that regard, it was similar to the 70-432 Implementation and Maintenance test.

Hopefully this helps a bit for anyone trying to go through the exam.  If you are doing so, consider joining the Google Community for the 90 days to MCSA challenge.  Good luck!

 

* Quick tip, I downloaded it so that I could watch it at 2x speed as it seemed that the presenters worked through the material at an extremely slow pace.  I find a lot of the technical videos that I watch could probably be consumed at a rate 1.5 to 2 times faster without loss of ability to follow along.

The (In)Equality of the spaces

Today I’d like to talk about an issue I ran into over the past week… It isn’t the first time that I have seen this, but it apparently didn’t sink in well enough the last time.  The issue has to do with how SQL interprets space at the end of a string when dealing with the equality operator.  One might expect the space to be counted when determining the equality of a string – it is, after all, being stored in the database.  And this is the case… kind of.

Lets look at a simple example:

 Declare @TestString as varchar(10) = ‘blah ‘;

Select case when @TestString = ‘blah’ then 1 else 0 end as myTest;

——-

Result 1

Here we have an trailing space on the variable.  I would probably expect this to return 0.  It does not.  We ignore the trailing space when it comes to equality.

Let’s look at a leading space.

Declare @TestString as varchar(10) = ‘ blah’;

Select case when @TestString = ‘blah’ then 1 else 0 end as myTest;

——-

Result 0

OK, this works as I would expect.  It is not, in fact, equal.  We treat spaces anywhere, other then trailing, as non-equal if present only on one side of the comparison.

Now, the real concern I ran into this week was not really having to do with either of these cases, but instead in the difference between how we interpret the trailing space with an equality, and what I would also consider to be an equality, the like operator with no wildcards.

Declare @TestString as varchar(10) = ‘blah ‘;

Select case when @TestString like ‘blah’ then 1 else 0 end as myTest;

——-

Result 1

Again, I’m a little surprised at the results here as we come back with a match.  It ignores the trailing space.  Now let’s reverse the assignment.

Declare @TestString as varchar(10) = ‘blah’;

Select case when @TestString like ‘blah ‘ then 1 else 0 end as myTest;

——

Result 0

Here we have the trailing space in the like clause.  No dice.

So, what have we learned?  I have no clue. I can say that there is an inequality in the way that we treat spaces with comparison operators in the database.  If you are finding that when auditing data you have differences in the counts, make sure that you are using the same operators, and that they are in fact doing what you would expect.  Particularly if you are storing patterns for comparison with a like operator, some of which contain wildcards and some of which do not.

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is written by Ted Krueger (@onpnt) and Jason Strate (@stratesql) – two guys who I’ve known for a few years now.  They’re fun and knowledgeable and if you ever run into them at a conference or on the street you should heckle them relentlessly.  It’s fun.

I got this book as a present to myself after trading in my coin jar for amazon money.  If the electronic version hadn’t been less than half of the physical book price I surely would have gotten that instead.  With that said, either version of the book is well worth it and this is a reference that I will be using quite a bit in the future.

The first half of the book goes through the basics of indexing: what they are; how you would create, alter or drop them; how they are stored; the differences between clustered, non-clustered, XML, full-text, spatial and column-store indexes; etc.  I was a bit surprised at the amount of detail that the authors went into for some of these topics (ex. storage).

The second half of the book is where the book really shines.  Particularly the last chapter on index analysis.  In this half, there are sections on: common indexing myths and best practices; how you would go about maintaining the indexes and statistics in your environment (you don’t just create them and then forget about them); what tools you can use to determine what indexes are currently working well in your environment, what indexes might work well in your environment if you were to implement them, and what indexes might be redundant; indexing strategies to best cover your workload; strategies for writing queries in order to best ensure that the indexes you have created are used; and the analysis of index statistics, wait statistics, and performance counters in order to help determine what in your environment could best use your attention.  One note: The section that is covered in chapter 8 relating to “surrogate keys” should instead be named “natural keys”.

This is by far the most comprehensive look at indexes which I have seen put together in one place to date.  I can’t recommend it enough.

70-461: Querying Microsoft SQL Server 2012

So, I had made the decision to go for the new certifications.  The next decision to be made was which to take first.  I looked over the areas of focus for each of the tests some more and decided to go ahead and take the querying exam.  It looked relatively easy as querying is something I do on an every day basis.

Feeling a bit like pushing myself I set up the exam for a little bit less than two weeks out.  I had a feeling that even if I took it the next day I should be able to pass.  However, I didn’t want to waste any time by taking the certification too quickly and having to retake it,  so I figured I’d spend just a few hours looking over the books online articles for the materials listed.  I also had access to the new Certification Prep Kick from Microsoft Press and figured I might skim through it to fill in a few holes.

Unfortunately for me, my compulsion to do things correctly kicked in after reading a few chapters from the prep kit book the first night and I ended up reading through all of it instead.  And I say unfortunately because I can not be kind if asked about this book.  It does not do any justice compared to Ben-Gan’s otherwise absolutely stellar books.

The information provided did not seem to go into enough depth to cover the fundamentals for several topics that were listed on the skills for the certification.  The code and sample exercises were often repetitive.  The depth to which the majority of the topics were covered was much weaker than previous books.  The practice tests included did not mimic the real tests in many ways (although I don’t think I can say how since we’re not supposed to talk about what was on the exam).  And, I’d say, most importantly, I don’t really think that this book provided enough information for one to read through it, use the practice material, and take the test.

So, all in all I ended up using my previous experience, reading the books online pages for a few topics and reading through the prep kit book.  I also took the practice exams that came with the book four times.  Overall I think I spent about 20 hours preparing for the exam.  At the beginning of the process I had figured I might spend 5, maybe 10.

The morning of the exam I figured that I would be in and out relatively quickly.  And, it really wasn’t that long of a process.  However, it took me about twice as long as I was expecting: not quite an hour and a half.  I’ll leave off discussing any real details about it other than to say that I passed, and that it was a more difficult experience than I thought it would be going in.

So, one test down with at least two more and in all probability six more to go for the year.  I’m not certain when I’ll set the next one up for, but more than likely some time next month.  I’d like to get the first three knocked out before the end of march as I don’t know how much extra-curricular time I’ll have after that.

 

More Certifications? But why?

I have decided to pursue the new-ish Microsoft SQL Server Certifications this year as one of my goals.  The certifications are the ones’ which most people refer to as the 2012 certifications but which have not been labeled as such intentionally.  I believe that this is the case since they don’t necessarily want to roll out a new test each time a new version of the software comes out but is not a major release (see: 2008 R2) and also because they don’t want to put themselves into the position of focusing on mainly the new features of a particular release.

I was a bit on the fence about going for the certifications as I am not certain that I like the idea of a certification only being valid for a certain period of time – in this case, three years.  I get that often times the knowledge that you had when you took a certification is not only questionable memory and skill wise, but also from a technology standpoint, when you look back three or more years later.  However, it still seems a bit off to me that you theoretically won’t be able to put it down on your resume or CV after a certain period of time.  Heck, if I really wanted to pimp a MCDBA certification ten years later, why not? (I don’t have one, nor would I continue to list it this far removed from that technology platform).

In the end, there were a few factors that pushed me in the direction to “write” these exams.  I am a bit sadistic and enjoy taking the tests as a way of gauging whether I really know mostly what I’m talking about.  It helps me to maintain some semblance of an understanding on the portions of the product stack which I don’t often use professionally.  I believe that they have some (small) value on resumes as another point of interest.  And, perhaps most importantly, they help out with the Microsoft Partner program and the company which I am working for has need for a few of its’ employees to get the new certifications to retain the benefits of the program.

After the decision was made to go for at least one of the certifications I had another choice: Take all of the tests individually as though I had not earned any 2008 certifications, or take the upgrade path for the MCSA and MCSE data platform certifications.  Oddly enough, this took me some time to come to a decision.  Doing the upgrade method would have saved me from taking two tests (one for the MCSA and one for the MCSE), which is a good amount of money, but it would have meant needing to focus on that much more for each of the tests.  In the end, I decided to go through and take the normal certification tests – using the certification pack for the MCSA that is available now which saves about 15% and gives you a second shot for each of the exams.  Also, there is some talk of a reward for each of the tests through my work… so more tests might be better in that direction as well.

So, this year I’ll be taking at least three of them since that is what I paid for up front.  Hopefully they will provide me with some form of satisfaction or benefit.  And, hopefully the public won’t mind reading about them now and again…

 

 

A look back at 2012 and a short look forward

2012 was a great year for more reasons than I can possibly list.  It was not, however, a great year for blogging or following through with my goals for the year.

Some highlights:

• Had another great year with my Wife
• Took a vacation to the mountains for our anniversary
• Bought a house
• Got a new job (helps with long term financial goals)
• Paid off my student loans
• Paid off my new-ish car (which we bought late 2010)
• Read or listened to 53 books
  • 8 Technical, or pseudo-technical books
  • 2 professional development books
  • 28 novels
  • 3 audio books
  • 2 novellas
  • 9 graphic novels
  • 1 children’s book (actually more I think, but I didn’t record them all)
• Went and Spoke at 3 SQL Saturdays (Jacksonville, Pensacola, Orlando)
• Made it to the 9 Jacksonville SQL Server Group meetings
• Hiked / Walked about 300 miles, ran about 120 miles
• Had a family outing to a Buccaneers and Chiefs game.
• Somewhat regularly had game nights either at our place or friends place for board games and general shenanigans
• Reconnected with several older friends while playing video games (diablo 3 and guild wars 2 have been my free time’s downfall this year)
• I got to see my sister, who had been living in Brazil for the past year + and in Scotland for several years before that.
• …. and we’re having a baby boy who is due in March / April 2013!

Like I said, a really great year.  I was at first disappointed when I saw that I was missing goals as the year went by.  I’d think I could catch up with them but knew that it probably wasn’t feasible.  But, as the year closed, I began to think about goals a little differently again.

I think that it is perhaps more important for me to set and reach shorter term goals.  One month at a time, re-evaluate each month.  I’m trying to set tasks for each week to help get to the monthly goals.  And, I am making sure that the goals I am setting fit in to long term goals that I have; that I’m not doing too much that doesn’t fit in to those goals. (Note that these goals need to include things like spending time with friends and family and having fun)

In either case, I’m not determining 2013 goals.  I have goals for January, but they are, for the most part, non-technical in nature.  About the only things that ARE technical in nature that I have for January is to read the Expert Performance Indexing book by Jason Strate and Ted Krueger and to take the 70-461: Querying SQL Server certification (posts upcoming).

I have no clue what this year will bring, but I know that it will include a lot of changes.  I know I’ll have less free time, but in the past I’ve actually found that it has helped me to focus on the important things.  Most of all, I can’t wait to meet my son and begin to have experience all of the things that come with being a part of a growing family.

Data Quality Assessment (Arkady Maydanchik)

Overview

Overall this is a very strong look into data quality assessment. In it, we examine several subjects including:

• What is meant by data quality
• Defining our data through collection of general meta data
• Profiling our data to see what it is that we really have
• Defining our data quality rules based off of the meta data and data profiles
• Preparing a system to capture the results of the data quality rules check
• Iteratively improving the data quality rules and capture process

Some subjects are covered in more detail than others. To me, this is a very good thing. For instance, there is a lot of source material out there for gathering meta-data. There is quite a bit out there for conceptual versus relational data models. The information presented is enough to pique your interest without bogging you down if you are unfamiliar with a given field.

With that said, we do get into quite a bit of detail on what I would consider to be the important subjects: what it that is meant by state dependent objects, what it is to data profile and how a data quality assessment project should be put together and whom it should contain. In addition, as would be suggested by the title of the book, the section on data quality rules for the assessments is very good. Here we get chapter level detail on Attribute and Domain Constraints, Relational Integrity Checks, Rules for Historical Data and State-Dependent Objects, as well as Attribute Dependency Rules.

Once we have a firmer grasp on what each of these mean, we go into the actual implementation details through the chapters on Implementing Data Quality Rules, Fine-Tuning the Rules, Cataloging Errors , Measuring Your Data Quality Scores, Implementing a Data Quality Meta-Data Warehouse and finally Recurrent Data Quality Assessment.

It should be noted that, like many other books in the field, this is not a complete reference for data quality. The authors states that we gloss over the subjects of data cleansing, monitoring data integration interfaces, ensuring quality of data conversions and consolidations, and building the data quality meta-data warehouse. I am not sure that I even agree with the list of subject areas that are skipped, but I find it somewhat interesting that the author continuously mentions that this book is the first in a series which will capture all aspects of data quality initiatives. When I look around it is the only title available. I think this volume is relatively strong on its’ own and probably could have done without the references to the non-existent material by stating that the subject area was out of scope for the book (which is essentially all that was done by these references).

Book Review: SQL Server Hardware

Overview

First off, I really enjoyed this book. I have been following Glenn Berry’s posts for a few years now and when I heard that he was finally going to write up his musings on the aspects of hardware as it relates to SQL Server I was excited. The book is a great introduction to the subject and consolidates a lot of the information I have been receiving piecemeal from Glenn’s blogs for the past several years. I would highly recommend this to a junior or mid level DBA who is looking to enhance their knowledge of hardware and how the choices when you are building out your server can effect your SQL Instance.

Chapter 01: Processors and Associated Hardware

The beginning chapter goes into detail on how you might evaluate processors by looking at the cache sizes, clock speeds, hyper threading, sockets, cores, logical cores, etc. We get some experience talking about the various generations of both intel and AMD processors as well as a look at evaluating motherboards. Although the main theme of the book seemed to be bigger better faster, this chapter went out of the way to explain that a newer two socket machine can easily outperform an older four socket or larger machine for drastically cheaper licensing costs.

Chapter 02: The Storage Subsystem

This chapter goes over the other major component of your server: the disk subsystem. Here we look at various types of disks, how these are attached or communicated with by the server, and how they might be arranged in RAID arrays. The chapter does a decent job of tying it all together and relaying the correct information to help out the junior or mid-level DBA who has not had to learn this information in the past.

Chapter 03: Benchmarking Tools

This chapter introduces us to a lot of really good tools for understanding how it is that your hardware is performing. We first look a little at some of the standard application benchmarks with the TPC-C, TPC-E and TPC-H benchmarks. After that we investigate other tools like SPEC, Geekbench, HDTune, CrystalDiskMark, SQLIO, SQLStress and SQLSim. These give us some general metrics where we can compare the CPU, Memory, Disk subsystem and overall application scores. None of the tools are investigated in great depth, but it is a really good place to look to discover tools which might greatly help you out.

Chapter 04: Hardware Discovery

We take a brief look at some other tools – CPU-Z, MSINFO32, The Task Manager, and the properties dialog – which are used to investigate the type of hardware on which you are currently running.

Chapter 05: Operating System Selection and Configuration

The chapter investigated the differences between Windows 2003, 2003 R2, 2008 and 2008 R2. There is some discourse on support, power savings modes, configurations, and service packs. All in all interesting material, that boils down to wanting the latest and greatest.

Chapter 06: SQL Server Version and Edition Selection

The chapter goes through versions of SQL Server from 2005 to 2008 R2 (with a few hints of what is to come in Denali (a.k.a. SQL 2012)) along with what the improvements and differences there were between versions and editions. I thought it did a really good job of walking through these differences as well as crafting a relatively good argument for why you want the latest and greatest.

Chapter 07: SQL Server Installation and Configuration

This chapter was quick to read as it included a tutorial with lots of good screenshots for walking through an installation which is probably a pretty good start for those users without much experience installing SQL Server. It also included a pre-installation checklist and some hints on settings you might want to change post installation which are good for users of all levels of experience.

Appendix A: Intel and AMD Processors and Chipsets

I really enjoyed this appendix. Not so much that it is all that terribly helpful, but rather that it is interesting to see the walk down memory lane of somewhat recent processors and how it is that they have evolved along with approximate dates of release. I’m not sure how terribly helpful this would be for helping to determine what processor and chipset it is that you would want to purchase, but I don’t think that this was the idea of the appendix anyway.

Appendix B: Installing a SQL Server 2008 R2 Cumulative Update

An appendix which walks through the steps you would need to find, download and install a CU. This is relatively verbose and probably very good instructions for a beginner.

Appendix C: Abbreviations

Two or three pages listing out some abbreviations used in the industry. Might be helpful for a beginner who is trying to learn the alphabet soup.

Pool League Project: Post 1 Introduction

Overview

Our pool league generally plays once a week (we miss some weeks for holidays and events).  To this point there have been 9 “seasons”, only the past two or three of which I have been a part.  However, we have the records going back to the beginning.

My intent is to go back to the first session and enter the information into a database, make it easy to enter new information (probably through excel), and then report off of this information both at the relational level and as an analysis services cube.

Pool League Description

In the league, we have had between four and five teams per session with four players per team per match.  It should be noted that players have been known to switch teams between sessions, and to fill in for other players if their team has a “bye” week.  Furthermore, teams can have more than four players in a single session, but only four (or less) will be active in a particular week.

During each match, two teams will play each other with each person competing in 8 games; two games against each of the four opponents.  It is not unheard of for players to have to “double up” if the team does not have all four players so it is possible for one person to play double the normal amount.  Sometimes, although very infrequently, two players will play twice per team.  If a player would have to play all four matches the game is a forfeit.

The league is played as a “handicap” league where the average number of victories per eight games of each player is used to determine the results of which team “should” win and the other team is given a number which is equal to the difference between the two in an effort to help even things out.   If a player has not played at least two games, they have no handicap and the other team is able to remove one of their players handicaps (to this point, it has always been the top handicap).  During each game of the match it is tracked who broke, who won, and whether or not the nine ball was sunk on the break (an automatic win).  At the end of the match, the games won are totaled, along with the handicap, and a winner is decided.  If there is a tie, the team with no handicap wins.  If there was no handicap, the team that won the most rounds (sets of games between opponents) wins.  If the rounds were all ties, or if each team won one round and tied the other two, the game is a tie.

The players handicaps are held in an excel spreadsheet where we keep track of the total number of games played and the total number of victories.  The spreadsheet does not currently have the details to get to these aggregate numbers.

Example

Example Match Score Sheet: “The Fuglies” beat the mighty “Trololo”.

As you can see, the handicaps meant that this game should have been an even game (15 to 15).  However, as there are 32 games per match, both teams were slated to win less than one half of the games (15 vs 16).  So, even if we both met their handicaps, someone would go over and win (32 – 30 = 2 games over handicaps).  This shows that, in essence, the handicaps are just used to get a relative measure of the skill of each team.  No nines were sunk on the break (would put an asterisk next to the 1 in the sheet if that were the case).

Book Review–Database Benchmarking

Database Benchmarking by Mike Ault, Kevin Kline, Dr. Bert Scalzo, Claudia Fernandez, Donald K. Burleson

Overview

I will give this book one thing, it was relatively easy to read.  I finished it in just a few days while working on a request at work to help determine standards going forward for benchmarking databases to do performance testing prior to major code changes or new implementations. 

In essence, I’ve been told if you don’t have anything good to say, don’t say anything at all.  So, I’ll try to find something good to say…

The book did remind me about the TPC-C and TPC-H database standards that are often used for benchmarking.  It caused me to look up other blog posts that introduced me to Hammerora which appears to be a decent tool that I’ll be using to produce database load while testing monitoring software.  The book introduced the readers to the concepts of benchmarking and had a few measurements that I would consider reasonable and good for tracking.  It told the user that tracking measurements over time is good and showed various examples of graphs showing the trending of that information. It even had some pretty pictures and silly cartoons.

With that said (See I said something nice!), the book should have been labeled as “Database Benchmarking with Quest Benchmark Factory”.  It felt like a marketing book for this software instead of a good book for theory.  The writing was inconsistent and it felt like the authors didn’t really talk (and the editor missed big time) in that several of the concepts were very briefly explained, again and again, in each chapter.  There was only a minor portion of one chapter which mentioned how you would actually benchmark a non-standardized database.  This caused the book to feel like database benchmarking was really only applicable for testing new hardware or new RDBMS software.  And that is not at all correct.

My advice would be to find another source of information for this topic.

A big thanks to Kevin Kline whom I got this autographed copy from at one of the SQL Saturdays a year or so back.  Even though it wasn’t what I was hoping for it was good to have. 

I read this book 2011-12-31 to 2012-01-02.