The April 15, 2008 edition of SDTimes (PDF download here) has some information about SSDS, the SQL Server in the cloud that was announced not long ago (and still in private beta). I'll mention a couple points here, but worth reading the article!

• Says that it doesn't use the same entity model as SQL Server
• Developers must use LINQ to access the data!
• Automatically indexes

An analyst in the article notes that "SQL Server is for more experienced DBA's".

Cloud computing is damned interesting. I've lived in the real SQL Server/database world for too long to want to use a light weight engine, but I can understand why many would. I'm starting to think that we need to training all deveopers to be mini DBA's. They see the power and possibility of LINQ, Silverlight, etc, etc, what is about SQL Server that they don't like?

Steve Jones recently posted an editorial about LINQ and the resulting discussion encapsulates most of the points of view on the topic. The biggest problem "we" have is we just don't have a good picture of the time savings for developers using LINQ (and I really mean LINQ to SQL) so that we have a better discussion about the value of trading off security & DBA maintainability in favor of reduced building cost/quicker time to market - both valid concerns of any business.

Worth reading his post and the conversation with an open mind, see if you can see the value in the side of the conversation you don't agree with:-)

At the recent Orlando Code Camp I had the opportunity to spend a good bit of time discussing LINQ to SQL with Jim Wooley, author of Linq In Action. It was really an interesting conversation. Clearly Jim likes LINQ and sees some opportunities there for developers. As we discussed it more a couple interesting ideas came up. One was that he suggested using table valued functions (essentially parameterized views) as a way to avoid granting table access for LINQ use. Interesting, but not my favorite technique because; one, functions don't always thrill me with performance, and two, it's a hack! It's actually a useable idea, but I think it definitely steps us in a direction I'm not sure I agree with.

I've taken it for granted that everyone understood that allowing full table access is a really bad idea from a security perspective. Should we rethink that? If I can call a proc and pass an employeeid, nothing stops me from writing a loop and calling the proc a thousand times to achieve the same results, right? It's just not as easy and I think that's part of the value. DBA's, if you really step back from this issue and look at it, if - big IF - they could save 10% coding time moving to LINQ is it worth the risk to the business, and is it real risk or just our perception? 

Another part of the conversation was me wanting to know why developers find LINQ to SQL so seductive and his answer (and I think he's right), is that developers see data access code as dull and they just don't want to do it. Strikes me as a little lazy! I wish I could only work on the cool stuff! Combine that with a lot of the friction that has developed between developer/DBA over the years and they are all to ready to do an end run whether it's a good idea or not. I've got to work on this some more, but I think that attitude/perception combined with a lack of helper tools has lead us to this place.

My final thought/worry is that MS itself is going to relegate stored procs to the backseat and the developers will follow. The optimistic thought is that they see something in it that we don't yet, the pessimistic thought is that they've given up on developers and data access and are trying to seduce them into tools that at least do a mininum level of decent data access. 

Anyway, if you need a LINQ book buy Jim's, he's a smart guy and a nice guy, and was kind enough to help me explore some thoughts on Linq - even if we didn't always agree!

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.

There's a lot of buzz in the development community about LINQ in general, and for those that do data access just as much buzz about Linq to SQL. It's hard to describe in a sentence, but think of LINQ as a way to write queries against .Net objects using an almost TSQL syntax. Linq to SQL uses that same slightly different syntax to access SQL Server, but it does so by building an object layer (think of ORM) that hides the data access. For those of you that are DBA's, the first thing to know is that nothing changes server side, Linq to SQL still emits the same TSQL we know and love!

I've done enough development to appreciate encapsulation and to realize that writing a lot of the data access code is drudgery. Linq eliminates some of that and makes data access more object oriented, but I think it contains a couple serious flaws and one fixable flaw:

• SERIOUS. It supports stored procedures, but it does NOT encourage them. By default it generates dynamic sql directly against the base tables/views. I get that all tools can be used for good or eveil, but most developers will use the defaults because MS must think that is the best way to do it and because it's just easier to use the defaults (what I call in my classes 'vanilla'). We've spent 10 years trying to explain the benefits of stored procedures for performance, security, and and maintenance, and this could easily set us back years in that effort. At the least I'd like to see them offer a step in the wizard that generates or regenerates stored procedures. Even better is to figure out a way to start with dynamic sql in the early dev stage, then make it easy to create and transition to procs later in the cycle. DBA's, this can be lose-lose. If you stop them using dynamic SQL you spend a lot of time/energy on that fight and just make them mad, and if you don't, well, good luck on explaining to the auditors why everyone has select * access to tables.
• SERIOUS. If you proceed with dynamic sql in the data layer there is another flaw that should make the average DBA feel faint. You will not be able to easily answer the question 'what changed' when something goes wrong. Imagine your dev team making some 'minor' changes to code that has been working fine after your last round of tuning. They deploy and 10 minutes later performance drops sharply - what changed, or is it something else? When they send us proc changes it's a deliberate step in the data access cycle that let's us look at what they are changing, compare to the performance of the existing proc, and to make changes to it or to the supporting indexed to maintain performance. When the entire app uses dynamic sql you'll be back to Profiling to figure out what changed. Remember, all it takes is adding one column to a select to change a plan from one that is nicely covered by an index to one that uses a scan or worse.
• FIXABLE. It actually generates reasonably dynamic sql, at least on 1-2 tables in the examples I've seen. It uses sp_executesql and you would expect to get reuseable plans, but the way they generate it for strings the input params are sized based on actual rather than declared length, so you wind up with x number of variations on the same plan. Not as bad as using Exec(), but still not good. Turns out they do the same wrong thing with stored procedures AND they execute the proc using sp_executesql (for the latter I'm not sure there is much wrong with that approach, but doesn't seem right either). MS should be able to fix this easily.

The good news is that with coaching we can make it work and let the developers keep most of the cool new stuff.