SQL CLR Multi-Threaded File Copy

  • steve.ledridge

    SSC-Addicted

    Points: 433

    Comments posted to this topic are about the item SQL CLR Multi-Threaded File Copy

  • buddy__a

    Hall of Fame

    Points: 3222

    Did you ever consider just using Powershell with the Start-Job cmdlet? I expect that the use of Powershell would supplant the need for CLR procs when those procs are just used in a SQL Agent Job. Procs used as part of application processing logic would be viable although most development keeps business logic out of the database.

  • steve.ledridge

    SSC-Addicted

    Points: 433

    As we all know, there is almost always 20 ways to solve the same problem. This just represents the one that I felt was the best fit for our environment and situation. In our world we have a few procedures and functions that can only be done in CLR and we currently deploy the CLR assembly to every one of our 200+ servers already. Once we have jumped that hurdle, it is just as easy for us to deploy new CLR sprocs as it is to deploy T-SQL ones.

    I have considered using Power Shell but we needed something that would integrate completely inside of T-SQL. We have been making attempts to remove all xp_cmdshell calls with more efficient procedures or functions that integrate into the SQL code better. At this point PS is just not integrated well enough into SQL.

    As far as the Agent Job's, I have created processes in the past to run asynchronously by spinning up dynamic agent jobs but have found this to be much more cumbersome that a single sproc. The other problem with an agent job strategy for this purpose is that you would then need to build in a looping monitor that does not move forward until all the files have been copied. I also believe that extra overhead of the agent method would not be as fast for a large number of small files while it might be just as good for a small number of large files.

  • buddy__a

    Hall of Fame

    Points: 3222

    I guess I assumed that this was already being run from a Job. How is the proc being called? From a standalone app?

  • steve.ledridge

    SSC-Addicted

    Points: 433

    I am currently using this in T-SQL Code that does Database restores. In our DR Site we have some mirrored and some log shipped databases. All of these databases must start from a full backup which is much more efficient to restore local files after copying them. Part of our DR Process is to deal with a corrupted database at the DR Site which requires us to reset it from backups. The call to copy is built into a restore sproc that is run manually. It accepts some basic parameters and then does the copy , restore, and re-mirror all in a single call.

    We are also using it in code that is used to refresh our lower environments from production backups which is being called from an external deployment management system. The fact is that most times we want to copy files, it is embedded in the middle of a bunch of T-SQL code that uses logic to determine what files to copy. It just made sense to create this as a procedure that would work inline with the existing code.

  • buddy__a

    Hall of Fame

    Points: 3222

    Sounds appropriate for your environment. I wrote some CLR procs when they first became available but really didn't find a whole lot of use for them. However, I'm doing more application development while your use is more administrative. It was a good article, keep it up.

  • Eric Higgins

    Old Hand

    Points: 325

    Fantastic work sir! Thanks for sharing!

  • sm8680

    SSC Enthusiast

    Points: 129

    Hi Steve,

    I was trying to create this in a test environment. I got as fair as creating the assemblies. But I'm lost as to creating the clr functions and/or how many are needed. Any help would be greatly appreciated.

    Thanks

    Steve

  • steve.ledridge

    SSC-Addicted

    Points: 433

    sm8680 (1/3/2014)


    Hi Steve,

    I was trying to create this in a test environment. I got as fair as creating the assemblies. But I'm lost as to creating the clr functions and/or how many are needed. Any help would be greatly appreciated.

    Thanks

    Steve

    I use Visual Studio to "Deploy" the Assembly to a SQL Server. The act of "Deploying" creates the assembly, installs in into database, and creates and of the procedures and functions associated with it. This also creates a .sql file under the project's bin directory that has the SQL code to create the sprocs and functions along with a .xml file that holds all of the comment blocks in your code.

    My process is a bit more complex because I then have a PostDeployScript.sql in my project that takes both of those files to identify the objects deployed and then uses an SMO app I wrote to script out each object in a particular order preceded by a drop script for the old version of the assembly and related objects, ending with a single deployment .sql file that can then be applied to the other 200+ SQL servers in an automated deployment.

    I will try to post examples of those soon.

  • sm8680

    SSC Enthusiast

    Points: 129

    I currently use SSDT to do the same kind of process that your referring to. Except my process doesn't account for multi-threaded files just sequential. I was able to create the dll and the register the assembly in SQL Server. I spent an hour or 2 trying to figure out the create function statements. Anyways I linkedin into you. If you want you could scale down your project sln and email it to me. I'll try and give it another go when I have more spere time. But this is definitrly very interesting. Thanks

  • steve.ledridge

    SSC-Addicted

    Points: 433

    if you have the assembly installed you just need this:

    CREATE PROCEDURE [dbo].[dbasp_FileHandler]

    @data XML

    AS EXTERNAL NAME [GettyImages.Operations.CLRTools].[GettyImages.Operations.StoredProcedures].[dbasp_FileHandler]

    replacing the names with the ones that you used in your version.

  • sm8680

    SSC Enthusiast

    Points: 129

    Steve thanks I got that to work.

    What does the create function for CLR Function: Directory Listing look like? I really appreciate the help.

    Steve

Viewing 12 posts - 1 through 12 (of 12 total)

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