Share via

Deploying CLR Database Objects

  • Article

Deployment is the process by which you distribute a finished application or module to be installed and run on another computer. Using Microsoft Visual Studio, you can develop common language runtime (CLR) database objects and deploy them to a test server. Alternatively, the managed database objects can also be compiled with the Microsoft .NET Framework redistribution files, instead of Visual Studio. Once compiled, the assemblies containing the CLR database objects can then be deployed to a test server using Visual Studio or Transact-SQL statements. Note that Visual Studio .NET 2003 cannot be used for CLR integration programming or deployment. SQL Server includes the .NET Framework pre-installed, and Visual Studio .NET 2003 cannot use the .NET Framework 2.0 assemblies.

Once the CLR methods have been tested and verified on the test server, they can be distributed to production servers using a deployment script. The deployment script can be generated manually, or by using SQL Server Management Studio (see the procedure later in this topic).

The CLR integration feature is turned off by default in SQL Server and must be enabled in order to use CLR assemblies. For more information, see Enabling CLR Integration.

Deploying the Assembly to the Test Server

Using Visual Studio, you can develop CLR functions, procedures, triggers, user-defined types (UDTs), or user-defined aggregates (UDAs), and deploy them to a test server. These managed database objects can also be compiled with the command line compilers, such as csc.exe and vbc.exe, included with the .NET Framework redistribution files. The Visual Studio Integrated Development Environment is not required to develop managed database objects for SQL Server.

Make sure that all compiler errors and warnings are resolved. The assemblies containing the CLR routines can then be registered in a SQL Server database using Visual Studio or Transact-SQL statements.

Note

The TCP/IP network protocol must be enabled on the SQL Server instance in order to use Microsoft Visual Studio for remote development, debugging, and development. For more information about enabling TCP/IP protocol on the server, see Configure Client Protocols.

To deploy the assembly using Visual Studio

  1. Build the project by selecting Build <project name> from the Build menu.

  2. Resolve all build errors and warnings before deploying the assembly to the test server.

  3. Select Deploy from the Build menu. The assembly will then be registered in the SQL Server instance and database specified when the SQL Server project was first created in Visual Studio.

To deploy the assembly using Transact-SQL

  1. Compile the assembly from the source file using the command line compilers included with the .NET Framework.

  2. For Microsoft Visual C# source files:

  3. csc /target:library C:\helloworld.cs

  4. For Microsoft Visual Basic source files:

vbc /target:library C:\helloworld.vb

These commands launch the Visual C# or Visual Basic compiler using the /target option to specify building a library DLL.

  1. Resolve all build errors and warnings before deploying the assembly to the test server.

  2. Open SQL Server Management Studio on the test server. Create a new query, connected to a suitable test database (such as AdventureWorks).

  3. Create the assembly in the server by adding the following Transact-SQL to the query.

CREATE ASSEMBLY HelloWorld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE;

  1. The procedure, function, aggregate, user-defined type, or trigger must then be created in the instance of SQL Server. If the HelloWorld assembly contains a method named HelloWorld in the Procedures class, the following Transact-SQL can be added to the query to create a procedure called hello in SQL Server.

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorld.Procedures.HelloWorld

For more information about creating the different types of managed database objects in SQL Server, see CLR User-Defined Functions, CLR User-Defined Aggregates, CLR User-Defined Types, CLR Stored Procedures, and CLR Triggers.

Deploying the Assembly to Production Servers

Once the CLR database objects have been tested and verified on the test server, they can be distributed to production servers. For more information about debugging managed database objects, see Debugging CLR Database Objects.

The deployment of managed database objects is similar to that of regular database objects (tables, Transact-SQL routines, and so on). The assemblies containing the CLR database objects can be deployed to other servers using a deployment script. The deployment script can be built by using the "Generate Scripts" functionality of Management Studio. The deployment script can also be built manually, or built using "Generate Scripts" and manually altered. Once the deployment script has been built, it can be run on other instances of SQL Server to deploy the managed database objects.

To generate a deployment script using generate scripts

  1. Open Management Studio and connect to the SQL Server instance where the managed assembly or database object to be deployed is registered.

  2. In the Object Explorer, expand the <server name> and Databases trees. Right-click the database where the managed database object is registered, select Tasks, and then select Generate Scripts. The Script Wizard opens.

  3. Select the database from the list box and click Next.

  4. In the Choose Script Options pane, click Next, or change the options and then click Next.

  5. In the Choose Object Types pane, choose the type of database object to be deployed. Click Next.

  6. For every object type selected in the Choose Object Types pane, a Choose <type> pane is presented. In this pane, you can choose from all the instances of that database object type registered in the specified database. Select one or more objects and click Next.

  7. The Output Options pane comes up when all of the desired database object types have been selected. Select Script to file and specify a file path for the script. Select Next. Review your selections and click Finish. The deployment script is saved to the specified file path.

Post Deployment Scripts

You can run a post deployment script.

To add a post deployment script, add a file called postdeployscript.sql in your Visual Studio project directory. For example, right click your project in Solution Explorer and select Add Existing Item. Add the file in the root of the project, rather than in the Test Scripts folder.

When you click deploy, Visual Studio will run this script after the deployment of your project.

See Also

Common Language Runtime (CLR) Integration Programming Concepts