SQL

Are IT Certifications worth it?

September 6, 2017 by Kevin3NF 4 Comments

I’ve been in IT for 2 decades. I got my MCSE in 1999. Via a BootCamp training class that Microsoft paid my way into.

I was the walking definition of a “paper MCSE” – all knowledge and no experience. At the time the MCSE was Windows NT 4.0, IIS 4 and some other elective, along with some networking tests. I really don’t remember, other than TCP/IP which I had to take, fail, study and take again.

When i contracted at Microsoft (the second time) back in 2005, there were 20+ contractors all there getting ramped up for the SQL 2005 release. Technically they were ramping up for SQL 7 and 2000, as the Full Timers were doing 2005 support. Most of them had the MCDBA certification….many hanging in their cubes. Not one of them could admin their way out of a paper bag. Nice people, but no experience.

I decided at that point that I would not be pursuing any more certifications, as these guys were making it look bad and watering the value down. Certification tests are expensive, when you consider study time, prep materials, practice tests and exam fees. I decided I would just learn the SQL Server things I needed to know and that were in wide use (skipped right on by Notification Services!). This has served me well for the 10+ years since that contract at MS ended.

Fast forward to last month when I decided to quit being an old dog and go learn some new tricks. Specifically Azure tricks…infrastructure and SQL, in parallel (MAXDOP 2).

I’m working through that. I’m not ready to even think about the first test yet, because I’m taking my time and actually LEARNING the material, whether its on the test or not. Shocking, eh? 😀 (Pluralsight is amazing for this, BTW).

The thing that triggered this blog came from a LinkedIn post this morning that caught the ire of Adam Machanic (w|t), as well as others:

Hello Folks,

Can someone please send me MCSA 70-764 and 70-765 exam dumps in PDF format.

My Email ID is: redacted@whatever.com

Thanks in advance..

Most of the responses were along the lines of “why not just study the material, you cheater?”

The original poster claimed this:

You are right . in my past I worked on old SQL version as of now working with new versions and everything is automation over here …hence just for reference I need them …

I call shenanigans.

OP is going for the “memorize, test, apply” method of certifications. Maybe not apply for a new job. Maybe just to keep one. Who knows. But still shenanigans….and in .PDF format if you please.

As you might expect, this spilled over to Twitter, as many things do. It was met there with a healthy dose of venom for those that shortcut the process, as well as places that issue fake certifications (I didn’t know that was a thing!).

For fun, I contacted my friend Gregory Knight at GTN Technical Staffing and Consulting (my first independent customer, way back in 2001) for some insider info on the other side of the recruiting and client pipelines.

My Question:

“Hey…curious how much recruiters and clients actually value Microsoft certifications these days, if at all…other then for resume filtering…thoughts?”

Gregory sent this wide internally at GTN and got these responses back:

I do. It tells me the following:

How much extra work a human has put into his/her career.
Suggests drive, love of the tech, going the extra mile
Low-level cert only or many unfinished certs (CCIE with no lab) and/or never finished college suggests lack of follow through.
Certs all over the place, e.g. MuleSoft, CCNA, MSCA suggests lack of career focus.
Date of completion matters. It tells me what the person was doing at a particular point of time.
E.g. an infrastructure/network candidate passing himself off as a network expert but has lots of Microsoft certs tells me the person likely leans more server side

It really depends on how much our individual client values it. MS Certs tend hold value and do demonstrate a general mastery of the technology. With that said, clients that do not require it tend to not be impressed or care enough about it to make a difference in choosing to select, interview, or hire a particular candidate.

I view certifications, any certifications, like a bachelor’s degree. It does not open the door for a candidate, but it can prevent the door from being slammed shut in their face…

Real world experience > certificates any time unless it’s Cisco.

Production > Lab

So…this settles nothing of course as far as whether you should pursue an IT cert or not. If you do it the right way, you’ll learn things and have something to show for it. If you shortcut the process, you’ll be found out when the time comes and others are depending on you to know what you are doing.

