Post reply

CLR Deployment - DLL Location

  • March 14, 2012 at 3:00 am

    #258549

    Where does SQL Server store the user-defined DLL you load as an ASSEMBLY for CLR Routines?

    Are they

    1) stored in the database as a binary object, and loaded into memory as needed.

    2) stored by SQL Server in some predefined disk location (if so where - and is it configurable), and loaded from disk as needed

    3) just referenced from the disk location you copied it to load it in the first place

    4) it depends ... (I've learned to always allow for this) ... if so on what?

    And is SQL Server 2005 different to SQL Server 2008 or R2?

  • March 14, 2012 at 6:32 am

    #1459122

    It's stored as binary data inside the database where you created the assembly.

    You can inspect each assembly's binary data in sys.assembly_files.

    You can therefore safely delete the dll file from disk after you have executed CREATE ASSEMBLY.

    Edit: We deploy CLR assemblies by reading the dll content and construct a CREATE ASSEMBLY FROM <assembly bits>. That way we don't need to worry about disk permissions or structure on each SQL Server.

  • March 14, 2012 at 6:52 am

    #1459135

    After you've deployed it once, you can script the assemblies out, just as Nils is describing;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 14, 2012 at 7:10 am

    #1459152

    Great. So I can bypass the need to put the dll on the Server by just scripting out my dll as a binay object and Lowell says - and run the SQL from my SSMS.

    This means I can bypass our DBAs iron grip on server permissions and his worries about what happens when a cluster fails over 😀

  • March 14, 2012 at 7:16 am

    #1459155

    Yes.

  • March 15, 2012 at 3:11 am

    #1459691

    @lowell

    How did you upload the image to the forum post?

    Edit: I managed this by uploading the image to my Flickr account and linking to the static image. But is there a way to do it within SQL Server Central?

  • March 15, 2012 at 5:14 am

    #1459739

    well, i have a shared web server out there somewhere, so i throw my screenshots there, and link directly to them.

    so if you are not linking to an image you found on google images or some site,

    to do it all within the forum, you upload an attachment, and then create an {IMG} IFCode tag to point at the attachment, even though it's ending in aspx.

    what can be wierd is you cannot see the image when you "preview" your post...you have to post to the forum first in order for the image to be public/visible, so during a preview you don't see img links pointing to the attachment in the same forum post.

    {img}https://www.sqlservercentral.com/forums/Attachment11082.aspx{/img}

    quote this post to see the tag with real brackets...the little image is a forum attachment, the big image is on my web server.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 18, 2013 at 8:43 am

    #1659745

    Hello, if you were to remove the dlls from an installation directory, what happens if the SQL Server Instance is restarted or the cluster fails over to another node? Does the CLR need to be reinstalled? Would it automatically install if the dlls are still located in a deployment directory?

    Thanks

  • October 18, 2013 at 11:29 am

    #1659810

    hdillow (10/18/2013)

    Hello, if you were to remove the dlls from an installation directory, what happens if the SQL Server Instance is restarted or the cluster fails over to another node? Does the CLR need to be reinstalled? Would it automatically install if the dlls are still located in a deployment directory?

    Thanks

    I believe the dll's are stored as a binary string inside the databases (sys.assemblies/sys.assembly_files) and used from INSIDE the database in question...they don't have to exist in the installation folder once you deploy them.

    so think of the installation folder as a temp folder, needed only long enough to run the command to installthem into the database.

    from there, since the CLR's are in the database, and not on disk, a failover on the cluster doesn't affect it as long as the database is available.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply