Career

PowerShell for DBAs – why I changed my mind.

January 19, 2017 by Kevin3NF 3 Comments

Quick post today…

I have been saying for several years that I don’t really need PowerShell to be an effective DBA. In most places that is true, but becoming less so.

I’ve resisted writing complex PS scripts for administration, as most Junior and Mid-level DBAs don’t know it, so I don’t want to leave a client in the lurch with code their staff cannot understand.

I’ve only once in an interview been asked if I know PS. “No” was a perfectly acceptable answer.

Things change.

2 very solid reasons (there are others) that every DBA should be learning and using PowerShell:

1 – Its very useful for admin at the O/S level.

At my current client I am team lead of System and SQL Admins, along with doing any of the work that comes our way. This means we need to be able to manage the modest server farm we have. Its big enough that we can’t log onto every server every day, but small enough nobody wants to buy a proper monitoring toolset. So…PS to the rescue!

One of our tasks is to manage the Event Logs and deal with Warnings and Errors that come up. I wrote this script to dump the last 7 days to a file we can work with. This has since been updated many times, and now uses a different CmdLet. One of my DBAs wrote a script that checks drive space on all servers and sends a color-coded email daily. Much of this came from Googling, which is perfectly fine.

2 – Its on the 2016 certification exam now (probably).

Course 20764B: Administering a SQL Database Infrastructure has Powershell as an objective:

Managing SQL Server using PowerShell

While the Exam 764 “Skills Measured” section doesn’t specifically call PS out, its very likely to be there.

For those new to SQL or getting certified to help your career grow, just go ahead and add PS to your toolkit. You are going to need to at least be able to read a script, and will probably be expected to know the SQL Server Module’s CmdLets, if not more. Just do it.

Thanks,

Kevin3NF

An interview with me

December 16, 2016 by Kevin3NF Leave a Comment

This seems to be running around Twitter today, so why not 🙂 (inspired by Kenneth Fisher’s post here)

What are the various database job roles and respective hiring requirements at your company?

I am a contractor at a very large client (name withheld), so lets pretend I actually work there. On my team, we have .Net Developers, ETL developers, business analysts and administrators (Database, O/S and storage). Hiring requirements include mid-senior level skill set, willingness to work a task to completion and being extremely thorough. Accuracy is far better than speed.

Describe an entry level job and the hiring requirements at your company?

We just hired a junior DBA that hasn’t even started yet. 2 years of experience, but interviews like a 4-5 year veteran. I asked questions that can only be known from experience, instead of just memorizing certification questions. Personality is a big part of it, since this is a small team within a global company.

What is your job role?

I am a Senior SQL Server DBA/Team lead. I herd all of the Administrative cats, and report to the team Senior Manager. When it goes bad, or we make a mistake I take the bullet. When it goes well, we share the glory.

What is your background?

I got a BBA in Finance with an Accounting minor from the University of North Texas in 1991. Did that type of work for 7 years before flipping to IT (see below)

Describe the path that led you to this job role.

I started my professional career as a Financial Analyst for an Inventory Finance firm, including roles in the field, the office and as a team lead. After a layoff I worked my way into IT as an Access developer, then into SQL Development and then on to Admin. I have been Full-time, contract, independent and unemployed. Still don’t know what I want to be when I grow up.

Give me an example of an interview question that you would ask an entry level applicant, and explain what you would look for in a response.

Assuming DBA, after the “get to know you” part, I will ask basic questions about backups, restores, other maintenance items, etc. I actually have a post here directed specifically to Junior DBAs. Most of these will be in the entry level DBA interview…just to gauge experiences more than test knowledge so I know what I will be teaching them

An interview of me was done here if you want to know more about me…

Thanks for reading!

Kevin3NF

Why SQL Summit?

October 26, 2016 by Kevin3NF Leave a Comment

Why am I attending the PASS Summit?

As I type this, I’m on AA flight 439, seat 8A hurtling through the sky hundreds of miles an hour in a pressurized metal tube. Tight quarters in here, but it’s a window seat that I picked…so still a win. 4 hour flight, direct from DFW to Seattle.

As a contractor, I don’t get paid for the three and a half days I’m using for Summit and travel. I also paid my own way for Summit registration, flight, hotel, shuttles, parking, food, entertainment…even a couple of decks of cards for Thursday’s game night should there not be enough. Oh…and a kilt. Because Grant Fritchey and more importantly the Women In Tech cause.

So again…why shell out the cash?

Because I am 48 years old. If I don’t keep fresh on the new technologies that makes me a dinosaur, relegated to taking care of the old legacy SQL Server that nobody dares to touch. Its SQL 2000 now…but SQL 2016 will also be ancient someday. I expect to be working until 60. Probably more.

If I don’t attend events like this and learn, the new technologies will go flying past me and I’ll have to chase things down on the fly (like I’m doing now with PoSH). I have a bad hip…not allowed to run!

If I don’t learn to exploit the goodness that is the cloud (which I am currently flying in) or Hekaton or the underpinnings of virtualization…I’m toast.

I’m not worried about some hotshot junior DBA taking my job. I want them to try. I’ll even help them through mentoring or team leadership. I have 18 years in, and that puts me at the top of a lot of candidate stacks.

As long as I keep up.

Why Summit?

There are webinars, SQL Saturdays, MVC classes, Twitter, blogs galore and hundreds of other places to learn things that are all a whole lot cheaper.

BUT…nowhere else can you gather all of that in one place. Add in more choices of learning such as BI, Professional development and SQL Development (I’m a pure admin)…and it gets even better.

