One size does not fit all

I was just reading a discussion on a popular networking site about stored procedures. In particular, one software architect thought they should not be used.  His reasoning was that the programming language was designed to handle the database access and it was easier to port his application to different database systems without them.

I was stunned.   But then I thought about it and realized that I shouldn't be.  More and more of the developers I work with have very little understanding of how a database works.  They use coding tools to bind their application to the database schema and then just call a programming function to do what needs to be done.  Want to update a record in the database?  Call the ADD parameter of the bound schema object.  Don’t worry about what is actually being done behind the scenes.  It does not matter what the DBMS is.  It’s all good.

No, it’s not.  I have looked at some the SQL produced by these interfaces and it can be amazingly bad.  Also, since it is ad hoc SQL, it is not optimized like stored procedures are.  Plus, if you need to optimize the SQL code, it is next to impossible because the SQL is generated and you have very little control over that.

Also, if your app is not optimized to take advantage of the DBMS it is using, then it already has a strike against it.  I have worked with a major application that advertised that it would work with any of the leading database providers (MS SQL, MySQL, Oracle), just select the correct driver when you install the application.  It did work with MS SQL and Oracle, but it had horrible performance issues and the nightly data loads from our sales system took hours to load less than 100,000 records.  This was directly due to the fact that it created all of the SQL through the application, with no stored procedures and no optimized code.  It was a nightmare.

I am not saying that you should never let the application handle the data access, it can work depending on the size and complexity of the project.  What I am saying is that you have to understand that it can be a major issue and that you have to make an educated decision about how and when you use these tools, and don’t just blindly allow the application to handle all the “low level” database calls.  If you do, don’t be surprised when you are stuck with a slow and frustrating  “one size fits all” product.

What is a Database Administrator?

What should the job duties of a Senior Database Administrator (DBA) be?  If you started a new job as a Sr. DBA what would you expect to be doing and what would you not?

Over the last two years I have worked contracts for five different companies.  At each company I held the same title, Sr. Database Administrator, but each company had me doing radically different things.  Sometimes I was asked to work with the application team and help them write stored procedures and views and optimize existing code.  Sometimes I was tasked with backup and restores and setting permissions.  At one position I was asked to migrate a client to a new environment.  I was responsible for migrating the database components, requesting and setting up the hardware, setting up data shares for incoming text files, and having the AS400 jobs, that delivered text files, updated with the new server information.  At another company, I worked for the business unit (BU) and not the IT department, so I was not granted SysAdmin rights to the servers even though my position was as a Database Administrator.  That was very frustrating and I found out later that this was an ongoing war between the BU and IT.

So, this got me to ponder, what is a database administrator?  It is someone who just keeps the SQL Servers running and installs new servers as needed?  Someone who makes sure that the backups get run and does restores as needed?  Or has it morphed into a Jack-of-all-trades position? 

It seems to me that companies are looking for people who can do everything and that are willing to do anything.  Is that reasonable and if true, should the position still be called Database Administrator or should it have a new name?

I like doing different things, it keeps me from getting bored and it keeps me learning.  Some of my colleagues hate it, they want to do the tradition duties and that’s it.  What about you?  Have you run into this and if so, how do you feel about it?  Is being a DBA more difficult now than it was 10 or 20 years ago?

The Job Market

Well, I have not posted to my blog for some time as you can tell (well since no one but me knows about this blog, maybe you can't). I have been busy with a bunch of stuff.

But lately I have been getting at least 4 or 5 calls or emails a week begging for leads for Database Admins, .Net developers, and architecture people. I have a couple of friends who are recruiters for consulting firms and they all say that business is brisk.

Has anyone else noticed an upturn in the technical job market? I live in St Louis, so not sure if this is a regional thing of if it is more wide spread? If you get a few minutes, let me know. Thanks!

The most important DBA skill?

If you were interviewing for a SQL Server DBA position and they asked you, "What is the most important skill that a DBA should have?", what would you say?

Technical skill? Working under pressure? Ability to work as part of a team? Ability to follow the standard procedures and rules that apply to your position?

All of these are good answers, but I think that one of the most important, if not the most important, is people skills.

Before you disagree with me (if you do) let me explain.

I have known lots of people with excellent technical skills. They were smarter than me by a long shot, but they could not communicate with people to save their lives. They were either socially hindered, arrogant, or just plain rude when it came to talking to our customers. This lead to our customers requesting that DBA X not be assigned to their project. This lead to a very bad situation inside the DBA team and the DBA in question finally left, much to the relief of all.

The same applies to all of the answers given above (although if you are a good team player your people skills should be OK as well). I would rather hire someone with a little less experience and technical skill who is a great communicator and people person than an expert who can not relate to people or is rude or arrogant. If you have the right base skills, you can always learn the technical stuff. Learning the people stuff is not so easy.

What do you think? Am I right or do you disagree? Let me know. Thanks!

Jim

Generate password in SQL

Here is a nice little stored procedure to let you generate a random strong password. I have used this to help automate the automatic changing of the SA password for all production SQL servers every 30 days.

The process looks up the SA password for the given server from a secure table and then connects to the server. Then this store procedure is used to generate a new password which is then applied to the SA account and saved in the secure location.

This process replaced the manual process of logging into 30 different production servers every month and doing it all by hand. Worked very well.

Here is the code for the stored procedure. As always code is provided as is. Use with care.

CREATE PROCEDURE GenPassWord
@len INT = 8
AS

-- Creates a complex password of the passed in lenght.
-- If no lenght passed in, defaults to 8 characters

DECLARE @password varchar(25)
DECLARE @type tinyintDECLARE @bitmap char(6)

SET @password=''

WHILE @len > 0
BEGIN

SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1 --Appending a random lower case alphabet to @password
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE
IF @type = 2 --Appending a random upper case alphabet to @password
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE
IF @type = 3 --Appending a random number between 0 and 9 to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE
IF @type = 4 --Appending a random special character to @password
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))

