Jaa

Deploying CLR Database Objects

  • Artikkeli

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 2005, 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 2005. Once compiled, the assemblies containing the CLR database objects can then be deployed to a test server using Visual Studio 2005 or Transact-SQL statements. Note that Visual Studio .NET 2003 cannot be used for CLR integration programming or deployment. SQL Server 2005 includes the .NET Framework 2.0 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 2005 and must be enabled in order to use CLR assemblies. For more information, see Enabling CLR Integration.

Note

On a SQL Server 2005 database with a compatibility level of "80," you cannot create managed user-defined types, stored procedures, functions, aggregates, or triggers. To take advantage of these CLR integration features of SQL Server 2005, you must use the sp_dbcmptlevel (Transact-SQL) stored procedure to set the database compatibility level to "90.".

Deploying the Assembly to the Test Server

Using Visual Studio 2005, 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 2005 Integrated Development Environment is not required to develop managed database objects for SQL Server 2005.

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

Note

The TCP/IP network protocol must be enabled on the SQL Server 2005 instance in order to use Microsoft Visual Studio 2005 for remote development, debugging, and development. For more information about enabling TCP/IP protocol on the server, see Configuring Client Network 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 2005 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 2005.

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 2005 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.

See Also

Other Resources

Database Engine .NET Framework Programming

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
Changed content:
  • Added note about enabling TCP/IP connections on the server for remote deployment.
  • Added note about database compatibility level.
  • Added note about Visual Studio 2003 and .NET Framework 2.0.