Is Knowing T-SQL required to administer a SQL Server

genXrcist

Hey all,

I wanted to learn more about administering an SQL server as far as installing, maintaining, backing up and restoring so my company agreed to pay for me to attend a training class. I spoke with my training coordinator over at benchmarklearning.com and they told me that the class I wanted to sign up for (6231 Maintaining a Microsoft SQL Server 2008 Database - Microsoft) had to be preceeded with a T-SQL course (2778 Writing Queries Using Microsoft SQL Server 2008 Transact-SQL - Microsoft).

So we took them at their word and signed up & paid for the T-SQL class thinking I would eventually attend the Maintenance course. Well I'm finishing up the 3rd and final day of the T-SQL course and I gotta say, I don't know what my coordinator was smoking.

I have no background in T-SQL and here I am in a class full of Developers who manipulate data in Access and want to learn to do it in T-SQL. This is SO far removed from what I do and what I want to do with SQL Server.

Thus far we've had 15 minutes of the entire 3 days for using T-SQL to monitor the server itself but even the instructor himself said this wasn't something the majority of the class would be interested in...and it isn't for them!

Anyway, I'm going to write a long email to my coordinator about this but I wanted to ask anyone out there who uses T-SQL, is there any real benefit to me knowing this stuff to administer SQL Server?

RobertKaucher

It's all the same. Most of the objects you will be accessing for server maintenance will be tables that store data regarding the system. Here is an example. Imagine you want to know who is authenticating to the SQL Server and using what method:

SELECT
    c.session_id
   ,s.login_name
   ,c.auth_scheme
   ,c.net_transport
   ,s.host_name
   ,s.login_time
 
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s on s.session_id = c.session_id  

If you don't know how to access data in T-SQL, you can't get the information. One of the fundamentals of being a Production DBA is knowing how to access data using T-SQL. Maintenance commands like BACKUP and DBCC require no theory to understand, really. What they do might require theory, like knowing what an index is how it becomes fragmented, but running the command itself, not so much. Besides, if you are running DBCC commands so often that you don't need to look at a reference, there is something wrong with your environment.

Your advisor did right by you to recommend that you take a T-SQL class. Although you might have had an easier time reading T-SQL in 10 Minutes... I don't know what the level of the class was. But the point is this: having a SQL server is great, having a person who can make sure it is not going to explode in the middle of the night is even better, but having that and someone who knows how to get information out of it for decision makers is golden.

I assure you that if DBA is going to be one of the hats you have to wear, then that class was worth your while. You just don't know it yet.

Edit: I just wanted to add. Your boss comes to you and says, "There are 5000 sales orders in the ERP for Company X. We need to change the billing address on the ones that ship to location Y to Z, but only if they were created since 2009. There are about 300 of these. We can't do it by hand." How could you handle that without knowing how to access and manipulate data?

genXrcist

RobertKaucher wrote: »

It's all the same. Most of the objects you will be accessing for server maintenance will be tables that store data regarding the system. Here is an example. Imagine you want to know who is authenticating to the SQL Server and using what method:

SELECT
    c.session_id
   ,s.login_name
   ,c.auth_scheme
   ,c.net_transport
   ,s.host_name
   ,s.login_time
 
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s on s.session_id = c.session_id  

If you don't know how to access data in T-SQL, you can't get the information. One of the fundamentals of being a Production DBA is knowing how to access data using T-SQL. Maintenance commands like BACKUP and DBCC require no theory to understand, really. What they do might require theory, like knowing what an index is how it becomes fragmented, but running the command itself, not so much. Besides, if you are running DBCC commands so often that you don't need to look at a reference, there is something wrong with your environment.

Your advisor did right by you to recommend that you take a T-SQL class. Although you might have had an easier time reading T-SQL in 10 Minutes... I don't know what the level of the class was. But the point is this: having a SQL server is great, having a person who can make sure it is not going to explode in the middle of the night is even better, but having that and someone who knows how to get information out of it for decision makers is golden.

I assure you that if DBA is going to be one of the hats you have to wear, then that class was worth your while. You just don't know it yet.

Edit: I just wanted to add. Your boss comes to you and says, "There are 5000 sales orders in the ERP for Company X. We need to change the billing address on the ones that ship to location Y to Z, but only if they were created since 2009. There are about 300 of these. We can't do it by hand." How could you handle that without knowing how to access and manipulate data?

Thanks for the response Robert, I was hoping you would post. I've seen/read a lot of your posts and recognize you as a top notch DBA/SysAdmin.

A couple of responses:

What do you mean by this statement "Besides, if you are running DBCC commands so often that you don't need to look at a reference, there is something wrong with your environment."

The class I'm in is VERY thorough but altogether it moves too fast so perhaps we covered that command but I don't remember it. Also, why would it be bad if I knew that command without looking at reference material for syntax help?

The environment I work in has three SQL servers, (2) 2K5 & (1)2K8 running ClientTrack.Net, Raiser's Edge 7 & Great Plains 10 respectively. All the data manipulation is done by the main users from within their respective applications and the little SQL report work we've needed was done by an outside consultant.

I explain all of this to emphasize the fact that not once have I ever been asked to do anything in SQL. I'm only expected to keep it running, provide backups and be able to restore the DB in case of a disaster.

