DBAs still know whats best for their DB, right?
-
This is probably heresy on a DBA board, but the correct answer is somewhere in between using all inline or all stored procedures.
I found the best approach is to use middle-tier data components running on the same server as SQL Server, a common class for ADO disconnected recordsets with a single signon, and use marshalling for ADO recordset batch update. This middle-tier can be used for multiple applications and the single logon will allow efficient connection pooling and procedure cacheing among thousands of users. Sending disconnected recordsets allow efficient programmer productivity, and the marshalling reduces network traffic by only sending updated data back. And for many (if not most) processing involving multiple records (i.e order line items), an application language can handle data processinng and updating in ADO recordsets and cursors better than complex stored procedures with multiple queries and cursors. Furthermore, having all the code (and SQL is code) in the code layers is more efficient than having some processing code in application programs and some in the database.
Naturally, no one way is always the best way. Some SQL queries will be very complex and will have significant performance gains running in stored procedures. Some applications (especially distributed or commercial) will not want any stored procedures, to keep the code hidden in compiled programs. Each application and system environment is different and have different requirements. There is no "one size fits all" or "hard and fast rules".
One final thought: DBA's who insist on all stored procedures look and sound just as ignorant as developers (or developer managers) that insist on all inline SQL. Learn to work together, or you will be considered ignorant.
Mark -
Good points. I would ask the Development leader why he/she wants to go inline. There may be some valid points to the response, but overall I would suggest using the stored procedures.
And I agree with Antares. It sounds like a control issue or trying to hide a weakness. My developers use stored procs most of the time and they like using them. They also like the fact that I go through the code a little bit. They have been through enough performance issues regarding database design that they see the benefits of my code reviews.
If the development team is going to create all of the SQL inline then how are indexes going to be decided on? Filegroups layouts for the indexes? Isolating problem queries will be tougher...
Sounds like they want to build a database into the corner and wash their hands of it when it has poor performance. I've worked with some good developers and have been a developer myself, but for the most part, developers aren't concerned about what is going on with the database...just that they get the results they want in a scaled down development environment...
Give me the Development Leads number...
"Keep Your Stick On the Ice" ..Red Green -
One point that hasn't been addressed yet. I guess most of the developers are sa to the production servers? If so, another interesting battlefield
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank, it took a year and a half..but I won that battle...almost fired because of it too. Bad DBA wanting secure database not good when only DBA with 10 developers. I was outmanned on the battlefield.
There is a fine art to doing things right and not denting any egos. Definitely worth a white paper
"Keep Your Stick On the Ice" ..Red Green -
Yes, it's like walking on very thin ice.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Good for you! That's one of the few battles that I will constantly fight.
No sa for developers
-
And what about sa for 'normal users' ?
I am a such a user as I'm not in the IT department at all.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I seem to remember an article somewhere on this site that said that SA use, not just access, should be avoided where possible.
If I was in the situation where I have to sell the benefits of stored procedures and non-SA access I would be updating my CV on a daily basis and actively looking for alternative employment.
It would be like having to sell the benefits of a dishwasher. There are situations where you wouldn't use either stored procedures or the dish washer, but for most occassions they are a god-send.
-
I don't think anyone except the DBA and potentially Sys Admins should have sa access and sys admins only for remote hands/emergencies. This group should be tightly controlled.
On the Unix side, my DBAs do not have root access, which requires sa assistence for patches, upgrades, etc.
-
Unfortunately I still am fighting the battle of who has SA access, I have started segmenting people based on needs but since I am a developement group and the manager doesn't seem to want to let me lock down I am stuck with several SAs who can and have caused me issues (nothing like having to test you backups regularly). I still fight over object ownership, the fact some of them think using the SA account itself to attach to with their app is ok. I just change that password every few weeks to tick em off and they eventually catch on.
-
It is a tough decision to make when there have to be Multiple Groups with that account. For some reason it does not happen very often when you want the Domain admin account
And Antares686 how often do you test the backups when they are really big (300GB db)?
* Noel -
That would be a toughy. Fortunately my largest DB got to be 75 GB that I recall and that one I refused to care about except a once a week full backup since it was duplicating data from a remote source for reporting. Most are under 20 GB and I test about once every other month or so.
-
I have a database in the size range and practice a restore about once every two months. Why? Because some data changed and a comparison is needed!!
I use a snapshot backup method off of an EMC cluster that when used with the vendor's tool I can roll the transaction log backups forward.
As for us and the 'sa' account. Absolutely nobody logs in with the account. Including myself. The passwords are changed through the enterprise quite frequently and I log in with a tailored down admin account. For upgrades and configuration settings I use the service account. I try to keep it very controlled.
On another note I want to celebrate!! Our lead developer came to me today and wants to use summary tables
Breaks my heart!!!
"Keep Your Stick On the Ice" ..Red Green -
Summary tables? Hm...have I heard this before? Sounds like a great idea
Now serious, I agree that no normal user should be sa. In my case I made a deal with the network admins who are partially dba's. I once said:
'Hey did you change something. I can't do this now (I think it was using dbcc inputbuffer).'
They ask:
'Do you need this?'
'Yes!'
'Ok, we'll put you back in, but kick you out when auditors come and when you don't help us solving db problems.'
I can live with that deal.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
thanks god those deals exist otherwise I would have been kicked out of my job
* Noel
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply