May 12, 2008 at 9:30 am
What is the best way to implement a centralized library of SQL CLR objects (call it LIBRARY), so that they can be referenced from every SQL instance in a production enterprise?
I have such a library of objects for file manipulations, such as list folder contents, copy, move, delete files etc. In the future I'm thinking of adding regex functions, as well as custom aggregate types to it.
I'm thinking of creating the LIBRARY database in a single server instance and somehow regularly 'replicate' the objects across the different server instances where they will be referenced. This is so that changes to these objects in the centralized location will be sync'ed across all instances.
How could this kind of "replication" work?
Perhaps do daily restores on each instance from the previous night's backup?
Any advice from anyone that attempted something like this before?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 12, 2008 at 3:02 pm
I have managed to replicate SQLCLR objects using an SSIS package. A config table on the central db containing the SQLCLR objects is populated with the server instances and local file shares to which the dll file will be copied.
The SSIS package loops through the config table and copies the dll from the central location to each local share. In a 2nd step, the script creating the assembly and individual objects is run on each server instance.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 14, 2008 at 7:37 pm
How did you create the script for the assembly deployment, I hope not manually. I have VS do this for me and if I want it on another db I need to config the project to the new db and redeploy via VS, I like your idea of automating this replication.
BTW, I have the same type of library for the filesystem, and wrote similar ones for FTP, and zip files too. I used tvf for the lists and udf for the commands, and it works great.
-Tom
May 15, 2008 at 8:00 am
The SSIS package I created loops through a table containing the server instances and local paths to which I wish to deploy the SQL CLR objects and dll file and:
(1) Copies the dll from the central location to the local path
(2) Creates the asymmetric key, associated login, assembly and database objects through a script.
I can post the script and describe the SSIS package in more detail if you wish.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 15, 2008 at 6:50 pm
I'm interested if the script you run in step 2 is generated dynamically via another script?
I don't want to have to mod the script every time I add a function to the library.
-tom
May 16, 2008 at 6:57 am
tom (5/15/2008)
I'm interested if the script you run in step 2 is generated dynamically via another script?I don't want to have to mod the script every time I add a function to the library.
-tom
No, unfortunately, by script is static. Objects are hardcoded, although I can't imagine it being a big issue storing the info in a db table and looping through it in a script, creating the objects through dynamic SQL.
That's actually a great idea. I will implement...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 16, 2008 at 7:01 am
Another question I have on this issue:
Is there a performance hit when SQLCLR objects are invoked from code residing on another database on the same instance? In other words, is it better, performance-wise, having the SQLCLR objects residing on each and every user database that uses them, rather than having them reside on their own database?
Anyone have any experience with this?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply