SQL Server 2008 - Deploying CLR Routines

Once a routine is written, tested, and, if necessary, debugged, it can finally be rolled out to production. The process of doing this is quite simple: the release version of the DLL is copied to the server, and a few T-SQL statements are executed.

In order to produce a release version, change the build option on the Standard toolbar from Debug to Release. Once the configuration is set, click Build from the main toolbar, and then click Build Solution. This will produce a release version of the DLL—a version with no debug symbols—in the [Project Root]\bin\Release folder. So if the root folder for the project is C:\Projects\SalesCrossTabs, the DLL will be in C:\Projects\SalesCrossTabs\bin\Release.

The release version of the DLL can be copied from this location onto any production server in order to deploy it. Only the DLL is required to deploy the CLR routines compiled within it. The DLL is registered with SQL Server using the CREATE ASSEMBLY statement, which has the following
syntax:

CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { | [,...n] }
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]
[ ; ]

The assembly_name represents a user-defined name for the assembly. Generally, it’s best to use the name of the project. The AUTHORIZATION clause is optional and allows the DBA to specify a particular owner for the object. The important part of the FROM clause is the client_assembly_specifier, which is the physical path to the DLL file. The assembly_bits option is used for situations in which the DLL has been binary-serialized.

The most important clause of CREATE ASSEMBLY, however, is the optional WITH PERMISSION_SET clause. The DBA is in complete control when it comes to what CLR routines can do. Routines can be assigned to one of three permission sets, each progressively less restrictive.

• SAFE: The default SAFE permission set restricts routines from accessing any external resources, including files, web services, the registry, or networks.

• EXTERNAL_ACCESS: The EXTERNAL_ACCESS permission set opens access to these external resources. This can be useful for situations in which data from the database needs to be merged with data from other sources.

• UNSAFE: The UNSAFE permission set opens access to all CLR libraries. It is recommended that this permission set not be used, as there is potential for destabilization of the SQL Server process space if libraries are misused.

By controlling CLR routine permission, the DBA can keep a close watch on what routines are doing and make sure that none are violating system policies. Assuming that the SalesCrossTabs DLL was copied to the C:\Assemblies folder on the SQL Server, it could be registered using the following T-SQL:

CREATE ASSEMBLY SalesCrossTabs
FROM 'C:\Assemblies\SalesCrossTabs.DLL'

Since this assembly doesn’t use any external resources, the default permission set doesn’t need to be overridden. Keep in mind that if the assembly had already been deployed using Visual Studio, this T-SQL would fail; assembly names must be unique within a database. If there is already an assembly called SalesCrossTabs from a Visual Studio deployment, it can be dropped using the DROP ASSEMBLY statement.

Once CREATE ASSEMBLY has successfully registered the assembly, the physical file is no longer accessed. The assembly is now part of the database in which it’s registered.
The next step is to tell SQL Server how to use the procedures, functions, and types in the assembly. This is done using slightly modified versions of the CREATE statements for each of these objects. To register the GetSalesPerTerritoryByMonth stored procedure, the following T-SQL would be used:

CREATE PROCEDURE GetSalesPerTerritoryByMonth
@StartDate DATETIME,
@EndDate DATETIME
AS
EXTERNAL NAME SalesCrossTabs.StoredProcedures.GetSalesPerTerritoryByMonth

The parameter list must match the parameters defined on the CLR method. The EXTERNAL NAME clause requires three parameters, delimited by periods: the user-defined name of the assembly, the name of the class defined in the assembly (in this case, the default StoredProcedures class), and finally the name of the method defined as the stored procedure in the assembly. This clause is case sensitive, so be careful. Changing the case from that defined in the routine will result in an error. Once the stored procedure is defined in this way, it can be called just like any native T-SQL stored procedure.

Source of Information : Apress Accelerated SQL Server