SQL Server 2012 : Exploring SQL CLR - 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 in this section, before discussing testing of your stored procedures and the other four basic SQL CLR entities.

1. Getting Ready

If you’re using a SQL Server Database Project, you’ll deploy your assembly using SSDT’s Publish function. But before doing so, you must be able to build the project successfully. A build will not only compile the SQL CLR assembly itself, but will also check the code for various errors, including unresolved references to database objects.

Go ahead and build the project now by selecting the SQLCLRDemo project node in the Solution Explorer, and then selecting Build | Build SQLCLRDemo from the main menu. Equivalent UI selections (after selecting the project node) are the Build option from the project node’s shortcut menu or the Shift+F6 keyboard shortcut.

If you’re using the SQLCLRDemo sample code demo project, after your attempt to build the project you should see a message that the build failed, with the following error displayed in the Error List window:

SQL71501: Trigger: [Person].[trgUpdatePerson] has an unresolved reference to object [Perso
n].
[Person].

If, in the Error List window, you double-click the error, a code editor window will open with a T-SQL deployment script generated by SSDT. In it is a CREATE TRIGGER command for the trgUpdatePerson trigger and a red squiggly under the name of the table to which the trigger is being applied, Person.Person. The T-SQL is actually valid, so you may wonder why SSDT might flag it with an error.

SQL Server Database projects are meant to mirror fully the databases to which their contents will be deployed. Although you can create projects that contain just a subset of a database—for example, the assets for a SQL CLR assembly—SSDT will block you as soon as any T-SQL in the project references an object in the database that is not also in your project. In the case of your project, the deployment script references the table Person.Person (because the SqlTrigger attribute applied to the trgUpdatePerson CLR trigger references it) and yet there is no T-SQL script in your project that defines that table. Therefore, by SSDT’s criteria, the T-SQL is improper.

You could solve this by adding a script for the Person.Person table and then adding further scripts, in a piecemeal fashion, for any objects upon which the table is dependent. While doing so would allow the project to build, it would still be a work-around, and thus something you ought to avoid. The real solution is to import the AdventureWorks2012 database into the project; that way your project will reflect a full definition of the database. “Importing” the database really translates into having SSDT discover the database’s structure and create corresponding scripts for each object.

Let’s do the import now. As a preparatory step, close Visual Studio, open Windows Explorer and navigate to the source code directory containing the project file (not the solution file) and the project’s contents (e.g. C:\Demos\VS\SQLCLRDemo\SQLCLRDemo). Look for a file called SQLCLRDemo.dbmdl and delete it, then re-open the solution in Visual Studio. This preparatory step is necessary to enable the Import Database functionality that we need to use; if the step is not performed after a failed build, the Import Database option will be grayed-out.

Let’s now perform the import operation. Once again, select the SQLCLRDemo project node in the Solution Explorer, then right-click that node and select Import | Database from the shortcut menu (you can also select Project | Import | Database from the main menu). This selection will bring up the Import Database dialog box, which is shown in Figure 1.

Figure 1. The Import Database dialog box.

Select the AdventureWorks2012 database from the Source Database Connection combo box, or click the New Connection button, define a connection to the AdventureWorks2012 database on localhost, and click OK. Now click Start. The Summary screen of the import database dialog box will appear and the import process will proceed. When the Finish button becomes enabled, click it to finish the import process. Now build the project once more. This time the build should succeed, in which case you are ready to deploy your assembly.

2. Deploying Your Assembly

Having completed a successful build, you’re now ready to publish the database, and deploy the assembly in the process. Select Build | Publish SQLCLRDemo from the main menu now (or right-click the SQLCLRDemo project node in the Solution Explorer and select Publish from the shortcut menu); this will bring up the Publish Database dialog box, shown in Figure 2.

Figure 2. The Publish Database dialog box.

Click the Edit button next to the Target Database Connection read-only text box to bring up the standard Connection Properties dialog box. Configure the connection to point to your target server (e.g. localhost), to use Windows Authentication and to point to the AdventureWorks2012 database (make sure not to accept the default database name of SQLCLRDemo), then click OK. Back in the Publish Database dialog box, click the Publish button to deploy your CLR assembly to the database.

Note

Before clicking Publish, you can optionally click the Save Profile As button to save this publish profile. When you next publish the database, you can use the Load Profile button to retrieve the profile and avoid configuring the target database connection manually once again.

For deploying the Class Library project version, assuming C:\Demos is the sample code parent directory, you can execute the following T-SQL statement from within a query window:

CREATE ASSEMBLY SQLCLRDemo
 AUTHORIZATION dbo
 FROM 'C:\Demos\VS\SQLCLRDemoManual\SQLCLRDemo\bin\Debug\SQLCLRDemo.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 AdventureWorks2012, in this case the clause is unnecessary (which is why we omitted it from our previous example).

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. Regardless, it’s a good practice to include both clauses.

If your assembly has dependencies on other assemblies, SQL Server looks to see whether 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 or in the global assembly cache (GAC), 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 an assembly directly inline by providing a binary stream 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 could be fetched from a table in a database.

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

Figure 3. The SSMS 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 AUTHORIZATION and WITH PERMISSION_SET details in the Assembly Owner text box (using the ellipsis button, if necessary) and the Permission Set combo box, respectively.

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 that if your database is backed up and restored, or deployed, any assemblies within it move along with the data itself and need not be added manually as a subsequent step.