SQLCLR: A DBA's friend or foe?

When I first heard about "stored procedures being written in C#, VB.Net, ... etc." I remember thinking; Is Transact-SQL dead? Am I out of a job? What does this mean for me and for DBAs in general? Luckily, I learned quickly that not only is Transact-SQL not dead but it's got some significant improvements that might make code written in client applications A LOT easier - for example:
    1.      New syntax to traverse hierarchical/recursive relationships,
    2.      Extended XML integration,
    3.      Much improved error handling,
    4.      Easier in-database pivoting of data

I was worried that the CLR would cause problems for the stability of the database, acting like badly written extended stored procedures which can leak memory, crashing the server or even worse, potentially become a vector for worms and viruses. However as I learned more about the technology, I think that Microsoft has put in the work to give the DBA control over this technology. SQL Server controls the memory used by the CLR, there is a fully integrated security model and the DBA has control over what the CLR code can do and can even turn off the CLR completely on systems that will not use it. In fact, I also learned that the SQLCLR was going to be "off by default" as well. And, for me, as well as many DBAs, I think these points have squashed many of our fears. But then I almost felt like I was missing something? If things didn't need to change then why are they changing?

So, then I just wanted to know more about why the extension was being made to begin with and when and why it might be useful. From considering that point alone came the most important thing I've learned about SQL Server 2005; there are many new ways of doing things: Transact-SQL; SQLCLR; XML; Full-Text; Integration Services; Analysis Services; and all of them have different benefits and implementation effects. DBAs need to learn when to choose each of the technologies and even more importantly when their use is not appropriate, even when they're not an expert in all of them (and I truly think it will be a challenge to be an expert in every area of SQL Server 2005).

SQL Server 2005's feature set has been expanded such that C# or VB.NET can natively run in the server. This gives you immediate access to the power of the .NET Framework and helps to allow better integration with existing utility components and web services already in place. Some of the greatest benefits are that it allows you to place complex computations on the server - close to the data - without having to send large amounts of data to a separate application server. And all of this is done while SQL Server manages memory and resources ensuring a more balanced server system. So, my main recommendation for DBAs who are skeptical is to not worry about knowing everything about SQLCLR but instead focus on why it might be proposed and determine if SQLCLR is really the best way to handle it!

I’m still convinced that Transact-SQL wins when interacting with SQL Server using set-based SQL commands, but if you have a stored procedure with a cursor it may be a candidate for the CLR, if you have code that loads all the data in a table to perform special aggregations it may be a candidate for the CLR, as might your table valued functions (as the CLR does a really optimized job here). To sum up: the DBA has to get a grip of these new technologies and not let the development community overturn years of best practices in database system design – the same principles apply, there is just a richer toolset that, when used wisely, should improve performance and developer productivity.

Categories:
Opinions | SQL Server 2005

31. March 2005 23:31

E-mail | Kick it! | DZone it! | del.icio.us
Permalink | Comments (1) | Post RSSRSS comment feed
Exciting Events Ahead...

