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

SQL Man of Mystery

Wes Brown is a PASS chapter leader and SQL Server MVP. He writes for SQL Server Central and maintains his blog at http://www.sqlserverio.com. Wes is Currently serving as a Senior Lead Consultant at Catapult Systems. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.

Adventures in SQL CLR and C#

I’ve toyed with the CLR in SQL Sever 2005 off and on since the first Yukon beta had it enabled. And I’ll be honest with you, I was not a fan.It wasn’t like “YOU got chocolate in my peanut butter!” kind of moment for me. I really thought it was going to be a disaster of biblical proportions. As SQL Server DBA’s we caught a break, adoption wasn’t exactly stellar. The problem was there are enough restrictions and little gotchas to keep developers from whole sale abandoning Transact SQL for something more familiar. Fast forward a few years and now I’m not so scared.My biggest worry back then was memory usage. I’m still not very comfortable with it, but on a 64-bit platform you can mitigate those issues by adding more memory. On a 32-bit platform you could cause all kinds of damage by squeezing the lower 4GB memory space to the point you could have connection and backup failures due to lack of memory. Oh and the fix is usually restarting SQL Server. An example of this comes directly from http://msdn.microsoft.com/en-us/library/ms131075.aspx 

Scalable Memory Usage

In order for managed garbage collection to perform and scale well in SQL Server, avoid large, single allocation. Allocations greater than 88 kilobytes (KB) in size will be placed on the Large Object Heap, which will cause garbage collection to perform and scale much worse than many smaller allocations. For example, if you need to allocate a large multi-dimensional array, it is better to allocate a jagged (scattered) array.

88KB!

This memory thing is serious.

The other biggie is what you can, or cannot do using the CLR.

Again from MSDN http://msdn.microsoft.com/en-us/library/ms131047.aspx

SAFE is the most reliable and secure mode with associated restrictions in terms of the allowed programming model. SAFE assemblies are given enough permission to run, perform computations, and have access to the local database. SAFE assemblies need to be verifiably type safe and are not allowed to call unmanaged code.

UNSAFE is for highly trusted code that can only be created by database administrators. This trusted code has no code access security restrictions, and it can call unmanaged (native) code.

EXTERNAL_ACCESS provides an intermediate security option, allowing code to access resources external to the database but still having the reliability guarantees of SAFE.

Most restrictive to least restrictive permissions. Something you don’t worry about in general as a C# programmer but in the database its always an issue in some way.

What it boils down to:

If you are just talking to SQL Server using basic C# stuff leave it in SAFE which is the default.

If you need access to the file system or the registry and some other limited stuff EXTERNAL_ACCESS is the way to go.

IF you want to have the ability to completely tank a production SQL Server UNSAFE puts it all into your hands. You can call unmanaged code via P/Invoke, all bets are off.

 

Some additional light reading on what libraries can and can’t be called in the CLR.

http://msdn.microsoft.com/en-us/library/ms403273.aspx

Fun stuff, no Finalizers or static fields, read-only static fields are ok though. You will see why this is important to me a little later on.

http://msdn.microsoft.com/en-us/library/ms403279.aspx

 

T-SQL vs. CLR

The other thing I had been promoting, and not always correctly, is putting complicated math functions in CLR. Generally, I’ve found that most math problems run faster in the CLR over native T-SQL. And I’ve found for the most part that holds true for the core algorithm. Once you add data retrieval into the mix things shift back in T-SQL’s favor for a lot of operations. Like everything else, test your ideas using real world scenarios or as close as you can before deciding on one technology over another. I prime example for me was coding up Pythagorean and Haversine equations for the classic distance between two zip codes in T-SQL and C# via CLR. Running test data through an array in the C# solution it ran rings around the T-SQL function I had coded up but once it had to start pulling and pushing data back to the database the T-SQL solution was the clear winner.

Another aspect where the CLR can be much better is string manipulation. I’ve written a couple of small UDF’s to handle some of this since using the LIKE ‘%’ would cause a table scan anyway the CLR UDF was faster internally when dealing with the string than T-SQL was using all the string handling functions.

I’m also seeing quite a bit on using the CLR for rolling aggregates and other kinds of aggregation problems. I don’t have any personal experience in that yet with the CLR.

