Apprentice

The Apprentice: Top 10 list

February 19, 2017 by Kevin3NF Leave a Comment

I got to work with the Apprentice today for just over an hour, and it seemed appropriate for us to go over the Top 10 SQL functions post I put up a few days ago, since he will be using them throughout his career.

We ran several against his registered servers list and went through why you would use them and when. @@Version to verify servers are up to appropriate SP/CU level, @@Servername to verify all of them are online and responding, etc.

We spent quite a bit of time talking about implicit vs. explicit transactions and looking for issues with DBCC Opentran, including writing our own INSERT and leaving it hanging/uncommitted. This took us down a fairly interesting rabbit hole.

I think the most fun he had was when he went off and used Cast, GetDate() and DateDiff to mess around with the sample I gave in the original post to figure out how many days old he is, plus how far back he could go with GetDate() – x. (Jan 1, 1753 as it turns out). When he starts doing things “off-topic” I just sit back and watch 🙂

The second half of the list wasn’t as relevant to him as the first, but then again he’s been doing DBA stuff for total of about a week now.

For each of these we were able to go through at least the basics, which he understood. And he finally bookmarked my blog 😉

This is fun for both of us.

Thanks for reading!

Kevin3NF

Top 10 SQL Server functions every DBA should know by heart

February 17, 2017 by Kevin3NF 4 Comments

I suck at syntax. A lot.

Basic Selects and Joins I’ve got. Insert, Update or Delete a row? No problem. Beyond that I have to look it up more than I probably should. But, since I was last a SQL developer in 2001, I function just fine. I am very familiar with pretty much all of the T-SQL keywords, what they do, etc. But I just don’t use them often enough to commit them to memory.

If you are a DBA, there are many functions built into SQL Server that you MUST commit to memory. If you don’t…they will creep in there and take up residence anyway. Like Borg…they will assimilate parts of your brain. Just play along and all will be ok.

My top 10 list:

1. @@servername – I use ‘Select @@Servername’ literally every morning. I open SSMS, go to registered servers, Select my ‘Prod’ local group and run this in a query…just to see if all of my little Production server children respond. Takes 5 seconds to make sure everyone is online that should be.

2. @@version – Depending on the client I may use this every day if their servers are everything from 2005 RTM to 2014 SP-whatever. Currently, all of my servers are exactly the same. Its a little weird. But in a good way.

3. DBCC Opentran – log file filling up? Processes blocked and timing out? See if some doofus (hopefully not you) left a transaction open and went to lunch. If you are lucky you can kill it. Unlucky and you find an undistributed replicated transaction.

4. Getdate() – Not sure why, but this winds up in probably 80% of the scripts I write. Not surprisingly, it often brings along it’s little brother, DateDiff.

5. sp_Configure – If you don’t know this one, run it by itself against your test instance…you get back a ton of information about the instance. Min/Max memory, Backup Compression, etc. Basically a bunch of the stuff you see if you right-click the server instance and browse the GUI. And you can make changes here too.

6. DB_Name()/DB_Id() – When the view you are using give you the DBName and you need the ID, or vice versa…use this to flip it. Go ahead:

Select DB_Name(dbid), * from sysprocesses

6.5 Object_Name/Object_ID – same as above but for tables, indexes, etc.

7. Set NoCount On – Don’t need (53 row(s) affected) in the messages pane? Use this at the top of your query

8. Set Statistics IO On/Off – want to see a bunch of info about scan counts, logical and physical reads, etc. from your query? Use Set Statistics IO ON. For duration info, use TIME ON. Generally speaking, bigger is not better on this.

9. Min/Max/Count/Sum – “Help! I need a quick query to give us the most recent sale to each client in TX!” MAX(DateSold) is your friend. All of these count as aggregate functions, and you will need to understand GROUP BY as well. Select Count(*) from Table….google up a better way 😉

10. Cast/Convert – Need to Concatenate an INT field into a string? CAST it into a varchar():

Print 'Today is day ' + 1 + 'of the rest of your life'
--Conversion failed when converting the varchar value 'Today is day ' to data type int.