I think that as my career progresses and I move onto different companies that I'll benefit from knowing some SQL but honestly I can't see myself ever really going beyond SELECT, FROM, WHERE & ORDER commands. I do have the 'Learn T-SQL in 10 Minutes' book and I do plan on reading it...I can also watch the CBTNuggets video on SQL Language if I get stuck on something.

I know this is an important & business valued skill but is it really critical to managing and maintaining a server when I can just use Perfmon for HW statistics?

I don't think I would claim to want to be a DBA. That's a pretty big hat to wear in addition to being a Server & Networking Professional don't you think?

RobertKaucher

genXrcist wrote: »

Thanks for the response Robert, I was hoping you would post. I've seen/read a lot of your posts and recognize you as a top notch DBA/SysAdmin.

Rep for that!

genXrcist wrote: »

What do you mean by this statement "Besides, if you are running DBCC commands so often that you don't need to look at a reference, there is something wrong with your environment."

The class I'm in is VERY thorough but altogether it moves too fast so perhaps we covered that command but I don't remember it. Also, why would it be bad if I knew that command without looking at reference material for syntax help?

This command is used to find and correct issues in a database. It has a ton of options and using it incorrectly in a production environment could cost you your job. It's like NTDSUTIL for AD. I have moved FSMO roles many times over the years, but I would never consider using NTDSUTIL w/o looking at a reference because the time it takes to check I am 100% correct is an easy trade to mitigate the chance I might get something wrong. And if you are constantly using NTDSUTIL as an AD admin, there are probably big problems...

genXrcist wrote: »

The environment I work in has three SQL servers, (2) 2K5 & (1)2K8 running ClientTrack.Net, Raiser's Edge 7 & Great Plains 10 respectively. All the data manipulation is done by the main users from within their respective applications and the little SQL report work we've needed was done by an outside consultant.

I explain all of this to emphasize the fact that not once have I ever been asked to do anything in SQL. I'm only expected to keep it running, provide backups and be able to restore the DB in case of a disaster.

This is an amazing chance for you to expand your skills and save your company some money. Learning how to get data from the servers, if it is something you might be interested in, could radically change how your company developes its business strategies. It could also radically change your career direction! But only worry about that if it interests you.

genXrcist wrote: »

I think that as my career progresses and I move onto different companies that I'll benefit from knowing some SQL but honestly I can't see myself ever really going beyond SELECT, FROM, WHERE & ORDER commands. I do have the 'Learn T-SQL in 10 Minutes' book and I do plan on reading it...I can also watch the CBTNuggets video on SQL Language if I get stuck on something.

Yes, and that class might have been more than you needed. Especially if you aren't going to be writing reports for people.

genXrcist wrote: »

I know this is an important & business valued skill but is it really critical to managing and maintaining a server when I can just use Perfmon for HW statistics?

Perfmon is a huge tool for troubleshooting SQL Server. But it is only half the tool set. The things built into SQL Server give you even more information that allow you to see a much fuller picture. But like anything else it's all about the priority this is. Knowing a lot of the DMVs and system stored procedures might not be a big deal.

genXrcist wrote: »

I don't think I would claim to want to be a DBA. That's a pretty big hat to wear in addition to being a Server & Networking Professional don't you think?

Well, it's all up to you! If you have no intentions to be a DBA, thenknowing a ton of T-SQL is probably not important. But from the perspective of a DBA, having strong networking and sys admin skills is important. So it's a possibility for you.

erpadmin

RobertKaucher wrote: »

Edit: I just wanted to add. Your boss comes to you and says, "There are 5000 sales orders in the ERP for Company X. We need to change the billing address on the ones that ship to location Y to Z, but only if they were created since 2009. There are about 300 of these. We can't do it by hand." How could you handle that without knowing how to access and manipulate data?

First you do a SELECT against your table that has your sales order table. The tables that handle the companies that are getting shipped to from X will be in a different tables. You will be using ALIASES. You also now have a condition where the date has to be GREATER THAN or EQUAL to 01/01/2009 (assume that that's the date format that's used...it can be different). Once you have verified that you got your 300 items to update, you now run an UPDATE based on your SELECT criteria.

I totally expected Robert to provide the correct answer to this: If you don't know (basic) T-SQL commands and can't structure your T-SQL to get what you want, you cannot do a job as a DBA.

Stuff like backups and the like CAN be (and IS) done with Enterprise Manager/SSMS either through that interface and/or with maintenance plans. But there are times where you will need to do it in T-SQL.

There are even times where you will need to catch a developer's mistake before it moves into production (It does happen from time to time).

RobertKaucher wrote: »

Rep for that!

Well, it's all up to you! If you have no intentions to be a DBA, thenknowing a ton of T-SQL is probably not important. But from the perspective of a DBA, having strong networking and sys admin skills is important. So it's a possibility for you.

+1 On this!! This gets rep.

RobertKaucher

erpadmin wrote: »

I totally expected Robert to provide the correct answer to this: If you don't know (basic) T-SQL commands and can't structure your T-SQL to get what you want, you cannot do a job as a DBA.

100% rhetorical!