There are also some things that aren’t practical at all using T-SQL, some would say you shouldn’t be using the database for some of this stuff in the first place but that is an argument for a different post.

 

And Now for Something Completely Different…

I’ve recently started working on my most complex project using the CLR, some aspects have been covered by other folks like Adam Machanic, Robin Dewson and Jonathan Kehayias but there was some specific requirements that I needed.

Thus was born….

SQL Server File System Tools

This is a codeplex hosted project and all the source code is available there for your viewing pleasure.

I’ve done a lot of C# stuff but this was my first hard core CLR app for SQL Server.

What the assembly does is pretty simple, store files in the database ether native, encrypted or compressed.Yoel Martinez wrote up a nice UDF that does blob compression using the CLR. Between this and examples in Pro SQL Server 2005 on storing files in the database I knew I could do what I needed to do.

The wrinkle in my project was not just reading the file and storing it compressed it was putting it back on disk compressed as well. Enter #ziplib (SharpZipLib). This library allows you to pretty easily create standard zip files that even Windows Explorer can open and extract from. So with all the bits in place I set out to build my little tool.

 

Development Cycle

The first thing I did was put together all the samples I’d found build them up as a set of stored procedures instead of UDF’s and just got the file in and out working. Next I added compression via C#’s DeflateStream to see what it would take to get the data flowing in and out and what the performance hit in memory and time would start looking like. At this point I was pretty optimistic I could knock this thing out in a day or two tops. That was all fine and dandy until I started integrating the #ziplib library. My initial goal was to have the assembly set to EXTERNAL_ACCESS since that was the most restrictive security model.

Since the guys that wrote #ziplib didn’t have the CLR in mind there are several things that break without UNSAFE set. As I mentioned earlier the use of finalizers and static fields were the two big ones. I will at some point recode those parts but for now they are still in place. The second thing is the library covers a lot more functionality that I actually need, So I’ve removed the bits I can without refactoring the library. The resulting DLL isn’t horribly big at this point but I figure when I get around to coding up the finalizers I’ll refactor down to what I need then. One big plus for me though is #ziplib is all managed code written in C# so it is pretty easily added directly into my DLL so I don’t have to register two assemblies or call down to the file system to a unmanaged DLL. Compression is handled by RijndaelManaged which is a built in .net 2.0 libraries.

The big downer for me was trying to debug the the code in Visual Studio 2008, when it did work it was ok but It would fail to connect or fail to register the assemblies so I just fell back to injecting debug messages and running tests manually in SSMS.

One thing I really like about programming languages like C# is method overloading, I really wished you could do that with stored procedures! Since I can’t there were only two options, a stored proc that had lots of flags and variables that may or may not be used and handle it all under the covers or just build each option into a proc with simple callers and a descriptive name. I voted for option two. Some of the T-SQL procedures are used internally by the CLR procedures while all the CLR procedures are called by the user.

Here is the list procedures and what they do.

Called by CLR procedures as helpers

InsertFile
Is called by every proc that inserts a file into the database.

SavePassphrase
Called by StorePassPhrase to handle insert into database.

RetrievePassPhrase
Called by any proc that has to decrypt a file stream

RetrieveFile
Called by any proc that retrieves a file from the database

Called by Users

T-SQL Procedures

InsertTag
Called by User supplying a file id and list of key words or “tags” as a search helper other than file name.

RetrieveFileDetails
Get details on a single file or every file stored in the database.

CLR Procedures

StorePassPhrase
Give it a password and it generates a secure hash stored into the database for symmetric encryption

Below all store a file from the file system into the database.
StoreFile
StoreFileEncrypted
StoreFileCompressed
StoreFileEncryptedCompressed

Below all retrieve a file from the database back to the file system.
ExtractFileToDisk
ExtractFileToDiskEncrypted
ExtractFileToDiskCompressed
ExtractFileToDiskEncryptedCompressed

Below all retrieve a file from the database but returns a record set with the file name and the BLOB data.
ExtractFileToRecord
ExtractFileToRecordEncrypted
ExtractFileToRecordCompressed
ExtractFileToRecordEncryptedCompressed