Print 'Today is day ' + cast(1 as varchar(1)) + ' of the rest of your life'
--Today is day 1 of the rest of your life

Convert is used when working with dates in one format and you need them in another. Read the linked doc and save in your favorites

Honorable mention: sp_whoisactive by Adam Machanic (b|t).

This would be top 5 on the list if Microsoft had written it. Step one in troubleshooting a performance issue, right after checking Task Manager to see if it really IS SQL Server taking up all the CPU, or some other program gone wild.

You, my dear rookie DBA need to get very comfortable with all of these. Look them up, test them, and memorize. Don’t just bookmark…memorize them. Give your brain some muscle memory.

Did I leave out something really obvious? Should it have been in my top 10? Tell me about it in the comments! Or harass me on Twitter 🙂

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

SQL 101: The SQL Server Security Model

February 1, 2017 by Kevin3NF 5 Comments

The SQL Server Security model has a lot of moving parts to it. Some friends of mine are so expert in it that it is their primary function on the DBA team they work for. My friend (and recently minted Data Platform MVP) Kenneth Fisher (b|t) regularly presents on it.

Fear not! There are a couple of key concepts that you have to get down early in order to not drown in all the other stuff.

As with most of my other 101 posts, this occurred to me while riding my bike. Road bike…the only thing that occurs to me when mountain biking is “don’t hit that tree.”

Imagine if you will, that you own a house.

Because you are the owner, you have a key to the house. You can do anything you want in any room of the house. You decide who else gets keys, and what they can do inside. We’ll assume your significant other also has a key and equal rights to all rooms.

Now just for kicks, lets assume you have 2 kids. Suzie and Little Bobby Tables (Bobby for short). Since mom and dad work, Suzie and Bobby also both have keys so they can get inside after school. However, they are not allowed into each other’s rooms, nor are they allowed to change the locks, paint the walls or use the stove. Because reasons.

So, 4 people can enter the house. 2 can do whatever they want, 2 have some restrictions. Simple enough?

In the SQL Server security model the above translates as follows:

House – an instance of SQL Server
Keys to the house – Logins
Mom and Dad – Logins with sysadmin role (God rights…)
Suzie and Bobby – Logins without sysadmin
Rooms – databases

You have to have a key/Login, to even open the door/connect to the instance.

You need specific permissions to access a database/room. Bobby is not allowed in Suzie’s room and vice-versa. Neither kid is allowed in dad’s man-cave, but mom is.

Suzie and Bobby need to be granted access to use a database/room, so they are created as USERS in the database. Mom and Dad do not explicitly need to be granted as USERS in any database, because they own the place and can do whatever they want at the server/house or database/room level.

If Uncle Joe is coming for a visit, he would also get a key/LOGIN and access to the rooms/databases he will be using. When he leaves, he gives his key back (USER/LOGIN removed).

If you want, imagine Bobby has a brother and they share a room. In their room there are two beds. They don’t share and they don’t switch. In this example the beds are database objects such as tables. You can grant or deny access down to that level as well.

So, LOGIN is created first (can’t go into a room until you get in the house). USER access for non sysadmins is created next at the database level. Inside the database, you GRANT or DENY permissions to objects in the room (if you choose). You can get very, very granular if your application/employer requires it.

This is high level overview, with a lot of specifics left out. Such as, give LOGINs to Groups (preferred, instead of individuals), server and database roles, etc. Just knowing the difference between LOGIN and USER puts you ahead of non-DBAs who use the terms interchangeably. Login can be either Windows based or created within SQL Server depending on how the instance is configured and your security teams requirements.

Hope this makes your life easier! Comments are welcomed.

Update: My Apprentice astutely made the observation that a physical or virtual server with more than one SQL Server Instance installed would be a “neighborhood” in this example. Would that make a Failover Cluster a duplex? 🙂 Would a multi-tenant database be an apartment building?

Thanks for reading,

Kevin3NF

The Apprentice: Intro to Troubleshooting

January 26, 2017 by Kevin3NF Leave a Comment

Just for kicks (and because I hadn’t prepared my intro to Indexes…), I broke The Apprentice’s database (dbname MoreCake if you are following along) when he wasn’t looking.

