SQL CLR Multi-Threaded File Copy

Question

SQL CLR Multi-Threaded File Copy

steve.ledridge

SSC-Addicted

Points: 433
More actions
November 19, 2013 at 12:01 am

#300280

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

Viewing 14 posts - 1 through 13 (of 13 total)

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

buddy__a Hall of Fame Points: 3222 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

Eric Higgins

Old Hand

Points: 325

November 20, 2013 at 1:53 pm

#1667917

Fantastic work sir! Thanks for sharing!

sm8680 SSC Enthusiast Points: 132 More actions · Answer 7

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 More actions · Answer 8

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: 132 More actions · Answer 9

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 More actions · Answer 10

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: 132 More actions · Answer 11

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

hsueh010 SSC Enthusiast Points: 126 More actions · Answer 12

I'm trying to understand why the "Copy" has all that additional code, and the line:

//System.IO.File.Copy(CS.Source, CS.Destination, true);

is commented out, while the Move and Delete use the System.IO.File.<Operator>

I know the code was from 7+ years ago, but found it very interesting, would you be able to help me understand.

hsueh010 SSC Enthusiast Points: 126 More actions · Answer 13

Hi, been studying to code a bit, and don't see where the QueueMax variable is ever used to truly restrict the number of threads running. Was wondering how QueueMax variable is used to limit the number of threads spun up?

Thanks.

Never mind. I now see how this is controlled in the while loop around line 222 of the code.

This reply was modified 4 years, 6 months ago by hsueh010.