And lastly, I put in an xp_getfiledetails clone since I wanted a way to verify the file is on disk and get attributes it seemed pretty straight forward since I’m getting the same details when i write the file to the database anyway.

Final Thoughts

This project isn’t done yet. there are a few more things to be added other than the code cleanup I mentioned already.

Off line decryption tool so the files dumped to disk still encrypted can be worked with.

Additional stored procedures for searching for files by tag or by attributes like name, size, etc.

A real installer and not a zip file with T-SQL scripts.

After that it goes into maintained mode with no new features but work on speeding it up, reducing the memory impact and fixing any bugs that are found. I really want to avoid this growing into a huge library, Keep it simple, do one thing and do it well.

Resources

Here are some things that helped me along the way.

Development Tools

Visual Studio 2008 
could have used notepad but hey I’m getting lazy in my old age.

JetBrains ReSharper 4.5
If you are using Visual Studio ReSharper is a must. I feel like I’m programming the the stone age without it.

GhostDoc
Free tool to help you document your C# code using XMLDoc. Yet something else I wished I could do with stored procedures

Doxygen
If you are building documentation and have XMLDoc in your code this can make it easier to gather it all together. It isn’t perfect but it is free.

Books

Both solid text Pro SQL Server 2005 has a chapter on CLR

Pro SQL Server 2005

 

This one is dedicated to just CLR and was also invaluable to me.

Pro SQL Server 2005 Assemblies

 

On The Web

http://www.sqlclr.net
Devoted to the CLR with some nice resources.


 image
My Twitter buddies are always there to answer a question or two!

Until next time!

-Wes

Comments

Posted by Anonymous on 10 October 2009

Pingback from  Dew Drop – October 10, 2009 | Alvin Ashcraft's Morning Dew

Posted by Adam Machanic on 10 October 2009

Sounds like an interesting project. Did you see my article on best practices for writing EXTERNAL_ACCESS and UNSAFE assemblies? I recommend two assembles--one for the external/unsafe code, and another, SAFE assembly, that calls into the unsafe code only as needed. While this does require two assemblies it promotes a least privilege methodology and you end up with a much tighter surface area and therefore a more secure end result.

www.code-magazine.com/Article.aspx

Posted by Anonymous on 11 October 2009

Pingback from  Adventures in SQL CLR and C# « Jasper Blog

Posted by Wesley Brown on 11 October 2009

Hey Adam,

No I hadn't read your article. I did explore two assemblies. I'm not skilled enough to implement it yet. I do like implementing the best security I can.

Posted by burton280 on 20 May 2010

This is great.  I have also used SharpZipLib in a CLR stored procedure.  On large files, it sometimes takes up to 40 seconds and the running CLR seems to block all other queries on my SQL server.  How did you get around this?

Posted by Anonymous on 20 May 2010

Pingback from  20 Headlight 1988 Gl Subaru Svx, Brat Baja Svx Parts Subaru Impreza

Posted by Anonymous on 20 May 2010

Pingback from  Manual Gmc G1500 Van Rally, G1500 Auto Parts S15 Jimmy

Posted by Anonymous on 21 May 2010

Pingback from  1999 Mercury Sable Repair Fuel Filter, Sable Dark

Posted by Anonymous on 21 May 2010

Pingback from  Part Schedule Honda S2000 Civic Hybrid, Turbo Brake Pads 2009 Honda S2000

Posted by Anonymous on 21 May 2010

Pingback from  Phoenix Sunbird Used 1980 Pontiac Grand Prix, Sunbird B Logo B

Posted by Anonymous on 21 May 2010

Pingback from  Relay 3 Parts Table Of Integrals, Relay Design Agency

Posted by Anonymous on 21 May 2010

Pingback from  Santa Cruz Promo, Santa Fe Annual Weather

Posted by Anonymous on 22 May 2010

Pingback from  Q45 Free Shipping Auto Parts Infiniti M45, Specs 2006 Infiniti G35

Posted by Anonymous on 22 May 2010

Pingback from  Mercedes Benz R350 Mpg, R3500 Heater Adapter

Posted by Anonymous on 23 May 2010

Pingback from  310 323, 1 3100 Lithium Ion Battery Ajp 3001s - 140.an74.com

