Implementing CLR Proc's

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rubens - 22 Jun 2009 14:58 GMT

Hello,

We have recently moved to SQL 2008 and our Developers are now asking about
implementing CLR proc's in the very near future. I know very little about
this, but found an interesting article here on how we can implement them.

http://www.sqldbatips.com/showarticle.asp?ID=22

The process seems very straight forward and I'm wondering if someone can
comment on the process as I see it...

1. DBA's enable CLR on the SQL Server.
2. The Developer sends the DBA's the code through TFS.
3. The Developer indicates what version of the .NET code this should be
compiled with.
4. DBA's review the code and compile it if all looks well.
5. DBA's create an assembly for the compiled code.
6. DBA's create the proc.

Questions

1. Is the recommended best practice to keep all CLR proc's in their own
dedicated database?
2. How are these proc's moved to a new server? My initial thought was
that I could just move the dedicated CLR database to the new server and
recreate the appropriate folder structure for the compiled DLLs, but from
what I can tell, I will need to run through all the steps above to recreate
each assembly / proc instead.
3. I am planning on keeping all the compiled DLL's in a folder on the
SQL server itself, make sense?

Thank-you,
Rubens

Reply to this Message

Bob Beauchemin - 22 Jun 2009 18:56 GMT

MHO Inline...

Hope this is useful,
Bob Beauchemin
SQLskills

> Hello,
>
[quoted text clipped - 19 lines]
> 1. Is the recommended best practice to keep all CLR proc's in their
> own dedicated database?

If your CLR procedures access tables, they should be colocated with the
tables. If they don't access data, you can keep them in their own database.

> 2. How are these proc's moved to a new server? My initial thought was
> that I could just move the dedicated CLR database to the new server and
> recreate the appropriate folder structure for the compiled DLLs, but from
> what I can tell, I will need to run through all the steps above to
> recreate each assembly / proc instead.

You can do this in two ways. Keep the DLLs and redeploy to the new database
with "create assembly from" file or deploy the binary with "create assembly
from" binary stream. To see the latter method, right-click on a CLR assembly
in the database and choose "Script as create to...". You do need to be
careful with the binary data so as not to introduce extra carriage
return/line feeds.

> 3. I am planning on keeping all the compiled DLL's in a folder on the
> SQL server itself, make sense?

Not sure that you'd have to do that, if you're using TFS, you'll can keep
the source and binaries there. You also have a complete copy of the binary
with point-in-time restore (based on your recovery option) in the database
itself.

> Thank-you,
> Rubens

Reply to this Message

Rubens - 24 Jun 2009 15:23 GMT

Excellent info and this makes a lot of sense and is how we are going to go
about it. Thank-you very much Bob.

Rubens

> MHO Inline...
>
[quoted text clipped - 54 lines]
>> Thank-you,
>> Rubens

Reply to this Message

Simon Sabin - 29 Jun 2009 13:50 GMT

Hello Rubens,

Just to add to Bobs comments. If you are using the Database Professional
(Data Dude) version of visual studio if you install the GDR. You can just
reference your .Net project in visual studio and Data dude will sort include
the assemblies in the deployment.

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Excellent info and this makes a lot of sense and is how we are going
> to go about it. Thank-you very much Bob.
[quoted text clipped - 59 lines]
>>> Thank-you,
>>> Rubens

Reply to this Message

Rubens - 23 Jul 2009 18:57 GMT

Hi Simon,

Sorry for the incredibly late response. I haven't been on the newsgroups in
quite some time.

Thank-you for your tip, I appreciate it!

Rubens

> Hello Rubens,
>
[quoted text clipped - 70 lines]
>>>> Thank-you,
>>>> Rubens

Reply to this Message