SET @len = @len - 1

END

--Here's the result
SELECT @password

Simple SQL Server Security

Do a search on the web for SQL Server security and you will find 1,000's of documents about best practices and how to secure your server. But many times, the biggest security threats come from inside.


No, I am not talking about a disgruntled employee or a DBA with a side job selling company information on EBay. Don't get me wrong, those are very real dangers and should be considered and protected against.


No, what I am talking about are small innocent mistakes that can cause HUGE problems for you and your company.


A perfect example is not having some elemental things in place like...

• Change Control
• Build Procedures and SOP's
• No peer review
• No proactive monitoring

These things are all very simple things that should be in place, but are often not or are overlooked.


Let's look at a simple one, a build check list. As part of your job, you probably build a couple of SQL servers a month. Sometimes more, sometimes less. When you build your server (hopefully you have a server department that burns a standard image for the OS) how do you do it? Do you just do it from memory because you have done a couple of hundred installs and are very good at it, or do you have a check list document that walks you through it?

The answer should be: a check list document that walks you through it. It is way to easy to be in the middle of installing a SQL Server instance and get called away for some emergency and then come back and start the build again from where you left off. But the problem is you skip a step you "know" you have already done and something gets left in place that should not or something does not get added that should. Having a build document greatly reduces the possibility of missing something vital and helps to make sure that every server is exactly the same as all the others.

I know that no process is perfect. Mistakes will be made. The question is how many? I know from experience how easy it is to not change the SQL port from the default 1433 to the company approved XXXXX and then have security call and ask why after they run their next security audit. Not a lot of fun. And potentially very serious, if you are in a PCI or similar environment.  And much more serious issues can arise like blank passwords, default accounts that should have been removed but were not, and so on.

Change control is another. A developer emails you a script and says it has to be ran right now due to the emergency nature of the issue. As soon as you see the email you run it and call the developer to let him know the change is done. Only to find out that he got tired of waiting for you (since you were in the bathroom for like 5 minutes) and he called DBA Bob and had him run it. Now you and Bob have made the same change withing minutes of each other without knowing it. Hopefully, the multiple runs don't mess anything up, but worse case is that the ecommerce site is now down and it will take you 20 minutes to fix what took 30 seconds to break. All because you don't have a defined change manage process.

Many of you are probably laughing and saying "Yea right, like that really happens.". Well I hope you are, because that means you have never had it happen to you. Yet. But it happens all to often in some fairly large shops that should know better.

So, secure your servers as suggested by Microsoft and the others, but also make sure you have the simple stuff covered as well.

The Devil is always in the details and the small ones are the ones he likes best.

Jim

The true cost of sub-select in queries

Yesterday I was working on a stored procedure trying to help to optimize it. It has some subselects in a big select statement and that was one of the main issues. One of the subselects was responsible for almost 50% of the relative cost of the stored procedure.

That got me to thinking, what is the real cost of subselects in queries? I "know" they are bad, but just how bad are they? This is probably old news to you all, but I found it very interesting to actually see the cost in how many reads were produced by each type of query. So I did a little test. Here are the parameters.

I created three simple tables (Table_A, Table_B, Table_C) with no indexes. Here is the structure (all three are the same).

ID INT (Identity)
OrderID INT
Fname VARCHAR(50)
Lname VARCHAR(50)


Then I filled each table with random numbers and letters.

Then I wrote the following 3 queries to help measure how many reads each type of query would produce.

#1 (subselect with no temp tables)
select * from table_a
where orderID not in (select distinct orderid from table_b)
and orderID not in (select distinct orderid from table_c)

#2 (subselect using temp tables)
Select distinct OrderID into #B from Table_B
Select distinct OrderID into #c from Table_C

select * from table_a
where orderID not in (select orderid from #b)
and orderID not in (select orderid from #c)

drop table #b
drop table #c

#3 (Joins using temp tables)
Select distinct OrderID into #B from Table_B
Select distinct OrderID into #c from Table_C

select * from table_a a
left join #b b on a.orderid = b.orderid
left join #c c on a.orderid = c.orderid
where b.orderid is null and c.orderid is null

drop table #b
drop table #c

Then using SQL Profiler, here are the results (please forgive the horrible, ugly table)...

# of records in table			Join	Total
A	B	C	Type	Reads
5,000	5,000	5,000	SubSelect no temp table	25,063
5,000	55,000	55,000	SubSelect no temp table	50,548
5,000	100,000	100,000	SubSelect no temp table	51,022
50,000	100,000	100,000	SubSelect no temp table	251,002
5,000	5,000	5,000	SubSelect with temp table	40,505
5,000	55,000	55,000	SubSelect with temp table	40,799
5,000	100,000	100,000	SubSelect with temp table	41,091
50,000	100,000	100,000	SubSelect with temp table	401,273
5,000	5,000	5,000	Join using temp table	476
5,000	55,000	55,000	Join using temp table	799
5,000	100,000	100,000	Join using temp table	1,091
50,000	100,000	100,000	Join using temp table	1,237

To tell you the truth, I was surprised at just how much more IO was produced using SubSelects than not. Just thought I would share.