A good thing or not?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RJ - 31 Dec 2009 16:53 GMT

I’m one of those developer, turned DB developer / forced DBA types, and I am
intrigued with the idea of managed code (CLR) being stored in the database
via assemblies. Our company creates financial software for distribution to
outside clients. We have many cases where a lot of data is sent across the
wire and then financial calculations are performed (java) on a front end
server and ultimately reported. I’m considering proposing that we bring the
financial calculations internal to the database via managed, reducing the
amount of data transmitted and hopefully improving performance. We would
start with some isolated pieces at first to show proof of concept.

My concern is that there doesn’t seem to be a “jumping on the band wagon”
regarding this topic. Note the number of issues in this forum! I have been
burned by Microsoft in the past and I don’t want to head down a proverbial
dead end. Is the reluctance strictly on the part of the DBA not wanting to
lose control? I understand that Oracle DBAs were reluctant to allow java
code into the database but as I understand it they are coming around. Still
it’s been 4 years since the release of SS 2005 and I am not seeing wide
acceptance as yet. The interest seems to be small.

I can see that deployment can be tricky beyond the test servers . Even
though we are a java shop we do have some aspects of our product in C#
already and we are fully committed to SQL Server so the framework requirement
shouldn’t be an issue. One can only assume that .Net environment is here to
stay a good long while.

What other problems have people run into regarding managed code? Why is
there seemingly so little on this topic? Can someone shed some light?

Thanks for your response!

Reply to this Message

Erland Sommarskog - 31 Dec 2009 18:27 GMT

> I'm one of those developer, turned DB developer / forced DBA types, and
> I am intrigued with the idea of managed code (CLR) being stored in the
[quoted text clipped - 25 lines]
> What other problems have people run into regarding managed code? Why is
> there seemingly so little on this topic? Can someone shed some light?

Moving the calculations from an outer layer to the database may be
the right thing. Moving them to the CLR may not be.

But that depends on what is in those calculations. The best way to work
with data is to work with it set-based, and that you do from SQL.

But that said, you can't do everything set-based, and complicated
calculations are certainly more efficiently done in C# than in SQL.
It all depends on what they do.

The system I work with is for securities trading and asset management,
and the main bulk of the business logic is in T-SQL. But the system
does include a calculation module for things like computing price from
yield and vice versa. And this module is a CLR assembly that lives in
SQL Server. The module had a predecessor in C++ that we called from
sp_OAxxxx back in SQL 2000. Moving to the CLR was a big win.

Overall, I think the CLR is a very good addition to SQL Server, but
it's certainly a feature that invites to abuse. If you write all your
stored procedures in C#, you are probably running the wrong way. But
using to for supplementary functions for which SQL is less well suited,
is definitely the right thing.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

RJ - 01 Jan 2010 18:49 GMT

Thanks for the response.

No, I had no intentions of converting all the stored procedures, new and
old, to CLR. T-SQL is definitely better suited for set based processing.
But as you say not every problem is solved by the set based approach.

From your description of the securities trading and asset management we seem
to have similar requirements regarding yields, covariances, matrix processing
and the like. I am glad to hear that moving the calculations from sp_OA and
C++ to C# was a big win. I have come to respect your opinion over the years
in responses to questions posted. You have even answered a few of mine in
the past.

So, I will venture down path with eyes wide open. I know I have a learning
curve ahead of me, as I have spent one whole week on this topic! I bought a
good book on the topic but as we all know, you get the real education when
you actually do some production type project. Do you have any opinion in
combining multiple sps and functions in one assembly? Is it better to have
multiple assemblies (one per class or functional requirement) or as few
assemblies as practical?

Any hints or direction you can offer is greatly appreciated!

RJ

Happy New Years!

> > I'm one of those developer, turned DB developer / forced DBA types, and
> > I am intrigued with the idea of managed code (CLR) being stored in the
[quoted text clipped - 48 lines]
> using to for supplementary functions for which SQL is less well suited,
> is definitely the right thing.

Reply to this Message

Erland Sommarskog - 01 Jan 2010 20:49 GMT

> So, I will venture down path with eyes wide open. I know I have a
> learning curve ahead of me, as I have spent one whole week on this
[quoted text clipped - 3 lines]
> assembly? Is it better to have multiple assemblies (one per class or
> functional requirement) or as few assemblies as practical?

I think that is a question I will have to pass on. I did not write the
CLR implementation myself. If memory serves, it is a single assembly, but
I may be wrong.

What I've mainly been involved with is how deploy the assemblies in the
production databases and to add support for that to our load tool.

Signature

Reply to this Message

RJ - 02 Jan 2010 00:14 GMT

