Programming Microsoft SQL Server 2005 : Deployment (part 1) - Deploying Your Assembly

Before you can test your SQL CLR code, you must deploy the assembly containing it and register the individual functions that you want recognized as stored procedures. A number of deployment methods are at your disposal; we will pause to cover them now, before discussing testing of your stored procedures and the other four basic SQL CLR entities.

Deploying Your Assembly

Visual Studio deploys the SQL Server project version of the sample code when you build, start, or step through the project or use the Build/Deploy function on Visual Studio’s main menu. If you’re working with the SQL Server project version of the samples, go ahead and use the Deploy option or one of the Start or Build options in Visual Studio now.

You can execute the following T-SQL command from within Management Studio:

CREATE ASSEMBLY Chapter03
AUTHORIZATION dbo
FROM 'C:\ProgrammingSQL2005\Chapter03\VS\Chapter03Manual\Chapter03\bin\Debug\Chapter03.dll'
WITH PERMISSION_SET = SAFE
GO

					  

The AUTHORIZATION clause allows you to specify a name or role to which ownership of the assembly is assigned. The default authorization is that of the current user, and because you are most likely logged in as dbo for AdventureWorks, in this case the clause is unnecessary (which is why we omitted it from our previous example).

The meaning and effect of the WITH PERMISSION_SET clause are discussed at the end of this article. For now, just note that this clause allows you to specify the security permissions with which your assembly runs. As with the AUTHORIZATION clause, in this case the WITH PERMISSION_SET clause is technically unnecessary because SAFE is the default PERMISSION_SET value used when a CREATE ASSEMBLY command is executed.

If your assembly has dependencies on other assemblies, SQL Server looks to see if those assemblies have already been loaded into the database and, if so, confirms that their ownership is the same as that of the specified assembly. If the dependent assemblies have not yet been loaded into the database, SQL Server looks for them in the same folder as the specified assembly. If it finds all dependent assemblies in that location, it loads them and assigns them the same ownership as the primary assembly. If it does not find the dependent assemblies in that folder, the CREATE ASSEMBLY command will fail.

You can supply a string expression instead of a literal in the FROM clause, allowing for some interesting data-driven possibilities. For example, you could fetch an assembly path reference from a table in your database. It is also possible to supply a bitstream in the FROM clause instead of a file specification. You do this by specifying a varbinary literal value or expression (or a comma-delimited list of varbinary values or expressions, when dependent assemblies must be specified) that contains the actual binary content of your assembly (or assemblies). This allows the creation of a database, including any CLR assemblies it contains, to be completely scripted, without requiring distribution of actual assembly files. The binary stream can be embedded in the script itself or, using an expression, it can be fetched from a table in a database.

More Info

See SQL Server Books Online for more information on this option.

In addition to using Visual Studio deployment and the T-SQL CREATE ASSEMBLY command, you can upload the assembly into your database interactively from Management Studio. Simply right-click the servername/AdventureWorks/Programmability/Assemblies node in the Object Explorer (where servername is the name of your server) and choose New Assembly... from the shortcut menu. The New Assembly dialog box, shown in Figure 3-7, appears.

Figure 1. The Management Studio New Assembly dialog box

Type the assembly path and file name in the Path To Assembly text box, or use the Browse... button to specify it interactively. You can specify AUTHORIZATIONWITH PERMISSION_SET details in the Assembly Owner text box (using the ellipsis button, if necessary) and the Permission Set combo box, respectively. and

Regardless of the deployment method you use, once your assembly has been added to your database, it becomes an integral part of that database and its underlying MDF file. This means if your database is backed up and restored, or xcopy deployed, any assemblies within it move along with the data itself and need not be manually added as a subsequent step.