UGTS Document #81 - Last Modified: 8/29/2015 3:23 PM
Pitfalls with SQL Server .NET Assemblies
.NET Assemblies for SQL Server can be tricky to setup correctly. Code Project has a good example of a .NET Assembly with custom aggregates, and explanations of why the aggregates were created that way. First, you need to identify the maximum .NET version that is supported by the minimum version of SQL Server you're going to support:
Basically, SQL Server 2000 does not support .NET assemblies, 2005 thru 2008 R2 will run anything with a base CLR version of 2.0, and 2012 needs a base CLR version of 4.0. Secondary to that consideration is the version of Windows Server that the database runs on. If your database runs on Windows Server 2003, then you're limited to SQL Server 2008 R2, and therefore .NET 3.5. Finally, note that as of Dec 2014, SQL Azure does not allow .NET assemblies at all - many people have requested it, and Microsoft is considering the upgrade, but we have no timeline for potential support.
So next create a class library project which targets either the 3.5 Client Profile or 4.0 Client Profile. Unfortunately, I know of no way to use configurations to dynamically choose the .NET platform. However, you could use Conditional Compilation Symbols to select code in your project for .NET 3.5 vs 4.0, and then change the platform at build time.
Your class library will run on the server, so obviously it can only reference other libraries that also are installed on that server. Otherwise, your assembly will fail to load or run.
Apart from external references, the class library will need to reference System, System.Core, System.Data, and System.Xml. System.Data contains the System.Data.SqlTypes and Microsoft.SqlServer namespaces which contain most of the defintions you'll need to interop with SQL Server.
Any classes you define in this library will have severe restrictions on their behavior. They cannot store or access any static/shared state information - the SQL server runtime environment does not allow it due to memory management restrictions, and Microsoft has not provided any ways to bypass this restriction. Furthermore, you can't instantiate any classes that have static/shared state (even if they belong to the CLR).
User Defined Aggregates: if you're defining aggregate functions, you'll define one or more classes in the class library that implement the Microsoft.SqlServer.Server.IBinarySerialize interface. As part of that interface, you'll need to define the functions public void Read(BinaryReader r) and public void Write(BinaryWriter w). The purpose of these functions is to save or load the progress of an aggregation in progress if SQL Server has to do that for lack of resources.
Next, if the class is a user-defined aggregate, the class should have the SqlUserDefinedAggregate tag and have an Init, Merge, Accumulate, and Terminate method. These methods should conform to the UDAGG (user defined aggregate) specification, or you'll get an error when you try to define the AGGREGATE in SQL Server after the library is loaded. Particularly, this means that the variable types in the paramaters and return values for these four methods should match the SQL Server data types that SQL Server is expecting for them. For example, SqlString is Unicode and therefore corresponds to nvarchar, not varchar. Other known issues that can interfere with the UDAGG specification is marking the class with the ComClass attribute. Don't use that attribute because COM interop is not supported for an SQL .NET assembly, and the ComClass will mangle the method signatures so that SQL Server can't find them - remove any ComClass attributes from your project.
After building your assembly, you must load it into SQL Server. You'll need to put that DLL on the server's filesystem or a network share that SQL Server can find, then run a CREATE ASSEMBLY SQL statement on the server to load that DLL into memory on the server as a named BLOB inside the database. Yes, the assembly will at that point live inside the database files as binary data - not in the GAC, not in the filesystem, or anywhere else. It becomes part of the database, increasing the size of the database correspondingly, and is bundled with it from there on. After loading it, you can move or delete the orginial file on the filesystem and it makes no difference because the assembly is now loaded inside the database. You can view the assembly in the SQL Server Management Studio under the [database] \ Programmability \ Assemblies. The command that you run to load the assembly into the database looks like this:
CREATE ASSEMBLY UGTS FROM '[path]' WITH PERMISSION_SET = SAFE; GO
But before you can use an assembly in SQL Server, you need to allow them to be run:
sp_configure 'show advanced options', 1;
sp_configure 'clr enabled', 1;
Finally, you create mappings between SQL server objects and assembly methos as follows:
CREATE AGGREGATE ugts.JoinStringsAggregate(@s nvarchar(max), @delimiter nvarchar(max), @sortOrder nvarchar(max))
EXTERNAL NAME UGTS.[UGTS.SqlServer.JoinStrings]
It's worth noting that assemblies don't belong to a schema, only SQL server objects do. So you set the schema when you define the objects. It's also worth noting that you can't drop an assembly unless all the objects which reference are defined to use it in this way are dropped first.
Once you define an SQL server object which calls an assembly, it behaves mostly like any other object. You can drop it, re-add it, or move it between schemas. However, you can't change it method signature easily without also changing the assembly. And you can't remove or replace the assembly without first dropping all the objects. Practically this means that methods in assemblies are non-extensible. Every slight change must be a new major revision of the assembly which is loaded side by side with the previous ones, or it must fully replace the current version. It's therefore a good idea to think clearly about the interface beforehand so that fewer revisions are needed, and also to combine the script which creates the assembly with the add and drop operations for the objects which refer to it, so that it's easier to revise the assembly.