I’d love to hear your thoughts in the comments section. Any comments bashing people (candidates, recruiters or clients) will not get approved. Thoughtful, considerate discussion? Yes please 🙂

Thanks for reading!

Kevin3NF

September DBA Fundamentals training in DFW – Free

September 3, 2017 by Kevin3NF Leave a Comment

I will be teaching an all day (9-5) class on SQL Server Database Administration fundamentals at the Microsoft building in Irving/Las Colinas on September 15, 2017.

Update: This is now a free class. There will be a donation bucket, with 100% going to the Red Cross.

The target audience for this is System admins, Developers, Tier 1 support staff, IT managers…anyone that has to deal with SQL Server on a regular basis and finds themselves Googling everything.

• What makes up a SQL Server

• How a database is structured

• How to install and what to look out for

• Finding what you need in Management Studio

• SQL Security Basics

• How to create and manage backups

• How to create a database, 3 different ways

• Basic performance troubleshooting

• Picking a Disaster Recovery option for your environment

Please see the FAQ section of the registration page, and feel free to contact me for anything not covered there.

Thanks!

Kevin3NF

SQL Saturday Minnesota 2017

August 31, 2017 by Kevin3NF Leave a Comment

Now that the official schedule has been published, I am pleased to announce that I will be doing a pre-con and a regular session at SQL Saturday Minnesota on October 6-7. 2017.

The Friday pre-conference will be “DBA Fundamentals for the Junior, Accidental and non-DBAs.” This will be a wide ranging day-long discussion on the underlying fundamental pieces of SQL Server Administration that are beneficial to both new DBAs, and those that have to work with or fill in for them. Please see the link for details, and feel free to contact me with any questions. There is a $110 (plus service fees) charge for this, which is split between me and the local SQL User group organizing the event, after expenses.

The Saturday regular session will be “Backups for new/non-DBAs…the Why, not the How”, where we will cover Disaster recovery scenarios and the part SQL Server plays in the recovery. We won’t spend much time on specifically defining backup types, etc. Currently this is scheduled for the 1-2pm time slot, but that may change.

Edit: my Saturday session is now 10:15am – 11:15am 🙂

I’m very excited to be coming to the MSP area! I even have a friend that lives in the area that I get to see for the first time in 3 years…plus all of the new friends I will make at the events!

Thanks,

Kevin3NF

SSIS Memory errors

August 30, 2017 by Kevin3NF 7 Comments

New one today….memory errors from SSIS package execution.

Update: Check out THIS article from my friend Tim on SSIS memory

Background: I’m a Database engine dude. I’m well versed in how the engine uses memory, buffer pool, mem-to-leave, etc. I teach these things to others around me all the time. I rarely use SSIS other than install and verify jobs run successfully. Until yesterday I had never created a functional package outside of the Import/Export Wizard.

My team supports an in-house, proprietary application written for us by a Very Big Vendor (VBV). We have this application and supporting apps installed in Dev, Test, Stage, and Prod.

There are multiple SSIS packages (37 specifically), each with dozens of tasks inside them. Many dataflow, lots of transformations, etc.. They are called by .Net applications that run as services on SSIS dedicated servers.

SO….

A very bright (and I mean that sincerely) analyst on my team brings me a piece of paper printed out with errors all over it from SSIS. I got the .txt copy and it looks like this:

Version 12.0.5000.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started: 7:48:25 PM
Error: 2017-08-25 19:48:35.17
Code: 0x8007000E
Source: dft_DataFlow1 SSIS.Pipeline
Description: Not enough storage is available to complete this operation.
End Error
Error: 2017-08-25 19:48:35.19
Code: 0x8007000E
Source: dft_DataFlow1 SSIS.Pipeline
Description: Not enough storage is available to complete this operation.
End Error
Error: 2017-08-25 19:48:35.19
Code: 0xC0047012
Source: dft_DataFlow1
Description: A buffer failed while allocating 10485760 bytes.
End Error
Error: 2017-08-25 19:48:35.20
Code: 0xC0047011
Source: dft_DataFlow1
Description: The system reports 2 percent memory load. There are 1,099,441,074,176 bytes of physical memory with 1074776596480 bytes free. There are 4294836224 bytes of virtual memory with 65310720 bytes free. The paging file has 1153128165376 bytes with 1124943544320 bytes free.
End Error
Error: 2017-08-25 19:48:35.20
Code: 0xC02020C4
Source: dft_DataFlow1 olesrc_Package1Stage_Package1 [259] Description: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.
End Error
Error: 2017-08-25 19:48:35.22
Code: 0xC0047038
Source: dft_DataFlow1 SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on olesrc_Package1Stage_Package1 returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
End Error
Error: 2017-08-25 19:48:37.58
Code: 0x8007000E
Source: dft_HOS SSIS.Pipeline
Description: Not enough storage is available to complete this operation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 7:48:25 PM
Finished: 7:48:44 PM
Elapsed: 19.047 seconds

This came from our UAT/Test environment AND a client specific setup that had all components in one server.

Notice the red sections above:

Not enough storage – was this physical memory, virtual memory or disk?
Buffer failed to allocate (10MB) – Why?
2% memory load – yes, this box has 1 TB of RAM…plenty for what we are doing you would think.
Attempt to add a row – well, yeah…if SSIS uses buffers and cannot allocate one, there are either none, or they are full. Makes logical sense to me

So now what? Off to Google of course…because nobody here is an SSIS admin.

My first generic query was to get a general understanding of how SSIS uses memory, which lead me here:
SSIS Out of Memory/Buffer Manager errors

Nice high level overview, with some key elements: DefaultMaxBufferSize and DefaultMaxBufferRows, as well as a link to a very detailed (and old) SSIS performane tuning article. See the section on Buffer Usage. and Measuring Performance (scroll to SSIS Performance Counters).

This gave me a very broad understanding that while SSIS uses buffers in a similar way to the DB Engine, they are not part of a configurable dedicated memory space.

The short version is this: SSIS looks at the estimated row size of the incoming data, then does a bunch of math to figure out how many rows it can fit into a buffer. Too many or too few and it does a little magic behind the scenes to get the best performance it can. Remember – these are estimates. If you have wildly different datatypes and row sizes, it may guess wrong, or less than optimally.

This made me wonder:

how many buffers were needed
how to measure this
is something else fighting for the memory space that SSIS wanted for new buffers
Is there possible memory fragmentation (is that a thing!?!?)

So I did something I’ve never done in my career: I opened Visual Studio and created a new SSIS solution, containing one package that imported a .txt file into SQL Server. Straight import, no transformations. I just needed something I could run and have Perfmon tracking.

The fun part about making a large enough .txt file is taking the contents and copying and pasting them over and over. I wound up with a file of 19 million records….Notepad actually saved all 3GB of it, but wouldn’t open it again. Neither would Notepad++. But SSIS was fine with it.

Here’s the very advanced package I developed:

Note that the Data Flow Task is highlighted, and the DefaultBufferMaxRows and DefaultBufferSize parameters are at their default values.

Once I ran, debugged and got this to complete, I set up a user defined Data Collector Set in Perfmon and started it:

I messed around with a lot of counters until I settled on these. All I really wanted to see was how many 10MB buffers I was using in my simple test, and verify I could accurately measure this in the problem environment.

I got this result:

Note the black line the Buffers in Use counter…in my case it started as 8. This capture is from when I had the package reading the same 19 million row .txt file into the same table simultaneously. It used 8 each time.

I was unable to reproduce the errors on my machine, no matter how funky I got with the package, since I am running 16GB of RAM, and you cannot configure a cap at the SSIS or package level (such as the SQL Server MaxMemory setting).

