November 8, 2010 at 11:23 am
This thread is a result of a long discussion I have been having with Gail Shaw (aka Gila Monster) about SQL Server 2005 and 2008 where in I described the practice of many application programmers I know (I can't say what proportion) know dangerously little about securing and configuring SQL Server and therefore implement the "simplest possible" formula which may include any of the following:
a. maintaining a single local instance of SQL Server, almost always the default one
b. ignoring securing their server
c. avoiding named instances, user accounts, roles, policies, etc
d. keeping the SA password blank because 'otherwise the connection strings would break'
e. using SQL Server authentication with a blank SA account on production apps
f. manually installing the databases for their apps at client sites
I also know programmers who never use any programming (stored procs, functions, triggers) or other advanced features (logs, traces, profiles, audits) on their back-ends. I once even met one who handled all his primary keys and foreign keys functionality from the app code, in effect reducing SQL Server to a text file store!
Members who are also programmers are free to corroborate the above with their own anecdotes where possible as the true list may be longer.
Since DBA is a skill set in its own right, it is but fair to admit that few programmers possess advanced skills in it, just like few DBAs may be familiar with the coding world. The problem is more pronounced for those of us freelancing/working solo, unlike those working in the corporate sector who can sound out a pro DBA colleague across the corridor at short notice, and who do not have to worry about all the arcane stuff that is handled by the SQL Server team.
A short intensive primer covering:
a. how to get best results when installing/uninstalling/upgrading SQL Server and its instances
b. benefits of properly configuring users, accounts, roles, policies and authentication
c. moving as much programmability from the app to SQL Server using sprocs, triggers and functions
d. securing the SA account and keeping it away from all access
e. how to build dynamic connection strings to secure databases
f. how to deploy secure databases to secure production servers automatically during application install
g. any other topic of interest
I look forward to a very lively and edifying discussion. No abuse please.
November 8, 2010 at 11:36 am
Let me put a question back at you. Since most of the topics you outline have extensive documentation, courseware, books, etc., how do you propose getting developers to that information? More than that, how do you propose getting them to want to get to that information? I think that's the issue, not setting up documentation on all those topics, because it already exists, in great quantities.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 8, 2010 at 12:30 pm
Grant Fritchey (11/8/2010)
Let me put a question back at you. Since most of the topics you outline have extensive documentation, courseware, books, etc., how do you propose getting developers to that information? More than that, how do you propose getting them to want to get to that information? I think that's the issue, not setting up documentation on all those topics, because it already exists, in great quantities.
To put it another way, paraphrasing a developer I've worked with:
Front-End Coder: "So, we bring up the table, drop it to this array..."
Me: "Wait, the entire table?"
FEC: "Yep, then we do our bubble search..."
Me: "Why not run a proc?"
FEC: "Because we have the array and can do it there."
Me: "But SQL was built for this."
FEC: "Yeah, but I know arrays, and we have the array. We're not changing now."
Me: ... ... "I was hired why?"
FEC: "To help speed up getting data from the database. Help me figure out how to feed the array faster."
Me: :blink: :crying:
Coders and DBAs are fellows in the system. They know how to obfuscate and encrypt IIS. I know how to properly secure a SQL Server. My desired knowledge ends in knowing what they can/can't do at a high level. Most coders are the same way in the other direction.
I know a few people who can switch mindsets well enough between iterative coding and set coding, but they are few. The majority of coders cannot think in sets... and that goes vice versa. It's a mental habit, one you train yourself in. Most people, even incredibly intelligent ones, do not switch between the mindsets easily. That's merely covering one of your seven bullets. All seven can be like that.
The primer you're asking for would be more useful as a simple checklist for those lone wolf front end coders, something to double check when they can use it. The majority of untrained, un-interested coders are better off (shoot me now) using the SQL Server as a back end file system. At the least, they'll be able to troubleshoot their own mess effeciently. Eventually, they'll decide to learn it, and start looking up the necessary documentation to do so. Any one of the points above requires a solid amount of knowledge. You're not going to cover any of those in a few pages of a primer. Not well, anyway.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 12:46 pm
Well, we have what Brits would call a jolly nice start to the topic.
Let me put the question back to you.
You have a very legitimate argument there, Grant, and many would applaud you for it.
But that is also the very reason I am putting it forward for discussion:
the amount of info and instruction available is staggering, to the point that many programmers are scared stiff of venturing close.
As I said, dba skills are not easy to acquire, just as programming skills too are not easy for dbas to learn.
The programmers have to contend with a lot of shifting ground catching up on new technologies coming out every day, they find it very easy to sacrifice db best practice. Just because they do not know what it means and how it affects their work.
I believe that programmers, especially freelancers like me, need to embrace basic sql admin skills in order to leverage their output. The reason they have not done so so far is NOT that they can't be bothered, BUT rather that they DO NOT KNOW where to start. They dont know if a simpler, easier way exists for acquiring these skills.
That is why I asked if we could get an abridged lesson covering the important topics in moderate detail.
For the record, in defence of programmers, I have to say that while DBAs are expert at administering and maintaining database servers, few of them would have comparable skills in database design/development, which remains a forte of teh best programmers. So, we are not complete dunderheads in the world of databases.
November 8, 2010 at 1:05 pm
goodguy (11/8/2010)
I believe that programmers, especially freelancers like me, need to embrace basic sql admin skills in order to leverage their output. The reason they have not done so so far is NOT that they can't be bothered, BUT rather that they DO NOT KNOW where to start. They dont know if a simpler, easier way exists for acquiring these skills.That is why I asked if we could get an abridged lesson covering the important topics in moderate detail.
Hrm, you have a point there. I would think, however, that instead of an abridged lesson, you would be better off with an abridged lesson plan. Something that would highlight what was important, a brief blurb of why, and then some dicussion, or links, to those who have already said what needs to be understood well.
For the record, in defence of programmers, I have to say that while DBAs are expert at administering and maintaining database servers, few of them would have comparable skills in database design/development, which remains a forte of teh best programmers. So, we are not complete dunderheads in the world of databases.
In terms of database design, I would have to disagree heavily here. Most front end developers code (nowadays, anyway) to the object oriented structures they carry through the code. These iterative structures are usually horribly de-normalized to the database mentality. It was worse 'back when', when I saw tables dedicated to a web/app page. While you may not be a dunderhead ( :w00t: ) and the folks you have in mind aren't, I've ran into many that were. The design was logical, sound from their perspective, connected into views intelligently... and made me cry from a database design perspective.
So... no, I must disagree with you that design and development at the database level remains a forte of 'the best programmers'. The average Database developer (DBA, since they get thrown into that role, too) has done, on average, a better job then all but the best of the application developers I've seen. This is, of course, my experience. I've contracted at about 16 companies at this point in the last 8 years, however, so I feel I've got some breadth there. At the same time, I've gotten a bit of a reputation with a few firms as a firestomper, so I may be seeing more than my fair share of horrible designs. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 1:13 pm
This is like discussing why pigs get fat... answer is simple, it is not the pig's fault but the fault of whoever feeds the pig!
In the environments I control no developer does database design work. Period.
You want healthy databases? start at design time.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 8, 2010 at 1:15 pm
goodguy (11/8/2010)
I believe that programmers, especially freelancers like me, need to embrace basic sql admin skills in order to leverage their output.
Admin, no mostly not (unless they're in a small company and are wearing 2 hats). Database development, yes.
That is why I asked if we could get an abridged lesson covering the important topics in moderate detail.
Certainly. When would you like us to get started on those 400-800 pages?
I'm not joking. To cover any one of those topics in moderate detail is a large amount of work.
For the record, in defence of programmers, I have to say that while DBAs are expert at administering and maintaining database servers, few of them would have comparable skills in database design/development, which remains a forte of teh best programmers.
I would beg to differ. The majority of front end developers that I've worked with (full time, as a consultant and in the community) have had little to no database design skills at all (though they would generally disagree with that assessment), regardless of their skill as a front end developer.
To do database design, you have to be familiar with set-based logic, normalisation forms and the features and limitations of the database you're designing for. None of those are things that the majority of front end developers have.
I wouldn't try designing a n-tier enterprise application. I don't have the skills, I'm a database developer (designer, etc), not a application developer/architect. Why do front end devs think that they can automatically design databases?
Right, so some basics:
Database design: Any one of Louis Davidson's database design books.
Basic T-SQL: T-SQL Fundamentals by Itzik Ben Gan
Security: TRy this: http://www.mssqltips.com/author.asp?authorid=25
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2010 at 1:24 pm
GilaMonster (11/8/2010)
Certainly. When would you like us to get started on those 400-800 pages?I'm not joking. To cover any one of those topics in moderate detail is a large amount of work.
You know, I'd been thinking about possibly getting involved in writing/publishing. Tinkering with it mostly. Most of the importnat topics are already beaten to death, or incredibly well covered, by other authors. I wonder if this might be a decent place to dig into.
Then again, this already exists:
http://www.dummies.com/store/product/Microsoft-SQL-Server-2008-For-Dummies.productCd-0470224657.html
EDIT: Miscopied the link.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 1:24 pm
"For the record, in defence of programmers, I have to say that while DBAs are expert at administering and maintaining database servers, few of them would have comparable skills in database design/development, which remains a forte of teh best programmers. So, we are not complete dunderheads in the world of databases."
While this may be true in your experience it is most unfortunate you feel this way. I have spent a lifetime developing everything from device drivers to large multi-tier high-transaction rate applications before I decided to enter the world of being a DBA. As such I've had responsibility to set standards and make recommendations regarding database architecture (some of which is ignored).
It is my experience that most "programmers" are not very good database designers. They do not think in terms of set based processing from what I see, nor do they understand the ramifications of poor practices and SQL Server strengths and weaknesses, especially the ones that will bring a server to its knees. That is unfortunate because inevitably (unless the database experience low levels of activity) this leads to major performance and scalability issues which could easily have been avoided.
I consider being a DBA the higher plateau ... a culmination of years of systems design, software development and understanding of multi-tier clinet server based architectures. A DBA needs a good knowledge of all of this to be effective in a large shop.
That's my $.02
The probability of survival is inversely proportional to the angle of arrival.
November 8, 2010 at 1:33 pm
Craig Farrell (11/8/2010)
You know, I'd been thinking about possibly getting involved in writing/publishing. Tinkering with it mostly. Most of the importnat topics are already beaten to death, or incredibly well covered, by other authors. I wonder if this might be a decent place to dig into.
Intro to databases for application developers? Or just intro to database security for application developers?
If you're serious, ping Steve and ask him to speak with Tony Davis at Simple Talk about it. Maybe next week, they'll all be at PASS this week)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2010 at 1:52 am
Wow! I evidently touched a raw nerve with my defence of programmers. But if most of you who objected had scrutinised my defence more carefully, you would have seen that it was intended for 'the best programmers', not all.
But, thanks to everyone who felt compelled to respond. Gives me a whole new insight into your world.
To give you all some background, I am based in Zanzibar, an autonomous island in the Indian Ocean, part of Tanzania in East Africa. Compared to you guys, we IT people here are really in the third world.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply