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.
How many Databases are affected: Is the CLR code doing anything that requires the Assembly to be marked with a
PERMISSION_SET of either
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
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
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
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 across so many client/app DBs, that a single plan is great for some but also quite horrible for others. If you don't want the performance hit of specifying
WITH RECOMPILE, then deploying that to each client/app DB would allow for more individualized optimization. In short: central DB is less memory used for plan cache, but potentially worse performance; separate DBs is more memory for plan cache, but fewer potential performance issues.
When it comes to SQLCLR objects, the same plan-caching pros and cons exist for each approach. But now that we are dealing with App Domains there are additional ramifications to consider. App Domains are memory spaces / sandboxes that .NET uses to run code in. Each App Domain is its own separate sandbox. In SQL Server, App Domains are created per each Database and Assembly Owner combination. So multiple Assemblies in the same DB owned by the same User will share an App Domain, but Assemblies in that same DB owned by another User will have a different App Domain, and Assemblies in other DBs will be in their own App Domains. With that in mind:
Memory consumption increases at a faster rate when deploying to individual client/app DBs since the Assemblies being used are loaded into the App Domains (but they are not loaded until they are first used). The App Domain also holds all of the variables, resource handles, etc (until those things are marked for Garbage Collection and GC decides that the moon and stars are aligned perfectly and takes that as a sign to run). So a 2 MB Assembly in one database using one AppDomain that has a certain amount of memory reserved for variables, etc can be quite different than loading that same Assembly into 100 DBs where it is now 200 MB (technically there is some portion of the DLL that is shared in memory across multiple instances of it, but I'm not sure how to measure that) plus 100 times the amount of space reserved for variables etc.
A related issue is if you are using Regular Expressions and making use of the RegEx option for
Compiled which compiles the expression down to Intermediate Language (MSIL). This does speed things up for repeatedly used expressions, but once an expression is compiled, it cannot be garbage collected and will stay in the AppDomain until it is restarted. If there is a commonly used RegEx function that is using the
Compiled option, the memory used to store it will be repeated per each DB if the Assembly is loaded into each DB. In this case it might make sense to put this code in a centralized DB.
Resource limitations can be an issue when using a centralized database. Depending on what classes you are using, you might unknowingly create a resource bottleneck. For example:
When using the static RegEx methods instead of the instance methods, the Regular Expressions that you use are cached. But the default cache size is only 15 expressions. If a great variety of expressions is being sent in from a large number of clients or apps, then expressions won't stay in the cache for very long. So if this is the only reason to consider loading the Assembly into each Database, then you could just increase the cache size. Please see the MSDN page for RegEx.CacheSize for details.
Similarly, if making
WebRequests then there is a default maximum number of active connections that can be made to a particular URI. And that default is just 2. If you make more requests to that same URI (very easy to do if it is a static location and you are using a centralized DB for this code) then any requests above that maximum will simply wait in line for a current connection to close (i.e. blocking). So you would have to either load the Assembly into each client/app DB or increase the connections-per-URI limit. You can set the default maximum for all URIs in the current App Domain by setting ServicePointManager.DefaultConnectionLimit (this can be set once per start up of the App Domain, such as in a static class constructor), or can be set on a per-URI basis by creating a HttpWebRequest and then setting its .ServicePoint.ConnectionLimit property (this needs to be done each time the WebRequest is instantiated since the object has a maximum time to live and once garbage collected, the ConnectionLimit will revert to the
ServicePointManager.DefaultConnectionLimit value, as noted above, when a new instance is created).
If you are using a static variable to cache certain values (shared memory -- rare but still a possibility), then you need to decide what the scope of sharing those values should be. If you want the sharing to be contained within each client/app DB, then load the Assembly into each client/app DB. But if you want to share those values across all DBs, then put the Assembly into a shared, centralized DB.
General functional aspects
Database access: Is the code referencing any database-specific objects? Keep in mind that executing SQL using the in-process /
Context Connection = true; connection will execute initially with the "current" database being set to the database where the object exists, not necessarily where the object is being called from. Hence code running in a customer/application database and calling an object in a centralized database won't be able to reference objects by using only 2-part names. However, you can still use a centralized database for such code as long as you have an input parameter for
[SqlFacet(MaxSize = 128)] SqlString DatabaseName) and then pass
DB_NAME() into it. Then you can use
DatabaseName.Value in the SQLCLR code for either a
USE statement or to concatenate into the Dynamic SQL to create the appropriate fully-qualified object names (i.e. 3-part names).
This is probably not a determining factor if you are only referencing system-based objects (i.e.
sys.databases) that return the same rows no matter what database you are in. Nor should it be an issue if you are making an external connection since you would already be passing in the database name for the connection string, or you will just be logging into the default database for the login making the connection.
Collation differences: If the Collations between the centralized DB and the client / app DBs are the same, then this is not a determining factor when deciding between these two models. But, if your system is going to support different collations, then you need to be aware of what you code is doing as it might be impacted by Collation Precedence. If you are sending in strings that will be compared to other strings, then the behavior might not be what is expected, even if there is no error produced. The collation used to compare local variables and string literals will be the default Collation where the object (i.e. Stored Procedure or Function) exists. If this Collation is different than the collation of the "current" database when that object is called (if passing in a literal or variable), or different than the field being passed in, then there can be a variety of differences in how that comparison is done. Hence, if supporting a variety of Collations, then string-based operations might be more stable/consistent when the code is deployed to each client/app DB.
The following are reasons given in this Question, and in the duplicate question linked at the top of this answer, for preferring one method or the other, but that I feel are not truly relevant in deciding which method is a better fit:
- Centralized DB is easier to support: How so? The code should only exist once in source control. And assuming that the code loaded into each client/app database is the same, then troubleshooting should be roughly the same in either case. If anything, placing the SQLCLR code in a common database, separate from the client/app DBs adds a layer of complication with cross-database calls that can technically be seen as a reason to not centralize this (or other) code.
- Centralized DB is easier on deployments: If this is the case, then I would say that one needs to fix their deployment process. If you are working with a model in which you duplicate the client / app databases to have identical schema and just different data, then you already face this problem when deploying schema changes. Deploying SQLCLR is also just DDL statements. It might be more difficult to deploy SQLCLR code if attempting to load the Assembly from the DLL, but there is no reason to do that. Just make sure to have a self-contained SQL script that does a
CREATE ASSEMBLY or
ALTER ASSEMBLY using the hex bytes (i.e.
- Individual Client / Application DBs are better for backup/restore: The argument here is that if there is a problem and you need to restore, then having everything contained in the Client / App DB being restored is easiest. I would say that if you need to do a restore, then restoring 2 DBs (Client / App DB and common DB) is pretty much the same work, and restoring 101 DBs (100 Client / App DBs and 1 common DB) is still pretty much the same. And from a reliability stand-point, either your backup process is reliable and can be trusted to properly back up both Client / App DBs and common DB, or you need to fix your backup process ;-).
- Individual Client / Application DBs are easier for testing variations: To a degree this is true if you need to test one version of the code without changing all references. Though usually this should be done by testing in a completely different environment to begin with. But if there is a customer who is going to beta-test some code change, for example, then this situation can be mitigated easily enough by using the model suggested by @AaronBertrand in his answer to this question which is very similar (it concerns mainly T-SQL objects and not specifically SQLCLR code): Create function in central database or repeat in each database? . The model discussed in that answer is to use a centralized DB for most/all cases, and to create Synonyms in each Client / App DB so that the local code can reference local names. Then, if a variation is needed, code can be placed in a particular Client / App DB and the Synonyms would then be removed. In that case, the local T-SQL code would still reference the same local names and not know the difference. Personally, I really like this approach and cannot think of any particular downside to it outside of needing to create / remove the Synonyms, which doesn't seem like a high price to pay for the flexibility gained.
Ergo: for the particular situation described in this Question (i.e. scalar function, no external resources, no database-object access, no resource limitations), it seems like using your single configuration database would be fine.
** If you find yourself thinking "but assemblies set to EXTERNAL_ACCESS or UNSAFE are security risks because of what they allow you to do": I was not saying that there is no risk at all with assemblies set to EXTERNAL_ACCESS or UNSAFE if you use the Certificate / Asymmetric Key -based method. What I am saying is, in that configuration, whatever risk exists is no different between placing the Assembly in a centralized database versus in each client/application database. This is because any potential security problems that could result from assemblies that are set to EXTERNAL_ACCESS or UNSAFE are not localized to the database in which those assemblies exist (unlike setting
ON). Any security issues are system wide. But, when setting databases to
TRUSTWORTHY ON, then you have additional, per-database security issues.