But I was confident that I could go to the UAT team and set this monitoring up to at least tell them what was happening, so we could then work to track down why.

At this point I was 8 hours in and still Googling things, and I found this thread on DBA Stack Exchange.

Crud. Really?

Did you miss it too? Go look at the very first line in the error text I posted.

Version 12.0.5000.0 for 32-bit.

Dagnabbit. 32-bit applications are limited to 2GB of memory by deafult. With some switchcraft (aka the /3GB switch) you can bump it.

When setting up the environment the person doing it pointed the SSIS packages to the C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn directory instead of C:\Program Files\Microsoft SQL Server\120\DTS\Binn.

We changed this config parameter in the application setup and everything worked perfectly.

SSIS filled up the 2GB with buffers (big file, lots of columns) and fell apart. Simple as that.

Hope this helps someone…please let me know if it helped you by commenting!

Update: If you read this far, also check out THIS article from my friend Tim on SSIS memory.

Thanks for reading!

Kevin3NF

My Pluralsight course for new SQL Server DBAs

Follow @Dallas_DBAs

PASS Summit Game Night poll

August 29, 2017 by Kevin3NF 3 Comments

Hello fellow SQL Server people!

I have volunteered to coordinate some of the activities for the 2017 Game Night at PASS Summit in Seattle on November 2, 2017. Please take a second to help me compile a “top 10” list of games that PASS can invest in to get the evening started.

Pick any three you like. If you have a favorite game that you think should be on the list, please add in the comments, send an email to [email protected], or DM me on Twitter: @Kevin3NF. You can also contact to me to volunteer with setup and cleanup!

A good game here is likely going to be one that 4 or more can enjoy, is relatively easy to learn, and doesn’t take a huge amount of space. All suggestions do need to adhere to the PASS Anti-Harassment Policy. I am pretty sure gambling is out as well 🙂

Poll closes 3pm Central, September 8.

Thanks!

What game(s) would you most like to see at Summit Game Night?

