Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLAndy

I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.

LINQ Again!

I recently attended SQLSaturday#2 in Tampa and had the brief chance to chat with David Hayden about LINQ (I suspect I am one of the passionate DBA's he mocks a little in that post!) and it really reinforced yet again for me how wide the chasm is between DBA and developer. For those who haven't ready my earlier post about Whats Wrong with Linq to SQL, I suggest starting there and then returning here.

So, here are some misc thoughts:

  • First, we have to be sure that we separate LINQ from LINQ to SQL. LINQ is a query language for objects and is long overdue, and the implementation I've seen looks good!
  • All tools have the ability to be used for good or evil, but I still argue that 90% of developers (and DBA's!) will use the default font, the default connection string, etc, etc. It's one reason we've fought so hard to make everyone use examples that don't rely on the SA account with a blank password. Most users take the defaults - as they should - as the vanilla implementation that will work for most people.
  • In truth I don't really care how developers package data access. They can use the MS library, write their own, scatter data access throughout the application, use Sonic, Hibernate, etc.
  • What I do care about is that sql statements are parameterzied (for performance and for protection against sql injection), that connection pooling happens, that minimal round trips to the server are made, and that an appropriate amount of resources are used
  • I do care about granting table level access. It is not a good idea and I only recommend it for cases where a truly dynamic search needs to be built (or ad hoc reporting, same thing) and then only when run under a read only account

As a DBA I get paid to secure the data, to safeguard it and server resources, and to make it available for use as the business needs. I think too often DBA's focus on the first three and not the last one, and that's where many of the trade offs occur (and the chasm widens). We expect both too much and too little from developers, and we have to work on fixing that.

What does concern me more is that the same gap might exist in the depths of Microsoft. If you look at SQL 2005 you can see they've added forced parameterization to fix the problem where developers don't parameterize their statements and plan guides (useful on COTS, bandaid on in house stuff) and then we see LINQ to SQL which seems to de-emphasize stored procedures as well as the work MS has done in their patterns and practices library as far as packaging data access. Is MS prepared to recommend granting read/write access to tables as a standard (aka BEST) practice? DBA's are trying to secure and safeguard and stored procedures have been recognized for more than 10 years as the best way to do that (with the exceptions for search noted above).

As a pragmatic DBA I'm more than willing to compromise at the enterprise level. Many of my clients use dynamic sql with parameters, it works fine, and no profit for them in changing things for the sake of change. As someone interested in industry trends I want to make sure that we (DBA, Developer, MS) share a common vision of best practices and when it's ok to diverge from them.

Finally, I'll just that if you've never worked as a DBA and a developer I highly recommend spending 60 days in the other persons shoes. Both jobs are challenging and absorbing, and understanding the pain of the other side can go a long way towards building a productive relationship.

 

 

Comments

Posted by CGSJohnson on 12 January 2012

Hi, Andy.  Thanks for your writing on this topic.

Any updates on this, given Microsoft's release of VS 2010 and Entity Framework 4.0/4.5?

- Chris

Leave a Comment

Please register or log in to leave a comment.