I had told him I was going to break something, just to watch him try to sort it out, so he knew it was coming.

Before we got started, I changed the name of the data file from MoreCake.mdf to MoreCake .mdf.

His steps:

Start SQL Services…notice it come up fine
Notice MoreCake came up “Recovery Pending”
Verify Restore is an option
Take offline
Try to bring online
Read error message after online failure (OS error 2, file not found)
Look in Windows Explorer, squint, remove space
Refresh database and smirk at me.

Elapsed time from start services to smirk? 3 minutes.

Me: Jaw on ground.

If you add it all together and are generous, he has maybe 4 days of training including videos.

I know some DBAs that would never have noticed the extra space.

3 minutes.

We went on to look at ERRORLOGs, talk about what is contained in the master database, and a little bit of creating a database with a bunch of records to start off Index training in our next session.

And then I deleted MoreCake along with all Backup and Restore history when he went to grab a soda. Still GUI, and with no backup history to populate it he figured out to go to devices and browse for the most recent .bak file.

3 minutes. Smart dude.

Thanks,

Kevin3NF

The Apprentice: Self-teaching mode, on…

January 16, 2017 by Kevin3NF Leave a Comment

My last lesson with The Apprentice was basically pointing him to a very good DB Fundamentals MVA. I told him a few sections to skip and what to focus on and asked him to make a list of the things that needed clarification. DB Design was off the list, since we are still navigating SSMS, working through Recovery Models, etc.

When I checked in with him Sunday, he had independently gone through the previously mentioned MORECAKE database of his music collection, added every song from his favorite band (manually), added an album table to the design, and created a view on a view.

When we created this DB, it just had artist and song tables. He has already picked up the idea of logical groupings of related records, and joining the tables by ID columns. One small fix to the Song table, and showing him a view can have more than 2 tables and his logic worked perfectly. He even spent quite a bit of time on his own Googling for the best way to get his view to work before bringing me into it.

Gotta say, I’m thoroughly impressed with the initiative! I left him with: “Figure out why the SQL Agent isn’t working in SSMS” so we can start learning to automate the backups.

He’s got the potential to go quite far with this…

Kevin3NF

The Apprentice: SQL Server edition

January 10, 2017 by Kevin3NF Leave a Comment

Not sure if this will be a series, but it could be. Or not. Names may or may not be changed to protect the innocent. Stories may be edited or embellished as I see fit, and for your amusement…

A young person (minor) that is not really sure what he wants to do for a career came to me asking about what I do. He’s an enthusiastic gamer and self-declared introvert (with a few very extrovert qualities). Not particularly fond of school, but does well due to a high intellect. Like off the charts IQ high…

After explaining what I do as a DBA (automate everything so I can nap…), we decided to enter into an apprenticeship arrangement, wherein he learns and I exploit him for profit and untold riches.

What I didn’t realize is how much training material I would be creating along the way. I am hoping this can be re-used for others that have seen the DBA light and want to enter the fold.

I have a rough outline of the path I intend to take him down, with the goal of him being able to get a Uber-Junior DBA position when he hits 18-ish. Part-time if he starts college classes, full-time if not. I will probably deviate from this quite a bit to keep it fun and fresh.

Lesson one: we started with “Database Fundamentals for n00bs”…definititions, history, basics. There’s a PowerPoint, but its not published yet…I may use it for presentations someday.

Lesson 2: Super basic installing SQL Server Dev edition video. This went smoothly on my laptop (see video), but not on his…we installed and uninstalled a dozen times, including backing up to SQL 2014 Dev. Eventually we got a clean install (5 days later).

Lesson tres: “A guided tour of SQL Server Management Studio, but only the stuff you will use the most frequently.” Long title, short class.

Lesson IV: “Hey, lets create a database, back it up, mess it up, and restore it!” We probably had way more fun making and breaking this than we should have. Hint: database name was MORECAKE. Clearly, it stored music information with Artist and Song tables.

Lesson 6 minus 1: I sent him a link to a MVA Database Fundamentals video, with instructions to make notes on anything he didn’t understand.

That’s all so far…keep coming back for more as I remember to write them.

Kevin3NF