OK, so I've been bad (at best) at blogging this year... but I'll blame some of that on the fact that I've done more travel in these first three months of the year then I've ever done. Yes, I've been to Charlotte, NC (for a SQL Server 2005 Ascend Workshop) then Dallas, TX (for yet another Ascend Workshop) then Cairo, Egypt (for the MiddleEast Developers Conference) then Brussels, Belgium (for Dev and ITPro Days) then San Francisco, CA (for SQLLive) then London, UK (for another Ascend Workshop) then Zurich/Interlaken, Switzerland (for TechDays) then Orlando, FL (for SQLConnections) and now I'm home for a couple of weeks (well, kind of that's another story for another day :)). But, yes, I did come back to Redmond (home) a few times in there but - it's been busy to say the least! It's really been an amazing year! From a technical perspective, I can't tell you how many exciting projects I've seen and how many interesting customer scenarios I've been presented with. Some of the planned implementations of SQL Server 2005 and even some of the changes still occuring to SQL Server 2000 implementations are incredibly interesting, challenging and innovative! And on the learning side, I've been completely burried in Yukon, x64 and Developer stuff too (like SQLCLR and C#). I've been working on new courses, new presentations, two already posted whitepapers (see “Online Resources” for links) and two more are coming... And - even more exciting, I'm also working on a couple of pre-conference seminars that will be a blast!! And, that's part of why I'm writing. There are three events coming up where the seating is limited. I thought I'd give you some of the details/highlights so if you're on the fence you can sign up before they sell out. So, let me tell you about the three coming up:

PASS 2005 European Summit
Munich, Germany - May 10, 2005 - 9:00 am to 5:00 pm
The Next Generation DBA
Critical New SQL Server 2005 Features YOU Need to Learn!
This is a full-day lecture/demo packed session where I'll also provide demo scripts (posted on my website) and I'll spend the entire day drilling into the new features of SQL Server 2005 that offer you better data availability. But what does that translate to: Database Mirroring, new Piecemeal Restore/Recovery, fast file initialization, database snapshots, table/index partitioning, snapshot isolation and even some details regarding index-related changes. However, I have two sessions at the event that will cover Best Practices and Changes to Indexing so I'll save those details for the breakout sessions and pack more details into the availability topics. In fact, THE ABSOLUTELY MOST GEEKY demo you will ever see - will be done during this session! (Wow, I've certainly set the bar high for that demo, I hope I can live up to that expectation :).
Check out this link for more details: http://www.sqlpass.org/events/Europe/2005/Pre_Con.cfm.

Microsoft Tech*Ed US
Orlando, FL - June 5, 2005  - 1:00 pm to 6:00 pm
Taming the Tools
Effectively Using and Understanding SQL Server 2005 Tools
This is a first! We’ve decided to have a room packed full of PCs – loaded with Yukon – and we’re going to hammer out a TON of exercises for a full 5 hours. I’ll be lecturing, demo’ing and doing “instructor-led walkthrough” exercises while you get real hands-on experience with Yukon. We'll be focusing on using the tools to make sure that you'll effectively use the right tool for the job! There are all sorts of little hidden treasures in SSMS (SQL Server Management Studio), Profiler, Computer Management, etc. and I've been working with the Tools team to make sure I know about them and I will certainly let you know about them! There will even be a few additional folks present (SQL folks) to make sure that everyone stays on track, focused and that all your exercises go smoothly. And - to make things even better, I’ll be providing all of the exercises and demo scripts (and of course, printed slides) so that you can take everything home and continue to play! This is an exciting one (and definitely limited in the number of seats, hint, hint J) and the early bird discounts end on April 15 (fyi).
Check out this link for more details: http://www.msteched.com/content/precons.aspx#DBA001.

Microsoft Tech*Ed Europe
Amsterdam, the Netherlands - July 4, 2005 - 10:00 am to 5:30 pm
Taming the Tools
Effectively Using and Understanding SQL Server 2005 Tools
OK, so this might be a tad misleading… it appears to be an exact copy of the US Tech*Ed session (at least in title) but in fact, it's not! First, the format is very different. The US session will be 5 hours – and hands-on. The European session will be lecture and demo and over 6+ hours so I’ll be adding more content, more demos and more stuff to work with. And, just like the US session, I’ll be providing all of the exercises and demo scripts (and of course, printed slides) so that you can take everything home and continue to play!  
Check out this link for more details: http://www.mseventseurope.com/TechEd/05/Pre/Content/PCTools.aspx.

And, if you're at any of these events, I look forward to seeing you there! I'm certain we're going to have some fun. Make sure to say hi and tell me you read my blog. It's always fun to meet people who read this! Oh speaking of reading this, here's a strange challenge (and probably very unlikely given the locations). If any of you attend all three... We can continue the discussions of the pre-conference seminars over dinner - in Amsterdam - on July 4th. My treat!! And - in the unlikely event that dozens of you attend all three (yeah, right) well, then beers and a not-quite-so-decadent-dinner-in-Amsterdam is on me! OK, I'm in a weird mood today... What can I say!

Finally, if you're wondering why I no longer allow comments.... ugh, blog spammers from h*ll. I'll bring back comments again after I do a bit of tweaking so I can get the blog spammers to go away!

Thanks for reading!

Categories:
Events | SQL Server 2005

30. March 2005 13:02

E-mail | Kick it! | DZone it! | del.icio.us
Permalink | Comments (0) | Post RSSRSS comment feed

Theme design by Nukeation based on Jelle Druyts