Just a quick 101 style video I put together to show the pieces of a SQL Server database for those that are starting to learn the product. Also a teaser for my teaching style of my DBA Fundamentals classes!
Enjoy!
SQL Server Database Consulting
Just a quick 101 style video I put together to show the pieces of a SQL Server database for those that are starting to learn the product. Also a teaser for my teaching style of my DBA Fundamentals classes!
Enjoy!
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:
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!
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.
Register (REQUIRED) on EventBrite to learn the following topics (subject to change due to time, class flow):
• 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!
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
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:
This came from our UAT/Test environment AND a client specific setup that had all components in one server.
Notice the red sections above:
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:
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.
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!
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 DallasBikr@gmail.com, 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?
Total Voters: 29