Pandemic (10%, 8 Votes)
Exploding Kittens (10%, 8 Votes)
Card Games (generic decks) (9%, 7 Votes)
Ticket to Ride (7%, 6 Votes)
7 Wonders (7%, 6 Votes)
Apples to Apples (6%, 5 Votes)
Catan (6%, 5 Votes)
Munchkin (6%, 5 Votes)
Codenames (6%, 5 Votes)
Fluxx (5%, 4 Votes)
Dominion (5%, 4 Votes)
Carcasonne (5%, 4 Votes)
King of Tokyo/New York (4%, 3 Votes)
SpyFall (4%, 3 Votes)
SuperFights (2%, 2 Votes)
Taboo (1%, 1 Votes)
Sushi Go (1%, 1 Votes)
Love Letter (1%, 1 Votes)
Chess (1%, 1 Votes)
Power Grid (1%, 1 Votes)
Qwirkle (1%, 1 Votes)
Gang of Four (0%, 0 Votes)
Tiny Epic Galaxies (0%, 0 Votes)
Dead Man's Draw (0%, 0 Votes)
Captain Sonar (0%, 0 Votes)
Pokémon Go (gather and go catch 'em all) (0%, 0 Votes)

Total Voters: 29

Loading ...

TSQL Tuesday: Interviewing Patterns and Anti-Patterns

August 8, 2017 by Kevin3NF 4 Comments

T-SQL Tuesday is a monthly blog party hosted by a different member of the SQL Server Community each month. It is the brainchild of Adam Machanic (b|t). This month’s host is Kendra Little (b|t) who gives us the topic of “Interviewing Patterns and Anti-Patterns“.

Of the suggested choices, I picked:

You can write about your own highs and lows as a candidate or as an interviewer

I’ve been in the workforce for 26 years as of this writing…and in IT since 1998. I have bounced around between Independent Contractor/Consultant, to Full-time employee, to W-2 contractor working for someone else’s client. I’ve been on probably around a hundred face-to-face interviews, several hundred if you add in phone screens and recruiter calls. On the other side of the desk I’ve interviewed dozens of database professionals, either to come work for/with me, or on the behalf of recruiters that didn’t have a SQL Server person that could do the tech screen.

I’ve seen a few things good, bad and ugly along the way. I’ve been asked a few brilliant questions, and some seriously stupid ones (“What kind of tree would you be?” Really? Dude….)

My number one rule for a candidate BEFORE you go into an interview:

Make sure you know something about the business and it’s industry before you walk in the door. Everyone knows how to back up a database, or use Profiler, or what an Availability Group is. Those are givens and of course you need to be able to discuss them. But, when it is your turn to ask questions…you need to have some ready to go. Know what industry the firm is in. Know where they rank. Are they gaining or losing ground? Why? What are they doing to get better as a firm? This shows that you care about more than just your paycheck.
Be ready to admit what you don’t know! When I was interviewing for a contract support gig at Microsoft in 2004, I told them I had never seen a cluster and had only touched replication once, in a lab. They put me on the team that supported clustering and replication (and security and DTS and….). Because I followed with “but I’m happy to learn them”. I still use that line today…only now its Azure and PowerShell!
If the interview is over a meal, DO NOT order something you eat with your hands. Specifically a Club Sandwich at a bank in Tulsa, OK. You’re going to drop some in your lap and look like a slob. If you are a slob, there’s no need to advertise! 😀

Yes, that’s more than one number one rule. Its ok, because its my blog and that works here. Its like magic or something.

My rules for when you are interviewing a SQL Server candidate:

Respect the candidate’s experience. If she has 15 years as a DBA, don’t waste your breath on the types of backups. Its insulting.
Do ask what features they know well, know a little, and don’t know at all. See above advice to candidates.
Do NOT make the job description look like a shopping list and put every SQL feature you’ve ever heard of in the requirements section. If you are contemplating a Data Warehouse project that isn’t even funded yet, that goes into the “nice to have” part. Focus on the top 5 skills that the person will spend the majority of their time on.
The candidates are human too…treat the first one you see as an equal, as well as the 10th, or 100th. They are giving you their time, just as you are using theirs…but you are getting paid to be there. The relationship and interaction with them in the first interview may very well set the tone for many years of working with this person.

Some wacky stories from my past:

See food suggestion above. That was me, obviously. Probably had food in the beard as well. Maybe even a bat in the cave just to round it out. Never heard back from them, after they flew me in for the lunch conversation. Be professional.
I’m a pretty confident interviewer…but I got too confident for one a few years ago and didn’t really prep. Did it from my truck outside a Taco Bell (hardly the first time). Nobody told me there would be 4 other people on a bad speakerphone in addition to the person I was supposed to talk to. Nor did they tell me that Negative Nancy was going to hit me with trick question after trick question. I said early on I was not a SQL developer…so she spent 20 minutes asking me T-SQL syntax questions. I could hear the giggle in her voice. I finally said I was done and left the call. Told the recruiter there was no way I would ever work for that team. Recruiter told me I did well based on what she heard back. NOPE.
Best question I ever asked: Interview with a home builder (there are dozens in the DFW area) post-2008 recession. Knowing that the housing industry was absolutely pummeled, I asked how they weathered the storm, and what actions were taken by the company to protect themselves. The previously quiet manager of the IT dept. piped up and said “We laid off 60% of the sales force, and built this amazing data center since materials were extremely cheap.” I showed him I was thinking outside of the (SQL) box, and responded by joining the team lead and me in the conversation. I got the gig, but turned down the FT offer a few months later.

That’s all I have. Well…I have tons more, but there are other posts in this months TSQL Tuesday for you to read, so go do that!

Thanks for reading,

Kevin3NF

SQL

Version 12.0.5000.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved.

Version 12.0.5000.0 for 32-bit.

Version 12.0.5000.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.