Given that I will be responsible for deployment I would be interested in your
experience on that topic. From what I can gather the scripting approach
would be as follows: deleted all referred items (sp, functions etc.) included
in an assembly, drop the assembly itself , create the new (updated) assembly
including all Add Files, and create the sps and functions etc referenced in
the new (updated) assembly.

I am assuming I do not need to include any project or source files in the
deployment to production servers.

Does that sound like I'm on the right track?

> > So, I will venture down path with eyes wide open. I know I have a
> > learning curve ahead of me, as I have spent one whole week on this
[quoted text clipped - 10 lines]
> What I've mainly been involved with is how deploy the assemblies in the
> production databases and to add support for that to our load tool.

Reply to this Message

Erland Sommarskog - 02 Jan 2010 10:03 GMT

> Given that I will be responsible for deployment I would be interested in
> your experience on that topic. From what I can gather the scripting
> approach would be as follows: deleted all referred items (sp, functions
> etc.) included in an assembly, drop the assembly itself , create the new
> (updated) assembly including all Add Files, and create the sps and
> functions etc referenced in the new (updated) assembly.

As long as there are no interface changes, all you need is ALTER
ASSEMBLY.

What I do in AbaPerls our load tool, is that I first try ALTER ASSEMBLY,
and if this fails (with any other error that the MVID is not different,
which means that the assembly has not been rebuild since it was loaded
to the database), I drop all referring objects. The way the update
scripts are built, the files with procedures and function definition
will be reloaded anyway.

Full details at http://www.sommarskog.se/AbaPerls/doc/CLR.html, although
without full context it may not make much sense to you. The interesting
part here, is that you probably have some means to deploy stored procedures
etc to the database, and you need to find a way to fit in assemblies
to this pattern.

> I am assuming I do not need to include any project or source files in the
> deployment to production servers.

Yes. In they way it works for us is that the assembly is built like we
build most other client-side programs. (Well, maybe not, but if there
are differences they are not relevant here anyway.) Then the assembly is
added to SourceSafe in the SQL tree from where AbaPerls takes care of it.

What I would like to have, but I don't have came around to, is to be able
to enter a C# file directly into the SQL tree, and AbaPerls would compile
it and load the assembly. The C# file would also have the SQL command to
create the function/procedure(s) defined by the file. This would be good
for simple CLR objects which is just 100 lines of code, and for which the
full building procedure would be a pain.

Signature

Reply to this Message

RJ - 02 Jan 2010 17:02 GMT

Thanks again for spending time in explaining the various aspects. It has been
invaluable to me.

> > Given that I will be responsible for deployment I would be interested in
> > your experience on that topic. From what I can gather the scripting
[quoted text clipped - 33 lines]
> for simple CLR objects which is just 100 lines of code, and for which the
> full building procedure would be a pain.

Reply to this Message

Adam Machanic - 01 Jan 2010 23:06 GMT

> you actually do some production type project. Do you have any opinion in
> combining multiple sps and functions in one assembly? Is it better to
> have
> multiple assemblies (one per class or functional requirement) or as few
> assemblies as practical?

It depends on what you're doing, of course. Some considerations:

- Security. If you have 100 methods that do "safe" operations and a
single one that requires an "unsafe" privilege, do you really want to have
to give all 101 methods the "unsafe" grant via the assembly? See the article
linked below at [1] for more information.

- Persisted columns. If you need to persist a calculation in either an
index or persisted computed column, redeployment of the assembly is a much
more complex process. You don't want to have to go through that pain if you
just need to change some other method that's not related.

- AppDomains. You may find a situation where you want to cache some data
in memory in the CLR space. And in some other situations you may find that
AppDomains are getting unloaded by the runtime for one reason or another.
Unloading the AppDomain will clear out your cache, so if you are doing this
you might want to segment things a bit. AppDomains are created per database
per assembly owner, so you need more than just another assembly but also
another owner to make this work.

- Personal and corporate preference. You or your company may prefer more
or fewer assemblies simply due to the way things are managed. What fits best
into the deployment strategies you have in mind? Will creating a lot of
assemblies complicate things (or the other way around)?

[1] http://www.code-magazine.com/Article.aspx?quickid=0705051

Good luck!
Adam Machanic
http://sqlblog.com

Reply to this Message

RJ - 01 Jan 2010 23:48 GMT

Thank you both for your educated input.

> > you actually do some production type project. Do you have any opinion in
> > combining multiple sps and functions in one assembly? Is it better to
[quoted text clipped - 35 lines]
>
> .

Reply to this Message

RJ - 01 Jan 2010 23:56 GMT

I took a quick look at the referred site and your point about encapsulation
makes a lot of sense. Thanks again.

> I’m one of those developer, turned DB developer / forced DBA types, and I am
> intrigued with the idea of managed code (CLR) being stored in the database
[quoted text clipped - 25 lines]
>
> Thanks for your response!

Reply to this Message