Even more…when the best of the best are crawling all over each other to pay their own way to come and present at this event, and learn from each other…you know it’s the number one SQL training event in the world.

Add in the networking opportunities! Between sessions, at meals, after parties, community zones, luncheons, breakfasts…if you can’t make a friend and meet people here you might want to reach out of your comfort zone and say hello to the person next to you. But if not…that’s ok too. I’m an introvert as well, so I get it.

I went to Summit 2008 and suffered from information overload. Now that I have recovered, its time for a return visit. The investment dollars pale in comparison to the future dollars in income that will likely grow organically out of being here.

I’ve been like a kid waiting for Christmas the last few days. Figuratively jumping up and down as the time to board got closer (remember…bad hip…no literal jumping). My teammates back at the office all say hi! And they are glad to be rid of me for a few days I suspect.

So as I hurtle along, playing armrest games with 8B, I’m excited. On Saturday I expect to be exhausted but happy.

Why are you at Summit? Why didn’t you come? Can you come next year?

Comments encouraged below!

Kevin3NF

The OnPurpose DBA

Dear Junior DBA…

September 2, 2016 by Kevin3NF 2 Comments

Congratulations on getting your first SQL Server DBA job!

Presumably you have a tech background or education, and have been through some basic training in SQL Server administration. I also assume you intended to be a DBA and want to be really good at it so you can advance your career and get mad raises/bonuses.

With any luck at all, you are in an environment where there is at least one other DBA there that knows more than you do. Ideally a Senior that is really into mentoring that can guide your path.

If not, here are some of the basic things that you may already know how to do in SQL Server Management Studio, but don’t really know the inner workings or the T-SQL to make them happen.

Also, almost everything you can do in current SSMS versions can be scripted. Look for the script button and click it after you make all of your selections so you can start learning the code behind the GUI. In time you’ll prefer going straight to the Query Window for some functions of your job.

The list I want my juniors to get intimately familiar with:

Backup and Restore…beyond the Maintenance Plans
Creating/Deleting databases
Creating Logins and Users (and knowing the difference)
Creating and maintaining indexes
Other database maintenance items
Basics of whatever HA/DR may be in place (Clustering, Log Shipping, Availability Groups)
Basic performance monitoring via SQL Trace or Extended Events in modern SQL versions

All of these can be setup/monitored in the GUI…so make sure you know all of the options there, and then start working on knowing them deeper. Start with Books Online/MSDN and go from there.

More on each list item:

Backup and Restore – I want you to be able to regurgitate exactly what the difference is between Full, Differential and Transaction Log backups. I want you to know when you would use each. You need to know how to restore to a point in time, to another server or as a new database name. Backwards and forwards…this is DBA 101 and the first question I ask if I interview you. You need to be able to throw down the basic Backup Database syntax on the fly. Also, recovery models…memorize and understand them (including Bulk-Logged)

Creating/Deleting databases – There are many ways to create a database…SSMS, T-SQL, Restore from a backup, deploy from a .dacpac/.bacpac, etc. Know how to do each, when you would use each, what options are available and how they affect behavior. Know about filegroups and best practices for laying out your .mdf, .ldf and .ndf files…for your environment. Know what to do before you delete a database. Does the requester mean Delete, Detach or just take offline? What’s the difference? Know your RPO and RTO by heart.

Creating Logins and Users (and knowing the difference) – I’m amazed at the number of experienced people and non-sql people that use the terms interchangeably. Know the difference. Also learn what server and database roles are. These days, the more you know about the various pieces of the security model in SQL Server the stronger a DBA you are.

Creating and maintaining indexes – If your databases are only a few GB, you can almost ignore indexes…but don’t. Know and be able to explain the difference between clustered and non-clustered indexes. Understand how to determine what indexes are needed and which existing ones are not. What is an included column? What is a covering index? Memorize the ins and outs of Reorganize vs Rebuild and Online vs. Offline maintenance…this matters. A surprising number of application developers will rely heavily on you for help in this area.

Other database maintenance items – DBCC CheckDB and its impact on tempdb. How to respond to CheckDB errors. Statistics…what are they, do they matter and how do you handle them? When?

Basics of whatever HA/DR may be in place (Clustering, Log Shipping, Availability Groups)

Know the difference between the various options available and what they are intended to do. High Availability, Disaster Recovery and Distributed Computing are NOT the same things. Understand that managers use Replication and Log Shipping interchangeably. Teach them gently. Your environment may or may not being doing any of this. Know your RPO and RTO by heart.

Basic performance monitoring via SQL Trace or Extended Events in modern SQL versions – any time you have a chance to get into a performance tuning issue jump on it. Don’t assume the issue is with SQL server just because someone said so. Prove it is or isn’t. Don’t automatically blame the storage or network teams unless you are never going to need them again (you will need them again…). Start at the server level, then drill down into the DB, then queries…jumping straight to queries may just be a waste of time if a rogue application is stealing all the CPU or leaking memory.

Yes, this is a lot of stuff. No, you won’t learn all of this today or next week. If you went from GUI to familiarity with all of this in 6 months I’d be impressed. Ask your mentors and teammates for help and guidance AFTER you do your research.

Also, there is a ton more you will pick up along the way. Ask questions. Go to SQL events if possible. Read blogs from Paul Randal, Brent Ozar and Grant Fritchey (and a bunch of others)

You can make a long career out of just database administration. But don’t deprive yourself of opportunities to learn about storage, virtualization, cloud computing, development, business intelligence, etc. The more you know, the more successful you can be. One of these days, you will be the mentor, not the mentee. Be kind to the new guy 😉

Kevin3NF