Posted by Anonymous on 23 May 2010

Pingback from  Ppb200 Buy, Cornwall Pb200 Daffodil Bulbs - 100.jeepsunlimted.com

Posted by Anonymous on 23 May 2010

Pingback from  Walmart Caballero, G1500 Auto S15 Jimmy Gmc Caballero - 188.animejin.com

Posted by Anonymous on 23 May 2010

Pingback from  Help 2002 Infiniti I35, I35 Radiator Brake Caliper Oiq - 74.tijuanareader.com

Posted by Anonymous on 24 May 2010

Pingback from  Jeep Wagoneer Bumper Wiper Arm, J10 Discount Jeep Grand Wagoneer - 451.cmanager.org

Posted by Anonymous on 24 May 2010

Pingback from  620 Sale Pickup Datsun 280zx Bucket Seats, Discount Dell Optiplex Gx620 Desktop - 62.tgrconversions.com

Posted by Anonymous on 24 May 2010

Pingback from  300te Body Kit Low, Buy Cb300 Selectivend Soda Vending Machines - 336.dlmreza.net

Posted by Anonymous on 24 May 2010

Pingback from  E300 Model Accessories, Fe300 Adoption - 379.dlmreza.net

Posted by Anonymous on 24 May 2010

Pingback from  50w Par20 Bulb, Camescope Sony Hdr Xr200v - 119.ja3ra.com

Posted by Anonymous on 24 May 2010

Pingback from  Cheap Corrado Cars For Sale, Corrado Price Volkswagen Eurovan - 366.binggreen.com

Posted by Anonymous on 24 May 2010

Pingback from  84 Standard Subaru Justy, Buy New Subaru Justy - 62.defutbolazo.com

Posted by Anonymous on 24 May 2010

Pingback from  100 Econoline Club Wagon Sale Explorer Sport Trac Ford E 150, Isuzu Dmax Pickup - 163.defutbolazo.com

Posted by Anonymous on 24 May 2010

Pingback from  250se Indie, 250se Sale Doral Se Power Boats - 84.tvshowzone.com

Posted by Anonymous on 24 May 2010

Pingback from  Jeep Grand Wagoneer Review, Grand Cherokee 2nd Hand Liberty Jeep Wagoneer - 455.defutbolazo.com

Posted by Anonymous on 25 May 2010

Pingback from  2005 S600 Mercedes Benz S550, S600 Radiator Coupon Code - 436.mfbattle.com

Posted by Anonymous on 25 May 2010

Pingback from  Clk500 Sale Pre Owned Mercedes Benz Slr Mclaren Black Opal, K1500 Automotive Pickup - 307.renters.ws

Posted by Anonymous on 25 May 2010

Pingback from  Coupon Holiday Inn Select La Mirada Staybridge Suites, Mirada Indiscreta Blog - 58.codebluehacks.org

Posted by Anonymous on 25 May 2010

Pingback from  C55 Clearance Clk500 Clk55 Amg Clk550, Headlight Low Price Mercedes Benz Clk550 - 199.cmanager.org

Posted by Anonymous on 25 May 2010

Pingback from  Schwinn Cimarron Comfort Bike, Jeans Cimarron Boutique Paris - 235.binggreen.com

Posted by Anonymous on 25 May 2010

Pingback from  1993 Mercedes S320, S320 Omnibus De Mexico - 140.jordanbrandallamerican.com

Posted by Anonymous on 25 May 2010

Pingback from  Ls430 Discount Factory Lexus Lx450, Ls430 Radiator Capture - 119.akemet.com

Posted by Anonymous on 25 May 2010

Pingback from  1958 Cadillac Eldorado Brougham, 1988 Cadillac Eldorado Sale - 491.dlmreza.net

Posted by Anonymous on 25 May 2010

Pingback from  Part Truck Oldsmobile Regency, Regency Tire Automotive - 25.jeepsunlimted.com

Posted by Anonymous on 27 May 2010

Pingback from  2000 - 1999 @ Coach Horses Premier Inn, Adobe Premiere Lyrics - 233.jordanbrandallamerican.com

Leave a Comment

Please register or log in to leave a comment.