How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and XML text and will make validation.

I will have 1 configuration database and many installations databases. From that prespective I wonder where to create that function - inside configuration database or inside each installation DB?

From support point of view it would be better to have only one CLR fuinction.

This appears to be a duplicate of this question:

Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

However, I do not feel that either of the two answers there are adequate since they do not mention some of the more important aspects of this question.

There is no obvious choice here as to which location is better for SQLCLR objects in general since there can be constraints imposed by what is being done with the SQLCLR code. There are some uses that will require the Assembly to be in each individual database, and one use that would require the Assembly to be in a centralized database. It all depends on a few different aspects of what the code is doing. Hence we need to look at what those aspects are in order to determine if there is even a choice to begin with, and if so, what the pros and cons would be.

SQLCLR-specific functional aspects

-
User-Defined Types (UDTs): UDTs cannot be referenced across databases; they cannot be declared with 3-part names (i.e. DatabaseName.SchemaName.UserDefinedTypeName). If any UDTs are being used then the Assembly will need to be added to each database in which the UDT will be used. However, if other SQLCLR objects are being used, then assuming that there is the choice to place those objects in either a centralized DB or in each customer/application DB, then you could always place the UDTs in an Assembly that gets placed in each customer/application DB and another Assembly containing Functions / Stored Procedures / User-Defined Aggregates / Triggers.

-
Security:

-
How many Databases are affected: Is the CLR code doing anything that requires the Assembly to be marked with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE? If so, are you importing any DLLs that were signed outside of your control and cannot be resigned? Typically these would be unsupported .NET Framework libraries or 3rd party DLLs. When you have no control over the signing of Assemblies that need to be marked as either EXTERNAL_ACCESS or UNSAFE, then you might be forced to set the database containing the Assembly(ies) to TRUSTWORTHY ON. Since setting a database to TRUSTWORTHY ON is a security risk, it is preferable to minimize the number of databases you would need to do this to, in which case putting the code into a centralized database seems like a better approach. And if you already have a central DB for other code and want to truly minimize this type of security risk, you could have a second centralized DB for just this code.

If you do have control over the signing of the DLL(s), then you should definitely be creating a Certificate or Asymmetric Key in the master database based on the DLL, and then creating a Login based on that Certificate or Asymmetric Key, and then assigning either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission to that Login. Those few steps right there (and the only things being created are the Certificate or Key and the Login) will allow any Assembly signed with the same private key to be set to either EXTERNAL_ACCESS or UNSAFE (depending on which permission was granted to the Login), no matter what database(s) it is loaded into. And if you are able to do this, then you can place Assemblies set to either EXTERNAL_ACCESS or UNSAFE in all customer/application databases without any more security risk than you would have placing that same code into a centralized database **.

-
Different permissions for different clients/apps need: If, for any reason, some clients/apps might need to have a different PERMISSION_SET than others, then that would requiring loading the Assembly(ies) into each client/app database. This would allow you to have some databases using SAFE while others were using EXTERNAL_ACCESS. This goes beyond what can be done with object-level permissions. By setting an Assembly that has code to do file system functions to SAFE, you guarantee that the code can't work, even if someone does find a way to bypass your regular security and can still EXECUTE the SQLCLR Stored Procedure.

-
AppDomains: This aspect concerns memory/resource utilization and separation. This is probably the area that is the most impacting in terms of considerations, yet it is also probably the least understood. So, let's start by looking at how T-SQL objects would handle the same central DB vs each client/app DB question.

T-SQL Functions and Stored Procedures, upon being executed, store their execution plans in the plan cache (well, not Inline TVFs), which is in memory. Thinking in terms of just memory utilization, using a centralized DB has the advantage of storing a single plan rather than one plan per each client/app DB, especially if there are 100 or more DBs. However, having a cached plan invites the question of whether or not it is an optimal plan for subsequent executions. It is possible, with a potentially wide range of variation in how it is being executed

StackExchange Database Administrators Q#125754, answer score: 8